How do you fix a MySQL "Incorrect key file" error when you can't repair the table?

Question

I'm trying to run a rather large query that is supposed to run nightly to populate a table. I'm getting an error saying Incorrect key file for table '/var/tmp/#sql_201e_0.MYI'; try to repair it but the storage engine I'm using (whatever the default is, I guess?) doesn't support repairing tables.

how do I fix this so I can run the query?

Solution 1

The storage engine (MyISAM) DOES support repair table. You should be able to repair it.

If the repair fails then it's a sign that the table is very corrupted, you have no choice but to restore it from backups.

If you have other systems (e.g. non-production with same software versions and schema) with an identical table then you might be able to fix it with some hackery (copying the frm an MYI files, followed by a repair).

In essence, the trick is to avoid getting broken tables in the first place. This means always shutting your db down cleanly, never having it crash and never having hardware or power problems. In practice this isn't very likely, so if durability matters you may want to consider a more crash-safe storage engine.

Solution 2

You must change the location of MySQL's temporary folder which is '/tmp' in most cases to a location with a bigger disk space. Change it in MySQL's config file.

Basically your server is running out of disk space where /tmp is located.

Solution 3

You'll need to run this command from the MySQL prompt:

REPAIR TABLE tbl_name USE_FRM;

From MySQL's documentation on the Repair command:

The USE_FRM option is available for use if the .MYI index file is missing or if its header is corrupted. This option tells MySQL not to trust the information in the .MYI file header and to re-create it using information from the .frm file. This kind of repair cannot be done with myisamchk.

Solution 4

Your query is generating a result set so large that it needs to build a temporary table either to hold some of the results or some intermediate product used in generating the result.

The temporary table is being generated in /var/tmp. This temporary table would appear to have been corrupted. Perhaps the device the temporary table was being built on ran out of space. However, usually this would normally result in an "out of space" error. Perhaps something else running on your machine has clobbered the temporary table.

Try reworking your query to use less space, or try reconfiguring your database so that a larger or safer partition is used for temporary tables.

MySQL Manual - B.5.4.4. Where MySQL Stores Temporary Files

Solution 5

Simple "REPAIR the table" from PHPMYADMIN solved this problem for me.

1. go to phpmyadmin
2. open problematic table
3. go to Operations tab (in my version of PMA)
4. at the bottom you will find "Repair table" link

View more solutions

Comments

[Elzo Valugi]

the tmp folder has a limit usually 2GB, try df -h to see it

[Koen.]

Thanks, fixed my table. But actually it should be REPAIR TABLE tbl_name USE_FRM

[mimoralea]

I got this issue after changing the location of the mysql data dir, because the fs was out of space.. so just a sudo rm -rf /var/lib/mysql/ibdata1 did it for me. Thanks!!!

[Ashish Karpe]

tmpdir = /mnt/mysql_tmp got 1.2 T space still getting error ? why ? [root@ADM-PROD-PERCONA-SL-RP-03 ~]# df -h Filesystem Size Used Avail Use% Mounted on /dev/xvda1 7.8G 1.6G 6.1G 21% / devtmpfs 61G 80K 61G 1% /dev tmpfs 61G 0 61G 0% /dev/shm /dev/md0 3.0T 1.9T 1.2T 62% /mnt

[LeeZee]

So if my tempdir is /var/folders/zz/zyxvpxvq6csfxvn_n000009800002_/T, can I just remove the files in there to free up space?

[Robert D]

This worked for me too - a life saver for a production system!!

[Aerodynamika]

Thank you, this is the best answer. Worked like a charm.

[lfurini]

This does not seem to add anything new to an existing and upvoted answer.

[Alexander Liptak]

No one else mentioned the USE_FRM option that I was desperately looking for. Thank you so much!

[Abdullah Tahan]

had to run REPAIR TABLE tbl_name USE_FRM; after clearing some space

[wawawa]

Why it shows me ''The storage engine for the table doesn't support repair''? Help ...

[Martin]

This only works if the table is repairable type. Typically InnoDB tables won't have this option.