Corrupted Table Data

CHECK TABLE table; REPAIR TABLE table;

Occasionally the MySQL data files might become corrupt and need repairing. The easiest way to check the integrity of a table is by using the CHECK TABLE SQL command. The following command checks the book table for errors:

CHECK TABLE book; 

If the output looks similar to the following, the table is okay and does not need any further action:

+-------------+-------+----------+----------+ | Table       | Op    | Msg_type | Msg_text | +-------------+-------+----------+----------+ | sampdb.book | check | status   | OK       | +-------------+-------+----------+----------+ 

Sometimes, however, you see errors in the Msg_text column telling you about problems with the table. When this happens, issue the REPAIR TABLE command, and MySQL attempts to fix the problem.

The myisamchk utility that ships with MySQL can be used to check and repair MyISAM tables with a higher degree of flexibility than the SQL commands. When myisamchk is run with no options, it simply checks a table for errors. A number of command line switches can be added to gain additional information or instruct the utility to begin a repair process.

The MyISAM table files that myisamchk examines are stored in your data directory and have .MYI file extensions. If your data directory is /var/lib/mysql, you can check all the tables in the sampdb database using the following command:

myisamchk /var/lib/mysql/sampdb/*.MYI 

The usual way to perform a quick table check is to use the --fast switch, which checks only tables that have not been properly closed.

The --medium-check option performs a more thorough check of the tables and finds the vast majority of errors. The --extend-check option is the most thorough option but is very slowonly use this if --medium-check does not find a problem.

After you have determined that a table file is corrupt, you can repair it using the --recover switch. Before repairing a table with myisamchk, you should stop mysqldyou do not want the MySQL server writing to your table files while you repair them.

Sometimes myisamchk reports that it cannot fix a problem using this option and then you should try using --safe-recover instead. This option is much slower but also more thorough.