Checking a table for errors.

If MySQL crashed (for example if the computer is turned off) when all data is not written to disk the tables may have become corrupted. To check a table use:

isamchk table_name

This finds 99.99 % of all errors. What it can't find is when only the data file has been corrupted.

isamchk -e table_name

This goes a complete and through check of all data. It does a check-read of all keys for every row to check that this indeed points to the right row. This may take a LONG time on a big tables with many keys. isamchk will normally stop after the first found error. If you want more information you can add the --verbose (-v) switch to isamchk. In normal usage a simple 'isamchk' is safe enough!

isamchk -e -i table_name

As the above but it also prints some statistics.

We at TcX run a cron job on all our important tables once a week.

35 0 * * 0 /path/to/isamchk -s /path/to/dbs/*/*.ISM

This prints out any crashed tables so we can go and examine and repair them when needed.

As we haven't had any unexpected crashes (without hardware trouble) tables for a couple of years now (this is really true), once a week is more than enough for us.

Of course, whenever the machine has done a reboot in the middle of a update one usually has to check all the tables that could have been affected. (This is an 'expected crashed table')

We recommend that to start with, one should do a isamchk -s on all updated tables each night until one comes to trust MySQL as much as we do.

Naturally, one could add a check to safe_mysql that, if there is an old pid file left after a reboot, it should check all tables that have been modified the last 24 hours.

Go to the first, previous, next, last section, table of contents.