Bug #68689 mysqldump got error 145 myisam table is marked as crashed and should be repaired
Submitted: 16 Mar 2013 5:21 Modified: 18 Mar 2013 10:26
Reporter: Paolo Pedrazzoli Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.5.9-enterprise-commercial-advanced-log OS:Linux (Red Hat Enterprise Linux Server release 5.6 (Tikanga))
Assigned to: CPU Architecture:Any
Tags: 145, crashed, error, myisam, mysqldump

[16 Mar 2013 5:21] Paolo Pedrazzoli
Description:
While mysqldump runs on MyISAM table used by application, the table crashed and must be repaired.

MYSQLDUMP LOG:

# cat backup_full.log 
20130316 043002: Starting mysqldump...
20130316 043002: Backup ID = 805
mysqldump: Couldn't execute 'show create table `assets`': Table './videoplatform/assets' is marked as crashed and should be repaired (145)
20130316 043012: Backup failed! Error in executing mysqldump

This problem cause a loss of service due to the unavailable crashed table.

How to repeat:
mysqldump --user=backupuser --password=xxxxxxxxxxx --host=myhost --port=myport --single-transaction --flush-logs --master-data=2 --all-databases

Suggested fix:
repair table <tablename>
[16 Mar 2013 5:48] MySQL Verification Team
Sorry but this is not a fault of mysqldump.  The table was corrupted by some previous incident, such as improper mysqld shutdown, crash, etc.

The course of action to consider is using a storage engine that can handle crash recovery.  Or, if MyISAM is required, add "myisam-recover=backup" to my.cnf so that repair table will be executed automatically if it is found to be marked as crashed on first access.
[16 Mar 2013 5:50] MySQL Verification Team
Just to be clear, myisam corruption is often an expected outcome.

If you can provide some SQL testcase that will cause a corruption under "normal" operations after running on a clean table, then we can consider a possible MyISAM storage engine bug here.
[18 Mar 2013 10:26] Paolo Pedrazzoli
Hi Shane, 
The table crash occurred at the same moment that of the mysqldump execution, hence my conclusion.
Here is a copy of the crontab of my backup server:

[root@mysqlmngmi-ese01 ~]# cat /etc/crontab  | grep -i videoplatform
# Backup TC - RW  +  Brightcove (BRTCV) + videoplatform (Videoportale)
30 4 * * * mysql /usr/local/sbin/mysql_entbackup/mysql_full_backup_EVO.sh esermw3sql-vip backup-esermw3sql 'ugcfcs mediacore BRTCV videoplatform DWLCONT wordpress'

this crontab runs the following mysqldump

mysqldump --user=$MYBACKUPUSR --password=$MYBACKUPPASSWD --host=$MYHOST --port=$MYPORT --single-transaction --flush-logs --master-data=2 --databases $MYDATABASESLIST > $MYBACKUPFILE 2>>$MYBACKUPLOG

and here is the error log of the mysql server:

[root@esermw3sql01 tmp]# cat error.log.2 | grep -i crashed | more
130316  4:30:03 [ERROR] /usr/sbin/mysqld: Table './videoplatform/assets' is marked as crashed and should be repaired

the mysqldump execution and the crash of the table are both at 04:30 AM so I deduce a correlation.

However I will add "myisam-recover=backup" to my.cnf in order to solve future crash problems.

Many thanks.