Oracle 5.0, Reference Manual

Page: 655 / 3232

How to Repair
MyISAM
Tables
635
•
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 like the previous 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.
7.6.3. How to Repair
MyISAM
Tables
The discussion in this section describes how to use
myisamchk
on
MyISAM
tables (extensions
.MYI
and
.MYD
).
You can also use the
CHECK TABLE
and
REPAIR TABLE
statements to check and repair
MyISAM
tables. See Section 13.7.2.3, “
CHECK TABLE
Syntax” , and Section 13.7.2.6, “
REPAIR TABLE
Syntax” .
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
MySQL error 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 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 ;