非常罕见的主从复制唯一索引列数据重复冲突案例
问题发生
某银行客户的开发测试环境，使用的是一主两从半同步复制GreatSQL。现场DBA发现一个slave节点的sql线程异常，show slave status检查错误码是1062，检查另一个slave的复制线程状态正常。
GreatSQL > show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
......
Last_Errno: 1062
Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '1b79061d-71ae-11ef-9633-00163e2cb875:17436241' at master log greatdb-bin.000288, end_log_pos 362599392.. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
......
检查表performance_schema.replication_applier_status_by_worker显示信息如下：
GreatSQL > SELECT * FROM performance_schema.replication_applier_status_by_worker \G
....
LAST_ERROR_NUMBER: 1062
LAST_ERROR_MESSAGE: Worker 1 failed executing transaction '1b79061d-71ae-11ef-9633-00163e2cb875:17436241' at master log greatdb-bin.000288, end_log_pos 362599392; Could not execute Write_rows event on table xxxx; Duplicate entry 'Ms001-0xx-20230901-1' for key 'fnd_xxxxx_yyy.UK0_fnd_xxxxx_yyy', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log First, end_log_pos 362599392
...
提示表nfesp_fesl1_db_jy.fnd_xxxxx_yyy表的唯一索引UK0_fnd_xxxxx_yyy有重复数据。
检查表nfesp_fesl1_db_jy.fnd_xxxxx_yyy的建表语句：
CREATE TABLE `fnd_xxxxx_yyy` (
`IvsmPd_ECD` varchar(80) COLLATE utf8mb4_bin NOT NULL COMMENT '产品编码',
`Txn_Mkt_ID` char(19) COLLATE utf8mb4_bin NOT NULL COMMENT '市场编号',
`FndCo_Agnc_Sale_InsID` varchar(9) COLLATE utf8mb4_bin NOT NULL COMMENT '代理销售商代码',
`Fnd_Qtn_Dt` char(8) COLLATE utf8mb4_bin NOT NULL COMMENT '行情日期',
`Ext_Fnd_StCd` char(1) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '外部产品状态',
`NetVal_TpCd` char(1) COLLATE utf8mb4_bin NOT NULL COMMENT '净值类型代码',
`Unit_Ast_NetVal` decimal(19,8) DEFAULT NULL COMMENT '单位净值',
`AcNetVal` decimal(19,8) DEFAULT NULL COMMENT '累计净值',
`Each_TenThsd_Pft_Amt` decimal(10,6) DEFAULT NULL COMMENT '每万份收益额',
`InBkMt_BdYdRto_ECD` char(10) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '收益率类型',
`Avrg_Exp_YldRto` decimal(19,8) DEFAULT NULL COMMENT '收益率值',
`Fnd_Co_Fdbk_Tot_Cps` decimal(19,2) DEFAULT NULL COMMENT '反馈总份数',
`Fnd_Co_Anc_Fnd_Sz` decimal(19,2) DEFAULT NULL COMMENT '发布产品规模',
`Tot_Pft_Amt` decimal(22,6) DEFAULT NULL COMMENT '当期总收益',
`Acm_Pft_Amt` decimal(31,16) DEFAULT NULL COMMENT '累计收益',
`MULTI_TENANCY_ID` char(5) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '#多实体标识',
`TXN_ITT_CHNL_ID` char(23) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '#发起渠道编号',
`TXN_ITT_CHNL_CGY_CODE` char(8) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '#发起渠道类别',
`Crt_Br_ID` char(9) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '创建分行编号',
`Mdf_Br_ID` char(9) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '变更分行编号',
`Scr_Crt_InsID` char(9) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '记录创建机构编号',
`Scr_Mdf_InsID` char(9) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '记录变更机构编号',
`Scr_Crt_TrID` char(8) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '记录创建柜员编号',
`Mdf_TrID` char(8) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '记录变更柜员编号',
`Ahn_TrID` char(8) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '授权柜员编号',
`Data_Rcd_Crt_Dt` char(8) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '#数据记录创建日期',
`Rcd_Crt_Tm` char(6) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '记录创建时间',
`StMd_Dt` char(8) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '状态变更日期',
`Rcd_Udt_Tm` char(6) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '记录更新时间',
`Data_Udt_Dt_Tm` datetime(3) DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '#数据更新日期时间',
`SN_Rcd_ID` bigint NOT NULL AUTO_INCREMENT COMMENT '记录序号',
PRIMARY KEY (`SN_Rcd_ID`),
UNIQUE KEY `UKQ_fnd_xxxxx_yyy` (`IvsmPd_ECD`,`Txn_Mkt_ID`,`Fnd_Qtn_Dt`,`NetVal_TpCd`)
) ENGINE=InnoDB AUTO_INCREMENT=1714 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='产品日信息';
发现：有唯一索引UK0_fnd_xxxxx_yyy ( IvsmPd_ECD , Txn_Mkt_ID , Fnd_Qtn_Dt , NetVal_TpCd ) ,检查主库中表fnd_xxxxx_yyy的数据，发现SN_Rcd_ID=860和1711的2条数据的唯一索引的列完全相同。
GreatSQL > SELECT IvsmPd_ECD, Txn_Mkt_ID, Fnd_Qtn_Dt, NetVal_TpCd, SN_Rcd_ID FROM gsqldb.fnd_xxxxx_yyy WHERE SN_Rcd_ID in(860,1711);
+------------+------------+------------+-------------+-----------+
| IvsmPd_ECD | Txn_Mkt_ID | Fnd_Qtn_Dt | NetVal_TpCd | SN_Rcd_ID |
+------------+------------+------------+-------------+-----------+
| MS001 | 0XX | 20230901 | 1 | 860 |
| MS001 | 0XX | 20230901 | 1 | 1711 |
+------------+------------+------------+-------------+-----------+
1 row in set (0.00 sec)
问题总结
- 一主两从的复制架构中，nfesp_fesl1_db_jy.fnd_xxxxx_yyy表在主节点中，唯一索引列存在2条重复数据；
- 主库重复数据的第2条，在一个slave节点上回放时，由于唯一索引重复而无法插入，导致sql_thread停止；
- 主库重复数据的第2条，在另一个slave上，可以正常回放，唯一索引列同样存在2条重复数据；
问题分析
解析binlog
使用mysqlbinlog解析master节点的binlog文件greatdb-bin.000288，显示该事务是一个insert语句，在事务中，有如下设置会话变量的信息：
SET **@@session.unique_checks=0**/*!*/;
表明在执行该事务时，用户修改了会话级变量unique_checks，禁用了唯一索引的唯一性检查。
分析程序源码
从源码上看，同时满足以下三个条件，可能导致向唯一索引列插入重复值：
- 禁用唯一索引检查unique_checks=OFF
- 使用了change buffer
- 要更新的唯一索引页不在buffer pool中
问题复现
- 配置一主两从的复制环境，配置所有节点使用change buffer。
GreatSQL > SHOW VARIABLES LIKE 'unique%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| unique_checks | ON |
+---------------+-------+
1 row in set (0.01 sec)
GreatSQL > SHOW VARIABLES LIKE 'innodb_change_buffering';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| innodb_change_buffering | all |
+-------------------------+-------+
1 row in set (0.01 sec)
-
创建测试表sbtest_uq，在（c,pad）上创建唯一索引。
CREATE TABLE `sbtest_uq` ( `id` int NOT NULL AUTO_INCREMENT, `k` int NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k_1` (`k`), unique key `uk_c_pad`(`c`,`pad`) ) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ; -
在sbtest_uq中插入唯一索引列(c,pad)='1','1'的一条数据(id=1)。
GreatSQL > TRUNCATE TABLE sbtest_uq;
Query OK, 0 rows affected (0.10 sec)
-- 插入测试数据
GreatSQL > insert into sbtest_uq(id,k,c,pad) values(1,1,'1','1');
Query OK, 1 row affected (0.01 sec)
-- 插入其他数据
GreatSQL > insert into sbtest_uq select * from sbtest1 where id>1;
Query OK, 999999 rows affected (55.99 sec)
Records: 999999 Duplicates: 0 Warnings: 0
- 在master节点，执行其他DML操作，将sbtest_uq从buffer pool中挤出；也可以在innodb_buffer_pool_load_at_startup=off下重启实例。
-- 执行其他操作，将sbtest_uq从buffer pool中挤出
-- 或者 在innodb_buffer_pool_load_at_startup=off下重启实例
GreatSQL > TRUNCATE TABLE sbtest12;
Query OK, 0 rows affected (0.51 sec)
GreatSQL > insert into sbtest12 SELECT * FROM sbtest2;
Query OK, 1000000 rows affected (28.36 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
-- 检查buffer pool中已没有sbtest_uq
GreatSQL > SELECT * FROM sys.innodb_buffer_stats_by_table WHERE object_schema='test' and object_name='sbtest_uq';
Empty set (0.26 sec)
- 在slave节点1，确认buffer pool中没有表sbtest_uq的数据。
-- 检查buffer pool中已没有sbtest_uq
GreatSQL > SELECT * FROM sys.innodb_buffer_stats_by_table WHERE object_schema='test' and object_name='sbtest_uq';
Empty set (0.26 sec)
- 在slave节点2，将sbtest_uq的索引数据加载到buffer pool中。
GreatSQL > SELECT * FROM sbtest_uq WHERE c='1' and pad='1';
+----+---+---+-----+
| id | k | c | pad |
+----+---+---+-----+
| 1 | 1 | 1 | 1 |
+----+---+---+-----+
1 row in set (0.19 sec)
GreatSQL > SELECT * FROM sys.innodb_buffer_stats_by_table WHERE object_schema='test' and object_name='sbtest_uq';
+---------------+-------------+------------+-----------+-------+--------------+-----------+-------------+
| object_schema | object_name | allocated | data | pages | pages_hashed | pages_old | rows_cached |
+---------------+-------------+------------+-----------+-------+--------------+-----------+-------------+
| test | sbtest_uq | 112.00 KiB | 48.45 KiB | 7 | 0 | 7 | 413 |
+---------------+-------------+------------+-----------+-------+--------------+-----------+-------------+
1 row in set (0.28 sec)
- 在master节点插入唯一索引列(c,pad)='1','1'的另一条数据（id=9999999）。
-- 关闭唯一索引检查
GreatSQL > set unique_checks=OFF;
Query OK, 0 rows affected (0.00 sec)
-- 插入重复数据
GreatSQL > insert into sbtest_uq(id,k,c,pad) values(9999999,1,'1','1');
Query OK, 1 row affected (0.01 sec)
- 检查master和slave上的数据及复制状态。
master节点：(c,pad)列上已存在重复数据。
-- 查询表sbtest_uq，确认c,pad列上已存在重复数据
GreatSQL > SELECT * FROM sbtest_uq WHERE id in(1,9999999);
+---------+---+---+-----+
| id | k | c | pad |
+---------+---+---+-----+
| 1 | 1 | 1 | 1 |
| 9999999 | 1 | 1 | 1 |
+---------+---+---+-----+
2 rows in set (0.00 sec)
slave节点1：(c,pad)列上已存在重复数据；复制线程Slave_SQL_Running=Yes
GreatSQL > SELECT * FROM sbtest_uq WHERE id in(1,9999999);
+---------+---+---+-----+
| id | k | c | pad |
+---------+---+---+-----+
| 1 | 1 | 1 | 1 |
| 9999999 | 1 | 1 | 1 |
+---------+---+---+-----+
2 rows in set (0.07 sec)
GreatSQL > show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
。。。。。。
Slave_IO_Running: Yes
**Slave_SQL_Running: Yes**
。。。。。。
Retrieved_Gtid_Set: e6ea0eeb-591c-11ef-8984-00163e919d0a:209358-209371
Executed_Gtid_Set: e6cbf195-591c-11ef-99d9-00163e33cf00:1-1572367,
e6d361d5-591c-11ef-b1d5-00163e0ea4a5:1-138208,
e6ea0eeb-591c-11ef-8984-00163e919d0a:1-209371
......
1 row in set, 1 warning (0.00 sec)
slave节点2：(c,pad)列上没有重复数据；复制线程Slave_SQL_Running=No，提示唯一索引重复。
GreatSQL > SELECT * FROM sbtest_uq WHERE id in(1,9999999);
+----+---+---+-----+
| id | k | c | pad |
+----+---+---+-----+
| 1 | 1 | 1 | 1 |
+----+---+---+-----+
1 row in set (0.00 sec)
GreatSQL > show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
......
Slave_IO_Running: Yes
**Slave_SQL_Running: No**
......
Last_Errno: 1062
Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'e6ea0eeb-591c-11ef-8984-00163e919d0a:209371' at master log greatdb-bin.000020, end_log_pos 606. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
......
Last_SQL_Errno: 1062
Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'e6ea0eeb-591c-11ef-8984-00163e919d0a:209371' at master log greatdb-bin.000020, end_log_pos 606. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
......
Retrieved_Gtid_Set: e6ea0eeb-591c-11ef-8984-00163e919d0a:209366-209371
Executed_Gtid_Set: e6cbf195-591c-11ef-99d9-00163e33cf00:1-1572367,
e6d361d5-591c-11ef-b1d5-00163e0ea4a5:1-138208,
e6ea0eeb-591c-11ef-8984-00163e919d0a:1-209370
......
1 row in set, 1 warning (0.00 sec)
GreatSQL > SELECT * FROM performance_schema.replication_applier_status_by_worker\G
*************************** 1. row ***************************
CHANNEL_NAME:
WORKER_ID: 1
THREAD_ID: NULL
SERVICE_STATE: OFF
LAST_ERROR_NUMBER: 1062
LAST_ERROR_MESSAGE: Worker 1 failed executing transaction 'e6ea0eeb-591c-11ef-8984-00163e919d0a:209371' at master log greatdb-bin.000020, end_log_pos 606; Could not execute Write_rows event on table test.sbtest_uq; Duplicate entry '1-1' for key 'sbtest_uq.uk_c_pad', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log greatdb-bin.000019, end_log_pos 606
LAST_ERROR_TIMESTAMP: 2026-09-14 09:35:47.947584
LAST_APPLIED_TRANSACTION: e6ea0eeb-591c-11ef-8984-00163e919d0a:209370
LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2026-09-14 09:31:27.529353
LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2026-09-14 09:31:27.529353
LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP: 2026-09-14 09:31:28.832359
LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP: 2026-09-14 09:31:55.294233
APPLYING_TRANSACTION: e6ea0eeb-591c-11ef-8984-00163e919d0a:209371
APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2026-09-14 09:35:47.858275
APPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2026-09-14 09:35:47.858275
APPLYING_TRANSACTION_START_APPLY_TIMESTAMP: 2026-09-14 09:35:47.861972
LAST_APPLIED_TRANSACTION_RETRIES_COUNT: 0
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE:
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
APPLYING_TRANSACTION_RETRIES_COUNT: 0
APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE:
APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
小结
测试过程验证了在innodb_change_buffering=all，unique_checks=OFF时，唯一索引列中存在插入重复数据可能。在主从复制的slave节点，如果buffer pool中没有唯一索引信息，该事务会正常回放，插入重复数据；如果buffer pool中存在唯一索引的信息，该事务回放时，会由于唯一索引重复而失败，从而导致sql线程中断。
对于客户的问题，由于buffer pool中没有索引UK0_fnd_xxxxx_yyy的信息，所以在master节点能够插入重复数据；但在slave端回放binlog时，如果索引UK0_fnd_xxxxx_yyy的信息在buffer pool中，可以正常插入，但唯一索引列有重复值；如果索引UK0_fnd_xxxxx_yyy的信息在buffer pool中，会由于唯一索引重复从而导致sql线程中断。
总结
经实验验证，当同时满足以下三个条件时，存在向唯一索引列插入重复值的风险：
- 禁用了唯一性检查（
unique_checks=OFF） - 启用了变更缓冲（Change Buffer）
- 待更新的唯一索引不在缓冲池（Buffer Pool）中
通常情况下，应用系统禁用唯一性检查（unique_checks=OFF）是为了提升数据加载速度。但此操作的前提是必须确保唯一索引列的数据绝对唯一；若无法保证，请务必不要禁用该检查，否则极易导致数据重复，进而威胁业务系统的正常运行。在主从复制架构中，这还可能导致从库（Slave）的 SQL 线程中断。此外，在导入 mysqldump 导出文件时需特别留意：默认配置下 mysqldump 会自动在文件中添加 unique_checks=0，若未加注意，极易造成唯一索引列的数据重复。
在 GreatSQL 8.4.4 版本中，innodb_change_buffering 的默认值已调整为 none。这主要是考虑到在现代 SSD 硬件环境下，变更缓冲（Change Buffer）带来的性能收益已有所下降。将该默认值设为 none，不仅能提升数据库开箱即用的稳定性和可控性，更能有效避免在 unique_checks=OFF 时向唯一索引插入重复值的问题。
浙公网安备 33010602011771号