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
TMPDIR environment variable as the path, or else a system default. You can also set the directory using the --tmpdir option of the mysqld server executable.I guess that in my case, MySQL was using the system default, because I do not have a
TMPDIR environment variable (I've got a TEMP and a TMP though, neither of which was used). Apparently, C:\WINDOWS\TEMP is the system default on windows.I decided that it would be best to set the directory using the
--tmpdir option, because I can add it as an entry in the my.ini file, which I can reuse whenever I upgrade the server software. So, I went right ahead, and added this entry to my.ini:
tmpdir=D:\MySQL\temp
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:
tmpdir=D:/MySQL/temp
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.
4 comments:
Just a quick note - after some more experimentation, I think that maybe cleaning up temporary files using the windows wizard is not what's responsible for the first error I'm describing in the entry.
It's probably because my disk space ran out for real. That just happened to coincide with me running the wizerd.
I believe it's disk space running out because I can provoke the same situation on the D: disk, without cleaning up temp files when I'm running an even insaner query that makes disk space run out there too.
Leading and trailing blanks are automatically deleted from option names and values. You may use the escape sequences ‘\b’, ‘\t’, ‘\n’, ‘\r’, ‘\\’, and ‘\s’ in option values to represent the backspace, tab, newline, carriage return, backslash, and space characters.
Because the ‘\\’ escape sequence represents a single backslash, you must write each ‘\’ as ‘\\’. Alternatively, you can specify the value using ‘/’ rather than ‘\’ as the pathname separator.
From:
http://dev.mysql.com/doc/refman/5.1/en/option-files.html
It should also work with backslashes if you use doublequotes.
tmpdir = "C:\temp"
cheers,
Tobias,
thanks for the comment and the pointer! Much appreciated. I would've expected that info in the "Editing the my.ini" file, but apparently I was wrong.
Thanks again for the pointer!
I Ran Out Of Megabytes On My Diskspace Ive Deleted As much as i Can If I delete Anymore ill have to use my Use
Post a Comment