MySQL "Incorrect key file for table /tmp/#sql_xxx_x.MYI" error
Posted June 1st, 2012 in MySql
I've written a post in the past about dealing with the "Incorrect key file for table" in MySQL for regular database tables, but there are instances when you might get the error for a temporary table and see something like this: "Incorrect key file for table '/tmp/#sql_xxx_x.MYI'; try to repair it"
It's a temporary table, so you can't repair it
The solution would normally be to run "REPAIR TABLE tablename" but in this case you can't repair it because it's a temporary file, and if you look for it you won't find it.
You've run out of disk space
The reason for an error like this is because there isn't enough disk space left on the partition where the temporary file would be written. Presumably MySQL has started to write data out to the file but then the disk space runs out, and that's when it errors out.
The simple solution is to check your disk space, and make sure there is enough free space by freeing some up.
In my case today, I discovered there was only about 100MB of space left on a fairly busy server, so I needed to compress and/or delete some old log files and backup files.
- How to show a MySQL warning that just happened (Tuesday, December 1st 2015)
- MySQL error can't open file - errno: 24 (Tuesday, March 3rd 2015)
- Cannot load from mysql.proc. The table is probably corrupted (Wednesday, February 22nd 2012)
- MySQL table is marked as crashed and should be repaired (Tuesday, August 2nd 2011)
- MySQL "Incorrect key file for table" error (Wednesday, June 24th 2009)