Learning MySQL

Checking and Repairing Corrupted Tables

Problems such as running out of disk space or a power failure could cause your databases files to be corrupted; in these cases, the server will often not have written all transactions to disk. It’s a good idea to check the tables before you start to use them again. Repairing tables will not guarantee that no data will be lost, but it does allow you to use the database again without losing any more data.

One way to check and repair tables is to use the CHECK TABLE and REPAIR TABLE commands from the monitor. For example, to check the artist table in the music database, you would write:

mysql> CHECK TABLE music.artist;
+--------------+-------+----------+------------------------------+
| Table        | Op    | Msg_type | Msg_text                     |
+--------------+-------+----------+------------------------------+
| music.artist | check | error    | Checksum for key:  1 doesn't |
|              |       |          |   match checksum for records |
| music.artist | check | error    | Corrupt                      |
+--------------+-------+----------+------------------------------+
2 rows in set (0.00 sec)

In this example, the table is damaged; you can repair it using the REPAIR TABLE command:

mysql> REPAIR TABLE music.artist;
+--------------+--------+----------+----------+
| Table        | Op     | Msg_type | Msg_text |
+--------------+--------+----------+----------+
| music.artist | repair | status   | OK       |
+--------------+--------+----------+----------+
1 row in set (0.00 sec)

If the music database was previously selected with the USE music