作者：vivo 互联网数据库团队 – Wei Haodong
本文介绍了 MySQL5.7 中常见的replace into 操作造成的主从auto_increment不一致现象，一旦触发了主从切换，业务的正常插入操作会触发主键冲突的报错提示。
一、问题描述
1.1 问题现象
在 MySQL 5.7 版本中，REPLACE INTO 操作在表存在自增主键的情况下，可能会出现表的auto_increment值主从不一致现象，如果在此期间发生主从故障切换，当原来的slave节点变成了新的master节点，由于表的auto_increment值是小于原主库的，当业务继续写入时，就会收到主键冲突的报错提示。
相关报错信息如下：
! 报错提示
ERROR 1062 (23000): Duplicate entry ‘XXX’ for key ‘PRIMARY’
1.2 影响评估
在业务逻辑中使用了Replace into,或者INSERT…ON DUPLICATE KEY UPDATE。
一旦出现了表的auto_increment值主从不一致现象，在出现MySQL主从故障切换后，业务的正常写入会报主键冲突的错误，当auto_increment相差不多，或许在业务重试的时候会跳过报错，但是auto_increment相差较多时，会超出业务重试的次数，这样造成的影响会更大。
二、问题复现
2.1 环境搭建
这里在测试环境中，搭建MySQL社区版 5.7 版本，一主一从的架构。
【OS】：CentOS Linux release 7.3
【MySQL】：社区版本 5.7
【主从架构】：一主一从
【库表信息】：库名：test2023
表名：test_autoincrement
表结构如下：
2.2 准备测试数据
正常情况下，插入一行数据，影响的行数是1。
此时查看主从节点表的autoincrement值，可以看到此时主从的AUTO_INCREMENT是一致的，都是4，即自增主键下一次申请的值是4。
2.3问题复现模拟
2.3.1 模拟REPLACE INTO操作
MySQL [test2023]> REPLACE INTO test_autoincrement (name,uid) values('张三丰',1001);Query OK, 2 rows affected (0.01 sec)
这里通过REPLACE INTO操作判断，如果存在唯一ID为1001的记录，那么将name字段的值更改为”张三丰”,可发现此时影响的行数是2。现在我们再次查看主从节点表的autoincrement值。
此时出现了主从节点表的AUTO_INCREMENT不一致现象。
2.3.2 模拟主从切换
由于是在测试环境，这里就直接进行了主从关系的更改。
（1）停止当前slave节点的复制线程
MySQL [test2023]> stop slave;Query OK, 0 rows affected (0.08 sec)
（2）查看当前slave节点的Executed_Gtid_Set值
（3）重做主从关系
2.3.3 模拟业务正常写入
MySQL [test2023]> insert into test_autoincrement(name,uid) select '赵六',1004;ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'
到这里我们看到了预期的报错现象，如果是正常业务系统，这里的主从节点表的AUTO_INCREMENT可能会相差非常大，业务的正常插入就会持续报错了。
意味着真实的操作是先做delete操作，然后再进行insert。
三、原因分析
3.1 为什么从库节点的 autoincrement 没有变化？
这里可以看到REPLACE INTO操作对应的binlog日志记录其实是update操作，从库节点在应用update操作时，发现命中数据时，对应的autoincrement是没有变化的。
3.2 REPLACE INTO 操作的官方定义是什么？
官方对于 REPLACE INTO 的定义如下：
摘选自
https://dev.mysql.com/doc/refman/5.7/en/replace.html
REPLACEworks exactly likeINSERT, except that if an old row in the table has the same value as a new row for aPRIMARY KEYor aUNIQUEindex, the old row is deleted before the new row is inserted. SeeSection13.2.5, “INSERT Statement”.
REPLACEis a MySQL extension to the SQL standard. It either inserts, ordeletesand inserts. For another MySQL extension to standard SQL—that either inserts orupdates—seeSection13.2.5.2, “INSERT … ON DUPLICATE KEY UPDATE Statement”.
这里可以看到一张表包含主键或者唯一键的情况下，replace操作会判断原有的数据行是否存在，如果存在的话，就先删除旧的数据，然后进行insert操作，如果不存在的话，就和insert操作时一样的。
第二段也提到了INSERT … ON DUPLICATE KEY UPDATE Statement ，其实这个操作也会造成上面的主从autoincrement不一致现象，这里就不展开讨论了。
! Note
REPLACEmakes sense only if a table has aPRIMARY KEYorUNIQUEindex. Otherwise, it becomes equivalent toINSERT, because there is no index to be used to determine whether a new row duplicates another.
3.3 为什么REPLACE INTO操作在binlog日志中记录的是update操作？
这里我们通过源码文件sql_insert.cc和log_event.cc进行分析。
上述源码中可以看到在主库中replace 操作其实是insert 或者 delete + insert
The manual defines the REPLACE semantics that it is either an INSERT or DELETE(s) + INSERT;而 MySQL 在主从同步的binlog日志中，将replace操作转换为update操作的条件为：当发生冲突的键是最后一个唯一键，且没有外键约束，且没有触发器，由于我们的测试表中是没有外键约束，也没有触发器的，所以从库接收到的binlog日志中转化为update的条件即为最后一个唯一键。
这里，我们再进行测试一下（去掉表中的唯一索引uid）。
（1）创建新表
CREATE TABLE `test_autoincrement_2` (`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',`name` varchar(100) NOT NULL DEFAULT 'test' COMMENT '测试名字',PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4
（2）插入测试数据
（3）replace into 操作验证主库和从库的AUTO_INCREMENT
MySQL [test2023]> REPLACE INTO test_autoincrement_2 (id,name) values(3,'郑十');Query OK, 2 rows affected (0.08 sec)
这里我们把id=3的这一行数据对应的name修改为’郑十’，可发现上述影响的行数是2。
再次验证主库和从库的AUTO_INCREMENT，发现并没有发生变化，还是4。
CREATE TABLE `test_autoincrement_2` (`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',`name` varchar(100) NOT NULL DEFAULT 'test' COMMENT '测试名字',PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4
（4）分析binlog日志文件
总结：可发现binlog日志记录的同样是update 操作。只是当表中除了主键外没有额外的唯一键时，replace into的操作并不会触发从库的auto_increment的异常问题。比如上述的案例REPLACE INTO test_autoincrement_2 (id,name) values(3,’郑十’);，这里仅更改了name字段，由‘吴九‘修改为’郑十’。但是主键id是没有变化的，当然也就不需要再次使用auto_increment，这里也可以看到主库的auto_increment当然也没有发现变化（当表中除了主键外含有额外的唯一键时，是会触发申请auto_increment的），binlog接收的仍然是update操作，所以从库的auto_increment也是没有变化的，这样就没法造成auto_increment和主库不一致的问题了。
四、解决方案
到这里，我们是明白了replace into 会造成主从的auto_increment 不一致，但是怎么去解决呢？
4.1 升级到 MySQL 8.0 版本
在 MySQL 8.0 版本中已将AUTO_INCREMENT值做了持久化，且在做更新操作时，会将表上的自增列被更新为比auto_increment更大的值，auto_increment值也将被更新。
4.2修改 AUTO_INCREMENT 值
线上环境可能已经有很多这种情况，在没有触发业务报错的情况下，一般是很难发现这个隐患，如何在日常巡检中找到这些问题才是关键。
巡检逻辑一：这里可以通过巡检判断从库的max(id) >= AUTO_INCREMENT的方式来找出已经存在问题的表信息。然后通过SQL语句：ALTER TABLE table_name AUTO_INCREMENT = new_value; 进行修改。
巡检步骤可参考：
（1）仅检测某从节点，包含auto_increment 属性的表，过滤SQL如下：
select TABLE_SCHEMA,TABLE_NAME,AUTO_INCREMENT from information_schema.tables where table_schema not in ('information_schema','mysql','performance_schema','sys') AUTO_INCREMENTis not null G（2）加锁后读表信息，语句如下：
① 给表加锁
lock tables table_name write;②读取数据和表auto_increment值进行比对
MAXID=select max(id) from table_name;AUTO_INCREMENT=select AUTO_INCREMENT from information_schema.tables where TABLE_NAME='t1' ;
③ 判断条件
如果MAXID >= AUTO_INCREMENT , 判断为异常
巡检逻辑二：可以在高可用切换的时候增加AUTO_INCREMENT值判断，如果AUTO_INCREMENT值不一致，则不发生切换，不过这里的slave节点AUTO_INCREMENT的值本身可能因为延迟等问题，就会稍落后maste主节点，正常的巡检还是有难度的，还有就是当MySQL主从切换触发时，如果是因为原主库宕机了，不触发切换也会有问题，所以还是需要提前尽快把这个隐患排除掉。
4.3 禁用 replace into 操作
业务侧禁用replace into 或 insert … on duplicate key update ,实现方式可以通过代码逻辑来实现。
4.4 replace into操作的表不增加其他唯一索引
这里其实实现还是有难度的，自增id是不可控的，业务一般是不会使用数据库自带的自增id。
五、问题总结
1. REPLACE INTO 操作在表存在自增主键且包含唯一索引的情况下，当出现数据冲突的时候，会触发AUTO_INCREMENT在主从节点的不一致，一旦主从发生切换，就会造成业务的写入报主键冲突的错误。解决建议：业务更改实现方式，避免使用replace into，或者使用MySQL8.0 及以上的版本来解决该问题服务器托管。
2. 该问题是一个官方的BUG，不过并没有在MySQL5.7的版本中得到修复 。
https://bugs.mysql.com/bug.php?id=83030
参考文献：
-
https://bugs.mysql.com/bug.php?id=83030
-
https://dev.mysql.com/worklog/task/?id=6204
-
https://bugs.mysql.com/bug.php?id=20188
END
猜你喜欢
-
vivo 消息中间件测试环境项目多版本实践
-
Java8内存管理原理解析及内存故障排查实践
-
Sharding-JDBC源码解析与vivo的定制开发
本文分享自微信公众号 – vivo互联网技术（vivoVMIC）。
如有侵权，请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。
服务器托管，北京服务器托管，服务器租用 http://www.fwqtg.net
标签：PostgreSQL.Druid.Mybatis.Plus； 一、简介 PostgreSQL是一个功能强大的开源数据库系统，具有可靠性、稳定性、数据一致性等特点，且可以运行在所有主流操作系统上，包括Linux、Unix、Windows等。 通过官方文档可…
