MySQL: my.ini Gotcha on Windows

Oct 14, 2008 / By Augusto Bott

Tags:

The other day we began to encounter weird and random errors on small and innocent queries that shouldn’t give any errors at all. It all lead to one of our most basic health checks failing for no apparent reason.

The first clue that popped into our minds was related to case-sensitivity, since the failing check was looking for the column names of the only table in the schema with UPPER CASE name. This symptom was especially weird since all of this was happening on MySQL setups running on Windows, and so we’re not sure if this was being caused by some internal code library, MySQL, or Windows itself.

Since that was the only clue we then had, it seemed obvious that we should start fiddling with the lower_case_table_names system variable. After a couple of restarts, this approach was leading us nowhere, so we finally gave up on it.

Then, we had the brilliant idea of actually executing that statement by hand on the command line to see what happened:

C:pythian>mysql -uXXXX -p XXXX -e "desc TABLE_NAME"
Enter password: *****
ERROR 1 (HY000) at line 1: Can't create/write to file 'C:MySQL      mp#sql_634_0.MYI' (Errcode: 22)

C:pythian>perror 22
OS error code  22:  Invalid argument

C:pythian>

None of us remembered seeing this one before, so we stared at the monitor for a few moments, not realizing the meaning of this message. I guess our focus on the case-sensitivity was driving us away a from the real cause. So . . .  time to check the my.ini file. Inside it, we found:

tmpdir=C:MySQLtmp

This line belongs to a recent configuration change in which the tmpdir has been relocated on some instances. Apparently, we missed a small detail earlier: t actually gets translated into a tab character when MySQL starts.

We need to change the escape sequence so we get a slash instead of tab. One of the ways we can accomplish this is by changing the line above to:

tmpdir=C:\MySQL\tmp

After that configuration update and a bounce on the DB (tmpdir is a read-only system variable), we did the same test:

C:pythian>mysql -uXXXX -p XXXX -e "desc TABLE_NAME"
Enter password: *****
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| col1    | varchar(30) | YES  |     | NULL    |       |
| col2    | int(11)     | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

C:pythianavail>

And there we go. Note that t is interpreted as the tab character across OS platforms. But, it’s only on MS Windows that it might appear as a filesystem path.

Cheers!

Useful links:
http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html
http://dev.mysql.com/doc/refman/5.0/en/string-syntax.html

10 Responses to “MySQL: my.ini Gotcha on Windows”

  • Jeremy Cole says:

    Hi Augusto,

    You’ve chosen the wrong solution with “tmpdir=C:MySQL\tmp” … you need to either escape ALL to \ or replace them with /. For simplicity sake, on Windows, I would usually use:

    tmpdir=c:/MySQL/tmp/

    Your configuration line only works because M isn’t a valid escape sequence, and fundamentally misunderstands the concept of escape sequences to begin with.

    Regards,

    Jeremy

  • Rob Wultsch says:

    In my opinion ‘\\’ is ugly. Forward slashes are the only game in town on *nix, and on Windows they work quite nicely. I suggest:

    “C:/MySQL/tmp/”

    in the future. Isn’t it prettier?

  • Augusto Bott says:

    Hi Jeremy, Rob!

    There is a reason/purpose for choosing ‘\’ as a path separator: unfortunately not all applications work well with ‘/’ as a path separator (yes, there are some still around). Since most of them can actually fetch those strings from the DB make no conversion/checking whatsoever. For this particular setup, picking ‘\’ was a less risky approach, having a better chance of fixing things instead of breaking other pieces of software connected to those servers :-)

    Cheers!

  • Rob Wultsch says:

    Ok, I’m curious what programs don’t support forward slashes on windows. All the *nix programs I’m aware of deal with it well.

  • Augusto Bott says:

    Hi Rob!

    That’s the thing: we don’t know all the apps using these particular database servers (or which OS those apps run on). That said, we can’t predict safely how they would behave with regular/forward slashes on the paths.

    Cheers!

  • Rob Wultsch says:

    The MySQL installer writes the my.ini with backslashes, and the manual shows the backslash as a separator (ex http://dev.mysql.com/doc/refman/5.1/en/windows-create-option-file.html). Anything that does not respect this syntax needs to fail *hard*.

    The more conservative option is to use backslashes as it is what the is default anyways, and is less error prone. As Jeremy Cole pointed out there is an error in your example that is just waiting to blow up.

    Mods:Forgive me if I post this twice, please delete one if that is the case.

  • Rob Wultsch says:

    Meh reversed forward and back slashes in last entry…

  • Augusto Bott says:

    Thanks for the feedback, Rob/Jeremy! The post has been corrected.
    Cheers!

  • Alex says:

    Nice… but where in the heck is the my.ini located in Windows ?!? Or is it my.cnf ?

  • Alex says:

    There it is… %WINDIR%\my.ini
    http://dev.mysql.com/doc/refman/5.5/en/option-files.html

    and other default places that MySql checks.

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>