What causes tables to need to be repaired?

Question

Every so often I get an error saying one of my tables "is marked as crashed and should be repaired". I then do a REPAIR TABLE and repair it. What causes them to be marked as crashed and how can I prevent it? I am using MyISAM tables with MySQL 5.0.45.

Answer 1

There can be a few reasons tables get corrupted, it is discussed in detail in the manual.

To combat it, the following things work best:

1. Make sure you always MySQL shutdown properly
2. Consider using --myisam-recover option to automatically check/repair your tables in the event that shutdown wasn't done properly
3. Make sure you are on the most recent versions as known corruption bugs are normally fixed ASAP
4. Double check your hardware with a test to see if it is causing problems. Tools like sysbench and memtest86 can often help verify if things are working as they should.
5. Make sure nothing is touching the data directory externally, such as virus checkers, backup programs, etc...