'myisamchk' and 'isamchk'

These utilities allow you to check and repair damaged tables, display table information, perform index key value distribution analysis, and disable or enable indexes. Chapter 4, "Query Optimization," provides more information on key analysis and index disabling. Chapter 13, "Database Backups, Maintenance, and Repair," provides more information on table checking and repair.

myisamchk is used for tables that use the MyISAM storage format. These tables have data and index filenames with .MYD and .MYI suffixes. Use isamchk for tables that use the older ISAM storage format. These have data and index filenames with .ISD and .ISM suffixes. If you tell either utility to operate on a table of the wrong type, it will print a warning message and ignore the table.

Do not allow the MySQL server to access a table while you're performing checking or repair operations. Chapter 13 discusses how to prevent the server from using a table while myisamchk or isamchk are working on it.

Usage

myisamchk [options] tbl_name[.MYI] ... 
isamchk [options] tbl_name[.ISM] ...

With no options, these utilities check the named tables for errors. Otherwise, the tables are processed according to the meaning of the specified options. If you perform an operation that may modify a table, it's a good idea to make a copy of it first.

A tbl_name argument can be either the name of a table or the name of the index file for the table. (Index files have an extension of .MYI for MyISAM tables or .ISM for ISAM tables.) Using index filenames is convenient because you can use filename wildcards to operate on all tables for a given storage type in a single command. For example, you can check all the MyISAM and ISAM tables in a directory as follows:

% myisamchk *.MYI 
% isamchk *.ISM

These utilities make no assumptions about where table files are located. You must specify the pathname to the files you want to use if they are not in the current directory. Because table files are not assumed to be located under the server's data directory, you can copy table files into another directory and operate on the copies rather than the originals.

Some of the options refer to index numbers. Indexes are numbered beginning with 1. You can issue a SHOW INDEX query or use the mysqlshow--keys command to find out the index numbering for a particular table. The Key_name column lists indexes in the same order that myisamchk and isamchk see them.

Standard Options Supported by isamchk and myisamchk

--character-sets-dir       --help                  --verbose 
--debug                    --set-variable          --version
--default-character-set    --silent

--default-character-set is available only for isamchk. The --silent option means that only error messages are printed. The --verbose option prints more information when given with the --description or --extend-check options for either program, or when given with the --check option for myisamchk. The --silent and --verbose options can be specified multiple times for increased effect.

Options Common to myisamchk and isamchk

isamchk and myisamchk have many options in common:

• --analyze, -a

  Perform key distribution analysis. This can help the server perform index-based lookups and joins more quickly. You can obtain information about key distribution after the analysis by running isamchk or myisamchk again with the --description and --verbose options.

• --block-search=n, -b n

  Print out the start of the table row that contains a block starting at block n., This is for debugging only.

• --description, -d

  Prints descriptive information about the table.

• --extend-check, -e

  Perform an extended table check. It should rarely be necessary to use this option because myisamchk and isamchk normally find any errors with one of the less extensive checking modes.

• --force, -f

  Force a table to be checked or repaired even if a temporary file for the

  table already exists. Normally, myisamchk and isamchk simply exit after printing an error message if they find a file named tbl_name.TMD because that may indicate another instance of the program is already running. However, the file may also exist if you killed a previous invocation of the program while it was running, in which case the file can be removed safely. If you know that to be the case, use --force to tell either utility to run even if the temporary file exists. (Alternatively, you can remove the temporary file manually.)

  If you use --force when checking tables, the program automatically restarts with --recover for any table found to have problems. In addition, as of MySQL 4.0.2, myisamchk will update the table state the same way the --update-state option does.

• --information, -i

  Print statistical information about table contents.

• --keys-used=n, -k n

  Used with --recover., For isamchk, tells MySQL to update only the first n indexes. In other words, this de-activates indexes numbered higher than n. For myisamchk, n is a bitmask that indicates which indexes to use. The first index is bit zero. In either case, specifying a value of 0 turns off all indexes. This can be used to improve the performance of INSERT, DELETE, and UPDATE operations. Turning the indexes back on restores normal indexing behavior. (For isamchk, specify an index number equal to the highest-numbered index; for myisamchk, specify a bitmask that includes a bit for each index.)

• --no-symlinks, -l

  If a table argument is a symbolic link, normally the table the symlink points to is repaired. When this option is specified, symlinks are not followed. Instead the symlink is replaced with the new (repaired) version of the file.

  For myisamchk, this option is unavailable as of MySQL 4 because it will not remove symlinks during repair operations.

• --quick, -q (boolean)

  This option is used in conjunction with --recover for faster repair than when --recover is used alone. The data file is not touched when both options are given. To force the program to modify the data file if duplicate key values are found, specify the --quick option

  twice.

• --recover, -r

  Perform a normal recovery operation. This can fix most problems except the occurrence of duplicate values in an index that should be unique.

• --safe-recover, -o

  Use a recovery method that is slower than the --recover method, but that can fix a few problems that --recover cannot. --safe-recover also uses less disk space than --recover.

• --sort-index, -S

  Sort the index blocks to speed up sequential block reads for subsequent retrievals.

• --sort-records=n, -R n

  Sort data records according to the order in which records are listed in index n., Subsequent retrievals based on the given index should be faster. The first time you perform this operation on a table, it may be very slow because your records will be unordered. If you have MySQL 3.23.28 or later, ALTER TABLE ... ORDER BY accomplishes the same thing as --sort-records, and normally will be faster.

