How do I repair an SQL table if it becomes corrupt?

13.7.2.6. REPAIR TABLE Syntax

REPAIR [NO_WRITE_TO_BINLOG | LOCAL] 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. Starting with MySQL 5.1.9, REPAIR is also valid for CSV tables. See Section 14.5, “The MyISAM Storage Engine”, and Section 14.12, “The ARCHIVE Storage Engine”, andSection 14.13, “The CSV Storage Engine”

This statement requires SELECT and INSERT privileges for the table.

Beginning with MySQL 5.1.27, REPAIR TABLE is also supported for partitioned tables. However, the USE_FRMoption cannot be used with this statement on a partitioned table.

Also beginning with MySQL 5.1.27, you can use ALTER TABLE ... REPAIR PARTITION to repair one or more partitions; for more information, see Section 13.1.7, “ALTER TABLE Syntax”, and Section 18.3.3, “Maintenance of Partitions”.

Normally, you should never have to run REPAIR TABLE. However, if disaster strikes, this statement is very likely to get back all your data from a MyISAM table. If your tables become corrupted often, you should try to find the reason for it, to eliminate the need to use REPAIR TABLE. See Section C.5.4.2, “What to Do If MySQL Keeps Crashing”, and Section 14.5.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 file system errors. See Chapter 7, Backup and Recovery.

Warning

If the server crashes 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. 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.

REPAIR TABLE returns a result set with the following columns.

ColumnValue
Table The table name
Op Always repair
Msg_type status, error, info, note, or warning
Msg_text An informational message

The REPAIR TABLE statement might produce many rows of information for each repaired table. The last row has aMsg_type value of status and 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 ofmyisamchk.) With myisamchk --safe-recover, you can also use options that REPAIR TABLE does not support, such as --max-record-length.

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

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.

The USE_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 .frm file. This kind of repair cannot be done with myisamchk.

Note

Use the 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 consequences:

  • The current 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.

  • 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 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.

Caution

As of MySQL 5.1.25, 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 REPAIR TABLE contains a line with a Msg_type value of error and a Msg_text value of Failed repairing incompatible .FRM file.

Prior to MySQL 5.1.25, do not use USE_FRM if your table was created by a different version of the MySQL server. Doing so risks the loss of all rows in the table. It is particularly dangerous to useUSE_FRM after the server returns this message:

Table upgrade required. Please do
"REPAIR TABLE `tbl_name`"
or dump/reload to fix it!

If 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 CHECK TABLE ... FOR UPGRADE. See Section 13.7.2.3, “CHECK TABLESyntax”, for more information. As of MySQL 5.1.25, REPAIR TABLE without USE_FRM upgrades the .frm file to the current version.

By default, the server writes REPAIR TABLE statements to the binary log so that they replicate to replication slaves. To suppress logging, use the optional NO_WRITE_TO_BINLOG keyword or its alias LOCAL.

Important

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.

You may be able to increase REPAIR TABLE performance by setting certain system variables. See Section 8.3.2.4, “Speed of REPAIR TABLE Statements”.

Credits to source: http://dev.mysql.com/doc/refman/5.1/en/repair-table.html

  • 1 Users Found This Useful
Was this answer helpful?

Related Articles

How do I change my remember password options in Google Chrome

Manage your website passwords This article applies to the Google Chrome browser on Windows,...

Change time is Gmail

Correct Your Gmail Time Zone To set your Gmail time zone: Click the gear in your Gmail's...

How to speed up Windows XP use at your own risk

Warning: occasionally people break their PC badly by using this list of suggestions. Usually...

How to run cmd in escalated mode with admin rights to modify firewall

a solution: runas /user:administrator@domainname.local cmd then in the resulting command...

How do I enable auto login in Windows

This article describes how to configure Windows to automate the logon...