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?

the tmp folder has a limit usually 2GB, try df -h to see it – Elzo Valugi Jan 18 '12 at 12:17 — Elzo Valugi, Jan 18 '12 at 12:17

score 60 · Answer 1 · answered Nov 02 '10 at 07:48

60

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.

answered Nov 02 '10 at 07:48

marknt15

5,047
14
59
67

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!!! – mimoralea Feb 24 '15 at 21:24
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 – Ashish Karpe Jul 01 '16 at 07:25
So if my tempdir is /var/folders/zz/zyxvpxvq6csfxvn_n000009800002_/T, can I just remove the files in there to free up space? – LeeZee Jul 06 '16 at 21:33
had to run REPAIR TABLE tbl_name USE_FRM; after clearing some space – Abdullah Tahan Oct 06 '19 at 00:25

score 51 · Answer 2 · edited Apr 12 '13 at 22:25

51

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.

edited Apr 12 '13 at 22:25

OmnipotentEntity

16,531
6
62
96

answered Aug 16 '11 at 17:00

Drew

743
1
6
11

1

Thanks, fixed my table. But actually it should be `REPAIR TABLE tbl_name USE_FRM` – Koen. Feb 14 '12 at 20:42
2

This worked for me too - a life saver for a production system!! – Robert D Aug 25 '16 at 16:47
2

Thank you, this is the best answer. Worked like a charm. – Aerodynamika Apr 24 '17 at 10:21
No one else mentioned the USE_FRM option that I was desperately looking for. Thank you so much! – Alexander Liptak Sep 17 '19 at 21:04
1

Why it shows me ''The storage engine for the table doesn't support repair''? Help ... – wawawa Jan 02 '20 at 13:43

score 12 · Answer 3 · answered Mar 12 '10 at 06:58

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

score 5 · Accepted Answer · answered Mar 12 '10 at 07:41

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.

score 2 · Answer 5 · answered Jul 20 '17 at 09:51

2

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

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

answered Jul 20 '17 at 09:51

Mladen Janjetovic

13,844
8
72
82

1

This only works if the table is repairable type. Typically `InnoDB` tables won't have this option. – Martin Sep 08 '21 at 12:18

score 1 · Answer 6 · answered Jul 04 '16 at 10:47

1

In my case, there was a disc space issue. I deleted some unwanted war files from my server and it worked after that.

answered Jul 04 '16 at 10:47

Ninad Pingale

6,801
5
32
55

score 1 · Answer 7 · answered Jul 08 '17 at 12:35

1

REPAIR TABLE tbl_name USE_FRM;

Command only run when MySQL 'Storage Engine' type should be 'MyISAM'

Hope this helps

answered Jul 08 '17 at 12:35

BhavyaSoft

41
5

This does not seem to add anything new to [an existing and upvoted answer](https://stackoverflow.com/a/7081976/4453460). – lfurini Jul 08 '17 at 13:10

score 1 · Answer 8 · answered Apr 08 '18 at 10:35

1

this issue is because of low storage space availability of a particular drive(c:\ or d:\ etc.,), release some memory then it will work.

Thanks Saikumar.P

answered Apr 08 '18 at 10:35

sai kumar

11
2

score 1 · Answer 9 · answered Mar 29 '20 at 17:18

This happenes might be because you ran out of disk storage and the mysql files and starting files got corrupted

The solution to be tried as below

First we will move the tmp file to somewhere with larger space

Step 1: Copy your existing /etc/my.cnf file to make a backup

cp /etc/my.cnf{,.back-`date +%Y%m%d`}

Step 2: Create your new directory, and set the correct permissions

mkdir /home/mysqltmpdir
chmod 1777 /home/mysqltmpdir

Step 3: Open your /etc/my.cnf file

nano /etc/my.cnf

Step 4: Add below line under the [mysqld] section and save the file

tmpdir=/home/mysqltmpdir

Secondly you need to remove or error files and logs from the /var/lib/mysql/ib_* that means to remove anything that starts by "ib"

rm /var/lib/mysql/ibdata1 and rm /var/lib/mysql/ibda.... and so on

Thirdly you will need to make sure that there is a pid file available to have the database to write in

Step 1 you need to edit /etc/my.cnf

pid-file= /var/run/mysqld/mysqld.pid

Step 2 create the directory with the file to point to

 mkdir /var/run/mysqld
 touch /var/run/mysqld/mysqld.pid
 chown -R mysql:mysql /var/run/mysqld

Last step restart mysql server

/etc/init.d/mysql restart

score 1 · Answer 10 · answered Feb 01 '21 at 03:11

1

Change to MyISAM engine and run this command

REPAIR TABLE tbl_name USE_FRM;

answered Feb 01 '21 at 03:11

Saul Euan

11
3

score 1 · Answer 11 · answered Jan 13 '22 at 12:38

I just resolved a similar issue “Incorrect key file:\bonga_process\alarms.MYI’:try to repair it”

How I resolved it:

I started the server configuration application, ServerConfiguration.exe
Maitenance tab
Click on Repair tables command button
Prompts to delete files to free disk space appear with Manage Storage command button
Click on Manage Storage command, this will run a take you to some files you need to delete to free more disk space. Delete any useless files.
Restart the system auditor application

score 0 · Answer 12 · edited May 03 '14 at 11:04

Apply proper charset and collation to database, table and columns/fields.

I creates database and table structure using sql queries from one server to another. it creates database structure as follows:

database with charset of "utf8", collation of "utf8_general_ci"
tables with charset of "utf8" and collation of "utf8_bin".
table columns / fields have charset "utf8" and collation of "utf8_bin".

I change collation of table and column to utf8_general_ci, and it resolves the error.

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

12 Answers12

Linked