MySQL incorrect key file for tmp table when making multiple joins

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...

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

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

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

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.

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.

On Unix, MySQL uses the value of the TMPDIR environment variable as the path name of the directory in which to store temporary files. If TMPDIR is not set, MySQL uses the system default, which is usually /tmp, /var/tmp, or /usr/tmp.

On Windows, Netware and OS2, MySQL checks in order the values of the TMPDIR, TEMP, and TMP environment variables. For the first one found to be set, MySQL uses it and does not check those remaining. If none of TMPDIR, TEMP, or TMP are set, MySQL uses the Windows system default, which is usually C:\windows\temp.

If the file system containing your temporary file directory is too small, you can use the --tmpdir option to mysqld to specify a directory in a file system where you have enough space.

In MySQL 5.0, the --tmpdir option can be set to a list of several paths that are used in round-robin fashion. Paths should be separated by colon characters (“:”) on Unix and semicolon characters (“;”) on Windows, NetWare, and OS/2.

I experience the same problem.

Here is my solution :

1. Dont use "select *". Just select field that you need.
2. Split the query. If the field you select is too much, splitting it to some query can be a result. You can "array_merge()" the result later if you want the variable that contain the result not changed.

On my case, I split the query to 5 queries, then array merge it using PHP.

The problem is lies on the mysql server. It is just a thing that application developer (such us me) don't has a previlege.

I had similar problem. In my own case, the problem occurred due to incorrect owner/permission. I just had to change the owner on my data directory to mysql user and this resolved the problem.

Only increase the file tmp, because mysql doesn't have space in it, for queries...

mount -o remount,size=[NEW MAX SIZE HERE] tmpfs /tmp

Links reference:

General error: 126 Incorrect key file for table ‘/tmp/#sql_254c_0.MYI’; try to repair it

[ERROR] /usr/sbin/mysqld: Incorrect key file for table '/mysqltmp/#sql_ca1a_0.MYI'; try to repair it

How to increase /tmp partition size

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

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.