26.1. How to repair a corrupt table

Three things are certain: Death, taxes and lost data. Guess which has occurred.

Occasionally, under circumstances involving high load on the server, multiple INSERTs and UPDATEs, coupled with many SELECTs (see Section 28.5 for the syntax of SQL code), or hardware failure (oh yes...whenever possible, blame the hardware! ), your database server may corrupt a table. This is something it shouldn't happen, but of course this doesn't help you if it does. According to the MySQL manual on Corrupted MyISAM Tables, you can get corrupted tables if some of the following things happens:

The mysqld process being killed in the middle of a write.
Unexpected shutdown of the computer (for example, if the computer is turned off).
A hardware error.
You are using an external program (like myisamchk) on a live table.
A software bug in the MySQL or MyISAM code.

and the typical symptoms for a corrupt table are:

You get the error

Incorrect key file for table: '...'. Try to repair it

while selecting data from the table.

Queries don't find rows in the table or return incomplete data.

For PHP-Nuke, a typical error is MySQL errno 145 (see MySQL errno: 145 Can't open file nuke bbsearch wordmatch.MYI):

Could not insert new word matches
DEBUG MODE
SQL Error : 1016 Can't open file: 'nuke_bbsearch_wordmatch.MYI'. (errno: 145)
INSERT INTO nuke_bbsearch_wordmatch (post_id, word_id, title_match) 
SELECT 4467, word_id, 0 FROM nuke_bbsearch_wordlist WHERE word_text IN ('testing')
Line : 282
File : /xxxx/xxxx/public_html/nuke/includes/functions_search.php

You can use the repair.php script of Section 25.8, or the REPAIR TABLE command from the MySQL prompt, not only to rebuild indexes, but also to generally repair a broken table (see Missing blocks and modules). Normally you should never have to run the REPAIR TABLE command, but if disaster strikes, you are very likely to get back all your data from a MyISAM table with it (if your tables get corrupted a lot, you should try to find the reason for this, to eliminate the need to use REPAIR TABLE, see What To Do If MySQL Keeps Crashing and MyISAM Table Problems). On the MySQL prompt, type:

repair table nuke_users;

to repair the nuke_users table, for example. To only check if the table is corrupt, use the "check table" SQL command:

check table nuke_users;

See REPAIR TABLE Syntax for the syntax of REPAIR TABLE.

If you don't have a ccess to the MySQL prompt, but can access the shell, you can use the myisamchk utility. Again, you can both check and repair a table. To check it, type

myisamchk /path/to/the/table

on the shell's command line. To repair it, use

myisamchk -r /path/to/the/table

If you ever needed the REPAIR TABLE function of MySQL (and went through the torture process of wondering what this might do to your beloved data), then you may be appreciating backups a bit more - see Section 27.16 and backup your database regularly!