MySQL incorrect key file for tmp table when making multiple joins

Paolo_Bergantino_16417 0
Viewed 102772

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

mysql-error-126 mysql sql
11 Answers

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
user_1373730

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
user_2437820

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.

user_12963250

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

user_655030

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.

Jon_Eisenstein_837540
Related