MySQL 查询后插入或更新导致表损坏解决方法

MySQL 查询后插入或更新导致表损坏解决方法

影响版本 MySQL 5.0 版本在5.0.70以下;MySQL 5.1 版本在5.1.28 以下出现过程

/*CREATE TABLE*/ CREATE TABLE `certuserid` ( `certUserID` int(11) NOT NULL auto_increment, `userID` int(11) NOT NULL, `certificateID` int(11) NOT NULL, `usertypeID` int(11) NOT NULL, `createdDatetime` datetime NOT NULL, PRIMARY KEY (`certUserID`) ) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC /*INSERT*/ INSERT INTO certuserid ( userID, certificateID, userTypeID, createdDatetime ) VALUES ( 5, 7, 6, '20080903014257' ) /*SELECT*/ SELECT * FROM certuserid /*ERROR CODE */ Error Code : 1194 Table 'certuserid' is marked as crashed and should be repaired (0 ms taken)

错误消息：Table 'certuserid' is marked as crashed and should be repaired

检查表提示： Found too long record (26) at 0

修复表的时候提示

Table Op Msg_type Msg_text -------------- ------ -------- ---------------------------------------- auc.certuserid repair info Wrong bytesec: 3- 0- 26 at 0; Skipped auc.certuserid repair status Operation failed

解决方法：去掉创建表的时候的 ROW_FORMAT=DYNAMIC 错误解释（关联bug http://bugs.mysql.com/bug.php?id=37310） http://lists.mysql.com/commits/52560 Problem: data consistency check (maximum record length) for a correct MyISAM table with CHECKSUM=1 and ROW_FORMAT=DYNAMIC option may fail due to wrong inner MyISAM parameter. In result we may have the table marked as 'corrupted'. Fix: properly set MyISAM maximum record length parameter.