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

Question

I wrote a query and this runs on my local server correctly it has less data,

but when i run this on production server it gets an error - (this has more data around 6GB)

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

Here is my query

SELECT   
    `j25_virtuemart_products`.`virtuemart_product_id`,
    `product_name`, 
    `product_unit`,
    `product_s_desc`,
    `file_url_thumb`,
    `virtuemart_custom_id`, 
    `custom_value`   
    FROM 
    `j25_virtuemart_product_customfields`,
    `j25_virtuemart_products`,
    `j25_virtuemart_products_en_gb`,
    `j25_virtuemart_product_medias`,
    `j25_virtuemart_medias`     
    WHERE
    (
    `j25_virtuemart_products`.`virtuemart_product_id`=`j25_virtuemart_products_en_gb`.`virtuemart_product_id`
    AND 
    `j25_virtuemart_products`.`virtuemart_product_id`=`j25_virtuemart_product_customfields`.`virtuemart_product_id`)

AND

    `j25_virtuemart_products`.`virtuemart_product_id`=`j25_virtuemart_product_medias`.`virtuemart_product_id`
    AND 
    `j25_virtuemart_product_medias`.`virtuemart_media_id`=`j25_virtuemart_medias`.`virtuemart_media_id`

    GROUP BY `j25_virtuemart_products`.`virtuemart_product_id`

    LIMIT 0, 1000;

Anyone know how to recover from that error - something like otimize this query or any other way thank you

Mircea Vutcovici · Answer 1 · 2021-03-27T16:15:01.703

58

The problem is caused by the lack of disk space in /tmp folder. The /tmp volume is used in queries that require to create temporary tables. These temporary tables are in MyISAM format even if the query is using only tables with InnoDB.

Here are some solutions:

optimize the query so it will not create temporary tables (rewrite the query, split it in multiple queries, or add proper indexes, analyze the execution plan with pt-query-digest and EXPLAIN <query>) See this Percona article about temporary tables.
optimize MySQL so it will not create temporary tables (sort_buffer_size, join_buffer_size). See: https://dba.stackexchange.com/questions/53201/mysql-creates-temporary-tables-on-disk-how-do-i-stop-it
make tables smaller. If possible, delete unneeded rows
use SELECT table1.col1, table2,col1 ... instead of select * to use only the columns that you need in the query, to generate smaller temp tables
use data types that take less space
add more disk space on the volume where /tmp folder resides
change the temp folder user by mysql by setting the TMPDIR environment variable prior to mysqld start-up. Point TMPDIR to a folder on a disk volume that has more free space. You can also use tmpdir option in /etc/my.cnf or --tmpdir in the command line of the mysqld service. See: B.5.3.5 Where MySQL Stores Temporary Files

edited Mar 27 '21 at 16:15

answered Oct 23 '13 at 20:44

Mircea Vutcovici

1,894
19
27

2

In our case it was the root (/) folder because our virtual machine has everything in one partition, and the /var/logs/ directory had filled up. Kudos on the space suggestion though! – oucil Jun 30 '15 at 17:11
This is the right answer. For innodb especially, since repair tables is not supported. Thank you! – lucian303 Nov 17 '15 at 19:20
This should be the accepted answer. As stated above, especially for InnoDB tables. I moved my tmp directory for a volume that had 12GB available to one that had 1.3TB available and my query ran no problem after generating a tmp file 19GB in size. – Ode Jan 28 '16 at 19:59
wow thanks, how we can improve those message? – Adi Prasetyo Jun 26 '16 at 22:15
Just wondering the first potential solution: 'optimize the query so it will not create temporary tables (rewrite the query, split it in multiple queries, or add proper indexes);, how can we do this? – wawawa Jan 02 '20 at 14:57
I updated my answer with a link to a Percona article that is explains this well. Sometimes it's not possible to avoid, like when using BLOB fields in your query, or sorting. – Mircea Vutcovici Jan 02 '20 at 15:13

Hawili · Accepted Answer · 2013-10-09T14:49:50.950

10

Do these steps

Stop mysql service

rename the .myi file to x.old

Start mysql

REPAIR all the tables in query ,MySQL will rebuild key file

edited Oct 09 '13 at 14:49

answered Aug 04 '12 at 04:05

Hawili

1,649
11
15

no mate, site is working corretly http://tsiopelakos.dev-centiva.com/index.php?option=com_virtuemart&view=category&virtuemart_category_id=2657&Itemid=134 but this only getting when the above query running you have a idea about optimize this query :) – Suneth Kalhara Aug 04 '12 at 04:10
1

