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('b','b','b');
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 |

master: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)


slave: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=4 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 版本进行修复。

发表评论