MySQL 5.7 replace into导致自增id重叠写入失败

一、案例背景

云平台集群技术组研发在07月23日 10:58反馈他们所属的以下mysql集群有多张表数据写入失败

集群: 网络-运维平台-连云

具体报错如下：

表结构：

CREATE TABLE `t_aggregation_flow_week` (
  `FId` int(11) NOT NULL AUTO_INCREMENT,
  `FAggName` varchar(128) NOT NULL,
  `FType` varchar(64) NOT NULL,
  `FUpdateTime` varchar(64) NOT NULL,
  `FIdc` varchar(64) NOT NULL,
  `FBandwidth` varchar(32) NOT NULL DEFAULT '' COMMENT '聚合端口带宽',
  `MaxInValues` varchar(64) NOT NULL,
  `MaxIn95Values` varchar(64) NOT NULL,
  `MinInValues` varchar(64) NOT NULL,
  `MinOutValues` varchar(64) NOT NULL,
  `MaxOutValues` varchar(64) NOT NULL,
  `MaxOut95Values` varchar(64) NOT NULL,
  `AvgOutValues` varchar(64) NOT NULL,
  `AvgInValues` varchar(64) NOT NULL,
  `MaxInRate` float(10,2) NOT NULL,
  `MaxIn95Rate` float(10,2) NOT NULL,
  `MaxOutRate` float(10,2) NOT NULL,
  `MaxOut95Rate` float(10,2) NOT NULL,
  `AvgOutRate` float(10,2) NOT NULL,
  `AvgInRate` float(10,2) NOT NULL,
  PRIMARY KEY (`FId`),
  UNIQUE KEY `FUpdateTime` (`FUpdateTime`,`FType`,`FAggName`)
) ENGINE=InnoDB AUTO_INCREMENT=249665 DEFAULT CHARSET=utf8 COMMENT='聚合接口表'

可以看到报错的sql语句用到了mysql中的replace into语法，插入数据时在primary key上报错duplicate entry，该集群在2024-07-22T00:44:50+08:00 因master节点容器所在的宿主机故障发生了主从切换

因为之前用户平台部的mysql 5.7集群遇到过主从切换后自增ID滞后导致数据插入报错的问题,所以第一时间初步判定是同一个问题

具体的问题场景是在mysql 5.7及以下版本中，如果表上用到了自增id的主键同时表上的其他字段上有unique key唯一索引，在这样的表上使用replace into语法时会导致主库和从库表的AUTO_INCREMENT产生不一致，从库的AUTO_INCREMENT落后于主库，当集群发生主从切换后，新主库上因为AUTO_INCREMENT滞后的原因写入数据会因为主键冲突而写入失败

二、现象还原及原因分析

下面通过测试用例来还原一下这种场景，随后我们看看为什么会产生这个问题

创建测试表并插入3行数据：

