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

@Paolo - worth checking ... not that I have any more ideas but, what MySQL version? — martin clayton, Jan 19 '10 at 00:53
Interesting read: http://forums.mysql.com/read.php?21,297810,297812#msg-297812 — OMG Ponies, Jan 19 '10 at 02:29
Does this query produce the same error: `SELECT * FROM CORE_USERNAME u JOIN CORE_PERSON p ON p.id = u.person_id JOIN CORE_SITE s ON s.id = u.site_id WHERE u.name = (SELECT MIN(u.name) FROM CORE_USERNAME u)` — OMG Ponies, Jan 19 '10 at 02:33
Well he says the query is incorrect but I just backed up the database and tried the first query in 3 other servers and it worked fine in all of them, so this is specific to the server I am on apparently. The MySQL version is 5.0.77 — Paolo Bergantino, Jan 19 '10 at 02:34
@Paolo - probably bogus and I know way to little of MySQL but if the problem is that MySQL uses a temp table to do the sorting, would an index on core_username.name do any good? — Lieven Keersmaekers, Jan 27 '10 at 16:17
@Paolo, have you tried dropping and re-adding the index for core_username.name? — AJ., Jan 27 '10 at 17:01
This is usually caused by a query that creates a massive tmp table and fills up /tmp. It may work on other servers that have larger tmp folders. — Slashterix, Jan 29 '10 at 06:37
I'm getting this error when querying large no of records (over 2 million) from `MySQL` using `Spark-SQL` (with [parallelized read](https://spark.apache.org/docs/2.0.0/api/R/read.jdbc.html)). My query in question is *simple* `SELECT..FROM` one (it doesn't even have a `WHERE` clause, let alone `JOIN` or `ORDER BY`). Interestingly, this issue comes up only when I do not `LIMIT` the query to under **~1.5/2 million records**, otherwise things play nicely. I'm on `Spark 2.2.0` — y2k-shubham, Mar 01 '18 at 09:06
In my case (most likely) it's not `MySQL`'s fault because copying the same table using [`sqoop`](http://sqoop.apache.org/) works just fine. So either its `Spark` who is playing the bad guy or its the *interface* between `Spark` and `MySQL` (aka `JDBC`) — y2k-shubham, Mar 01 '18 at 09:14

score 106 · Accepted Answer · answered Sep 15 '10 at 10:34

106

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

answered Sep 15 '10 at 10:34

codeulike

22,514
29
120
167

1

My df -h result looks as follows: Filesystem Size Used Avail Use% Mounted on /dev/sda 2.0G 1.3G 646M 67% / udev 490M 4.0K 490M 1% /dev tmpfs 200M 232K 199M 1% /run none 5.0M 0 5.0M 0% /run/lock none 498M 0 498M 0% /run/shm overflow 1.0M 0 1.0M 0% /tmp So I'm unsure if that means /tmp is in the 1MB overflow or not. Either way changing my.cnf to use /var/tmp as the folder fixed this issue for me. How well will MySQL or Ubuntu handle clearing this, as I've had disk space issues on my current server? – M1ke Apr 02 '13 at 14:18
I just needed to clean up hard drive space on my primary partition and this error went away. Thanks for the tip guys. – fregas May 03 '13 at 15:16
You can use `while true; do df -h /tmp; sleep 3; done` while running the script to see how much space is used by temporary tables. – biniam Nov 05 '15 at 15:00

score 22 · Answer 2 · answered Mar 04 '10 at 11:01

22

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

answered Mar 04 '10 at 11:01

Francesc Rosàs

221
1
3

5

Thanks, this was what was happening to me. You could also do `watch -n0.5 df -h /tmp` – Sam Jan 21 '13 at 11:04
1

This did the trick - watched a few gig get eaten up by a hungry query. – Laizer Aug 29 '13 at 02:15
1

Yep lack of disk space was the reason for this error message. – malhal Aug 27 '14 at 20:46

score 6 · Answer 3 · answered Jan 28 '10 at 13:36

6

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

answered Jan 28 '10 at 13:36

Pentium10

204,586
122
423
502

1

This doesn't work on temp tables (which is what the OP is having a problem with). – Mark E. Haase Apr 11 '11 at 16:27

score 4 · Answer 4 · edited Jan 09 '14 at 09:43

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.

score 3 · Answer 5 · answered Dec 12 '13 at 00:29

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.

score 2 · Answer 6 · answered Mar 27 '12 at 18:22

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.

score 1 · Answer 7 · answered Aug 09 '12 at 05:33

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.

score 1 · Answer 8 · answered Jul 08 '16 at 23:18

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

score 0 · Answer 9 · answered Sep 02 '14 at 13:27

0

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.

answered Sep 02 '14 at 13:27

Lateef

1

score -1 · Answer 10 · answered Jan 28 '10 at 13:31

-1

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

answered Jan 28 '10 at 13:31

Gabriel Solomon

29,065
15
57
79

score -1 · Answer 11 · answered Jan 29 '10 at 17:42

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.

MySQL incorrect key file for tmp table when making multiple joins

11 Answers11

Linked

Related