A fix for "Incorrect key file for table '/tmp/#sql_xxxx_x.MYI'" in MySQL
By Stephen Jayna, 8th March 2011
The Most Likely Cause: You're Out Of Space
Chances are, that if you're seeing this error, you've run out of space in /tmp or wherever tmpdir points to. If only for the time it takes MySQL to create a temporary file (filling the parition) and delete it in disgust.
In Ever So Slightly More Detail
DBD::mysql::st execute failed: Incorrect key file for table '/tmp/#sql_4091_0.MYI'; try to repair it [for Statement "...
The idea of it being suggested that one needs to run a repair on a temporary table should raise an eyebrow. It just doesn't make much sense: it suggests it's somehow corrupted, but since it's transitory and has just been freshly created by MySQL, it should never be needed. Not to mention tricky to implement.
In my experience, limited though it is, the primary reason for seeing this error message is because your tmpdir has run out of space. Like me you'll check how much free space you have: 1Gb, 2Gb, 4Gb. It may not be enough. And here's why: MySQL can create temporary tables bigger than that in a matter of seconds, quickly filling up any free space. Depending on the nature of the query and the size of the database naturally.
Take this evening for example: I had a temporary table created that was around 3.6Gb in size, from a database that's only 5.4Gb in total, and I had only had 1.8Gb of free space. Imagine a couple of queries of that nature running in parallel.
The moment the space has been filled, the above error will be thrown, the temporary file on disk will be deleted, and you'll be none the wiser.
Conclusion
Perhaps the MySQL instance could be better configured - but that's not the point - the error message is oblique at best. Make sure you've got enough temporary space, you may need more than you imagine.
Your Comments
You saved my DAY! Thanks a
You saved my DAY! Thanks a lot
You are very right
What you say is absolutely right.
The tmp dir size needs to be at least twice the size of the biggest table you have .
CHeck the mysql link below for more info
http://dev.mysql.com/doc/refman/5.1/en/temporary-files.html
ALso note that mysql does mention:
To spread the load effectively, these paths should be located on different physical disks, not different partitions of the same disk.
You saved me!
I was getting crazy. After saving a wrong view that created billions of row, the server became extremely slow. We had to kill mysql, but nothing changed.
After reading your post I went in /tmp and I found 50 giga of tmp tables.
Every time the server started, user of the site began querying the db, and soon everything became slow because of mysqld writing giga and giga on /tmp, and we can't modify the query because it was nearly impossible connect, but also give commands to the shell.
The solution I found was to stop mysql, change port enter as the only user, update the view and reset the port.
This worked, but I think it's incredible that saving a wrong query can block an entire server, in a way that is very difficult restore from.
Post new comment