《MysqL必读深入探寻MysqL自增列导致主键重复问题的原因》要点：
本文介绍了MysqL必读深入探寻MysqL自增列导致主键重复问题的原因，希望对您有用。如果有疑问，可以联系我们。
@H_502_5@
废话少说,进入正题.MysqL教程@H_502_5@
拿到问题后,首先查看现场,发现问题表的中记录的最大值比自增列的值要大,那么很明显,当有记录进行插入时,自增列产生的值就有可能与已有的记录主键冲突,导致出错.首先想办法解决问题,通过人工调大自增列的值,保证大于表内已有的主键即可,调整后,导数据正常.问题是解决了,接下来要搞清楚问题原因,什么 *** 作导致了这种现象的发生呢?MysqL教程@H_502_5@
这里有一种可能,即业务逻辑包含更新自增主键的代码,由于MysqL的update动作不会同时更新自增列值,若更新主键值比自增列大,也会导致上述现象：记录最大值比自增主键值大.但开发反馈说这张表仅仅存在load data infile *** 作,不会进行更新主键 *** 作,所以这个解释行不通.继续分析,表中含有唯一约束,会不会和唯一约束有关,线下实验模拟没有重现.后来想想会不会和主备切换有关系,因为前两天做过一次主备切换.于是乎,配合主备环境作了测试,果然和主备切换有关系,一切问题的来源都清晰了.MysqL教程@H_502_5@
问题发生的前置条件：MysqL教程@H_502_5@
1.MysqL复制基于row模式MysqL教程@H_502_5@
2.innodb表MysqL教程@H_502_5@
3.表含有自增主键,并且含有唯一约束MysqL教程@H_502_5@
4.load data infile 采用replace into语法插入数据【遇到重复唯一约束,直接覆盖】MysqL教程@H_502_5@
问题发生的原理：MysqL教程@H_502_5@
1.主库遇到重复unique约束时,进行replace *** 作；MysqL教程@H_502_5@
2.replace在主库上面实际变化为delete+insert,但binlog记录的是update；MysqL教程@H_502_5@
3.备库重做update动作,更新主键,但由于update动作不会更新自增列值,导致更新后记录值大于自增列值MysqL教程@H_502_5@
问题重现实验：MysqL教程@H_502_5@
| 准备工作MysqL教程@H_502_5@ | Create table test_autoinc(ID int auto_increment,c1 int,c2 varchar(100),primary key(ID),unique key(c1));MysqL教程@H_502_5@ insert into test_autoinc(c1,c2) values(1,'abc');MysqL教程@H_502_5@ insert into test_autoinc(c1,c2) values(2,c2) values(3,'abcdd');MysqL教程@H_502_5@ insert into test_autoinc(c1,c2) values(4,c2) values(5,'abcdd');MysqL教程@H_502_5@ | |||
| 1MysqL教程@H_502_5@ | *** 作MysqL教程@H_502_5@ | 备注MysqL教程@H_502_5@ | MasterMysqL教程@H_502_5@ | slaveMysqL教程@H_502_5@ |
| 2MysqL教程@H_502_5@ | 查看自增列值MysqL教程@H_502_5@ Show create tableMysqL教程@H_502_5@ test_autoinc\GMysqL教程@H_502_5@ | 插入5条记录后,自增列值变为6MysqL教程@H_502_5@ | CREATE table `test_autoinc` (MysqL教程@H_502_5@ `ID` int(11) NOT NulL auto_INCREMENT,MysqL教程@H_502_5@ `c1` int(11) DEFAulT NulL,MysqL教程@H_502_5@ `c2` varchar(100) DEFAulT NulL,MysqL教程@H_502_5@ PRIMARY KEY (`ID`),MysqL教程@H_502_5@ UNIQUE KEY `c1` (`c1`)MysqL教程@H_502_5@ ) ENGINE=InnoDBauto_INCREMENT=6DEFAulT CHARSET=utf8 | CREATE table `test_autoinc` (MysqL教程@H_502_5@ `ID` int(11) NOT NulL auto_INCREMENT,0); padding-top: 0px">auto_INCREMENT=6DEFAulT CHARSET=utf8MysqL教程@H_502_5@ MysqL教程@H_502_5@ |
| 3MysqL教程@H_502_5@ | 查看表数据MysqL教程@H_502_5@ | MysqL教程@H_502_5@ | ID | c1 | c2 MysqL教程@H_502_5@ ---+------+------MysqL教程@H_502_5@ 1 | 1 | abc MysqL教程@H_502_5@ 2 | 2 | abc MysqL教程@H_502_5@ 3 | 3 | abcddMysqL教程@H_502_5@ 4 | 4 | abcddMysqL教程@H_502_5@ 5 | 5 | abcddMysqL教程@H_502_5@ | ID | c1 | c2 MysqL教程@H_502_5@ ---+------+------MysqL教程@H_502_5@ 1 | 1 | abc MysqL教程@H_502_5@ 2 | 2 | abc MysqL教程@H_502_5@ 3 | 3 | abcddMysqL教程@H_502_5@ 4 | 4 | abcddMysqL教程@H_502_5@ 5 | 5 | abcddMysqL教程@H_502_5@ |
| 4MysqL教程@H_502_5@ | 查看binlog位置MysqL教程@H_502_5@ show master status\GMysqL教程@H_502_5@ | 记录当前binlog位点,MysqL教程@H_502_5@ 后续可以查看replace动作产生的binlog事件MysqL教程@H_502_5@ | MysqL-bin.000038MysqL教程@H_502_5@ 59242888MysqL教程@H_502_5@ | MysqL教程@H_502_5@ |
| 5MysqL教程@H_502_5@ | replace *** 作MysqL教程@H_502_5@ replace into test_autoinc(c1,'eeee');MysqL教程@H_502_5@ | 影响两条记录,主库replace=MysqL教程@H_502_5@ delete+insertMysqL教程@H_502_5@ | MysqL教程@H_502_5@ query OK, 2 rows affectedMysqL教程@H_502_5@ (0.00 sec)MysqL教程@H_502_5@ | MysqL教程@H_502_5@ |
| MysqL教程@H_502_5@ MysqL教程@H_502_5@ 6MysqL教程@H_502_5@ | 查看表数据MysqL教程@H_502_5@ | MysqL教程@H_502_5@ | ID | c1 | c2 MysqL教程@H_502_5@ ---+------+-------MysqL教程@H_502_5@ 1 | 1 | abc MysqL教程@H_502_5@ 3 | 3 | abcddMysqL教程@H_502_5@ 4 | 4 | abcddMysqL教程@H_502_5@ 5 | 5 | abcddMysqL教程@H_502_5@ 6 | 2 | eeee MysqL教程@H_502_5@ | ID | c1 | c2 MysqL教程@H_502_5@ ---+------+-------MysqL教程@H_502_5@ 1 | 1 | abc MysqL教程@H_502_5@ 3 | 3 | abcddMysqL教程@H_502_5@ 4 | 4 | abcddMysqL教程@H_502_5@ 5 | 5 | abcddMysqL教程@H_502_5@ 6 | 2 | eeee MysqL教程@H_502_5@ |
| 7MysqL教程@H_502_5@ | 查看binlog事件MysqL教程@H_502_5@ show binlog events in 'MysqL-bin.000038' from 59242888;MysqL教程@H_502_5@ | 也可以通过MysqLbinlog工具分析日志,查询从库执行的update语句MysqL教程@H_502_5@ | Pos | Event_type MysqL教程@H_502_5@ ---------+---------------MysqL教程@H_502_5@ 59242888 | query MysqL教程@H_502_5@ 59242957 | table_map MysqL教程@H_502_5@ 59243013 |Update_rows_v1MysqL教程@H_502_5@ 59243072 | XID MysqL教程@H_502_5@ | MysqL教程@H_502_5@ |
| 8MysqL教程@H_502_5@ | 查看自增列值MysqL教程@H_502_5@ Show create tableMysqL教程@H_502_5@ | 此时master的自增列为7,而slave的自增列为6,与表内最大值相同MysqL教程@H_502_5@ | CREATE table `test_autoinc` (MysqL教程@H_502_5@ `ID` int(11) NOT NulL auto_INCREMENT,0); padding-top: 0px">auto_INCREMENT=7MysqL教程@H_502_5@ | CREATE table `test_autoinc` (MysqL教程@H_502_5@ `ID` int(11) NOT NulL auto_INCREMENT,0); padding-top: 0px">auto_INCREMENT=6MysqL教程@H_502_5@ |
经过第8步 *** 作后,若发生主备切换,slave提供服务,此时通过自增列插入主键6的记录,就会发生主键冲突.MysqL教程@H_502_5@
如何解决这个BUG?对于replace *** 作,生成binlog时也生成delete和insert两个事件而非一个update事件；或者在执行update更新主键的同时也更新自增列值.当然了,这个只是纯原理分析,具体采用什么方法解这个问题,要根据MysqL内部的实现,避免引入新的问题.这个BUG我同事已经提交到社区,http://BUGs.MysqL.com/73563,大家可以看看.MysqL教程@H_502_5@ 总结
以上是内存溢出为你收集整理的Mysql必读深入探寻mysql自增列导致主键重复问题的原因全部内容，希望文章能够帮你解决Mysql必读深入探寻mysql自增列导致主键重复问题的原因所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）