Repairing tables

In most cases, only the index will be corrupted (the index is a separate, smaller, file with records that point to the main data file) - actual data corruption is extremely rare. Fixing most forms of corruption is relatively easy. As with checking, there are three ways to repair tables. These all only work with MyISAM tables - to repair corruption of the other table types, you will need to restore from backup:

• The REPAIR TABLE SQL statement (obviously the server must be running for this)
• The mysqlcheck command-line utility (the server can be running)
• The myisamchk command-line utility (the server must be down, or the tables inactive)

Repairing a table requires twice as much disk space as the original table (a copy of the data is made), so make sure you are not going to run out of disk space before you start.

Repairing a table with REPAIR TABLE

The syntax is, as would be expected, REPAIR TABLE tablename[,tablename1...] [options]. This method only works with MyISAM tables. The following options are available.

In most cases, a simple REPAIR without any options should work fine. An unusual case is when the .MYI is missing. Here is what would happen:

mysql> REPAIR TABLE fixtures;
+-------------------------+--------+----------+---------------------------------------------+
| Table                   | Op     | Msg_type | Msg_text                                    |
+-------------------------+--------+----------+---------------------------------------------+
| sports_results.fixtures | repair | error    | Can't find file: 'fixtures.MYI' (errno: 2)  |
+-------------------------+--------+----------+---------------------------------------------+

The repair has failed because the index file is missing or has a corrupted header. To use the definition file to repair, use the USE_FRM option, as follows:

mysql> REPAIR TABLE fixtures USE_FRM;
+-------------------------+--------+----------+------------------------------------+
| Table                   | Op     | Msg_type | Msg_text                           |
+-------------------------+--------+----------+------------------------------------+
| sports_results.fixtures | repair | warning  | Number of rows changed from 0 to 2 |
| sports_results.fixtures | repair | status   | OK                                 |
+-------------------------+--------+----------+------------------------------------+

Everything has gone smoothly this time, as indicated by the OK Msg_text.

Repairing tables with mysqlcheck

The mysqlcheck command-line utility can be used while the server is running, and, like all the methods of repair, only works with MyISAM tables. The syntax is:

%mysqlcheck -r sports_results fixtures -uuser -ppass
sports_results.fixtures                            OK

You can also repair multiple tables in a database, by listing them after the database name, or all tables in a database by just passing the database name, for example:

%mysqlcheck -r sports_results fixtures events -uuser -ppass
sports_results.fixtures                            OK
sports_results.events                              OK

%mysqlcheck -r sports_results -uuser -ppass
sports_results.fixtures                            OK
sports_results.events                              OK
...

Repairing tables with myisamchk

The server must be down, or the tables inactive (which is ensured if the --skip-external-locking option is not in use). The syntax is myisamchk [options[ [tablenames]. Remember again that you must be in, or specify, the path to the relevant .MYI files. The following options are available:

An example of its usage:

% myisamchk -r fixtures
- recovering (with keycache) MyISAM-table 'fixtures.MYI'
Data records: 0

I hope that you will never have to do any repairing, but I am sure at least some of you reading this article (at least those who got this far) are here precisely because you've got some corruption. I hope that your repairs will be as painless as all mine have been. Good luck!