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: | |
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
[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.