13.4.2 Comment réparer une table

The symptoms of a corrupted table are usually that requêtesabort unexpectedly and that you observe errors such as these:

  • `nom_table.frm' is locked against change
  • Can't find file `nom_table.ISM' (Errcode: ###)
  • Got error ### from table handler (Error 135 is an exception in this case)
  • Unexpected end of file
  • Record file is crashed

In these cases, you must repair your tables. isamchk can usually detect and fix most things that go wrong.

The repair process involves up to four stages, described below. Before you begin, you should cd to the database directory and check the permissions of the table files. Make sure they are readable by the Unix user that mysqld runs as (and to you, since you need to access the files you are checking). If it turns out you need to modify files, they must also be writable by you.

Stage 1: Checking your tables

Run isamchk *.ISM or (isamchk -e *.ISM if you have more time). Use the -s (silent) option to suppress unnecessary information.

You have to repair only those tables for which isamchk announces an error. For such tables, proceed to Stage 2.

If you get weird errors when checking (such as out of memory errors), or if isamchk crashes, go to Stage 3.

Stage 2: Easy safe repair

First, try isamchk -r -q nom_table (-r -q means ``quick recovery mode''). This will attempt to repair the index file without touching the data file. If the data file contains everything that it should and the delete links point at the correct locations within the data file, this should work and the table is fixed. Start repairing the next table. Otherwise, use the following procedure:

  1. Make a backup of the data file before continuing.
  2. Use isamchk -r nom_table (-r means ``recovery mode''). This will remove incorrect records and deleted records from the data file and reconstruct the index file.
  3. If the preceding step fails, use isamchk --safe-recover nom_table. Safe recovery mode uses an old recovery méthode that handles a few cases that regular recovery mode doesn't (but is slower).

If you get weird errors when repairing (such as out of memory errors), or if isamchk crashes, go to Stage 3.

Stage 3: Difficult repair

You should only reach this stage if the first 16K block in the index file is destroyed or contains incorrect information, or if the index file is missing. In this case, it's necessary to create a new index file. Do so as follows:

  1. Move the data file to some safe place.
  2. Use the table description file to create new (empty) data and index files: 
    shell> mysql nom_base_de_donnees
mysql> DELETE FROM nom_table;
mysql> quit
  3. Copy the old data file back onto the newly created data file. (Don't just move the old file back onto the new file; you want to retain a copy in case something goes wrong.)

Go back to Stage 2. isamchk -r -q should work now. (This shouldn't be an endless loop).

Stage 4: Very difficult repair

You should reach this stage only if the description file has also crashed. That should never happen, because the description file isn't changed after the table is created.

  1. Restore the description file from a backup and go back to Stage 3. You can also restore the index file and go back to Stage 2. In the latter case, you should start with isamchk -r.
  2. If you don't have a backup but know exactly how the table was created, create a copy of the table in another database. Remove the new data file, then move the description and index files from the other database to your crashed database. This gives you new description and index files, but leaves the data file alone. Go back to Stage 2 and attempt to reconstruct the index file.