MySQL踩坑记之Replace Into导致自增ID冲突

一、问题描述

最近有客户反馈，说线上服务在一小段时间内出现偶发的请求失败情况，需要我们排查并给出具体问题。对于这样的问题，我们一般都会猜疑是不是网络抖动，但是需要有理有据的解释清楚问题，还是要基于日志事实求事去分析。在日志中我发现的报错是这个样子的：

Error 1062: Duplicate entry '782' for key 'PRIMARY', errno: 0
Error 1062: Duplicate entry '784' for key 'PRIMARY', errno: 0

看了下表结构，表的PRIMARY Key是自增ID呀，自增ID除非手动insert故意插入脏数据，系统自己运行一般不会出现呀？

通过报错日志信息结合源代码我找到了报错时具体执行的sql大概长这个样子

replace into table_name (`f1`, `f2`, `f3`, `createdAt`, `updatedAt`) values (?,?,?,?,?)

猜疑：难道是replace into操作会出现自增id冲突问题？

简单谷歌+百度后了解到部分mysql版本在同时有自增主键和唯一键的条件下，replace into变更会导致自增Id冲突。确实有该问题存在，但是都是存在主从复制结构中。遂咨询运维朋友最近是否做过DB主从切换，收到的回答是“有”。那问题基本上确定的八九不离十了。

开始准备复现。

二、问题复现

mysql主从搭建过程：传送门

mysql 版本 5.7.9

复制模式：row模式

除主键外表需要有唯一键

2.1 主库创建表并初始化数据

CREATE TABLE `user_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `num` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `num` (`num`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;
insert into user_info(num) value (1),(2);

插入两条数据，此时主从库上AUTO_INCREMENT都等于3，也就是说下一次默认查入行id=3

2.2 执行replace into

replace into user_info(id,num) value (3,2);

执行后，提示影响行数为2，查看主库AUTO_INCREMENT=4 符合预期

此时重点来了，我们查看从库AUTO_INCREMENT

从库数据已经同步变了，但是AUTO_INCREMENT=3，表中id=3这条数据已经存在了呀

2.3 主从切换，往从库中写入数据

 insert into user_info(num) value(4);

和业务日志中报错的主键冲突一致，问题复现。再次尝试插入, 这次就成功了，因为在上次失败后AUTO_INCREMENT

进行了一次自增。也符合客户描述的偶发现象

三、原理分析

在这之前就必须简单提一下binlog的几种数据类型，详细的可百度深入了解。

bin log有三种格式，分别是Statement格式，Row格式和MIXED格式。

Statement格式就是将执行的原始SQL语句记录下来，但是对于涉及到一些函数如uuid或者是需要依赖上下文关系的sql语句，master的原始sql在从库中执行会出现数据不一致。

ROW格式解决了Statement遇到的问题，ROW格式的binlog记录了sql执行后数据每一行的改动情况。但它依然不完美，假如一条sql操作后影响的数据行有十万，那么binlog中将会写入十万行的log，会导致磁盘资源占用过大，也会影响主从同步的性能。

MIXED格式是一个STATEMENT和ROW的居中解决方案，由mysql自己判断当前执行语句在主从同步过程中是否会由数据不一致的可能，如果有可能，则选择ROW格式写入binlog，反之选择STATEMENT格式写入。

REPLCE INTO 导致的主键问题就发生在基于ROW模式同步的情况下，我们打开看一下刚才操作过程中的的binlog

mysqlbinlog --start-datetime="2022-03-14 00:00:00" --stop-datetime="2022-03-14 00:40:59" /usr/local/mysql/data/mysql-bin.000001 -vv --base64-output=decode-rows  -r  test.sql

查看binlog要记得使用-vv --base64-output=decode-rows进行解码显示原始SQL

从Binlog我们看到刚刚的replace into操作居然记录的是update，这就是根本所在了，是否还记得在前面执行replace into时影响行数为2

四、总结

replace into时，如果唯一键数据存在，则执行delete+insert操作，所以返回行数为2，binlog记录的却是update操作，从库同步binlog到relaylog重放时就仅执行了update，不会变更AUTO_INCREMENT值。导致AUTO_INCREMENT值小于等于DB中已存在的值，主从切换后往从库中写数据就会出现自增主键冲突问题并在尝试多次后恢复正常。 目前发现问题版本：5.7.9, 5.7.17

博主同时尝试了：5.6.16-log，5.7.27版本未复现该问题，其他有该问题的版本可以在评论区留言进行补充避免更多同学踩坑。