投稿
  1. 极客之音首页
  2. 数据库

MySQL replace into 语句导致主从自增主键不一致

小半 • • 数据库 • 阅读 426

引言

replace into 是 MySQL 针对 insert 语句的扩展语法，实际生产环境中经常会遇到，尤其是在数据同步过程中，但是该语句在部分版本中有坑，因此需要慎用。

本文介绍 5.7.24 版本中 replace into 导致主从自增主键不一致的问题，并简单介绍自增主键相关知识点。

现象

如下所示，业务反馈执行 replace into 写入报错主键冲突。

### Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException:  Duplicate entry '91781' for key 'PRIMARY'

其中数据库版本 5.7.24，主从 binlog_format=ROW。

分析

自增主键

分别查看主从库的自增主键。

主库

mysql> show create table masterbasic.ob_jit_conf  G
*************************** 1. row ***************************
       Table: ob_jit_conf
Create Table: CREATE TABLE `ob_jit_conf` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
    ...
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ix_site_no_transporttype_org_no` (`SITE_NO`,`WAREHOUSE_NO`,`TRANSPORT_TYPE`,`ORG_NO`,`DISTRIBUTE_NO`),
  KEY `idx_update_time` (`UPDATE_TIME`)
) ENGINE=InnoDB AUTO_INCREMENT=91783 DEFAULT CHARSET=utf8

从库

mysql> show create table masterbasic.ob_jit_conf  G
*************************** 1. row ***************************
       Table: ob_jit_conf
Create Table: CREATE TABLE `ob_jit_conf` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
    ...
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ix_site_no_transporttype_org_no` (`SITE_NO`,`WAREHOUSE_NO`,`TRANSPORT_TYPE`,`ORG_NO`,`DISTRIBUTE_NO`),
  KEY `idx_update_time` (`UPDATE_TIME`)
) ENGINE=InnoDB AUTO_INCREMENT=93101 DEFAULT CHARSET=utf8

可见，主从的自增主键分别等于 91783、93101，的确是不一致，并且表中有自增主键以及多个业务字段组成的联合唯一索引。

最大主键

主库

mysql> select max(id) from masterbasic.ob_jit_conf;
+---------+
| max(id) |
+---------+
|   93100 |
+---------+
1 row in set (0.09 sec)

从库

mysql> select max(id) from masterbasic.ob_jit_conf ;
+---------+
| max(id) |
+---------+
|   93100 |
+---------+
1 row in set (0.08 sec)

可见，主从业务数据一致，但是主库当前最大主键远大于自增主键，从库正常。

主从切换

查看主从切换记录，该集群近期有发生过主从切换。

MySQL replace into 语句导致主从自增主键不一致
image-20221106094603773

经与研发沟通，业务中针对该表的操作都是 replace into 语句，而且使用自增主键，因此怀疑在 replace into 语句与主从切换的前提下可能导致主键冲突，下面尝试复现。

复现

准备数据

