Repairing Database Corruption in MySQL

Ah, the wonderful feeling of being hauled out of the spa/candlelit
dinner/Quake game to be told in panicked tones that there is a ‘database
error’. You rattle off the usual suspects, determining that their keyboard is
actually plugged in, monitor on, monitor’s brightness turned up (yes, it has
happened to me), and that they are actually on the right page. Eventually the
conclusion is inescapable – there is no other alternative, the database really
is behaving strangely.

Some cynics may say it happens more than usual with MySQL, and looking
through the list of MySQL bugfixes is enough to strike terror into the hardiest
of souls. From the MySQL documentation:

Fixed in 4.0.18

  • INSERT DELAYED … SELECT … could cause table corruption
    because tables were not locked properly. This is now fixed by ignoring DELAYED
    in this context. (Bug #1983)

Fixed in 4.0.16

  • Fixed bug in overrun check for BLOB values with compressed
    tables. This was a bug introduced in 4.0.14. It caused MySQL to regard some
    correct tables containing BLOB values as corrupted. (Bug #770, Bug #1304, and
    maybe Bug #1295)

Fixed in 4.0.15

  • Fixed rare bug in
    MYISAM introduced in 4.0.3 where the index file header was not updated directly
    after an UPDATE of split dynamic rows. The symptom was that the table had a
    corrupted delete-link if mysqld was shut down or the table was checked directly
    after the update.

Fixed in 4.0.14

  • Comparison/sorting for latin1_de character set was rewritten. The
    old algorithm could not handle cases like "sä" < "ßa".
    See section 5.6.1.1 German character set. In rare cases, it resulted in table
    corruption.

But then, has anyone seen a SQL-Server buglist recently? Nevertheless, table
corruption should be rare when using MySQL (though an overheating server
continually restarting at the most inopportune times has meant I have seen more
than my fair share of corruption recently, hence the inspiration for the
article). Luckily, MySQL has some easy-to-use tools that can easily repair most
cases of table corruption, and this article introduces you to these. You should
always look at removing the causes of the corruption of course, but this
article only deals with the firefighting aspect – repairing the symptoms.

Identifying table corruption

Table corruption should be relatively easy to identify. Queries that worked
before suddenly stop working, or begin working inconsistently. Your first
suspected culprit would be the code of course, but when a query such as UPDATE
table_x SET x_key=’d’
doesn’t work for no good reason, it’s time to check
the tables. If you see any of the following errors, it is also prudent to check
the tables for corruption:

  • Record file is crashed

  • Unexpected end of file

  • can’t find file tablethatshouldbethere.MYI

  • tablethatwasworking.frm is locked against change

  • Got error ### from table handler.

The latter error returns an error number, and you can get more details about
this error with the perror utility. perror sits in the same
directory as all the other MySQL binaries, such as mysqladmin, mysql
and those we’ll discuss shortly, such as mysqlcheck and myisamchk.
Some of the errors, which often indicate table corruption, include:

  • 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: Tables was marked as crashed and should be repaired

Checking tables

There are three ways to check tables. All of these work with MyISAM tables,
the default, non-transactional table type, and one with InnoDB, the most mature
of the MySQL transactional table types. Fortunately, MySQL now allows you to
check tables while the server is still running, so corruption in a minor table
need not affect everything on the server.

  • The CHECK TABLE SQL statement (obviously the server must
    be running for this)

  • Running the mysqlcheck command-line utility (the server
    can be running)

  • Running the myisamchk command-line utility (the server
    must be down, or the tables inactive)

Checking tables with CHECK TABLE

The first method for checking tables is to run the CHECK TABLE SQL
statement while connected to the server. The syntax is:

CHECK TABLE tablename[,tablename2…] [option][,option2…], for
example:


mysql> CHECK TABLE fixtures;
+————————-+——-+———-+———-+
| Table | Op | Msg_type | Msg_text |
+————————-+——-+———-+———-+
| sports_results.fixtures | check | status | OK |
+————————-+——-+———-+———-+
1 row in set (0.01 sec)

There are a number of options to specify as well, which allow you to do a
more in-depth, or a more superficial kind of check than normal:

QUICK

The quickest option, and does not scan the rows to check
for incorrect links. Often used when you do not suspect an error.

FAST

Only checks tables if they have not been closed properly.
Often used when you do not suspect an error, from a cron, or after a power
failure that seems to have had no ill-effects.

CHANGED

Same as FAST, but also checks tables that have been
changed since the last check.

MEDIUM

The default if no option is supplied. Scans rows to check
that deleted links are correct, and verifies a calculated checksum for all
keys with a calculated a key checksum for the rows.

EXTENDED

The slowest option, only used if the other checks report
no errors but you still suspect corruption. Very slow, as it does a full key
lookup for all keys for every row. Increasing the key-buffer-size
variable in the MySQL config. file can help this go quicker.

Note that CHECK TABLE only works with MyISAM and InnoDB tables. If
CHECK finds corruption, it will mark the table as corrupt, and it will be
unusable. See the Repairing tables section below for how to handle this.

Checking tables with mysqlcheck

The second method is to run the mysqlcheck command-line utility. The
syntax is:
mysqlcheck [options] dbname tablename [tablename2… ].

The following options pertain to checking (mysqlcheck can also repair, as
well as analyze and optimize, which are not covered here).

–auto-repair

Used together with a check option, it will automatically
begin repairing if corruption is found.

–check, -c

Checks tables (only needed if using mysqlcheck under
another name, such as mysqlrepair. See the manual for more details)

–check-only-changed, -C

Same as the CHECK TABLE … CHANGED option above.

–extended, -e

Same as the CHECK TABLE … EXTENDED option above.

–fast, -F

Same as the CHECK TABLE … FAST option above.

–medium-check, -m

Same as the CHECK TABLE … MEDIUM option above.

–quick, -q

Same as the CHECK TABLE … QUICK option above.

For example:


% mysqlcheck -uuser -ppass sports_results fixtures
sports_results.fixtures OK

Note that you can specify multiple tables, and that mysqlcheck
only works with MyISAM tables.

Checking tables with myisamchk

Finally, there is the myisamchk command-line utility. The server must be
down, or the tables inactive (which is ensured if the –skip-external-locking
option
is not in use). The syntax is: myisamchk [options] tablename.MYI,
and you must be in, or specify, the path to the relevant .MYI files (each
MyISAM database is stored in its own directory). These are the available check
options:

–check, -c

The default option

–check-only-changed, -C

Same as the CHECK TABLE … CHANGED option above.

–extend-check, -e

Same as the CHECK TABLE … EXTENDED option above.

–fast, -F

Same as the CHECK TABLE … FAST option above.

–force, -f

Will run the myisamchk repair option if any errors are
found

–information, -i

Display statistics about the checked table

–medium-check, -m

Same as the CHECK TABLE … MEDIUM option above.

–read-only, -T

Does not mark the table as checked

–update-state, -U

This option stores when the table was checked, and the
time of crash, in .MYI file.

For example:


% myisamchk fixtures.MYI
Checking MyISAM file: fixtures.MYI
Data records: 1415 Deleted blocks: 2
– check file-size
– check key delete-chain
– check record delete-chain
– check index reference
– check data record references index: 1
– check record links

You can also use wildcard to check all the .MYI tables at
the same time, for example:

% myisamchk *.MYI

Note that myisamchk only works with MyISAM tables.
For those of you still using the old ISAM table types, there is also isamchk,
though there is really little reason not to upgrade to MyISAM.

Ian Gilfillan
Ian Gilfillan
Ian Gilfillan lives in Cape Town, South Africa. He is the author of the book 'Mastering MySQL 4', published by Sybex, and has been working with MySQL since 1997. These days he develops mainly in PHP and MySQL, although confesses to starting out with BASIC and COBOL, way back when, and still has a soft spot for Perl. He developed South Africa's first online grocery store, and has developed and taught internet development and other technical courses for various institutions. He has majors in Programming and Information Systems, as well as English and Philosophy. For 5 years he was Lead Developer and IT Manager for Independent Online, South Africa's premier news portal. However, he has now 'retired' from fulltime work, and is hoping that his next book will be more in the style of William Blake and Allen Ginsberg.

Latest Articles