Recovering Corrupt Tables : MyISAM Table Crash Recovery

If you have to restore MyISAM tables that have become corrupt, try to recover them using

REPAIR TABLE 
or

myisamchk -r

That should work in 99.9% of all cases. In this article we will cover to repair/recover MyISAM tables using myisamchk.

• MyISAM tables have .MYD and .MYI  files for storing data and indexes. You can use myisamchk to check, repair, or optimize database tables.
• Even though table repair with myisamchk is quite secure, it is always a good idea to make a backup before doing a repair or any maintenance operation that could make a lot of changes to a table.
• myisamchk operations that affect indexes can cause FULLTEXT indexes to be rebuilt with full-text
  parameters that are incompatible with the values used by the MySQL server.
• MyISAM table maintenance can also be done using the SQL statements that perform operations similar to what myisamchk can do:
  •  To check MyISAM tables, use CHECK TABLE
  • To repair MyISAM tables, use REPAIR TABLE
  • To optimize MyISAM tables, use OPTIMIZE TABLE
  • To analyze MyISAM tables, use ANALYZE TABLE

These statements can be used directly or by means of the mysqlcheck client program. One advantage
of these statements over myisamchk is that the server does all the work. With myisamchk, you must
make sure that the server does not use the tables at the same time so that there is no unwanted interaction
between myisamchk and the server.

myisamchk for Crash Recovery

If you run mysqld with external locking disabled (which is the default), you cannot reliably use myisamchk to check a table when mysqld is using the same table. If you can be certain that no one will access the tables through mysqld while you run myisamchk, you only have to execute mysqladmin flushtables before you start checking the tables. If you cannot guarantee this, you must stop mysqld while you check the tables. If you run myisamchk to check tables that mysqld is updating at the same time, you may get a warning that a table is corrupt even when it is not.

If the server is run with external locking enabled, you can use myisamchk to check tables at any time. In
this case, if the server tries to update a table that myisamchk is using, the server will wait for myisamchk
to finish before it continues.

If you use myisamchk to repair or optimize tables, you must always ensure that the mysqld server is not
using the table (this also applies if external locking is disabled). If you do not stop mysqld, you should at
least do a mysqladmin flush-tables before you run myisamchk. Your tables may become corrupted
if the server and myisamchk access the tables simultaneously.

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

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

myisamchk works by creating a copy of the .MYD data file row by row. It ends the repair stage by
removing the old .MYD file and renaming the new file to the original file name. If you use –quick,
myisamchk does not create a temporary .MYD file, but instead assumes that the .MYD file is correct
and generates only a new index file without touching the .MYD file. This is safe, because myisamchk
automatically detects whether the .MYD file is corrupt and aborts the repair if it is.

You can also specify the –quick option twice to myisamchk. In this case, myisamchk does not abort on some errors (such as duplicate-key errors) but instead tries to resolve them by modifying the .MYD 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 of the table before running myisamchk.

myisamchk  : Check MyISAM Tables for Errors

• myisamchk tbl_name : This finds 99.99% of all errors. What it cannot find is corruption that involves only the data file (which is very unusual). If you want to check a table, you should normally run myisamchk without options or with the -s (silent) option.
• myisamchk -m tbl_name : This finds 99.999% of all errors. It first checks all index entries for errors and then reads through all rows. It calculates a checksum for all key values in the rows and verifies that the checksum matches the checksum for the keys in the index tree.
• myisamchk -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 for a large table that has many indexes. Normally, myisamchk stops after the first error it finds. If you want to obtain more information, you can add the -v (verbose) option. This causes myisamchk to keep going, up through a maximum of 20 errors.
• myisamchk -e -i tbl_name : This is same as above command, but the -i option tells myisamchk to print additional statistical information.

 In most cases, a simple myisamchk command with no arguments other than the table name is  sufficient to check a table.

myisamchk : Repair MyISAM Tables

You can also use the CHECK TABLE and REPAIR TABLE statements to check and repair MyISAM tables.

Symptoms of corrupted tables include queries that abort unexpectedly and observable errors such as:-

• tbl_name.frm is locked against change
• Can’t find file tbl_name.MYI (Errcode: nnn)
• Unexpected end of file
• Record file is crashed
• Got error nnn from table handler

If you are going to repair a table from the command line, you must first stop the mysqld server. Note that
when you do mysqladmin shutdown on a remote server, the mysqld server is still available for a while
after mysqladmin returns, until all statement-processing has stopped and all index changes have been
flushed to disk.

Step 1 : Checking tables

• Run myisamchk *.MYI or myisamchk -e *.MYI if you have more time. Use the -s (silent) option to suppress unnecessary information.
• If the mysqld server is stopped, you should use the –update-state option to tell myisamchk to mark
  the table as “checked.”
