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.
