To check/repair ISAM tables (
.ISD) you should use the
isamchk utility. To check/repair MyISAM tables (
you should use the
10.18 MySQL table types..
In the following text we will talk about
isamchk but everything
also applies to
You can use the
isamchk utility to get information about your database
tables, check and repair them or optimize them. The following sections
describe how to invoke
isamchk (including a description of its
options), how to set up a table maintenance schedule, and how to use
isamchk to perform its various functions.
If you run
--skip-locking (which is the default on
some systems, like Linux), you can't reliably use
check a table when
mysqld is using the same table. If you
can be sure that no one is accessing the tables through
while you run
isamchk, you only have to do
flush-tables before you start checking the tables. If you can't
guarantee the above, then you must take down
mysqld while you
check the tables. If you run
mysqld is updating
the tables, you may get a warning that a table is corrupt even if it
If you are not using
--skip-locking, you can use
to check tables at any time. While you do this, all clients that try
to update the table will wait until
isamchk is ready before
If you use
isamchk to repair or optimize tables, you
MUST always ensure that the
mysqld server is not using
the table (this also applies if you are using
If you don't take down
mysqld you should at least do a
mysqladmin flush-tables before you run
You can in most cases also use the command
OPTIMIZE TABLES to
optimize and repair tables, but this is not as fast or reliable (in case
of real fatal errors) as
isamchk. On the other hand,
OPTIMIZE TABLE is easier to use and you don't have to worry about
isamchk is invoked like this:
shell> isamchk [options] tbl_name
options specify what you want
isamchk to do. They are
described below. (You can also get a list of options by invoking
isamchk --help.) With no options,
isamchk simply checks your
table. To get more information or to tell
isamchk to take corrective
action, specify options as described below and in the following sections.
tbl_name is the database table you want to check. If you run
isamchk somewhere other than in the database directory, you must
specify the path to the file, since
isamchk has no idea where your
database is located. Actually,
isamchk doesn't care whether or not
the files you are working on are located in a database directory; you can
copy the files that correspond to a database table into another location and
perform recovery operations on them there.
You can name several tables on the
isamchk command line if you
wish. You can also specify a name as an index file
name (with the `.ISM' suffix), which allows you to specify all
tables in a directory by using the pattern `*.ISM'.
For example, if you are in a database directory, you can check all the
tables in the directory like this:
shell> isamchk *.ISM
If you are not in the database directory, you can check all the tables there
by specifying the path to the directory:
shell> isamchk /path/to/database_dir/*.ISM
You can even check all tables in all databases by specifying a wildcard
with the path to the MySQL data directory:
shell> isamchk /path/to/datadir/*/*.ISM
isamchk supports the following options:
Analyze the distribution of keys. This improves join performance by
enabling the join optimizer to better choose in which order it should
join the tables and which keys it should use.
Output debug log. The
debug_options string often is
Prints some information about the table.
Check the table VERY thoroughly. This is necessary only in extreme cases.
isamchk should find all errors even without this option.
Overwrite old temporary files.
If you use
-f when checking tables (running
isamchk will automatically restart with
on any table for which an error occurs during checking.
Display a help message and exit.
Print informational statistics about the table that is checked.
-k #, --keys-used=#
-r. Tell the NISAM table handler to update only the first
# indexes. Higher-numbered indexes are deactivated. This can be used
to get faster inserts! Deactivated indexes can be reactivated by using
Do not follow symbolic links when repairing. Normally
isamchk repairs the table a symlink points at.
-r to get a faster repair. Normally, the original data file
isn't touched; you can specify a second
-q to force
the original data file to be used.
Can fix almost anything except unique keys that aren't unique.
Uses an old recovery method; this is slower than
-r, but can
handle a couple of cases that
-r cannot handle.
-O var=option, --set-variable var=option
Set the value of a variable. The possible variables are listed below.
Silent mode. Write output only when errors occur.
You can use
-s twice (
-ss) to make
Sort the index tree blocks in high-low order.
This will optimize seeks and will make table scanning by key faster.
-R index_num, --sort-records=index_num
Sorts records according to an index. This makes your data much more localized
and may speed up ranged
ORDER BY operations on
this index. (It may be VERY slow to do a sort the first time!)
To find out a table's index numbers, use
SHOW INDEX, which shows a
table's indexes in the same order that
isamchk sees them. Indexes are
numbered beginning with 1.
Unpack a table that was packed with
Verbose mode. Print more information. This can be used with
-v multiple times (
-vvv) for more
isamchk version and exit.
Wait if the table is locked.
Possible variables for the
-O) option are:
key_buffer_size current value: 16776192
read_buffer_size current value: 262136
write_buffer_size current value: 262136
sort_buffer_size current value: 2097144
sort_key_blocks current value: 16
decode_bits current value: 9
Memory allocation is important when you run
uses no more memory than you specify with the
-O options. If you are
going to use
isamchk on very large files, you should first decide how
much memory you want it to use. The default is to use only about 3M to fix
things. By using larger values, you can get
isamchk to operate
faster. For example, if you have more than 32M RAM, you could use options
such as these (in addition to any other options you might specify):
shell> isamchk -O sort=16M -O key=16M -O read=1M -O write=1M ...
-O sort=16M should
probably be enough for most cases.
Be aware that
isamchk uses temporary files in
TMPDIR points to a memory file system, you may easily get out of
memory errors. If this happens, set
TMPDIR to point at some directory
with more space and restart