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:
mysqld process being killed in the middle of a write
Unexpected shutdown of the computer (for example, if the computer is turned
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
|File || Purpose
|`tbl_name.frm' || Table definition (form) file
|`tbl_name.ISD' || Data file
|`tbl_name.ISM' || Index file
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
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
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
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
To check a table, use the following commands:
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
isamchk -e tbl_name
This does a complete and thorough check of all data (
``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
-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
print some informational statistics, too.
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.
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
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
isamchk *.ISM or (
isamchk -e *.ISM if you have more time).
-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
memory errors), or if
isamchk crashes, go to Stage 3.
Stage 2: Easy safe repair
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
Make a backup of the data file before continuing.
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.
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
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
Move the data file to some safe place.
Use the table description file to create new (empty) data and index files:
shell> mysql db_name
mysql> DELETE FROM tbl_name;
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.
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
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.
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 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
isamchk also has a number of other options you can use to improve
the performance of a table:
-R index_num, --sort-records=index_num
For a full description of the option see 13.1.1
isamchk invocation syntax.