How to repair a corrupt MySQL Database

For various reasons, database tables could get corrupted, and when it happens you should not panic. Reasons could be many, table corruption should be rare when using MySQL (though an overheating server continually restarting at the most inopportune times has meant I have seen more than my fair share of corruption recently, hence the inspiration for the article).  

Luckily, MySQL has some easy-to-use tools that can easily repair most cases of table corruption, and this article introduces you to these. You should always look at removing the causes of the corruption of course, but this article only deals with the firefighting aspect – repairing the symptoms.

Identifying table corruption

Table corruption should be relatively easy to identify. Queries that worked before suddenly stop working, or begin working inconsistently. Your first suspected culprit would be the code of course, but when a query such as UPDATE table_x SET x_key=’d’ doesn’t work for no good reason, it’s time to check the tables. If you see any of the following errors, it is also prudent to check the tables for corruption:

• Record file is crashed
• Unexpected end of file
• can’t find file tablethatshouldbethere.MYI
• tablethatwasworking.frm is locked against change
• Got error ### from table handler.

The latter error returns an error number, and you can get more details about this error with the perror utility. To execute perror, on your command line enter with the error code and it will display the errors. You can enter several error codes on one line e.g

%perror 126 127 132 134 135 136 141 144 145            

perror sits in the same directory as all the other MySQL binaries, such as mysqladmin, mysql.  

Some of the errors, which often indicate table corruption, include:

126: Index file is crashed / Wrong file format

127: Record file is crashed

132: Old database file

134: Record was already deleted (or record file crashed)

135: No more room in the record file

136: No more room in the index file

141: Duplicate unique key or constraint on write or update

144: Table is crashed and last repair failed

145: Tables was marked as crashed and should be repaired

Checking tables

There are three ways to check tables. All of these work with MyISAM tables, the default, non-transactional table type, and one with InnoDB, the most mature of the MySQL transactional table types. Fortunately, MySQL now allows you to check tables while the server is still running, so corruption in a minor table need not affect everything on the server.

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

The first method for checking tables is to run the CHECK TABLE SQL statement while connected to the server. The syntax is:

CHECK TABLE tablename[,tablename2…] [option][,option2…], for example:

mysql> CHECK TABLE fixtures;

There are a number of options to specify as well, which allow you to do a more in-depth, or a more superficial kind of check than normal:

• QUICK –  The quickest option, and does not scan the rows to check for incorrect links. Often used when you do not suspect an error.
• FAST –  Only check tables if they have not been closed properly. Often used when you do not suspect an error, from a cron, or after a power failure that seems to have had no ill-effects.
• CHANGED –  Same as FAST, but also checks tables that have been changed since the last check.
• MEDIUM –  The default if no option is supplied. Scans rows to check that deleted links are correct and verifies a calculated checksum for all keys with a calculated a key checksum for the rows.
• EXTENDED –  The slowest option, only used if the other checks report no errors but you still suspect corruption. Very slow, as it does a full key lookup for all keys for every row. Increasing the key-buffer-size variable in the MySQL config. the file can help this go quicker.

Note that the CHECK TABLE only works with MyISAM and InnoDB tables. If CHECK finds corruption, it will mark the table as corrupt, and it will be unusable. See the Repairing tables section below for how to handle this.

Checking tables with mysqlcheck

The second method is to run the mysqlcheck command-line utility. The syntax is:

mysqlcheck [options] dbname tablename [tablename2… ].

The following options pertain to checking (mysqlcheck can also repair, as well as analyze and optimize, which are not covered here).

• –auto-repair –  Used together with a check option, it will automatically begin repairing if corruption is found.
• –check, -c –  Checks tables (only needed if using mysqlcheck under another name, such as mysqlrepair. See the manual for more details)
•  
• –extended, -e –  Same as the CHECK TABLE … EXTENDED option above.
• –fast, -F –  Same as the CHECK TABLE … FAST option above.
• –medium-check, -m –  Same as the CHECK TABLE … MEDIUM option above.
• –quick, -q –  Same as the CHECK TABLE … QUICK option above.

For example:

% mysqlcheck -uuser -ppass sports_results fixtures

% mysqlcheck -u root -p –repair –all-databases

% mysqlcheck -u root -p –check –all-databases

Note that you can specify multiple tables, and that mysqlcheck only works with MyISAM tables.

Checking tables with myisamchk

Finally, there is the myisamchk command-line utility. 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] tablename.MYI, and you must be in or specify, the path to the relevant.MYI files (each MyISAM database is stored in its own directory). These are the available check options:

• –check, -c –  The default option
• –check-only-changed, -C –  Same as the CHECK TABLE … CHANGED option above.
• –extend-check, -e –  Same as the CHECK TABLE … EXTENDED option above.
• –fast, -F –  Same as the CHECK TABLE … FAST option above.
• –force, -f –  Will run the myisamchk repair option if any errors are found
• –information, -i –  Display statistics about the checked table
• –medium-check, -m –  Same as the CHECK TABLE … MEDIUM option above.
• –read-only, -T –  Does not mark the table as checked
• –update-state, -U –  This option stores when the table was checked, and the time of crash, in .MYI file.

For example:

% myisamchk fixtures.MYI

You can also use wildcard to check all the .MYI tables at the same time, for example:

% myisamchk *.MYI

Note that myisamchk only works with MyISAM tables. For those of you still using the old ISAM table types, there is also isamchk, though there is really little reason not to upgrade to MyISAM.

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.

• QUICK –  The quickest, as the data file is not modified.
• EXTENDED –  Will attempt to recover every possible data row file, which can result in garbage rows. Use as a last resort.
• USE_FRM –  To be used if the .MYI file is missing or has a corrupted header. Uses the .frm file definitions to rebuild the indexes.

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;

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;

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

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

%mysqlcheck -r sports_results -uuser -ppass

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:

• –backup, -B –  Makes a .BAK backup of the table before repairing it
• –correct-checksum –  Corrects the checksum
• –data-file-length=#, -D # –  Specifies the maximum length of the data file when recreating
• –extend-check, -e –  Attempts to recover every possible row from the data file. This option should not be used except as a last resort, as it may produce garbage rows.
• –force, -f –  Overwrites old temporary.TMD files instead of aborting if it encounters a pre-existing one.
• keys-used=#, -k # –  Can make the process faster by specifying which keys to use. Each binary bit stands for one key starting at 0 for the first key.
• –recover, -r –  The most commonly used option, which repairs most corruption. If you have enough memory, increase the sort_buffer_size to make the recovery go more quickly. Will not recover from the rare form of corruption where a unique key is not unique.
• –safe-recover, -o –  More thorough, yet slower repair option than -r, usually only used only if -r fails. Reads through all rows and rebuilds the indexes based on the rows. This also uses slightly less disk space than a -r repair since a sort buffer is not created. You should increase the key_buffer_sizevalue to improve repair speed if there is available memory.
• –sort-recover, -n –  MySQL uses sorting to resolve the indexes, even if the resulting temporary files are very large.
• –character-sets-dir=…  The directory containing the character sets
• –set-character-set=name –  Specifies a new character set for the index
• –tmpdir=path, -t –  Passes a new path for storing temporary files