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.