I nearly ran out of diskspace on the C: disk of my notebook (running windows there). You now how it goes: I knew I did not have too much space left, but that little...It just couldn't be true. So, I ran a little Windows Wizard to clean up temporary files and such, and the next thing that happened:
ERROR 126 (HY000): Incorrect key file for table 'C:\WINDOWS\TEMP\#sql_474_0.MYI'; try to repair it
Whoamm! The mysql client utility quit running my query. Seems like a temporary table got cleaned up too...
(I'm running an insane query for experimentation purposes).
The good news was that my disk space problems were indeed instantly solved.
Of course, I'm a little bit surprised that it would be possible for windows to throw away the files associated with the temporary table. I would expect them to be locked by the MySQL server so that it would be impossible for windows to delete it.
Anyway, I decided that maybe I should not be so lazy and put some effort in not running into this kind of trouble so easily. I've got lots (...well, it's never enough, right?) of space left on my D: disk, so I looked in the manual to look for some hint on how to specify the location for the MySQL temporary files.
Well, that was easy enough: http://dev.mysql.com/doc/refman/5.1/en/temporary-files.html tells you that MySQL will use the value of the
TMPDIRenvironment variable as the path, or else a system default. You can also set the directory using the
--tmpdiroption of the
I guess that in my case, MySQL was using the system default, because I do not have a
TMPDIRenvironment variable (I've got a
TMPthough, neither of which was used). Apparently,
C:\WINDOWS\TEMPis the system default on windows.
I decided that it would be best to set the directory using the
--tmpdiroption, because I can add it as an entry in the
my.inifile, which I can reuse whenever I upgrade the server software. So, I went right ahead, and added this entry to
I restarted the server, and re-ran the query. Almost instantly, it went:
ERROR 1 (HY000): Can't create/write to file 'D:\MySQL emp\#sql_648_0.MYI' (Errcode: 22)
LOL! It seems that the \t sequence in the path is interpreted as the horizontal tab character (ascii 0x09). I looked it up in the manual in http://dev.mysql.com/doc/refman/5.1/en/mysql-config-wizard-editing.html, but I could not find a note documenting this behaviour. Well, it's always worth trying to use forward slashes:
Yes! Now I can run the query. You can probably also escape the \ using \\ but I'm not going to try it right now as I like the forward slashes better.