6

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

Donal Fellows
  • 133,037
  • 18
  • 149
  • 215
Manoj Srivatsav
  • 280
  • 2
  • 11

2 Answers2

11

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.

Community
  • 1
  • 1
RolandoMySQLDBA
  • 43,883
  • 16
  • 91
  • 132
  • Thanks a lot!!! I never new about the file handle part for tables. I just setup a MYISAM repair script and works good!!!... Just one more thing, since InnoDB has crash recovery built-in how is the performance of it?. Is there a performance difference between MYISAM and InnoDB? – Manoj Srivatsav Aug 09 '12 at 04:32
  • 1
    Way too many pros and cons to discuss. Try my DBA.SE answers, 1) http://dba.stackexchange.com/a/2194/877, 2) http://dba.stackexchange.com/a/6008/877, 3) http://dba.stackexchange.com/a/17434/877. I hope these help !!! – RolandoMySQLDBA Aug 09 '12 at 04:39
  • Thanks the link helped a lot. For now I will stick with MYISAM with key_buffer_size set to cap and with ALTER TABLE td.tb ROW_FORMAT=Fixed... I would also try to run the recovery scirpt for MYISAM tables from with the code periodically. Thanks a TON!!! you have given me lots of idea to work for... Thanks a lot Rolando!!!!! – Manoj Srivatsav Aug 09 '12 at 05:04
0

I noticed that when I attempt to do a LVM snapshot of my database volume, after running FLUSH TABLES WITH READ LOCK, then rsync that snapshot to a new system, the tables are marked as crashed and have to be repaired.

I suspect this has to do with there being a file handle on the original machine with the table open, and then I'm syncing the that status to the new machine and it sees a mismatch in the file handles and decides it needs to repair.

This repair is problematic because it takes hours (it is a giant table). So the only reliable way to actually get a snapshot that isn't crashed is to shutdown the database before taking the snapshot, but then I cannot get the SHOW MASTER STATUS to setup replication.

basedrum1
  • 1
  • 1