CREATE TABLE `tb_test_auto_increment` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `name` varchar(100) NOT NULL DEFAULT 'test' COMMENT '测试名字',
  `uid` int(11) NOT NULL COMMENT '测试表唯一键',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uid` (`uid`)
) ENGINE=InnoDB;


insert into tb_test_auto_increment(name,uid) select '用户01',1001;
insert into tb_test_auto_increment(name,uid) select '用户02',1002;
insert into tb_test_auto_increment(name,uid) select '用户03',1003;

插入完数据后，在主库和从库上查看表结构定义：

主库：

| tb_test_auto_increment | CREATE TABLE `tb_test_auto_increment` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `name` varchar(100) NOT NULL DEFAULT 'test' COMMENT '测试名字',
  `uid` int(11) NOT NULL COMMENT '测试表唯一键',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uid` (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4

从库：

| tb_test_auto_increment | CREATE TABLE `tb_test_auto_increment` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `name` varchar(100) NOT NULL DEFAULT 'test' COMMENT '测试名字',
  `uid` int(11) NOT NULL COMMENT '测试表唯一键',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uid` (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4

可以看到在正常插入数据的情况下，自增ID从1开始分配，插入了3条数据后，主库和从库上表上的AUTO_INCREMENT=4

如果此时通过replace into插入一行数据，但是这行数据唯一键所在的列uid的值不重复

主库上执行：

mysql> select * from tb_test_auto_increment;
+----+----------+------+
| id | name     | uid  |
+----+----------+------+
|  1 | 用户01   | 1001 |
|  2 | 用户02   | 1002 |
|  3 | 用户03   | 1003 |
+----+----------+------+
3 rows in set (0.00 sec)


mysql>

mysql> REPLACE INTO tb_test_auto_increment (name,uid) values('repalcec插入01',1004);
Query OK, 1 row affected (0.00 sec)


mysql> select * from tb_test_auto_increment;
+----+------------------+------+
| id | name             | uid  |
+----+------------------+------+
|  1 | 用户01           | 1001 |
|  2 | 用户02           | 1002 |
|  3 | 用户03           | 1003 |
|  4 | repalcec插入01   | 1004 |
+----+------------------+------+
4 rows in set (0.00 sec)


mysql>

| tb_test_auto_increment | CREATE TABLE `tb_test_auto_increment` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `name` varchar(100) NOT NULL DEFAULT 'test' COMMENT '测试名字',
  `uid` int(11) NOT NULL COMMENT '测试表唯一键',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uid` (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4

从库：

| tb_test_auto_increment | CREATE TABLE `tb_test_auto_increment` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `name` varchar(100) NOT NULL DEFAULT 'test' COMMENT '测试名字',
  `uid` int(11) NOT NULL COMMENT '测试表唯一键',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uid` (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4

可以看到这种情况下主库和从库的 AUTO_INCREMENT=5，没有出现不一致的情况

解析一下主库的binlog看一下上面的replace into语句记录格式：

# at 3224
#240723 17:45:54 server id 173701265  end_log_pos 3296 CRC32 0xbab6bceb         Query   thread_id=653329        exec_time=0     error_code=0
SET TIMESTAMP=1721727954/*!*/;
BEGIN
/*!*/;
# at 3296
#240723 17:45:54 server id 173701265  end_log_pos 3365 CRC32 0x59ace264         Table_map: `mydb`.`tb_test_auto_increment` mapped to number 345064
# at 3365
#240723 17:45:54 server id 173701265  end_log_pos 3427 CRC32 0x0c82bbf0         Write_rows: table id 345064 flags: STMT_END_F
### INSERT INTO `mydb`.`tb_test_auto_increment`
### SET
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
###   @2='repalcec插入01' /* VARSTRING(400) meta=400 nullable=0 is_null=0 */
###   @3=1004 /* INT meta=0 nullable=0 is_null=0 */
# at 3427
#240723 17:45:54 server id 173701265  end_log_pos 3458 CRC32 0x919ee23c         Xid = 11814675
COMMIT/*!*/;

可以看到在binlog中，该语句被改写成普通的insert into, 因为unique key没有出现重复，所以replace into就是一条普通的数据插入，replace into 执行后打印Query OK, 1 row affected (0.00 sec)，扫描了1行数据，主库从库的自增id都等于5，没有问题

接下来看看replace into时出现unique key和表中的数据重复的情况：

mysql>  select * from tb_test_auto_increment;
+----+------------------+------+
| id | name             | uid  |
+----+------------------+------+
|  1 | 用户01           | 1001 |
|  2 | 用户02           | 1002 |
|  3 | 用户03           | 1003 |
|  4 | repalcec插入01   | 1004 |
+----+------------------+------+
4 rows in set (0.00 sec)


mysql> 


mysql> REPLACE INTO tb_test_auto_increment (name,uid) values('repalcec插入02',1001);
Query OK, 2 rows affected (0.00 sec)


mysql> 


mysql> select * from tb_test_auto_increment;
+----+------------------+------+
| id | name             | uid  |
+----+------------------+------+
|  2 | 用户02           | 1002 |
|  3 | 用户03           | 1003 |
|  4 | repalcec插入01   | 1004 |
|  5 | repalcec插入02   | 1001 |
+----+------------------+------+
4 rows in set (0.00 sec)


mysql>

replace into语句的unique key字段值1001与表中已有的值重复，可以看到replace into执行后打印 Query OK, 2 rows affected (0.00 sec)，扫描了2行数据，另外id =1 的行被删除，执行完成后检查下主库和从库的AUTO_INCREMENT

主库：

| tb_test_auto_increment | CREATE TABLE `tb_test_auto_increment` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `name` varchar(100) NOT NULL DEFAULT 'test' COMMENT '测试名字',
  `uid` int(11) NOT NULL COMMENT '测试表唯一键',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uid` (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4

从库：

| tb_test_auto_increment | CREATE TABLE `tb_test_auto_increment` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `name` varchar(100) NOT NULL DEFAULT 'test' COMMENT '测试名字',
  `uid` int(11) NOT NULL COMMENT '测试表唯一键',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uid` (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4

可以看到此时主库和从库的AUTO_INCREMENT产生了不一致，主库上AUTO_INCREMENT=6但从库仍然是5

仍然解析一下上面一条replace into的binlog会发现binlog中记录的是一次update:

# at 3595
#240723 17:57:13 server id 173701265  end_log_pos 3664 CRC32 0x2b2f156c         Table_map: `mydb`.`tb_test_auto_increment` mapped to number 345064
# at 3664
#240723 17:57:13 server id 173701265  end_log_pos 3746 CRC32 0x32404658         Update_rows: table id 345064 flags: STMT_END_F
### UPDATE `mydb`.`tb_test_auto_increment`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='用户01' /* VARSTRING(400) meta=400 nullable=0 is_null=0 */
###   @3=1001 /* INT meta=0 nullable=0 is_null=0 */
### SET
###   @1=5 /* INT meta=0 nullable=0 is_null=0 */
###   @2='repalcec插入02' /* VARSTRING(400) meta=400 nullable=0 is_null=0 */
###   @3=1001 /* INT meta=0 nullable=0 is_null=0 */
# at 3746
#240723 17:57:13 server id 173701265  end_log_pos 3777 CRC32 0x85390c28         Xid = 11820253
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

查看官网对replace into的说明：

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted. See Section 13.2.5, “INSERT Statement”.

https://dev.mysql.com/doc/refman/5.7/en/replace.html

也就是说当replace into操作的数据与表里现有的行有主键或者唯一键冲突时，会先delete老的行，然后插入新行，这也就是上面2 rows affected并且id=1的行被删掉的现象，新插入了一行所以表的AUTO_INCREMENT加了1

但是在binlog里面该语句就记录了一条update语句，而update现有的行不会更新表的AUTO_INCREMENT，因此就产生了主库和从库表的AUTO_INCREMENT不一致，从库的AUTO_INCREMENT落后于主库的情形

在这种情况下，因为从库上表的AUTO_INCREMENT落后于主库，所以当主库出现异常发生主从切换后，新主库(原来的从库)上表的AUTO_INCREMENT=5，但是表里已经有主键id=5的数据了，就会出现案例中同样的duplicate entry的报错

主从切换后，在新主库上插入数据：

mysql> select * from tb_test_auto_increment;
+----+------------------+------+
| id | name             | uid  |
+----+------------------+------+
|  2 | 用户02           | 1002 |
|  3 | 用户03           | 1003 |
|  4 | repalcec插入01   | 1004 |
|  5 | repalcec插入02   | 1001 |
+----+------------------+------+
4 rows in set (0.00 sec)


mysql> insert into tb_test_auto_increment(name,uid) select '新主库插入01',8888;
ERROR 1062 (23000): Duplicate entry '5' for key 'PRIMARY'
mysql> 


| tb_test_auto_increment | CREATE TABLE `tb_test_auto_increment` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `name` varchar(100) NOT NULL DEFAULT 'test' COMMENT '测试名字',
  `uid` int(11) NOT NULL COMMENT '测试表唯一键',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uid` (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4

同时可以看到，虽然执行报错，但语句执行后AUTO_INCREMENT还是会递增，如果连续执行报错并不断重试，当AUTO_INCREMENT递增到超过表里id的最大值时，插入数据也就不会再有主键冲突的问题

只有主键没有唯一键的情况：

| tb_test_auto_increment2 | CREATE TABLE `tb_test_auto_increment2` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `name` varchar(100) NOT NULL DEFAULT 'test' COMMENT '测试名字',
  `uid` int(11) NOT NULL COMMENT '测试表唯一键',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4


mysql> select * from tb_test_auto_increment2;
+----+----------+------+
| id | name     | uid  |
+----+----------+------+
|  1 | 用户01   | 1001 |
|  2 | 用户02   | 1002 |
|  3 | 用户03   | 1003 |
+----+----------+------+
3 rows in set (0.00 sec)


mysql>

如果写入不带自增id,则AUTO_INCREMENT +1，不会出现不一致

REPLACE INTO tb_test_auto_increment2 (name,uid) values('repalcec插入01',1004);

如果写入带自增id, id值在表里已经存在，虽然也是delete/insert，但是指定了自增id并且小于表的AUTO_INCREMENT，所以表的AUTO_INCREMENT不变，不会出现不一致

| tb_test_auto_increment2 | CREATE TABLE `tb_test_auto_increment2` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `name` varchar(100) NOT NULL DEFAULT 'test' COMMENT '测试名字',
  `uid` int(11) NOT NULL COMMENT '测试表唯一键',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4


mysql> REPLACE INTO tb_test_auto_increment2 (id,name,uid) values(4,'repalcec插入02',1005);
Query OK, 2 rows affected (0.00 sec)


mysql>


| tb_test_auto_increment2 | CREATE TABLE `tb_test_auto_increment2` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `name` varchar(100) NOT NULL DEFAULT 'test' COMMENT '测试名字',
  `uid` int(11) NOT NULL COMMENT '测试表唯一键',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4

如果写入带自增id, 指定的id大于表的AUTO_INCREMENT，这种情况下也是普通insert, 不会出现主从AUTO_INCREMENT不一致

| tb_test_auto_increment2 | CREATE TABLE `tb_test_auto_increment2` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `name` varchar(100) NOT NULL DEFAULT 'test' COMMENT '测试名字',
  `uid` int(11) NOT NULL COMMENT '测试表唯一键',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4


mysql> REPLACE INTO tb_test_auto_increment2 (id,name,uid) values(8888,'repalcec插入02',8888);
Query OK, 1 row affected (0.00 sec)


mysql> 


| tb_test_auto_increment2 | CREATE TABLE `tb_test_auto_increment2` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `name` varchar(100) NOT NULL DEFAULT 'test' COMMENT '测试名字',
  `uid` int(11) NOT NULL COMMENT '测试表唯一键',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8889 DEFAULT CHARSET=utf8mb4

综上总结，MySQL 5.7版本中当使用了自增ID主键并且表的其他列上有唯一索引，在使用replace into 或insert ... on duplicate key update写入数据时会产生主库和从库表上的AUTO_INCREMENT不一致，从库落后于主库的情况。在这种不一致出现的情况下如果发生了主从切换，会产生数据写入因为主键冲突而失败的现象。

三、解决方法

3.1 临时修复方法

手动调大表的AUTO_INCREMENT,让表的AUTO_INCREMENT大于表中自增id列已有数据的最大值

alter table tb_name AUTO_INCREMENT=xxxx;

如果像本案例中一样一个实例上有大量这种表，可以通过下面的语句查出可能出问题的表检查处理或者写脚本批量处理：

select inc.table_schema,inc.table_name,inc.column_name,inc.auto_increment from sys.schema_auto_increment_columns inc,information_schema.TABLE_CONSTRAINTS con

where inc.table_schema not in('information_schema','mysql','performance_schema','sys')

and con.CONSTRAINT_TYPE='UNIQUE' and con.table_schema not in('information_schema','mysql','performance_schema','sys')

and inc.table_schema=con.table_schema

and inc.table_name=con.table_name;

3.2 禁用 replace into/insert ... on duplicate key update语句

应用代码中禁用replace into 和 insert ... on duplicate key update

3.3 升级mysql版本到8.0

mysql 5.7中表的AUTO_INCREMENT只保存在内存中没有持久化存储， 8.0中实现了AUTO_INCREMENT的持久化，解决了这个问题

3.4 replace into语句操作的表上去掉unique key

posted @ 2025-02-26 10:31 HunterHuang 阅读(178) 评论(0) 收藏举报

刷新页面返回顶部

HunterHuang