Fast Table Repairs for myisam

Linux
2 Comments

SQL table corruption can happen from time to time for a number of reasons. As we have scaled ExchangeDefender, I ran into a number of situations in which the data inserts were incomplete, or some maintenance tasks (large delete queries) crashed the table. This in turn would affect processes that produced reports and BI based on that data and really create a ton of pain and complaints.

So how does one repair crashed myisam tables?

myisamchk -r /var/lib/mysql/database/table.MYI

This process rebuilds the indexes and the entire table, but is very reactive in nature, you have to be aware of the crashed table in order to repair it.

There is a more maintenance-conscious process called mysqlcheck:

mysqlcheck –auto-repair -A -u dbuser -p database

This is a very thorough (read: slow as @#%@) process that will check all your tables and at the end of it proceed with the repair. Fantastic. Unless you have thousands and thousands of tables that you want to check quickly. This process on our dev box took a day and a half to run. Here is one that executes in less than 10 minutes in just a few lines of php:

$myquery = mysql_query(“show keys from $table”);
if(mysql_error() != “”) { backup / repair / report / log }

Stick that into a loop that goes through all your tables and you’re set.

2 Responses to Fast Table Repairs for myisam

  1. Chris Knight says:

    This is what I’d call an ugly hack.

    The solution would be to swap out the crap DBMS (or in MySQL’s case, the crap storage engine) for something that works. If *basic* database operations – and it doesn’t get more basic than insert or delete – are corrupting your data, then you’re using the wrong DBMS (or storage engine), if you could even call something that crappy a DBMS.

    Unfortunately in the free space your choice is limited. PostgreSQL has FTS scaling issues and FirebirdSQL sucks all your CPU to name two of the more well known free DBMSes and their obvious shortcomings.

    I think the proper fix is rm -rf /var/lib/mysql 🙂

  2. vlad says:

    Yeah, yeah, call when you have something realistic 🙂

    -Vlad

Comments are closed.