Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 43.3Mb
PDF (A4) - 43.4Mb
Man Pages (TGZ) - 297.3Kb
Man Pages (Zip) - 402.5Kb
Info (Gzip) - 4.3Mb
Info (Zip) - 4.3Mb
Excerpts from this Manual

15.7.3.5 REPAIR TABLE Statement

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

REPAIR TABLE repairs a possibly corrupted table, for certain storage engines only.

This statement requiresSELECT andINSERT privileges for the table.

Although normally you should never have to runREPAIR TABLE, if disaster strikes, this statement is very likely to get back all your data from aMyISAM table. If your tables become corrupted often, try to find the reason for it, to eliminate the need to useREPAIR TABLE. SeeSection B.3.3.3, “What to Do If MySQL Keeps Crashing”, andSection 18.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 asCHECK TABLE ... FOR UPGRADE. SeeSection 15.7.3.2, “CHECK TABLE Statement”, for more information.

Important

  • 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. SeeChapter 9,Backup and Recovery.

  • If the server exits during aREPAIR TABLE operation, it is essential after restarting it that you immediately execute anotherREPAIR 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.

  • In the event that a table on the source becomes corrupted and you runREPAIR TABLE on it, any resulting changes to the original table arenot propagated to replicas.

REPAIR TABLE Storage Engine and Partitioning Support

REPAIR TABLE works forMyISAM,ARCHIVE, andCSV tables. ForMyISAM tables, it has the same effect asmyisamchk --recovertbl_name by default. This statement does not work with views.

REPAIR TABLE is supported for partitioned tables. However, theUSE_FRM option cannot be used with this statement on a partitioned table.

You can useALTER TABLE ... REPAIR PARTITION to repair one or more partitions; for more information, seeSection 15.1.9, “ALTER TABLE Statement”, andSection 26.3.4, “Maintenance of Partitions”.

REPAIR TABLE Options

  • NO_WRITE_TO_BINLOG orLOCAL

    By default, the server writesREPAIR TABLE statements to the binary log so that they replicate to replicas. To suppress logging, specify the optionalNO_WRITE_TO_BINLOG keyword or its aliasLOCAL.

  • QUICK

    If you use theQUICK option,REPAIR TABLE tries to repair only the index file, and not the data file. This type of repair is like that done bymyisamchk --recover --quick.

  • EXTENDED

    If you use theEXTENDED 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 bymyisamchk --safe-recover.

  • USE_FRM

    TheUSE_FRM option is available for use if the.MYI 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 data dictionary. This kind of repair cannot be done withmyisamchk.

    Caution

    Use theUSE_FRM optiononly if you cannot use regularREPAIR 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 consequences:

    • The currentAUTO_INCREMENT value is lost.

    • The link to deleted records in the table is lost, which means that free space for deleted records remains unoccupied thereafter.

    • The.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 thatUSE_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 useUSE_FRM for a table that was created by a different version of the MySQL server than the one you are currently running,REPAIR TABLE does not attempt to repair the table. In this case, the result set returned byREPAIR TABLE contains a line with aMsg_type value oferror and aMsg_text value ofFailed repairing incompatible .FRM file.

    IfUSE_FRM is used,REPAIR TABLE does not check the table to see whether an upgrade is required.

REPAIR TABLE Output

REPAIR TABLE returns a result set with the columns shown in the following table.

ColumnValue
TableThe table name
OpAlwaysrepair
Msg_typestatus,error,info,note, orwarning
Msg_textAn informational message

TheREPAIR TABLE statement might produce many rows of information for each repaired table. The last row has aMsg_type value ofstatus andMsg_test normally should beOK. For aMyISAM table, if you do not getOK, you should try repairing it withmyisamchk --safe-recover. (REPAIR TABLE does not implement all the options ofmyisamchk. Withmyisamchk --safe-recover, you can also use options thatREPAIR TABLE does not support, such as--max-record-length.)

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.MYD or.MYI file is different from the user ID of themysqld process,REPAIR TABLE generates a "cannot change ownership of the file" error unlessmysqld is started by theroot user.

Table Repair Considerations

REPAIR TABLE upgrades a table if it contains old temporal columns in pre-5.6.4 format (TIME,DATETIME, andTIMESTAMP columns without support for fractional seconds precision) and theavoid_temporal_upgrade system variable is disabled. Ifavoid_temporal_upgrade is enabled,REPAIR TABLE ignores the old temporal columns present in the table and does not upgrade them.

To upgrade tables that contain such temporal columns, disableavoid_temporal_upgrade before executingREPAIR TABLE.

You may be able to increaseREPAIR TABLE performance by setting certain system variables. SeeSection 10.6.3, “Optimizing REPAIR TABLE Statements”.