How do you recover from an "incorrect key file" with MySQL (InnoDB)?

Question

Incorrect key file for table 'widgets'; try to repair it

This is the error message MySQL gives me when attempting to apply a new index to an existing (very large) table. Of course when I follow the error message's suggestion of trying to repair it:

mysql> repair table widgets;
+-------------------+--------+----------+---------------------------------------------------------+
| Table             | Op     | Msg_type | Msg_text                                                |
+-------------------+--------+----------+---------------------------------------------------------+
| tedsdb.widgets    | repair | note     | The storage engine for the table doesn't support repair | 
+-------------------+--------+----------+---------------------------------------------------------+
1 row in set (0.00 sec)

What's my best course of action here (following the obvious full backup before tinkering)?

UPDATE: I created a new table (MyISAM) of the same schema, copied over all the records (insert into select), the changed the engine on the new table (InnoDB), renamed the corrupt table and renamed the new table, then tried again and got the same error.

score 13 · Accepted Answer · answered Sep 14 '10 at 17:37

13

Looks like an old question, but I just ran into this problem. MySQL was writing out a temporary table to disk and the /tmp partition filled up.

answered Sep 14 '10 at 17:37

Edwin Knuth

156
1
3

the tmp folder has a limit usually 2GB, try df -h to see it – Elzo Valugi Jan 18 '12 at 12:14
Same for me ... some failed backup dumps filled up the /tmp space. Nice reminder this answer – smile2day Jan 28 '14 at 17:23
Might be the solution for many, but didn't work for me. (Our /tmp is only 3% in use) – rinogo Feb 01 '17 at 23:08

score 0 · Answer 2 · answered Jun 22 '09 at 13:17

0

I strongly suggest backing up before trying this.

Perform a dummy

ALTER TABLE widgets;

answered Jun 22 '09 at 13:17

Robert Munteanu

67,031
36
206
278

score 0 · Answer 3 · answered Jan 28 '16 at 20:20

When we are doing Alter on innodb the "tmpdir" need to be observed. The Error 1034 : Key repair is definitely because of "tmpdir" full, and your alter will throw this error.

So I suggest when the Alter is got triggered give one more eye on your "tmpdir" utilization. If tmp is using 100% there you can see this error for sure :).

How do you recover from an "incorrect key file" with MySQL (InnoDB)?

3 Answers3