13.4 Using isamchk for crash recovery

The file format that MySQL uses to store data has been extensively tested, but there are always external circumstances that may cause database tables to become corrupted:

• The mysqld process being killed in the middle of a write
• Unexpected shutdown of the computer (for example, if the computer is turned off)
• A hardware error

This chapter describes how to check for and deal with data corruption in MySQL databases. If your tables get corrupted a lot you should try to find the reason for this! G.1 Debugging a MySQL server

When performing crash recovery, it is important to understand that each table tbl_name in a database corresponds to three files in the database directory:

Each of these three file types is subject to corruption in various ways, but problems occur most often in data files and index files.

isamchk works by creating a copy of the `.ISD' (data) file row by row. It ends the repair stage by removing the old `.ISD' file and renaming the new file to the original file name. If you use --quick, isamchk does not create a temporary `.ISD' file, but instead assumes that the `.ISD' file is correct and only generates a new index file without touching the `.ISD' file. This is safe, because isamchk automatically detects if the `.ISD' file is corrupt and aborts the repair in this case. You can also give two --quick options to isamchk. In this case, isamchk does not abort on some errors (like duplicate key) but instead tries to resolve them by modifying the `.ISD' file. Normally the use of two --quick options is useful only if you have too little free disk space to perform a normal repair. In this case you should at least make a backup before running isamchk.

13.4.1 How to check tables for errors

To check a table, use the following commands:

isamchk tbl_name

This finds 99.99% of all errors. What it can't find is corruption that involves ONLY the data file (which is very unusual). If you want to check a table, you should normally run isamchk without options or with either the -s or --silent option.

isamchk -e tbl_name

This does a complete and thorough check of all data (-e means ``extended check''). It does a check-read of every key for each row to verify that they indeed point to the correct row. This may take a LONG time on a big table with many keys. isamchk will normally stop after the first error it finds. If you want to obtain more information, you can add the --verbose (-v) option. This causes isamchk to keep going, up through a maximum of 20 errors. In normal usage, a simple isamchk (with no arguments other than the table name) is sufficient.

isamchk -e -i tbl_name

Like the previous command, but the -i option tells isamchk to print some informational statistics, too.

13.4.2 How to repair tables

The symptoms of a corrupted table are usually that queries abort unexpectedly and that you observe errors such as these:

• `tbl_name.frm' is locked against change
• Can't find file `tbl_name.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 tbl_name (-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 tbl_name (-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 tbl_name. Safe recovery mode uses an old recovery method 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 db_name
   mysql> DELETE FROM tbl_name;
   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.

13.4.3 Table optimization

To coalesce fragmented records and eliminate wasted space resulting from deleting or updating records, run isamchk in recovery mode:

shell> isamchk -r tbl_name

You can optimize a table in the same way using the SQL OPTIMIZE TABLE statement. OPTIMIZE TABLE is easier, but isamchk is faster. There is also no possibility of unwanted interaction between a utility and the server, because the server does all the work when you use OPTIMIZE TABLE.

isamchk also has a number of other options you can use to improve the performance of a table:

-S, --sort-index

-R index_num, --sort-records=index_num

-a, --analyze

For a full description of the option see 13.1.1 isamchk invocation syntax.