How can I fix MyISAM tables?

How can I fix MyISAM tables?

Translation: http://dev.mysql.com/doc/refman/5.0/en/myisam-repair.html

You can use the check table and repair table commands to CHECK and repair myisam tables.

Symptoms of Table Corruption include unpredictable exit and obvious errors, as shown below:

L tbl_name.frm cannot be changed because it is locked

L The tbl_name.MYI file cannot be found.

L unknown end of File

L Log File Crash

L error nnn obtained from the table handle

For more error information, run perror nnn. Here nnn is the error code. The following example shows how to use perror to find the meaning of the most common error codes of a table:

Shell> perror 126 127 132 134 135 136 141 144 145

Mysqlerror code 126 = Index file is crashed

MySQL error code 127 = Record-file is crashed

MySQL Error Code 132 = old database file

MySQL error code 134 = record was already deleted (or record file crashed)

MySQL error code 135 = no more room in record file

MySQL error code 136 = no more room in index file

MySQL error code 141 = duplicate unique key or constraint on write or update

MySQL Error Code 144 = table is crashed and last repair failed

MySQL error code 145 = table was marked as crashed and shoshould be retried red

Note: errors 135 (no space for record files) and errors 136 (no space for index files) cannot be easily fixed and fixed. In this case, you must use alert table to add the max_rows and avg_row_length options:

Alter table tbl_name max_rows = xxx avg_row_length = YYY;

If you do not know the option value of the current table, use show create table.

For other errors, You must fix your table. Myisamchk can detect and fix most problems encountered.

The repair process involves four steps, which are described here. Before starting the repair, you must change the current directory to the database directory and check the file permissions. In Unix systems, the user running mysqld needs to determine whether these files are readable. If you need to modify these files, make sure they are writable.

This section describes how to troubleshoot a checklist failure or use the extended features provided by myisamchk.

Myisamchk can be set to control the memory allocation to improve performance.

If you want to repair a table through the command line, you must first stop the mysqld service. Note that when you use mysqladmin shutdown on a remote server, the mysqld service is still available for a short period of time after mysqladmin returns, until all command processing has been stopped and all indexes have been written to the disk.

Step 1: Check the table

Run myisamchk *. myi. If you have more time, you can also run MyISAM-E *. myi. Use the-s (slient) option to compress unnecessary information.

If the mysqld service is stopped, you can use the -- Update-State option to tell myisamchk to mark that the table has been checked.

Myisamchk declares that the table you want to fix is the same error. For these tables, the processing goes to step 2.

During the check, if you get an unknown error (such as an error with insufficient memory) or myisamchk crashes, move to step 3.

Step 2: simple table repair

First, try myisamchk-r-q tbl_name (-r-q indicates the fast recovery mode ). This is an index file that is not exposed to data files. In the data file, it contains all the Delete Connection Points in the data file under the correct directory. This should be normal and the data table will be repaired. Start to repair the next data table. Otherwise, use the following steps:

1. Make sure to back up the data file before resuming the restoration.

2. Use myisamchk-r tbl_name (-r indicates recovery mode ). This will delete the wrong rows, delete these wrong rows from the data file, and reconstruct the index file.

3. If the previous step fails, use myisamchk -- safe-recover tbl_name. The security recovery mode uses the old recovery mode to handle situations that cannot be handled in some conventional modes.

Note: If you want to resume the operation to run faster, when running myisamchk, you should set the sort_buffer_size value and the value of the key_buffer_size variable. Each value should be about 25% of your available memory.

During the check, if you get an unknown error (such as an error with insufficient memory) or myisamchk crashes, move to step 3.

Step 3: difficult to fix

When the 16 KB block at the beginning of the index file is damaged or contains error information or the index file is lost, you should proceed to step 3. In this case, you must create a new index file. Follow the instructions below:

1. Move data files to a safe place.

2. Use the table description file to create a new data and index file.

Shell> mysql db_name

Mysql> SET autocommit = 1;

Mysql> truncate table tbl_name;

Mysql> quit

3. Copy the old data file to the new data file (do not just put the old file back into the new file, you must keep a copy to avoid errors ).

Important: if you are using replication, you should stop executing the previous steps because it involves the file system and MySQL does not record these steps.

Back to step 2, myisamchk-r-q should work properly (this is the last step ).

You can use the repair table tbl_name USE_FRM SQL command to automatically execute the entire step. There is no useless interaction between applications and services, because the server does not do all the work when you use repair table.

Step 4: more difficult to fix

This step is performed only when there is a conflict in the. frm description file. This should never happen because the description file will not change after the table is created.

1. Restore the description file with a backup file and return to step 3. You can re-store the index file and return the second step. In the latter case, you should start myisamchk-r.

2. If you do not have a backup, but you know exactly how to create a table, you can create a copy of the table in another database. Remove the new data file, and then move the. frm description file and. MYI index file from another database to your crashed database. This will give you a new description and index file, but only the. MYD data file is left. Return to step 2 and try to reconstruct the index file.