MySQL Bugs: #68689: mysqldump got error 145 myisam table is marked as crashed and should be repaired

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

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>

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.

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.

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.