18.104.22.168 REPAIR TABLE Statement
REPAIR [NO_WRITE_TO_BINLOG | LOCAL] TABLE tbl_name [, tbl_name] ... [QUICK] [EXTENDED] [USE_FRM]
REPAIR TABLE repairs a possibly corrupted table, for certain storage engines only.
Although normally you should never have to run
REPAIR 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
REPAIR TABLE. See Section B.4.3.3, “What to Do If MySQL Keeps Crashing”, and Section 15.2.4, “MyISAM Table Problems”.
REPAIR TABLE checks the table to see whether an upgrade is required. If so, it performs the upgrade, following the same rules as
CHECK TABLE ... FOR UPGRADE. See Section 22.214.171.124, “CHECK TABLE Statement”, for more information.
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
REPAIR TABLEoperation, it is essential after restarting it that you immediately execute another
REPAIR TABLEstatement 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.
In the event that a table on the source becomes corrupted and you run
REPAIR TABLEon it, any resulting changes to the original table are not propagated to replicas.
REPAIR TABLE is supported for partitioned tables. However, the
USE_FRM option cannot be used with this statement on a partitioned table.
By default, the server writes
REPAIR TABLEstatements to the binary log so that they replicate to replicas. To suppress logging, specify the optional
NO_WRITE_TO_BINLOGkeyword or its alias
If you use the
EXTENDEDoption, 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_FRMoption is available for use if the
.MYIindex file is missing or if its header is corrupted. This option tells MySQL not to trust the information in the
.MYIfile header and to re-create it using information from the
.frmfile. This kind of repair cannot be done with myisamchk.Caution
USE_FRMoption only if you cannot use regular
REPAIRmodes. Telling the server to ignore the
.MYIfile makes important table metadata stored in the
.MYIunavailable to the repair process, which can have deleterious consequences:
AUTO_INCREMENTvalue is lost.
The link to deleted records in the table is lost, which means that free space for deleted records will remain unoccupied thereafter.
.MYIheader 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_FRMshould 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_FRMfor a table that was created by a different version of the MySQL server than the one you are currently running,
REPAIR TABLEdoes not attempt to repair the table. In this case, the result set returned by
REPAIR TABLEcontains a line with a
Failed repairing incompatible .FRM file.
REPAIR TABLEdoes not check the table to see whether an upgrade is required.
REPAIR TABLE returns a result set with the columns shown in the following table.
||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. For a
MyISAM table, if you do not get
OK, 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
REPAIR 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
.MYI 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
REPAIR TABLE upgrades a table if it contains old temporal columns in pre-5.6.4 format (
TIMESTAMP columns without support for fractional seconds precision) and the
avoid_temporal_upgrade system variable is disabled. If
avoid_temporal_upgrade is enabled,
REPAIR TABLE ignores the old temporal columns present in the table and does not upgrade them.
You may be able to increase
REPAIR TABLE performance by setting certain system variables. See Section 8.6.3, “Optimizing REPAIR TABLE Statements”.