MySQL table is marked as crashed

Question

I fairly new to MySQL database. I am repeatedly seeing an error from MySQL saying the table is marked as crashed and should be repaired. However I am able to repair the crashed table by using the command myisamchk. By the way, I am using MYISAM database engine in MySQL.

I just wanted to know under what circumstances would a DB table crash and how I can prevent it from happening again?

I am connecting to MySQL(5.0) database from Tcl (8.5) script using mysqltcl library (3.0).

Answer 1

MyISAM tables are very easy to crash. There is header info in each table that keeps track of how many open file handles a MyISAM table has.

If mysqld crashes, any MyISAM table that had open file handles to it never had the opportunity to decrement the file handle count upon each file handle closing. Thus, if a new file handle opens a MyISAM table (.MYD file) and mysqld discovers a mismatch between the number of file handles a MyISAM table believes is open and the the number of file handles the MyISAM table actually has open, the table is declared crashed.

There are four(4) methods for handling this:

METHOD #1 : Setup automatic MyISAM repair

See my post https://dba.stackexchange.com/a/15079/877 on how to set this up upon a MySQL restart (Mar 15, 2012)

METHOD #2 : Use InnoDB instead of MyISAM

InnoDB has crash recovery built into the Storage Engine's initialization. MyISAM does not

METHOD #3 : Use Aria instead of MyISAM

Aria is MariaDB's drop-in replacement for MyISAM. It features crash recovery mechanisms for individual tables.

METHOD #4 : Don't kill -9 on mysqld

If mysqld crashes, deliberately or involuntarily, header info for all open MyISAM tables will get them into a crashed state. Avoid having to manually kill mysqld.