How to repair tables.

The file format that MySQL uses to store data has been extensively tested, but there are always instances (like a hard kill on the mysqld process in the middle of a write, a hardware error or a unexpected shutdown of the computer) when some tables may be corrupted.

The sign of a corrupted table is usually when queries abort unexpectedly and one gets errors like:

• table.frm is locked against change.
• Can't find file '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 have to repair your tables. The isamchk external utility can usually detect and fix most things that go wrong. See section The MySQL table check, optimise and repair program.

If you are going to use isamchk on very large files, you should first decide how much memory you want to give to isamchk. More memory gives more speed. For example, if you have more than 32M ram, try:

isamchk -O sortbuffer=16M -O keybuffer=16M -O readbuffer=1M
        -O writebuffer=1M ....

• Part 1: Checking
  • Check the permissions of the table files. Make them readable for the user running mysqld.
  • cd to the database directory.
  • Run 'isamchk *.ISM' or ('isamchk -e *.ISM' if you have more time).
  • You only have to repair those tables for which isamchk gives an error. Use option -s to avoid unnecessary information.
• Part 2: Easy safe repair.
  • If you get weird errors when checking or repairing, such as out of memory errors or if isamchk crashes, go to part 3.
  • Try first 'isamchk -r -q table'. This will try to repair the .ISM file without touching the important data table (.ISD). If the data file (.ISD) contains everything and the delete links point at the right places in the data file, this should work and the table is fixed. Start repairing next table.
  • Make a backup of the data file (table.ISD) before continuing.
  • Use 'isamchk -r table'. This will remove wrong records and deleted records from the data file and reconstruct the index (.ISM) file.
  • If the above fails, use 'isamchk --safe-recover table'. This is a little slower than 'isamchk --recover table' but should work in all cases.
• Part 3:; Hard repair.
  • This should only happen if the first 16K block in the .ISM file is destroyed, contains wrong information or if the .ISM is missing.
  • In this case we have to create a new .ISM file. Do as follows:
  • Move the data file .ISD file to some safe place.
  • Recreate the .ISM file from the .frm file:

    shell> mysql database
    mysql> delete from table_name;
    mysql> quit
    

  • Copy (don't move) the data file (.ISD) back on the newly created .ISD file
  • Go back to Part 2. (This shouldn't be a endless loop). isamchk -r -q should now work.
• Part 4: Very hard repair.
  This can only happen if the descriptor file (.frm) also has crashed. This should never happen, because the .frm file isn't written after the table is created.
  • Restore the .frm file from a backup and go back to Part 3. You can also restore the .ISM file and go back to Part 2. In the latter case you should start with 'isamchk -r'.
  • If you don't have a backup but know exactly how the table was created, create a copy of the table in another database and copy the .frm and .ISM file from there to your crashed database and go back to Part 2.

Go to the first, previous, next, last section, table of contents.