《Mysql必读深入探寻mysql自增列导致主键重复问题的原因》要点：
本文介绍了Mysql必读深入探寻mysql自增列导致主键重复问题的原因，希望对您有用。如果有疑问，可以联系我们。
废话少说,进入正题.MYSQL教程
拿到问题后,首先查看现场,发现问题表的中记录的最大值比自增列的值要大,那么很明显,当有记录进行插入时,自增列产生的值就有可能与已有的记录主键冲突,导致出错.首先想办法解决问题,通过人工调大自增列的值,保证大于表内已有的主键即可,调整后,导数据正常.问题是解决了,接下来要搞清楚问题原因,什么操作导致了这种现象的发生呢?MYSQL教程
这里有一种可能,即业务逻辑包含更新自增主键的代码,由于mysql的update动作不会同时更新自增列值,若更新主键值比自增列大,也会导致上述现象：记录最大值比自增主键值大.但开发反馈说这张表仅仅存在load data infile操作,不会进行更新主键操作,所以这个解释行不通.继续分析,表中含有唯一约束,会不会和唯一约束有关,线下实验模拟没有重现.后来想想会不会和主备切换有关系,因为前两天做过一次主备切换.于是乎,配合主备环境作了测试,果然和主备切换有关系,一切问题的来源都清晰了.MYSQL教程
问题发生的前置条件：MYSQL教程
1.mysql复制基于row模式MYSQL教程
2.innodb表MYSQL教程
3.表含有自增主键,并且含有唯一约束MYSQL教程
4.load data infile 采用replace into语法插入数据【遇到重复唯一约束,直接覆盖】MYSQL教程
问题发生的原理：MYSQL教程
1.主库遇到重复unique约束时,进行replace操作；MYSQL教程
2.replace在主库上面实际变化为delete+insert,但binlog记录的是update；MYSQL教程
3.备库重做update动作,更新主键,但由于update动作不会更新自增列值,导致更新后记录值大于自增列值MYSQL教程
问题重现实验：MYSQL教程
|
准备工作MYSQL教程 |
Create table test_autoinc(id int auto_increment, c1 int,c2 varchar(100),primary key(id),unique key(c1));MYSQL教程 insert into test_autoinc(c1,c2) values(1,'abc');MYSQL教程 insert into test_autoinc(c1,c2) values(2,'abc');MYSQL教程 insert into test_autoinc(c1,c2) values(3,'abcdd');MYSQL教程 insert into test_autoinc(c1,c2) values(4,'abcdd');MYSQL教程 insert into test_autoinc(c1,c2) values(5,'abcdd');MYSQL教程 |
|||
|
1MYSQL教程 |
操作MYSQL教程 |
备注MYSQL教程 |
MasterMYSQL教程 |
slaveMYSQL教程 |
|
2MYSQL教程 |
查看自增列值MYSQL教程 Show create tableMYSQL教程 test_autoinc\GMYSQL教程 |
插入5条记录后,自增列值变为6MYSQL教程 |
CREATE TABLE `test_autoinc` (MYSQL教程 `id` int(11) NOT NULL AUTO_INCREMENT,MYSQL教程 `c1` int(11) DEFAULT NULL,MYSQL教程 `c2` varchar(100) DEFAULT NULL,MYSQL教程 PRIMARY KEY (`id`),MYSQL教程 UNIQUE KEY `c1` (`c1`)MYSQL教程 ) ENGINE=InnoDBAUTO_INCREMENT=6DEFAULT CHARSET=utf8 |
CREATE TABLE `test_autoinc` (MYSQL教程 `id` int(11) NOT NULL AUTO_INCREMENT,MYSQL教程 `c1` int(11) DEFAULT NULL,MYSQL教程 `c2` varchar(100) DEFAULT NULL,MYSQL教程 PRIMARY KEY (`id`),MYSQL教程 UNIQUE KEY `c1` (`c1`)MYSQL教程 ) ENGINE=InnoDBAUTO_INCREMENT=6DEFAULT CHARSET=utf8MYSQL教程 MYSQL教程 |
|
3MYSQL教程 |
查看表数据MYSQL教程 |
MYSQL教程 |
id | c1 | c2 MYSQL教程 ---+------+------MYSQL教程 1 | 1 | abc MYSQL教程 2 | 2 | abc MYSQL教程 3 | 3 | abcddMYSQL教程 4 | 4 | abcddMYSQL教程 5 | 5 | abcddMYSQL教程 |
id | c1 | c2 MYSQL教程 ---+------+------MYSQL教程 1 | 1 | abc MYSQL教程 2 | 2 | abc MYSQL教程 3 | 3 | abcddMYSQL教程 4 | 4 | abcddMYSQL教程 5 | 5 | abcddMYSQL教程 |
|
4MYSQL教程 |
查看binlog位置MYSQL教程 show master status\GMYSQL教程 |
记录当前binlog位点,MYSQL教程 后续可以查看replace动作产生的binlog事件MYSQL教程 |
mysql-bin.000038MYSQL教程 59242888MYSQL教程 |
MYSQL教程 |
|
5MYSQL教程 |
replace操作MYSQL教程 replace into test_autoinc(c1,c2) values(2,'eeee');MYSQL教程 |
影响两条记录,主库replace=MYSQL教程 delete+insertMYSQL教程 |
MYSQL教程 Query OK, 2 rows affectedMYSQL教程 (0.00 sec)MYSQL教程 |
MYSQL教程 |
|
MYSQL教程 MYSQL教程 6MYSQL教程 |
查看表数据MYSQL教程 |
MYSQL教程 |
id | c1 | c2 MYSQL教程 ---+------+-------MYSQL教程 1 | 1 | abc MYSQL教程 3 | 3 | abcddMYSQL教程 4 | 4 | abcddMYSQL教程 5 | 5 | abcddMYSQL教程 6 | 2 | eeee MYSQL教程 |
id | c1 | c2 MYSQL教程 ---+------+-------MYSQL教程 1 | 1 | abc MYSQL教程 3 | 3 | abcddMYSQL教程 4 | 4 | abcddMYSQL教程 5 | 5 | abcddMYSQL教程 6 | 2 | eeee MYSQL教程 |
|
7MYSQL教程 |
查看binlog事件MYSQL教程 show binlog events in 'mysql-bin.000038' from 59242888;MYSQL教程 |
也可以通过mysqlbinlog工具分析日志,查询从库执行的update语句MYSQL教程 |
Pos | Event_type MYSQL教程 ---------+---------------MYSQL教程 59242888 | Query MYSQL教程 59242957 | Table_map MYSQL教程 59243013 |Update_rows_v1MYSQL教程 59243072 | Xid MYSQL教程 |
MYSQL教程 |
|
8MYSQL教程 |
查看自增列值MYSQL教程 Show create tableMYSQL教程 |
此时master的自增列为7,而slave的自增列为6,与表内最大值相同MYSQL教程 |
CREATE TABLE `test_autoinc` (MYSQL教程 `id` int(11) NOT NULL AUTO_INCREMENT,MYSQL教程 `c1` int(11) DEFAULT NULL,MYSQL教程 `c2` varchar(100) DEFAULT NULL,MYSQL教程 PRIMARY KEY (`id`),MYSQL教程 UNIQUE KEY `c1` (`c1`)MYSQL教程 ) ENGINE=InnoDBAUTO_INCREMENT=7MYSQL教程 |
CREATE TABLE `test_autoinc` (MYSQL教程 `id` int(11) NOT NULL AUTO_INCREMENT,MYSQL教程 `c1` int(11) DEFAULT NULL,MYSQL教程 `c2` varchar(100) DEFAULT NULL,MYSQL教程 PRIMARY KEY (`id`),MYSQL教程 UNIQUE KEY `c1` (`c1`)MYSQL教程 ) ENGINE=InnoDBAUTO_INCREMENT=6MYSQL教程 |
经过第8步操作后,若发生主备切换,slave提供服务,此时通过自增列插入主键6的记录,就会发生主键冲突.MYSQL教程
如何解决这个bug?对于replace操作,生成binlog时也生成delete和insert两个事件而非一个update事件；或者在执行update更新主键的同时也更新自增列值.当然了,这个只是纯原理分析,具体采用什么方法解这个问题,要根据mysql内部的实现,避免引入新的问题.这个bug我同事已经提交到社区,http://bugs.mysql.com/73563 ,大家可以看看.MYSQL教程
转载请注明本页网址：
http://www.vephp.com/jiaocheng/2170.html