• To repair only those tables for which myisamchk announces an error. For such tables, proceed to Step 2.
• If you get unexpected errors when checking (such as out of memory errors), or if myisamchk crashes, go to Step 3.

Step 2 : Easy Safe Repair

First, try myisamchk -r -q tbl_name (-r -q means “quick recovery mode”). This attempts 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:

• Make a backup of the data file before continuing.
• Use myisamchk -r tbl_name (-r means “recovery mode”). This removes incorrect rows and deleted rows from the data file and reconstructs the index file.
• If the preceding step fails, use myisamchk –safe-recover tbl_name. Safe recovery mode uses an old recovery method that handles a few cases that regular recovery mode does not (but is slower).
• If you want a repair operation to go much faster, you should set the values of the sort_buffer_size and key_buffer_size variables each to about 25% of your available memory when running myisamchk.
• If you get unexpected errors when repairing (such as out of memory errors), or if myisamchk crashes, go to Step 3.

Step 3 : Difficult Repair

You should reach this step only if the first 16KB block in the index file is destroyed or contains incorrect information, or if the index file is missing. In this case, it is necessary to create a new index file. Do so as follows:

• Move the data file to a safe place.
• Use the table description file to create new (empty) data and index files:                                                            mysql> SET autocommit=1;
  mysql> TRUNCATE TABLE tbl_name;
  mysql> quit
• Copy the old data file back onto the newly created data file. (Do not just move the old file back onto the new file. You want to retain a copy in case something goes wrong.)
• If you are using replication, you should stop it prior to performing the above procedure, since it involves file system operations, and these are not logged by MySQL
• Go back to Step 2. myisamchk -r -q should work. (This should not be an endless loop.)

You can also use the REPAIR TABLE tbl_name USE_FRM SQL statement, which performs the whole procedure automatically. There is also no possibility of unwanted interaction between a utility and the server, because the server does all the work when you use REPAIR TABLE.

Step 4 : Very Difficult Repair

You should reach this step only if the .frm description file has also crashed. That should never happen, because the description file is not changed after the table is created:

• Restore the description file from a backup and go back to Step 3. You can also restore the index file and go back to Step 2. In the latter case, you should start with myisamchk -r.
• If you do not 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, and then move the .frm description and .MYI index files
  from the other database to your crashed database. This gives you new description and index files, but
  leaves the .MYD data file alone. Go back to Step 2 and attempt to reconstruct the index file.

MyISAM Table Optimization

To coalesce fragmented rows and eliminate wasted space that results from deleting or updating rows, run myisamchk in recovery mode:

shell> myisamchk -r tbl_name

• You can optimize a table in the same way by using the OPTIMIZE TABLE SQL statement.
• OPTIMIZE TABLE does a table repair and a key analysis, and also sorts the index tree so that key lookups are 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

myisamchk has a number of other options that you can use to improve the performance of a table:

• –analyze or -a : Perform key distribution analysis. This improves join performance by enabling the join optimizer to better choose the order in which to join the tables and which indexes it should use.
• –sort-index or -S : Sort the index blocks. This optimizes seeks and makes table scans that use indexes faster.
• –sort-records=index_num or -R index_num : Sort data rows according to a given index. This makes your data much more localized and may speed up range-based SELECT and ORDER BY operations that use this index.

Setting Up a MyISAM Table Maintenance Schedule

It is a good idea to perform table checks on a regular basis rather than waiting for problems to occur. One way to check and repair MyISAM tables is with the CHECK TABLE and REPAIR TABLE statements.

Another way to check tables is to use myisamchk. For maintenance purposes, you can use myisamchk -s. The -s option (short for –silent) causes myisamchk to run in silent mode, printing messages only when errors occur.

It is also a good idea to enable automatic MyISAM table checking. For example, whenever the machine has done a restart in the middle of an update, you usually need to check each table that could have been affected before it is used further. (These are “expected crashed tables.”) To cause the server to check MyISAM tables automatically, start it with the –myisam-recover option.

You should also check your tables regularly during normal system operation. For example, you can run a
cron job to check important tables once a week, using a line like this in a crontab file:

35 0 * * 0 /path/to/myisamchk –fast –silent /path/to/datadir/*/*.MYI

To start with, execute myisamchk -s each night on all tables that have been updated during the last 24 hours. As you see that problems occur infrequently, you can back off the checking frequency to once a week or so.

Normally, MySQL tables need little maintenance. If you are performing many updates to MyISAM tables with dynamic-sized rows (tables with VARCHAR, BLOB, or TEXT columns) or have tables with many deleted rows you may want to defragment/reclaim space from the tables from time to time. You can do this by using OPTIMIZE TABLE on the tables in question. Alternatively, if you can stop the mysqld server for a while, change location into the data directory and use this command while the server is stopped:

shell> myisamchk -r -s –sort-index –myisam_sort_buffer_size=16M */*.MYI