• --unpack-u

  Unpack a packed file. myisamchk can unpack MyISAM files packed with myisampack; isamchk can unpack ISAM files packed with pack_isam., This option can be used to convert a compressed read-only table to modifiable form. It cannot be used with --quick or with --sort-records.

• --wait, -w

  If a table is locked, wait until it is available. Without --wait, the program will wait 10 seconds for a lock and then print an error message if no lock can be obtained.

Options Specific to myisamchk

Although there are no options specific to isamchk, there are several specific to myisamchk.

• --backup, -B

  For options that modify the data (.MYD) file, make a backup using a filename of the form tbl_name-time.BAK. time is a number representing a timestamp. The backup file is written to the directory where the table files are located. This option was introduced in MySQL 3.23.25, the same version in which the BACKUP TABLE statement appeared, but the option does not have the same function as that statement.

• --check, -c

  Check tables for errors. This is the default action if no options are specified.

• --check-only-changed, -C

  Check tables only if they have not been changed since the last check. This option was introduced in MySQL 3.23.22. Prior to that, use --fast instead.

• --correct-checksum

  For tables created with the CHECKSUM = 1 option, ensure that the checksum information in the table is correct. This option was introduced in MySQL 4.0.0.

• --data-file-length=n, -D n

  The maximum length to which the data file should be allowed to grow when rebuilding a data file that has become full. (This occurs when a file reaches the size limit imposed by MySQL or by the file size constraints of your operating system. It also occurs when the number of rows reaches the limit imposed by internal table data structures.) The value is specified in bytes. This option is effective only when used with --recover or --safe-recover.

• --fast, -F

  Check tables only if they have not been closed properly. Prior to MySQL 3.23.22, this option acts like --check-only-changed; that is, tables are checked only if they have been modified since they were last checked.

• --medium-check, -m

  Check a table using a method that is faster than --extend-check, but slightly less thorough. (The myisamchk help message says that this method finds "only" 99.99% of all errors.) This checking mode should be sufficient for most circumstances. Medium check mode works by calculating CRC values for the keys in the index and comparing them with the CRC values calculated from the indexed columns in the data file.

• --parallel-recover, -p

  Perform recovery as for --recover, but rebuild the indexes in parallel using multiple threads. This can be faster than a non-parallel rebuild, but this option should be considered experimental. This option was introduced in MySQL 4.0.2.

• --read-only, -T

  Do not mark the table as having been checked.

• --set-auto-increment[=n], -A[n]

  Set the AUTO_INCREMENT counter so that subsequent sequence values start at n (or at a higher value if the table already contains records with AUTO_INCREMENT values as large as n). If no value is specified, this option sets the next AUTO_INCREMENT value to one greater than the current maximum value stored in the table.

  If n is specified after -A, there must be no intervening space or the value will not be interpreted correctly.

• --set-character-set=charset

  When rebuilding indexes, use the collating order of the given character set to determine the order of index entries. This is useful for reordering indexes of MyISAM tables after changing the server's default character set and normally is used in conjunction with --recover and --quick. This option was introduced in MySQL 3.23.14.

• --sort-recover, -n

  Force sorted recovery even if the temporary file necessary to perform the operation would become quite large. This option was introduced in MySQL 3.23.22.

• --start-check-pos=n

  Begin reading the data file at position n. This option was introduced in MySQL 3.23.25. It is used only for debugging.

• --tmpdir=dir_name, -t dir_name

  The pathname of the directory to use for temporary files. The default is the value of the TMPDIR environment variable or /tmp if that variable is not set. As of MySQL 4.1, the option value can be given as a list of directories to be used in round-robin fashion. Under UNIX, separate directory names by colons; under Windows, separate them by semicolons.

• --update-state, -U

  Update the internal flag that is stored in the table to indicate its state. Tables that are okay are marked as such, and tables for which an error occurs are marked as in need of repair. Using this option makes subsequent invocations of myisamchk with the --check-only-changed option more efficient for tables that are okay. This option was introduced in MySQL 3.23.14.

Variables for myisamchk and isamchk

The following isamchk and myisamchk variables can be set using the instructions in the "Setting Program Variables" section earlier in this appendix.

• key_buffer_size

  The size of the buffer used for index blocks.

• read_buffer_size

  The read buffer size.

• write_buffer_size

  The write buffer size.

• sort_buffer_size

  The size of the buffer used for key value sorting operations. (This is used for --recover, but not for --safe-recover.)

• sort_key_blocks

  This variable is related to the depth of the B-tree structure used for the index. You should not need to change it.

• decode_bits

  The number of bits to use when decoding compressed tables. Larger values may result in faster operation but will require more memory. Generally, the default value is sufficient.

The following variables can be set for myisamchk only. They were added in MySQL 4.0.0.

• ft_max_word_len

  The maximum length of words that can be included in a FULLTEXT index. Longer words are ignored.

• ft_max_word_len_for_sort

  The length of words that are considered short enough to be inserted into a FULLTEXT index using a more efficient optimized algorithm. Longer words are inserted more slowly.

• ft_min_word_len

  The minimum length of words that can be included in a FULLTEXT index. Shorter words are ignored.

• myisam_block_size

  The block size used for index blocks.