使用Percona 5.5版本的用户请注意了

以前使用percona 5.0系列版本,一直顺风顺水的,没碰到什么问题。最近尝试了一下5.5.10(以及5.5.12)版本,结果碰到bug了 :(
问题现象简述:
1. 有两个数据表

CREATE TABLE `t1` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`guid` bigint(20) unsigned NOT NULL DEFAULT '0',
`name` varchar(255) NOT NULL DEFAULT '',
`charguid` bigint(20) unsigned NOT NULL DEFAULT '0',
`charname` varchar(255) NOT NULL DEFAULT '',
`level` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `idx_charguid` (`charguid`),
KEY `idx_guid` (`guid`)
) ENGINE=InnoDB;
CREATE TABLE `t2` (
`aid` bigint(20) NOT NULL AUTO_INCREMENT,
`guid` bigint(20) unsigned NOT NULL DEFAULT '0',
`name` varchar(255) NOT NULL DEFAULT '',
`level` int(10) unsigned NOT NULL DEFAULT '0',
`charguid` bigint(20) unsigned NOT NULL DEFAULT '0',
`charname` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`aid`),
KEY `idx_guid` (`guid`),
KEY `idx_charguid` (`charguid`)
) ENGINE=InnoDB;

2. 创建存储过程

delimiter $$$
drop procedure testproc $$$
create procedure testproc()
begin
truncate table t1;
insert into t1(guid,charguid,level,charname,name) select guid,charguid,level,charname,name from t2;
update t1,t2 set t1.charguid=t2.charguid where t1.guid=t2.guid;
end $$$
delimiter ;
mysql> call testbug();
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

3. 查看数据库报错信息

InnoDB: error in sec index entry update in
InnoDB: index `idx_charguid` of table `test`.`t1`
InnoDB: tuple DATA TUPLE: 2 fields;
0: len 8; hex 80e7fe000001bd12; asc         ;;
1: len 8; hex 80000000000051bf; asc       Q ;;
InnoDB: record PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 80e7fe000001bd12; asc         ;;
1: len 8; hex 80000000000048bb; asc       H ;;
TRANSACTION 43A5C4, ACTIVE 0 sec, process no 22742, OS thread id 1351932224 updating or deleting, thread declared inside InnoDB 210
mysql tables in use 2, locked 2
300 lock struct(s), heap size 31160, 22902 row lock(s), undo log entries 7105
MySQL thread id 24962, query id 2206535128 localhost root updating reference tables
update t1,t2 set t1.charguid=t2.charguid where t1.guid=t2.guid;
InnoDB: Submit a detailed bug report to http://bugs.mysql.com

4. 解决办法
查看bug描述

http://bugs.mysql.com/bug.php?id=37364
http://bugs.mysql.com/bug.php?id=17937

在bug描述中,认为产生此问题是因为表损坏了,重建一下表即可:

ALTER TABLE t1 ENGINE = InnoDB;
技术相关: 

评论

强~~