Flite Careers

MyISAM Crash Recovery, and Triggers

I’ve recently run into a scenario where MyISAM auto-repair does not appear to work in MySQL 5.6. Most of my tables are InnoDB, but having recently upgraded from MySQL 5.5 to 5.6 I still have a handful of MyISAM tables in order to use FULLTEXT indexes since FULLTEXT on InnoDB was not supported in MySQL 5.5. These MyISAM tables are essentially copies of InnoDB tables, and are kept in sync via triggers.

I’m using myisam_recover_options=FORCE,QUICK so I expect any crashed MyISAM tables to be repaired automatically when mysqld tries to open them, but when one of these MyISAM tables crashes the auto-repair does not happen unless I explicitly interact with the table. For example I’ll see an error like this if replication updates a table that triggers an update on a crashed MyISAM table:

2014-06-02 15:01:01 8617 [ERROR] /usr/sbin/mysqld: Table './flite/ad_index' is marked as crashed and should be repaired
2014-06-02 15:01:01 8617 [ERROR] Slave SQL: Error 'Table './flite/ad_index' is marked as crashed and should be repaired' on query. Default database: 'flite'. Query: 'update AD_INDEX set ...

That error stops replication, so I need to repair the relevant MyISAM table before I can resume replication. If I execute an explicit query on that table then mysqld will repair it automatically. For example a query as simple as this leads to auto-repair:

select count(*) from flite.ad_index;

I’m hoping to get rid of these MyISAM tables soon since I can add FULLTEXT indexes on the InnoDB tables now. Once I do that this issue will not affect me any more. Nonetheless it seems to me that auto-repair should handle this scenario, so I entered a bug report.