Repairing Database Corruption in MySQL
January 21, 2004
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
Fixed in 4.0.16
Fixed in 4.0.15
Fixed in 4.0.14
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:
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:
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.
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:
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
The following options pertain to checking (mysqlcheck can also repair, as well as analyze and optimize, which are not covered here).
% 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:
% 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.