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:
For PHP-Nuke, a typical error is MySQL errno 145 (see MySQL errno: 145 Can't open file nuke bbsearch
Could not insert new word matches
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:
to repair the nuke_users table, for example. To only check if the table is corrupt, use the "check table" SQL command:
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
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!