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.

Solution

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.

Comments

[Elzo Valugi]

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

[smile2day]

Same for me ... some failed backup dumps filled up the /tmp space. Nice reminder this answer

[TommyAutoMagically]

Might be the solution for many, but didn't work for me. (Our /tmp is only 3% in use)