Skip navigation links

Additional languages
12.5.2.7 RESTORE TABLE Syntax »
Section Navigation      [Toggle]

12.5.2.6. REPAIR TABLE Syntax

REPAIR [LOCAL | NO_WRITE_TO_BINLOG] TABLE
    tbl_name [, tbl_name] ... [QUICK] [EXTENDED] [USE_FRM]

REPAIR TABLE repairs a possibly corrupted table. By default, it has the same effect as myisamchk --recover tbl_name. REPAIR TABLE works for MyISAM and for ARCHIVE tables. See Section 13.1, “The MyISAM Storage Engine”, and Section 13.7, “The ARCHIVE Storage Engine”.

This statement requires SELECT and INSERT privileges for the table.

Normally, you should never have to run this statement. However, if disaster strikes, REPAIR TABLE is very likely to get back all your data from a MyISAM table. If tables become corrupted often, you should try to find the reason for it and so to eliminate the need to use REPAIR TABLE. See Section A.1.4.2, “What to Do If MySQL Keeps Crashing”, and Section 13.1.4, “MyISAM Table Problems”.

Caution

It is best to make a backup of a table before performing a table repair operation; under some circumstances the operation might cause data loss. Possible causes include but are not limited to filesystem errors.

Warning

If the server dies during a REPAIR TABLE operation, it is essential after restarting it that you immediately execute another REPAIR TABLE statement for the table before performing any other operations on it. (It is always a good idea to start by making a backup.) In the worst case, you might have a new clean index file without information about the data file, and then the next operation you perform could overwrite the data file. This is an unlikely but possible scenario.

REPAIR TABLE returns a result set with the following columns:

Column Value
Table The table name
Op Always repair
Msg_type One of status, error, info, or warning
Msg_text The message

The REPAIR TABLE statement might produce many rows of information for each repaired table. The last row has a Msg_type value of status and Msg_test normally should be OK. If you do not get OK, you should try repairing the table with myisamchk --safe-recover. (REPAIR TABLE does not yet implement all the options of myisamchk.) With myisamchk --safe-recover, you can also use options that REPAIR TABLE does not support, such as --max-record-length.

If QUICK is given, REPAIR TABLE tries to repair only the index tree. This type of repair is like that done by myisamchk --recover --quick.

If you use EXTENDED, MySQL creates the index row by row instead of creating one index at a time with sorting. (Before MySQL 4.1, this might be better than sorting on fixed-length keys if you have long CHAR keys that compress very well.) This type of repair is like that done by myisamchk --safe-recover.

As of MySQL 4.0.2, there is a USE_FRM mode for REPAIR TABLE. Use this if the .MYI index file is missing or if its header is corrupted. In this mode, MySQL re-creates the .MYI file using information from the .frm file. This kind of repair cannot be done with myisamchk.

Note

Use this mode only if you cannot use regular REPAIR modes. The .MYI header contains important table metadata (in particular, current AUTO_INCREMENT value and Delete link) that are lost in REPAIR ... USE_FRM. Don't use USE_FRM if the table is compressed because this information is also stored in the .MYI file.

Caution

Do not use USE_FRM if your table was created by a different version of the MySQL server than the one you are currently running. Doing so risks the loss of all rows in the table. It is particularly dangerous to use USE_FRM after the server returns this message: 

Table upgrade required. Please do
"REPAIR TABLE `tbl_name`" to fix it!

Before MySQL 4.1.1, REPAIR TABLE statements are not written to the binary log. As of MySQL 4.1.1, REPAIR TABLE statements are written to the binary log so that such statements used on a MySQL server acting as a replication master will be replicated to replication slaves. Logging can be suppressed with the optional NO_WRITE_TO_BINLOG keyword or its alias LOCAL.