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
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.3.3.3, “What to Do If MySQL Keeps Crashing”, and
Section 16.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 188.8.131.52, “CHECK TABLE Statement”, for more
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
option cannot be used with this statement on a partitioned
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 data dictionary. 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
normally should be
OK. For a
MyISAM table, if you do not get
OK, you should try repairing it with
REPAIR TABLE does not
implement all the options of myisamchk.
With myisamchk --safe-recover, you can also
use options that
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
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
variable is disabled. If
REPAIR TABLE ignores
the old temporal columns present in the table and does not
You may be able to increase
TABLE performance by setting certain system
variables. See Section 8.6.3, “Optimizing REPAIR TABLE Statements”.