Showing posts with label Mysql REPAIR TABLE. Show all posts
Showing posts with label Mysql REPAIR TABLE. Show all posts

Monday, 10 September 2018

MySQL "Incorrect key file for table" error

When saving a record to a MySQL table the other day I got the error message "Incorrect key file for table 'mytable'; try to repair it". I am uncertain why the error occured and how to ensure it doesn't happen again in the future but a quick fix for the time being is simple.

How to repair a MySQL table

All you need to do is to repair the table by running the following SQL command, where "mytable" is the name of the table that gave the error:
REPAIR TABLE `mytable`;
You can run this from e.g. the MySQL CLI or phpMyAdmin. From phpMyAdmin select the table, then "Operations" from the navigation tabs in the right frame above the table info; then "Repair Table" from the "Table maintenance" options at the bottom of the page.

When the table is /tmp/#sql_xxx_x.MYI

If the error looks like "Incorrect key file for table '/tmp/#sql_xxx_x.MYI'; try to repair it" where it refers to a temporary location on the filesystem, it's likely you've run out of diskspace. Read my follow up post for more information.

Related posts:

MySQL table is marked as crashed and should be repaired

How do you fix the error when running a query on a MySQL table and get the following error: "ERROR 145 (HY000) at line 1: Table '<tablename>' is marked as crashed and should be repaired". Very easily, but it may take some time depending how big the table is.

How to repair a MySQL table

While logged into the MySQL command line run the following command:
REPAIR TABLE <tablename>;
where <tablename> is the name of the table that has become corrupted.
Depending on how big the table is, this may take a long time.

Output when the process is finished

Once it's finished you'll get some output like this:
+----------------------+--------+----------+----------+
| Table                | Op     | Msg_type | Msg_text |
+----------------------+--------+----------+----------+
| <dbname>.<tablename> | repair | status   | OK       |
+----------------------+--------+----------+----------+
1 row in set (0.07 sec)

Long running repairs

The issue that prompted me to write this post was for a very large table, and after an hour it's still running the repair. My SSH connection to the server (which was via another SSH server) actually broke while the process was running, but fortunately the process kept running.
Use "show processlist" to see what's currently running if this happens to you, once you reconnect to the server. Then you can see if it's still running, and how long it's been running for.
Note that in many cases the repair process, if it's taking a long time, can cause other queries to be locked as they won't be able to access the table table while it's being repaired.

Related posts:

Subscribe to: Posts (Atom)