(mysql.info) repair
Info Catalog
(mysql.info) check
(mysql.info) table-maintenance
(mysql.info) table-optimization
5.10.4.3 How to Repair Tables
.............................
The discussion in this section describes how to use `myisamchk' on
`MyISAM' tables (extensions `.MYI' and `.MYD').
You can also (and should, if possible) use the `CHECK TABLE' and
`REPAIR TABLE' statements to check and repair `MyISAM' tables. See
check-table, and repair-table.
Symptoms of corrupted tables 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: NNN)
* Unexpected end of file
* Record file is crashed
* Got error NNN from table handler
To get more information about the error, run `perror' NNN, where NNN 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 must 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 do not know the current table option values, use `SHOW CREATE
TABLE'.
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.
This section is for the cases where a table check fails (such as those
described in check), or you want to use the extended features
that `myisamchk' provides.
The options that you can use for table maintenance with `myisamchk' are
described in myisamchk.
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 alive for a
while after `mysqladmin' returns, until all statement-processing has
stopped and all index changes 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 stopped, 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 unexpected errors when checking (such as `out of memory'
errors), or if `myisamchk' crashes, go to Stage 3.
*Stage 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:
1. Make a backup of the data file before continuing.
2. 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.
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 does not (but is
slower).
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'.
If you get unexpected 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 is necessary to create a new index
file. Do so as follows:
1. Move the data file to a 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. 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.)
Go back to Stage 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'. See
repair-table.
*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
is not 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 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 Stage 2 and attempt to reconstruct the
index file.
Info Catalog
(mysql.info) check
(mysql.info) table-maintenance
(mysql.info) table-optimization
automatically generated byinfo2html