MySQL bug replace into 语句导致主从切换后主键冲突

一、背景

生产环境遇到一个 MySQL 写入报错的问题，业务写入数据时报主键冲突。经过调查，这套 MySQL 集群版本为 Percona 5.7.19，在报主键冲突前，做过主从切换，报主键冲突的SQL语句为 replace into，表的主键是自增列，调查该表的 auto_increment，发现一个很奇怪的问题，该表的 auto_increment 竟然比表数据的 max(id) 还小，难怪会出现主键冲突了。

那么为什么会出现 auto_increment 比 max(id) 还小？搜索一下，发现果然是 MySQL 的一个 Bug，见链接：

https://bugs.mysql.com/bug.php?id=83030

简单来说，replace into 语句通过唯一键相同修改记录时，主库上该表的 auto_increment 会变化，而从库上该表的 auto_increment 值不变。所以在从库上会出现 auto_increment 值比 max(id) 还小的情况，做主从切换之后，写入数据，就可能会发生主键冲突。

二、复现方法

另外也搜索到一篇文章，描述了同样的 Bug 和 复现方法，见链接：

https://developer.aliyun.com/article/57855

拷贝上述链接原文如下：

2.1 复现条件

• MySQL 5.7
• ROW模式

表结构：

CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`col_1` varchar(100) DEFAULT NULL,
`col_2` varchar(100) DEFAULT NULL,
`col_3` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `col_1` (`col_1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


2.2 复现步骤

初始化数据：

mater:lc> REPLACE INTO test (col_1,col_2,col_3) values('a','a','a');
Query OK, 1 row affected (0.00 sec) --注意，这里是影响了1条记录

master:lc> REPLACE INTO test (col_1,col_2,col_3) values('c','c','c');
Query OK, 1 row affected (0.00 sec) --注意，这里是影响了1条记录


查询主库表结构，发现 AUTO_INCREMENT=4，如下：

master > show create table test

| test | CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`col_1` varchar(100) DEFAULT NULL,
`col_2` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `col_1` (`col_1`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |


查询从库表结构，发现 AUTO_INCREMENT=4，如下：

slave > show create table test

| test | CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`col_1` varchar(100) DEFAULT NULL,
`col_2` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `col_1` (`col_1`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |


以上都很正常，下面就是见证奇迹的时刻，在主库执行下面的SQL：

mater:lc> REPLACE INTO test (col_1,col_2) values('c','cc');
Query OK, 2 rows affected (0.00 sec) --注意，这里是影响了两条记录


然后再看主、从库表结构的 AUTO_INCREMENT 值，发现主库 AUTO_INCREMENT=5，从库 AUTO_INCREMENT=4，主从库数据完全一致，如下：

mater:lc> show create table test
| test | CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`col_1` varchar(100) DEFAULT NULL,
`col_2` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `col_1` (`col_1`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |

slave:lc> select * from test
+----+-------+-------+
| id | col_1 | col_2 |
+----+-------+-------+
| 1 | a | a |
| 2 | b | b |
| 4 | c | cc |
+----+-------+-------+
3 rows in set (0.00 sec)


在不同的MySQL版本上测试该 Bug，结果如下：

• 5.7.30 未修复
• 8.0.20 已修复

这个 Bug 很容易复现，很久之前就已经被提出来，直到现在都没有在 5.7 版本进行修复。