REPAIR [NO_WRITE_TO_BINLOG | LOCAL] TABLE
tbl_name] ... [QUICK] [EXTENDED] [USE_FRM]
TABLE is supported for partitioned tables. However, the
cannot be used with this statement on a partitioned table.
In MySQL 5.7.1,
must be set to
AUTOMATIC before issuing this statement. This restriction does not
apply in MySQL 5.7.2 or later. (Bug #16062608, Bug #16715809, Bug #69045)
Although normally you should never have to run
TABLE, if disaster strikes, this statement is very likely to get back all your data from a
MyISAM table. If your tables become corrupted often, try to find the reason for it,
to eliminate the need to use
C.5.4.2, "What to Do If MySQL Keeps Crashing", and Section
MyISAM Table Problems".
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 file system errors. See Chapter 7, Backup and Recovery.
If the server crashes during a
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. 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
that underscores the value of making a backup first.
TABLE returns a result set with the following columns.
||The table name|
||An informational message|
REPAIR TABLE statement might produce many rows of information for each repaired
table. The last row has a
Msg_type value of
Msg_test normally should be
OK. If you do not get
OK for a
MyISAM table, you should try repairing it
with myisamchk --safe-recover. (
REPAIR TABLE does not implement all the options of myisamchk.) With myisamchk --safe-recover, you can also use options that
REPAIR TABLE does not support, such as
If you use the
EXTENDED option, MySQL creates the index row by row instead of
creating one index at a time with sorting. This type of repair is like that done by myisamchk --safe-recover.
USE_FRM option is available for use if the
index file is missing or if its header is corrupted. This option tells MySQL not to trust the information in the
.MYI file header and to re-create it using information from the
.frm file. This kind of repair cannot be done with myisamchk.
USE_FRM option only if
you cannot use regular
REPAIR modes! Telling the server to ignore the
.MYI file makes important table metadata stored in the
.MYI unavailable to the repair process, which can have deleterious
AUTO_INCREMENT value is lost.
The link to deleted records in the table is lost, which means that free space for deleted records will remain unoccupied thereafter.
.MYI header indicates whether the table is
compressed. If the server ignores this information, it cannot tell that a table is compressed and
repair can cause change or loss of table contents. This means that
USE_FRM should not be used with compressed tables. That should not be
necessary, anyway: Compressed tables are read only, so they should not become corrupt.
If you use
USE_FRM for a table that was created by a different version
of the MySQL server than the one you are currently running,
REPAIR TABLE will not attempt to repair the table. In this case, the
result set returned by
TABLE contains a line with a
Msg_type value of
error and a
Msg_text value of
Failed repairing incompatible .FRM file.
USE_FRM is not used,
REPAIR TABLE checks the table to see whether an upgrade is required. If so, it
performs the upgrade, following the same rules as
TABLE ... FOR UPGRADE. See Section 184.108.40.206, "
CHECK TABLE Syntax", for more information.
REPAIR TABLE without
USE_FRM upgrades the
.frm file to the current version.
By default, the server writes
statements to the binary log so that they replicate to replication slaves. To suppress logging, specify the
NO_WRITE_TO_BINLOG keyword or its alias
In the event that a table on the master becomes corrupted and you run
REPAIR TABLE on it, any resulting changes to the original table are not propagated to slaves.
TABLE table catches and throws any errors that occur while copying table statistics from the old
corrupted file to the newly created file. For example. if the user ID of the owner of the
file is different from the user ID of the mysqld process,
REPAIR TABLE generates a "cannot change ownership of the file" error unless
mysqld is started by the