mysql> create table t_increment(
    -> `id` int(11) NOT NULL AUTO_INCREMENT,
    -> `age` int(11) DEFAULT NULL,
    ->  `msg` varchar(10) DEFAULT NULL,
    ->  PRIMARY KEY (`id`),
    ->  UNIQUE KEY `uniq_age` (`age`)
    ->  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

插入数据

mysql> replace into t_increment (age, msg) values (1,'aaa'),(2,'bbb'),(3,'ccc');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

主从自增主键相同

mysql> show create table t_increment G                                                                                                         
*************************** 1. row ***************************
       Table: t_increment
Create Table: CREATE TABLE `t_increment` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `age` int(11) DEFAULT NULL,
  `msg` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

在主库写入之前刷新日志，便于后续解析 binlog 日志。

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

主库写入

首先查看表中数据，自增主键依次为 1、2、3。

mysql> select * from t_increment;
+----+------+------+
| id | age  | msg  |
+----+------+------+
|  1 |    1 | aaa  |
|  2 |    2 | bbb  |
|  3 |    3 | ccc  |
+----+------+------+
3 rows in set (0.00 sec)

执行 replace into 语句，返回的受影响行数等于2，表明实际操作是 delete + insert。

mysql> replace into t_increment (age, msg) values (1, '111');
Query OK, 2 rows affected (0.00 sec)

再次查看表中数据，发现 id=1 的记录被删除，新增 id=4 的记录，其中发生冲突的唯一键 age 已更新。

mysql> select * from t_increment;
+----+------+------+
| id | age  | msg  |
+----+------+------+
|  2 |    2 | bbb  |
|  3 |    3 | ccc  |
|  4 |    1 | 111  |
+----+------+------+
3 rows in set (0.00 sec)

再次查看表结构，主从自增主键不一致。

# master
ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

# slave
ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8


binlog 解析后发现针对实际操作 delete + insert，binlog 中写入 update，前者主键 +1，后者主键不变。

[root@exps-test3 ~]# /export/servers/mysql/bin/mysqlbinlog -vv --base64-output=decode-rows /export/zhangkai321/mysql/3341/data/mysql-bin.000034
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#221104 14:20:54 server id 3341  end_log_pos 123 CRC32 0xc53fbd23  Start: binlog v 4, server v 5.7.24-log created 221104 14:20:54
# at 123
#221104 14:20:54 server id 3341  end_log_pos 234 CRC32 0x0a689b6c  Previous-GTIDs
# b5c3c3d6-2fc2-11ec-bfa0-fa163e19c3b7:1-10286111,
# f248888e-2feb-11ec-a7b0-fa163e19c3b7:1000011
# at 234
#221104 14:22:01 server id 3341  end_log_pos 299 CRC32 0x594c957e  GTID last_committed=0 sequence_number=1 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'b5c3c3d6-2fc2-11ec-bfa0-fa163e19c3b7:10286112'/*!*/;
# at 299
#221104 14:22:01 server id 3341  end_log_pos 374 CRC32 0x01d66154  Query thread_id=1119 exec_time=0 error_code=0
SET TIMESTAMP=1667542921/*!*/;
SET @@session.pseudo_thread_id=1119/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1411383296/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 374
#221104 14:22:01 server id 3341  end_log_pos 451 CRC32 0xf6b0b54b  Rows_query
# replace into t_increment (age, msg) values (1, '111')
# at 451
#221104 14:22:01 server id 3341  end_log_pos 512 CRC32 0x58bd5cd7  Table_map: `test_zk`.`t_increment` mapped to number 795
# at 512
#221104 14:22:01 server id 3341  end_log_pos 574 CRC32 0x7e9ee434  Update_rows: table id 795 flags: STMT_END_F
### UPDATE `test_zk`.`t_increment`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2=1 /* INT meta=0 nullable=1 is_null=0 */
###   @3='aaa' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
### SET
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
###   @2=1 /* INT meta=0 nullable=1 is_null=0 */
###   @3='111' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
# at 574
#221104 14:22:01 server id 3341  end_log_pos 605 CRC32 0x65d084bf  Xid = 10772773
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*/;

从库写入

为模拟主从切换的场景，直接在从库写入，不过开启了事务，便于回滚。

果然尽管唯一键没有冲突，但是发生主键冲突。

mysql> replace into t_increment (age, msg) values (4, 'ddd');
ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'

如果同时指定主键写入，不会发生主键冲突。

mysql> replace into t_increment (id, age, msg) values (4, 4, 'ddd');
Query OK, 2 rows affected (0.00 sec)

因此，尽管主从的自增主键不同，平时其实不会出现问题，最多就是主库自增主键不连续，但是如果发生主从切换，就会导致写入报错主键冲突。

总结

综上表明 replace into 将导致主从自增主键不同，可以认为是 MySQL5.7 的 bug，8.0 中已修复。

因此，建议结合业务场景慎重选择，尽量使用 delete + insert。本次线上发现的 replace into 属于历史代码。

而实际上 replace into 也有它适用的场景，比如常用于数据同步。


比如公司内部使用的蜂巢系统用于生产库到报表库的同步，两者都是 MySQL，其中就使用了 replace into 语句。

replace into 语句的优点主要包括：

  • 防止主键冲突，报表库中使用多个业务字段与主键作为联合主键，从生产库中同步时指定主键写入，使用联合主键的原因是有可能是多个生产库对应一个报表库，而不同生产库对应联合主键中业务字段不同的值。写入时指定主键也可以解决自增主键不一致的问题；
  • 满足幂等性，假设数据同步过程中发生了主从切换，可能需要向前移动位点避免数据丢失，而多次执行同一条 replace into 语句不会发生冲突。

如下所示是报表库中一张表的表结构，其中使用 ID、仓号作为联合主键。查询生产库的一条记录在报表库中对应的记录时就指定联合主键的四个字段进行查询即可。

mysql> show create table report.ob_shipment_d G
*************************** 1. row ***************************
       Table: ob_shipment_d
Create Table: CREATE TABLE `ob_shipment_d` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  ...
  PRIMARY KEY (`ID`,`WAREHOUSE_NO`,`ORG_NO`,`DISTRIBUTE_NO`)
  ...


又比如 pt-osc 工具中也使用到了 replace into 语句。

在执行 DDL 过程中历史数据通过通过拷贝同步，实时数据通过触发器同步，分别创建以下三个触发器。

操作类型 语法
DELETE DELETE IGNORE
UPDATE DELETE IGNORE + REPLACE INTO
INSERT REPLACE INTO

此外，历史数据的拷贝同步对应 INSERT LOW_PRIORITY IGNORE INTO 语句。

其中 UPDATE 与 INSERT 操作均对应 replace into 语句，因此对于 UPDATE 操作，如果历史数据还没有同步到新表中，新数据直接插入，历史数据不再写入，如果历史数据已经同步到新表中，更新为新数据，保证新表中的数据是最新的。

原理

replace into

如果没有唯一键冲突，replace into 效果类似于 insert，否则先 delete 后 insert。

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.

那么，对用户来说怎么判断是否发生 delete 呢？

可以根据 replace 语句返回的受影响行数进行判断，等于 delete 与 insert 的行数之和。

The REPLACE statement returns a count to indicate the number of rows affected. This is the sum of the rows deleted and inserted.

显示受影响行数为2，表明实际操作是 delete + insert。

mysql> replace into t_increment (age, msg) values (1, '111');
Query OK, 2 rows affected (0.00 sec)

不过该语句将导致主从自增主键不一致，原因是新数据写入主库对应 insert 语句，而从库对应 update 语句，前者更新自增主键，后者不变。

auto_increment

实际上，insert、insert ignore、replace into 三种数据插入方式在唯一键冲突与事务回滚的情况下，自增主键都会 +1 而无法回滚，进而导致自增主键不连续。

如下所示，事务回滚后自增主键没有回滚。

mysql> show create table t_increment G
       Table: t_increment
       ...
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_increment values(5,5,'eee');
Query OK, 1 row affected (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from t_increment;
+----+------+------+
| id | age  | msg  |
+----+------+------+
|  2 |    2 | bbb  |
|  3 |    3 | ccc  |
|  4 |    1 | 111  |
+----+------+------+
3 rows in set (0.00 sec)

mysql> show create table t_increment G
*************************** 1. row ***************************
       Table: t_increment
       ...
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)


根本原因是自增值的保存是在插入数据真正执行前完成的。如下图所示，自增锁并不是一个事务锁，而是每次申请完就马上释放，便于其他事务申请。

MySQL replace into 语句导致主从自增主键不一致
image-20221106203702470

那么，自增值为什么不支持回滚呢？

如下所示，两个并发执行的事务，在申请自增值时，为避免两个事务申请到相同的自增指，需要加锁顺序申请。

假设 A 申请到了 id=1 ，B 申请到了 id=2，表的自增值变为 3，然后 B 提交，A 回滚。如果要将自增值回滚到 id=1，继续执行的事务将报错主键冲突。

session A session B
begin;

begin;
insert into t values();

insert into t values();
commit;
rollback;

为了高效解决该问题，InnoDB 不支持自增值回滚，可能不连续。


然后，考虑另外一个问题，自增值保存在哪里呢？

首先，自增值并没有保存在表结构中，.frm 文件中保存表结构，但并不保存自增值。

不同存储引擎与不同版本中自增值对应不同的保存策略。

  • MyISAM 引擎的自增值保存在数据文件中；
  • InnoDB 引擎，MySQL 5.7 及之前的版本，自增值保存在内存中，没有持久化，每次重启时第一次打开表时找自增值的最大值 max(id) + 1 作为表当前的自增值，因此重启前后自增值可能会变化；
  • InnoDB 引擎，MySQL 8.0 版本，自增值持久化在 redo log 中，重启时根据 redo log 恢复到重启前的值，因此重启前后自增值不会发生变化。

binlog_format

上面提到根本原因是 binlog_format=ROW 时，将实际操作 delete + insert 记录成为 update。

如果将 binlog_format 设置为 STATEMENT，由于该模式下 binlog 中记录的是原始 SQL，因此不会出现主从自增主键不一致的问题。

查看 binlog，其中记录的是原始的 replace into 语句，而非 update。

#221106 21:06:43 server id 3341  end_log_pos 670 CRC32 0xb83fc8d4  GTID last_committed=1 sequence_number=2 rbr_only=no
SET @@SESSION.GTID_NEXT= 'b5c3c3d6-2fc2-11ec-bfa0-fa163e19c3b7:10286124'/*!*/;
# at 670
#221106 21:06:43 server id 3341  end_log_pos 755 CRC32 0x8288c45f  Query thread_id=1122 exec_time=0 error_code=0
SET TIMESTAMP=1667740003/*!*/;
BEGIN
/*!*/;
# at 755
# at 787
#221106 21:06:43 server id 3341  end_log_pos 787 CRC32 0x00774818  Intvar
SET INSERT_ID=6/*!*/;
#221106 21:06:43 server id 3341  end_log_pos 920 CRC32 0xa0f3f7b6  Query thread_id=1122 exec_time=0 error_code=0
use `test_zk`/*!*/;
SET TIMESTAMP=1667740003/*!*/;
replace into t_increment (age, msg) values (1, '111')
/*!*/;
# at 920
#221106 21:06:43 server id 3341  end_log_pos 951 CRC32 0x0be52705  Xid = 10772905
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*/;


可是，binlog_format=STATEMENT 实际上也有风险。

对于 insert … select 语句，如果设置 binlog_format=STATEMENT，如果相同的 SQL 在主从库上使用不同的索引，就可能导致主从数据的插入顺序不同，因此不建议这样设置。

处理

技术上可行的处理方案包括：

  • 临时处理修改主库自增主键；

  • 重启从库，所有表的 auto_increment 会修正；

  • 插入新数据后，自增值自动追平，主库 replace 后如果有 insert 语句，从库将自动追平。


建议临时提SQL工单修改自增主键，语句如下所示。

alter table cgb2.sdr_task auto_increment=20;

长期方案建议结合业务评估是否可以将 replace into 改写为 insert。

结论

业务 SQL 使用 replace into 语法与自增主键，对于 binlog_format=ROW，发生唯一键冲突时，会将实际操作 delete + insert 记录成为 update。因此主库自增主键 +1，从库不变，最终导致主从自增主键不一致。

这种情况下，尽管主库自增主键不连续，但不会导致其他问题。不过如果发生主从切换，就会导致写入报错主键冲突。


因此，主键冲突的原因是主从自增主键不一致。

主从自增主键不一致的原因是 replace into 在主从执行的语句不同。

主从执行的语句不同的原因是 binlog_format=ROW。

但是，如果将 binlog_format 设置为 STATEMENT，会有数据不一致的风险，所以不建议使用。


针对 replace into 导致主从自增主键不一致的问题，建议的处理方法是：

  • 不建议使用 replace into 语法，而是利用业务逻辑判断数据冲突；
  • 升级到 MySQL 8.0 版本；
  • 自增主键与数据本身同等重要，需要配置相应监控检测自增主键是否一致。

不过，这样也并不代表 replace into 语法一无是处，数据同步过程中很实用。

待办

  • mysqlbinlog
  • 自增锁

参考教程

  • 当MySQL自增主键遇到主从切换
https://zhuanlan.zhihu.com/p/356975126
  • replace操作导致主从auto_increment不一致
https://daminger.github.io/2017/12/03/mysql-replace-autoincrement-inconsistent/
  • MySQL Document: REPLACE Statement
https://dev.mysql.com/doc/refman/5.7/en/replace.html
https://www.cnblogs.com/seasonzone/p/14302899.html
https://time.geekbang.org/column/article/80531

原文始发于微信公众号（丹柿小院）：MySQL replace into 语句导致主从自增主键不一致

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/178670.html

赞 (0)
小半的头像小半
0 0

相关推荐

发表回复

登录后才能评论
极客之音——专业性很强的中文编程技术网站，欢迎收藏到浏览器，订阅我们！