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

It could be many things, but MySQL Performance Blog mentions bad memory, OS or MySQL bugs that could cause hidden corruption. Also, that and another article mention several things to keep in mind when doing crash recovery.

Answer 2

Usually, it happens when the database is not shut down properly, like a system crash, or hardware problem.

Answer 3

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...

Answer 4

I used to get errors from mysql just like you.

I solved my problems in this way 1) Convert to all myisam tables to InnoDB (you can search "myisam vs InnoDB" in stackoverflow.com and search engines to find out why) 2) For getting best performance from MySQL, use a third-party program MONyog (MySQL Monitor and Advisor) and check performance tips

These two steps saved me. I hope these also help you lot.