废话少说,进入正题。
拿到问题后,首先查看现场,发现问题表的中记录的最大值比自增列的值要大,那么很明显,当有记录进行插入时,自增列产生的值就有可能与已有的记录主键冲突,导致出错。首先想办法解决问题,通过人工调大自增列的值,保证大于表内已有的主键即可,调整后,导数据正常。问题是解决了,接下来要搞清楚问题原因,什么操作导致了这种现象的发生呢?
这里有一种可能,即业务逻辑包含更新自增主键的代码,由于mysql的update动作不会同时更新自增列值,若更新主键值比自增列大,也会导致上述现象:记录最大值比自增主键值大。但开发反馈说这张表仅仅存在loaddatainfile操作,不会进行更新主键操作,所以这个解释行不通。继续分析,表中含有唯一约束,会不会和唯一约束有关,线下实验模拟没有重现。后来想想会不会和主备切换有关系,因为前两天做过一次主备切换。于是乎,配合主备环境作了测试,果然和主备切换有关系,一切问题的来源都清晰了。
问题发生的前置条件:
1.mysql复制基于row模式
2.innodb表
3.表含有自增主键,并且含有唯一约束
4.loaddatainfile采用replaceinto语法插入数据【遇到重复唯一约束,直接覆盖】
问题发生的原理:
1.主库遇到重复unique约束时,进行replace操作;
2.replace在主库上面实际变化为delete+insert,但binlog记录的是update;
3.备库重做update动作,更新主键,但由于update动作不会更新自增列值,导致更新后记录值大于自增列值
问题重现实验:
准备工作
Createtabletest_autoinc(idintauto_increment,c1int,c2varchar(100),primarykey(id),uniquekey(c1));
insertintotest_autoinc(c1,c2)values(1,'abc');
insertintotest_autoinc(c1,c2)values(2,'abc');
insertintotest_autoinc(c1,c2)values(3,'abcdd');
insertintotest_autoinc(c1,c2)values(4,'abcdd');
insertintotest_autoinc(c1,c2)values(5,'abcdd');
1
操作
备注
Master
slave
2
查看自增列值
Showcreatetable
test_autoinc\G
插入5条记录后,自增列值变为6
CREATETABLE`test_autoinc`(
`id`int(11)NOTNULLAUTO_INCREMENT,
`c1`int(11)DEFAULTNULL,
`c2`varchar(100)DEFAULTNULL,
PRIMARYKEY(`id`),
UNIQUEKEY`c1`(`c1`)
)ENGINE=InnoDBAUTO_INCREMENT=6DEFAULTCHARSET=utf8
CREATETABLE`test_autoinc`(
`id`int(11)NOTNULLAUTO_INCREMENT,
`c1`int(11)DEFAULTNULL,
`c2`varchar(100)DEFAULTNULL,
PRIMARYKEY(`id`),
UNIQUEKEY`c1`(`c1`)
)ENGINE=InnoDBAUTO_INCREMENT=6DEFAULTCHARSET=utf8
3
查看表数据
id|c1 |c2
---+------+------
1| 1|abc
2| 2|abc
3| 3|abcdd
4| 4|abcdd
5| 5|abcdd
id|c1 |c2
---+------+------
1| 1|abc
2| 2|abc
3| 3|abcdd
4| 4|abcdd
5| 5|abcdd
4
查看binlog位置
showmasterstatus\G
记录当前binlog位点,
后续可以查看replace动作产生的binlog事件
mysql-bin.000038
59242888
5
replace操作
replaceintotest_autoinc(c1,c2)values(2,'eeee');
影响两条记录,主库replace=
delete+insert
QueryOK, 2rowsaffected
(0.00sec)
6
查看表数据
id|c1 |c2
---+------+-------
1| 1|abc
3| 3|abcdd
4| 4|abcdd
5| 5|abcdd
6| 2|eeee
id|c1 |c2
---+------+-------
1| 1|abc
3| 3|abcdd
4| 4|abcdd
5| 5|abcdd
6| 2|eeee
7
查看binlog事件
showbinlogeventsin'mysql-bin.000038'from59242888;
也可以通过mysqlbinlog工具分析日志,查询从库执行的update语句
Pos |Event_type
---------+---------------
59242888|Query
59242957|Table_map
59243013|Update_rows_v1
59243072|Xid
8
查看自增列值
Showcreatetable
此时master的自增列为7,而slave的自增列为6,与表内最大值相同
CREATETABLE`test_autoinc`(
`id`int(11)NOTNULLAUTO_INCREMENT,
`c1`int(11)DEFAULTNULL,
`c2`varchar(100)DEFAULTNULL,
PRIMARYKEY(`id`),
UNIQUEKEY`c1`(`c1`)
)ENGINE=InnoDBAUTO_INCREMENT=7
CREATETABLE`test_autoinc`(
`id`int(11)NOTNULLAUTO_INCREMENT,
`c1`int(11)DEFAULTNULL,
`c2`varchar(100)DEFAULTNULL,
PRIMARYKEY(`id`),
UNIQUEKEY`c1`(`c1`)
)ENGINE=InnoDBAUTO_INCREMENT=6
经过第8步操作后,若发生主备切换,slave提供服务,此时通过自增列插入主键6的记录,就会发生主键冲突。
如何解决这个bug?对于replace操作,生成binlog时也生成delete和insert两个事件而非一个update事件;或者在执行update更新主键的同时也更新自增列值。当然了,这个只是纯原理分析,具体采用什么方法解这个问题,要根据mysql内部的实现,避免引入新的问题。这个bug我同事已经提交到社区,http://bugs.mysql.com/73563,大家可以看看。