MySQL incorrect key file for tmp table when making multiple joins

Question

I don't come here for help often but I am pretty frustrated by this and I am hoping someone has encountered it before.

Whenever I try to fetch records from a table using more than one join I get this error:

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

So this query will produce the error:

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`)
ORDER BY `core_username`.`name` ASC LIMIT 1

But this one won't:

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

And neither will this one:

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

What could be causing this? I don't really know how to go about repairing a tmp table but I don't really think that's the problem as it is a new tmp table every time. The username table is fairly large (233,718 records right now) but I doubt that has anything to do with it.

Any help would be much appreciated.

UPDATE: After some further testing, it appears that the error only happens when I try to order the results. That is, this query will give me what I expect:

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

But if I add the:

ORDER BY `core_username`.`name` ASC

The error is triggered. This is only happening on the specific webserver I am currently using. If I download the database and try the same thing on my localhost as well as other servers it runs fine. The MySQL version is 5.0.77.

Knowing this I am fairly confident that what is happening is that the tmp table being created is way too big and MySQL chokes as described in this blog post. I am still not sure what the solution would be, though...

Answer 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

Answer 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