one of these tables have a bad key file `j25_virtuemart_product_customfields`, `j25_virtuemart_products`, `j25_virtuemart_products_en_gb`, `j25_virtuemart_product_medias`, `j25_virtuemart_medias` , you have to repair it then the query should work – Hawili Aug 04 '12 at 04:13
thanks man it works nicely, i repaired above tables ex- REPAIR TABLE cent_virtuemart_products_en_gb now it working nicely :) great... – Suneth Kalhara Aug 04 '12 at 04:34
1

I'm not sure this is good advice. The .myi is in /tmp, meaning it's created dynamically during the query, so it'll likely be gone after the server stops, leaving nothing to rename or repair. Repairing all tables will likely not resolve it either. – Cerin Oct 17 '13 at 00:35
I only needed to run the repair command. I didn't need to rename the file. – Rhyous Mar 05 '14 at 18:19
I'd like to note that this happens on Windows too, and the answer is to simply clear out C:\Windows\Temp. Repair doesn't seem necessary, but that might depend on the extent of the corruption. – Renee Aug 10 '15 at 22:37
4

This is not a good answer. The problem is more likely one of disk space in /tmp folder. See answer with most upvotes. – Christopher Lörken Aug 17 '16 at 11:54

score 7 · Answer 3 · edited May 23 '17 at 10:31

So many answers are above and the question owner already got the solution as suggested by @Hawili, and a long time has been passed since this problem was raised. But as this a common issue, I wanted to share my experience so that if someone got this issue again due to different reasons then can get solution from here.

Case 1:

Most common reason is that a query is fetching data greater than the size of your /tmp partition. Whenever you get this issue during a query, look at your /tmp folder size. Temporary tables are created and removed automatically, and if available space here gets down to 0 during this query it means either you need to optimize your query or need increase /tmp's partition size.

Note: Sometimes it isn't an individual query: if a combination of heavy queries do this at the same time on the same server then you can get this issue, where normally the individual queries would execute without any error.

Case 2:

In case of a corrupt myisam table where you need to repair, the directory path will be different than /tmp in the error message.

Case 3: (rare case)

Sometimes due to an incorrect table join you can get this error. This is actaully a syntax error but mysql can throw this error instead. You can check details here at below link-

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

score 3 · Answer 4 · answered Aug 04 '12 at 04:00

3

Check the location of your tmp dir, by running df -h. Make sure there's enough space to grow the temp file, it could be several gigs.

Edit: If you have enough free space, I'd check to make sure every column you're indexing on or including in the WHERE clause is indexed.

answered Aug 04 '12 at 04:00

ajacian81

7,419
9
51
64

I know it i want a way for recover this from, something like optimized query or... – Suneth Kalhara Aug 04 '12 at 04:02
How much free space do you have on /tmp/ ? – ajacian81 Aug 04 '12 at 04:03
i don't have cpanel access i just running this code via my php file http://tsiopelakos.dev-centiva.com/index.php?option=com_jumi&view=application&fileid=2&Itemid=322&txtTitle=&txtIsbn=&txtIsbn13=&txtAuthor=&txtEditor=&txtDfrom=&txtDto=&txtType=&txtPricefrom=&txtPriceto= – Suneth Kalhara Aug 04 '12 at 04:12

score 0 · Answer 5 · answered Jan 09 '15 at 11:12

0

Check your database server that you have enough disk space on it. If disks are full this error is displayed. Now, which folders you should look at it depends on your setup.

answered Jan 09 '15 at 11:12

klodoma

4,181
1
31
42

score 0 · Answer 6 · edited Oct 23 '16 at 15:13

0

I used this following command and the error was gone:

mysqlcheck --all-databases -r #repair

I got this solution from the cpanel forum

edited Oct 23 '16 at 15:13

abhi

1,760
1
24
40

answered Oct 23 '16 at 14:15

Sourja Banerjee

1
1

Rossano Fenner · Answer 7 · 2018-01-15T19:58:07.157

Same problem to me

Run df -h to see if your partition /tmp have enough space

In my case /tmp was a overflow filesystem:

overflow 1,0M 24K 1000K 3% /tmp

What happened:

I had some problem here and my partition / was full, then Debian distro created a new partition /tmp in RAM memor, to use temporarily. This /tmp 1MB partition is not big enough for system use.

Solution: Run the following command to remove this temporary created partition /tmp

sudo umount -l /tmp

score -2 · Answer 8 · edited Apr 11 '16 at 10:14

-2

Restart MySQL service using this command line /etc/init.d/mysqld restart in terminal.

edited Apr 11 '16 at 10:14

Tomasz

4,847
2
32
41

answered Apr 11 '16 at 09:50

undefined

161
3
16

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

8 Answers8

Linked