Table of Contents for

MySQL in a Nutshell, 2nd Edition

Use this statement to repair corrupted MyISAM tables. Multiple tables may be given in a comma-separated list. To prevent this statement from recording its activities in the binary log file, give the NO_WRITE_TO_BINLOG keyword or its LOCAL alias. The QUICK keyword instructs MySQL to repair the table indexes only. The EXTENDED keyword rebuilds the indexes one row at a time. This option takes longer, but it can be more effective, especially with rows containing duplicate keys.

Before running this statement, make a backup of the table. If a table continues to have problems, there may be other problems (e.g., filesystem problems) that you should consider. Here is an example of this statement:

REPAIR TABLE systems QUICK EXTENDED;

+----------------------+--------+----------+----------+
| Table                | Op     | Msg_type | Msg_text |
+----------------------+--------+----------+----------+
| workrequests.systems | repair | status   | OK       |
+----------------------+--------+----------+----------+

In this example, the repair is successful. This is indicated by the OK in the Msg_text field. If it is unsuccessful, you could try the USE_FRM option with this statement. That option will create a new index file (.MYI) using the table schema file (.frm). It won’t be able to determine the current value for AUTO_INCREMENT columns or for DELETE LINK, so it shouldn’t be used unless the original .MYI file is lost. Incidentally, if the MySQL server dies while the REPAIR TABLE statement is running, you should run the statement again as soon as the server is back up, before running any other SQL statements.