4.6.2.9 How to Repair Tables - MySQL

The discussion in this section describes how to use myisamchk on MyISAM tables (extensions .MYI and .MYD). If you are using ISAM tables (extensions .ISM and .ISD), you should use isamchk instead; the concepts are similar.

If you are using MySQL 3.23.16 and above, you can (and should) use the CHECK TABLE and REPAIR TABLE statements to check and repair MyISAM tables.

The symptoms of a corrupted table include queries that abort unexpectedly and observable errors such as these:

• tbl_name.frm is locked against change

• Can't find file tbl_name.MYI (Errcode: ###)

• Unexpected end of file

• Record file is crashed

• Got error ### from table handler

To get more information about the error you can run perror ###, where ### is the error number. The following example shows how to use perror to find the meanings for the most common error numbers that indicate a problem with a table:

shell> perror 126 127 132 134 135 136 141

144 145
126 = Index file is crashed / Wrong file

format
127 = Record-file is crashed
132 = Old database file
134 = Record was already deleted (or record

file crashed)
135 = No more room in record file
136 = No more room in index file
141 = Duplicate unique key or constraint on

write or update
144 = Table is crashed and last repair failed
145 = Table was marked as crashed and should

be repaired

Note that error 135 (no more room in record file) and error 136 (no more room in index file) are not errors that can be fixed by a simple repair. In this case, you have to use ALTER TABLE to increase the MAX_ROWS and AVG_ROW_LENGTH table option values:

ALTER TABLE tbl_name MAX_ROWS=xxx

AVG_ROW_LENGTH=yyy;

If you don't know the current table option values, use SHOW CREATE TABLE tbl_name.

For the other errors, you must repair your tables. myisamchk can usually detect and fix most problems that occur.

The repair process involves up to four stages, described here. Before you begin, you should change location to the database directory and check the permissions of the table files. On Unix, make sure that they are readable by the user that mysqld runs as (and to you, because 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.

The options that you can use for table maintenance with myisamchk and isamchk are described in several of the earlier subsections of Section 4.6.2, "Table Maintenance and Crash Recovery."

The following section is for the cases where the above command fails or if you want to use the extended features that myisamchk and isamchk provide.

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 will still be alive for a while after mysqladmin returns, until all queries are stopped and all keys have been flushed to disk.

Stage 1: Checking your 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 down, you should use the --update-state option to tell myisamchk to mark the table as 'checked'.

You have to repair only those tables for which myisamchk announces an error. For such tables, proceed to Stage 2.

If you get weird errors when checking (such as out of memory errors), or if myisamchk crashes, go to Stage 3.

Stage 2: Easy safe repair

Note: 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 or isamchk.

First, try myisamchk -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 following procedure:

1. Make a backup of the data file before continuing.

2. Use myisamchk -r tbl_name (-r means "recovery mode"). This will remove incorrect records and deleted records from the data file and reconstruct the index file.

3. 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 doesn't (but is slower).

If you get weird errors when repairing (such as out of memory errors), or if myisamchk crashes, go to Stage 3.

Stage 3: Difficult repair

You should reach this stage 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's necessary to create a new index file. Do so as follows:

1. Move the data file to some safe place.

2. Use the table description file to create new (empty) data and index files:

   shell> mysql db_name
   mysql> SET AUTOCOMMIT=1;
   mysql> TRUNCATE TABLE tbl_name;
   mysql> quit

3. If your version of MySQL doesn't have TRUNCATE TABLE, use DELETE FROM tbl_name instead.

4. 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. myisamchk -r -q should work now. (This shouldn't be an endless loop.)

As of MySQL 4.0.2, you can also use REPAIR TABLE tbl_name USE_FRM, which performs the whole procedure automatically.

Stage 4: Very difficult repair

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

1. 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 myisamchk -r.

2. 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 .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 Stage 2 and attempt to reconstruct the index file.

4.6.2.10 Table Optimization

To coalesce fragmented records and eliminate wasted space resulting from deleting or updating records, run myisamchk in recovery mode:

shell> myisamchk -r tbl_name

You can optimize a table in the same way by using the SQL OPTIMIZE TABLE statement. OPTIMIZE TABLE does a repair of the table and a key analysis, and also sorts the index tree to give faster key lookups. 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 also has a number of other options you can use to improve the performance of a table:

• -S, --sort-index

• -R index_num, --sort-records=index_num

• -a, --analyze

For a full description of the options, see Section 4.6.2.1, "myisamchk Invocation Syntax."