MySQL incorrect key file for tmp table when making multiple joins

Solution 1:

Sometimes when this error happens with temp tables:

#126 - Incorrect key file for table '/tmp/#sql_64d_0.MYI'; try to repair it

It can be because the /tmp folder is running out of space. On some Linux installations, /tmp is in its own partition and does not have much space - big MySQL queries will fill it up.

You can use df -h to check whether \tmp is in its own partition, and how much space is allocated to it.

If it is in its own partition and short of space, you can either:

(a) modify /tmp so that its parition has more space (either by reallocating or moving it to the main partition - e.g. see here)
(b) changing MySql config so that it uses a different temp folder on a different partition, e.g. /var/tmp

Solution 2:

Check your MySQL tmpdir available space (/tmp in your case) while running the queries as it can eat hundreds of MBs when working with big tables. Something like this worked for me:

$ while true; do df -h /tmp; sleep .5; done

Solution 3:

run this

REPAIR TABLE `core_username`,`core_site`,`core_person`;

or do this:

select * from (
 SELECT * FROM `core_username`
 INNER JOIN `core_person` ON (`core_username`.`person_id` = `core_person`.`id`)
 INNER JOIN `core_site` ON (`core_username`.`site_id` = `core_site`.`id`)
 LIMIT 1)
ORDER BY `name` ASC

Solution 4:

You may find running "ANALYZE TABLE " helps.

We had this problem suddenly appear on a large table (~100M rows) and MySQL tried to use /tmp to write a temporary table of over 1GB, which failed as /tmp was limited to ~600M.

It turned out that the statistics for the InnoDB table were rather stale. After running "ANALYZE TABLE ...", the statistics were updated and the problem cleared. With the more accurate statistics, MySQL was able to optimize the query correctly and the large tmp file was no longer required.

We now run "mysqlcheck -Aa" periodically to keep all table statistics fresh.

Solution 5:

I had this problem with a query on a table that had 500K+ records. It was giving me the same exact type of error, pointing to a .MYI file in the /tmp directory that was rarely there upon checking. I had already increased the heap and temp file sizes in the /etc/my.cnf file.

The problem with the query was the it did indeed contain a ORDER clause at the end, omitting it made the query work without error. It also had a LIMIT. I was trying to look at the most recent 5 records in the table. With the ORDER clause included it choked and gave the error.

What was happening was the mysqld was creating an internal temp table with ALL the records from the giant table to apply the ORDER.

The way that I got around this is to apply an additional WHERE condition, limiting the records from the giant table to some smaller set. I conveniently had a datetime field to do the filtering from.

I hope that helps someone.