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

the index keys for one of the 3 tables might be bad, try running a repair command on all 3 tables.

Answer 2

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

Answer 3

Using the EXPLAIN keyword may help find out how to best optimize this query. Essentially, what you need to do is get the result set as small as possible as quickly as possible. If you have a result set of every row in core_username until the end, when you order it you run the risk of... this.

If you can do the ordering on core_username alone without a problem, you may want to get the min() row as a subquery.

Answer 4

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

Answer 5

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