Invalid MySQL key file for tmp table when creating multiple connections

Question

I don’t often come here for help, but I’m very upset about this, and I hope someone has come across it before.

Whenever I try to retrieve 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 

Thus, this query will result in an 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 will not be:

 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 this will not be:

 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 the reason for this? I really don't know how to repair a tmp table, but I really don't think the problem is that it is a new tmp table every time. The username table is quite large (233,718 entries right now), but I doubt this has anything to do with it.

Any help would be greatly appreciated.

UPDATE After some additional testing, it seems that the error only occurs when trying to streamline 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:

 ORDER BY `core_username`.`name` ASC 

The error is triggered. This only happens on the specific web server that I am currently using. If I load the database and try the same on my localhost as well as on other servers, it works fine. MySQL Version 5.0.77.

Knowing this, I'm pretty sure that the tmp table being created is too large, and MySQL is throttling as described in this blog post . I'm still not sure what the solution will be, though ...

Answer 1

Sometimes, when this error occurs with temporary tables:

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

This may be due to lack of space in the /tmp folder. On some installations, Linux /tmp is in its own partition and does not have much space - large MySQL queries will populate it.

You can use df -h to check if \tmp in your own partition and how much space is allocated to it.

If it is in its own section and there is not enough space, you can:

(a) modify / tmp so that its palette has more space (either by redistributing it or moving it to the main section - for example, see here )
(b) changing the MySql configuration so that it uses a different temp folder on a different partition, for example. /var/tmp

Answer 2

Check the available MySQL tmpdir space (/ tmp in your case) during query execution, as it can eat hundreds of MB when working with large tables. Something like this worked for me:

 $ while true; do df -h /tmp; sleep .5; done 

Answer 3

run it

 REPAIR TABLE `core_username`,`core_site`,`core_person`; 

or do the following:

 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 4

You may find that ANALYZE TABLE works.

We encountered this problem, which suddenly appeared on a large table (~ 100M rows), and MySQL tried to use / tmp to write a temporary table larger than 1 GB, which failed because / tmp was limited to ~ 600M.

It turned out that the InnoDB table statistics were pretty outdated. After running "ANALYZE TABLE ..." the statistics were updated and the problem was fixed. With more accurate statistics, MySQL was able to optimize the query correctly, and a large tmp file is no longer required.

Now we run "mysqlcheck -Aa" to save all the table statistics.

Answer 5

I had this problem with a query in a table with 500K + records. This gave me the exact exact type of error pointing to the .MYI file in the / tmp directory, which was rarely present during the scan. I have already increased the size of the heap and temporary files in the /etc/my.cnf file.

The problem with the request was that it really contained the ORDER clause at the end, omitting it, it launched the request without errors. He also had a LIMIT. I tried to see the last 5 entries in a table. With the inclusion of the ORDER clause, he suffocated and gave an error.

What happens, mysqld created an internal temporary table with all the records from the giant table to apply ORDER.

The way I got around is to apply the extra WHERE clause, restricting the entries from the giant table to some smaller set. I find it convenient to have a datetime field for filtering.

I hope this helps someone.

Answer 6

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

On Windows, Netware, and OS2, MySQL checks the values ​​of the environment variables TMPDIR, TEMP, and TMP. For the first set found, MySQL uses it and does not check the remaining ones. If none of TMPDIR, TEMP, or TMP is installed, MySQL uses the Windows system standard, which is usually C: \ windows \ temp.

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

In MySQL 5.0, the -tmpdir parameter can be set to a list of several paths that are used in circular mode. Paths must be separated by colons (":") on Unix and semicolons (";") on Windows, NetWare, and OS / 2.

Answer 7

I have the same problem.

Here is my solution: 1. Do not use "select *". Just select the field you need. 2. Separate the request. If the field you select is too large, the result may be splitting into some query. You can "array_merge ()" the result later if you want the variable containing the result not to be changed.

In my case, I split the request into 5 requests, then the array combined it using PHP.

The problem lies with the mysql server. It's just that an application developer (like me, me) is not an exaggeration.

Answer 8

I had a similar problem. In my case, the problem was due to the wrong owner / permission. I just had to change the owner in my data directory to mysql user, and this solved the problem.

Answer 9

Only enlarge the tmp file, because mysql does not have a place in it, for queries ...

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

Link Link:

General error: 126 Invalid key file for table '/tmp/#sql_254c_0.MYI; try to repair it

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

How to increase tmp partition size

Answer 10

index keys for one of the three tables may be bad; try running the restore command on all three tables.

Answer 11

Using the EXPLAIN keyword can help you figure out how best to optimize this query. Essentially, you need to set the result set as small as possible. If you have a result set from each row in the core_username name to the end, when you order it, you risk ... this.

If you can only order by the name core_username without any problems, you might want to get the string min () as a subquery.