本文介绍了 MySQL5.7 中常见的 replace into 操作造成的主从 auto_increment 不一致现象，一旦触发了主从切换，业务的正常插入操作会触发主键冲突的报错提示。
一、问题描述
1.1 问题现象
在 MySQL 5.7 版本中，REPLACE INTO 操作在表存在自增主键的情况下，可能会出现表的 auto_increment 值主从不一致现象，如果在此期间发生主从故障切换，当原来的 slave 节点变成了新的 master 节点，由于表的 auto_increment 值是小于原主库的，当业务继续写入时，就会收到主键冲突的报错提示。
相关报错信息如下：
! 报错提示
ERROR 1062 (23000): Duplicate entry 'XXX' for key 'PRIMARY'
1.2 影响评估
在业务逻辑中使用了 Replace into,或者 INSERT...ON DUPLICATE KEY UPDATE。
一旦出现了表的 auto_increment 值主从不一致现象，在出现 MySQL 主从故障切换后，业务的正常写入会报主键冲突的错误，当 auto_increment 相差不多，或许在业务重试的时候会跳过报错，但是 auto_increment 相差较多时，会超出业务重试的次数，这样造成的影响会更大。
二、问题复现
2.1 环境搭建
这里在测试环境中，搭建 MySQL 社区版 5.7 版本，一主一从的架构。
【OS】：CentOS Linux release 7.3
【MySQL】：社区版本 5.7
【主从架构】：一主一从
【库表信息】：库名：test2023
表名：test_autoincrement
表结构如下：
CREATE TABLE `test_autoincrement` ( `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 DEFAULT CHARSET=utf8mb4;
复制代码
2.2 准备测试数据
MySQL [test2023]> insert into test_autoincrement(name,uid) select '张三',1001;Query OK, 1 row affected (0.08 sec)Records: 1 Duplicates: 0 Warnings: 0 MySQL [test2023]> insert into test_autoincrement(name,uid) select '李四',1002;Query OK, 1 row affected (0.06 sec)Records: 1 Duplicates: 0 Warnings: 0 MySQL [test2023]>MySQL [test2023]> insert into test_autoincrement(name,uid) select '王五',1003;Query OK, 1 row affected (0.08 sec)Records: 1 Duplicates: 0 Warnings: 0
复制代码
正常情况下，插入一行数据，影响的行数是 1。
此时查看主从节点表的 autoincrement 值，可以看到此时主从的 AUTO_INCREMENT 是一致的，都是 4，即自增主键下一次申请的值是 4。
2.3 问题复现模拟
2.3.1 模拟 REPLACE INTO 操作
MySQL [test2023]> REPLACE INTO test_autoincrement (name,uid) values('张三丰',1001);Query OK, 2 rows affected (0.01 sec)
复制代码
这里通过 REPLACE INTO 操作判断，如果存在唯一 ID 为 1001 的记录，那么将 name 字段的值更改为"张三丰",可发现此时影响的行数是 2。现在我们再次查看主从节点表的 autoincrement 值。
此时出现了主从节点表的 AUTO_INCREMENT 不一致现象。
2.3.2 模拟主从切换
由于是在测试环境，这里就直接进行了主从关系的更改。
（1）停止当前 slave 节点的复制线程
MySQL [test2023]> stop slave;Query OK, 0 rows affected (0.08 sec)
复制代码
（2）查看当前 slave 节点的 Executed_Gtid_Set 值
MySQL [test2023]> show master status\G*************************** 1. row *************************** File: binlog.000002 Position: 4317 Binlog_Do_DB: Binlog_Ignore_DB:Executed_Gtid_Set: 9cc90407-ff89-11ed-8b7a-fa163e2d11e1:1-82,a0c1d6ff-5764-11ee-94ea-fa163e2d11e1:1-111 row in set (0.01 sec)
复制代码
（3）重做主从关系
MySQL [test2023]> CHANGE MASTER TO MASTER_HOST = '原slave节点的IP地址', MASTER_USER = '复制账户', MASTER_PASSWORD = '密码', MASTER_PORT = 端口, MASTER_AUTO_POSITION = 1 ;Query OK, 0 rows affected, 2 warnings (0.21 sec) MySQL [test2023]> start slave;Query OK, 0 rows affected (0.05 sec)MySQL [test2023]> show slave status\G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: XXX Master_User: XXX Master_Port: XXX Connect_Retry: 60 Master_Log_File: binlog.000002 Read_Master_Log_Pos: 4317 Relay_Log_File: relay.000004 Relay_Log_Pos: 445 Relay_Master_Log_File: binlog.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 4317 Relay_Log_Space: 726 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 461470011 Master_UUID: a0c1d6ff-5764-11ee-94ea-fa163e2d11e1 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: a0c1d6ff-5764-11ee-94ea-fa163e2d11e1:11 Executed_Gtid_Set: 9cc90407-ff89-11ed-8b7a-fa163e2d11e1:1-82,a0c1d6ff-5764-11ee-94ea-fa163e2d11e1:1-11 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version:1 row in set (0.00 sec)
复制代码
2.3.3 模拟业务正常写入
MySQL [test2023]> insert into test_autoincrement(name,uid) select '赵六',1004;ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'
复制代码
到这里我们看到了预期的报错现象，如果是正常业务系统，这里的主从节点表的 AUTO_INCREMENT 可能会相差非常大，业务的正常插入就会持续报错了。
意味着真实的操作是先做 delete 操作，然后再进行 insert。
三、原因分析
3.1 为什么从库节点的 autoincrement 没有变化？
# at 10790#230927 16:23:45 server id 46147000 end_log_pos 10863 CRC32 0x85c60fb7 Update_rows: table id 122 flags: STMT_END_F BINLOG 'keYTZRO4JcACRQAAACYqAAAAAHoAAAAAAAEACHRlc3QyMDIzABJ0ZXN0X2F1dG9pbmNyZW1lbnQAAwMPAwKQAQCCO6qBkeYTZR+4JcACSQAAAG8qAAAAAHoAAAAAAAEAAgAD///4AQAAAAYA5byg5LiJ6QMAAPgEAAAACQDlvKDkuInkuLDpAwAAtw/GhQ=='/*!*/;### UPDATE `test2023`.`test_autoincrement`### WHERE### @1=1 /* INT meta=0 nullable=0 is_null=0 */### @2='张三' /* VARSTRING(400) meta=400 nullable=0 is_null=0 */### @3=1001 /* INT meta=0 nullable=0 is_null=0 */### SET### @1=4 /* INT meta=0 nullable=0 is_null=0 */### @2='张三丰' /* VARSTRING(400) meta=400 nullable=0 is_null=0 */### @3=1001 /* INT meta=0 nullable=0 is_null=0 */# at 10863#230927 16:23:45 server id 46147000 end_log_pos 10894 CRC32 0xe204d99b Xid = 331COMMIT/*!*/;
复制代码
这里可以看到 REPLACE INTO 操作对应的 binlog 日志记录其实是 update 操作，从库节点在应用 update 操作时，发现命中数据时，对应的 autoincrement 是没有变化的。
3.2 REPLACE INTO 操作的官方定义是什么？
官方对于 REPLACE INTO 的定义如下：
摘选自https://dev.mysql.com/doc/refman/5.7/en/replace.html
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”.
REPLACE is a MySQL extension to the SQL standard. It either inserts, or deletes and inserts. For another MySQL extension to standard SQL—that either inserts or updates—see Section 13.2.5.2, “INSERT ... ON DUPLICATE KEY UPDATE Statement”.
这里可以看到一张表包含主键或者唯一键的情况下，replace 操作会判断原有的数据行是否存在，如果存在的话，就先删除旧的数据，然后进行 insert 操作，如果不存在的话，就和 insert 操作时一样的。
第二段也提到了 INSERT ... ON DUPLICATE KEY UPDATE Statement ，其实这个操作也会造成上面的主从 autoincrement 不一致现象，这里就不展开讨论了。
! Note
REPLACE makes sense only if a table has a PRIMARY KEY or UNIQUE index. Otherwise, it becomes equivalent to INSERT, because there is no index to be used to determine whether a new row duplicates another.
3.3 为什么 REPLACE INTO 操作在 binlog 日志中记录的是 update 操作？
这里我们通过源码文件 sql_insert.cc 和 log_event.cc 进行分析。
sql_insert.cc:.../* Check if there is more uniq keys after field */ static int last_uniq_key(TABLE *table,uint keynr){ /* When an underlying storage engine informs that the unique key conflicts are not reported in the ascending order by setting the HA_DUPLICATE_KEY_NOT_IN_ORDER flag, we cannot rely on this information to determine the last key conflict. The information about the last key conflict will be used to do a replace of the new row on the conflicting row, rather than doing a delete (of old row) + insert (of new row). Hence check for this flag and disable replacing the last row by returning 0 always. Returning 0 will result in doing a delete + insert always. */ if (table->file->ha_table_flags() & HA_DUPLICATE_KEY_NOT_IN_ORDER){ return 0; } while (++keynr < table->s->keys){ if (table->key_info[keynr].flags & HA_NOSAME){ return 0; } } return 1;}... /* The manual defines the REPLACE semantics that it is either an INSERT or DELETE(s) + INSERT; FOREIGN KEY checks in InnoDB do not function in the defined way if we allow MySQL to convert the latter operation internally to an UPDATE. We also should not perform this conversion if we have timestamp field with ON UPDATE which is different from DEFAULT. Another case when conversion should not be performed is when we have ON DELETE trigger on table so user may notice that we cheat here. Note that it is ok to do such conversion for tables which have ON UPDATE but have no ON DELETE triggers, we just should not expose this fact to users by invoking ON UPDATE triggers. */ if (last_uniq_key(table,key_nr) && !table->file->referenced_by_foreign_key() && (!table->triggers || !table->triggers->has_delete_triggers())) { if ((error=table->file->ha_update_row(table->record[1], table->record[0])) && error != HA_ERR_RECORD_IS_THE_SAME) goto err; if (error != HA_ERR_RECORD_IS_THE_SAME) info->stats.deleted++; else error= 0; thd->record_first_successful_insert_id_in_cur_stmt(table->file->insert_id_for_cur_row); /* Since we pretend that we have done insert we should call its after triggers. */ goto after_trg_n_copied_inc; } else {... }...
复制代码
上述源码中可以看到在主库中 replace 操作其实是 insert 或者 delete + insert
The manual defines the REPLACE semantics that it is either an INSERT or DELETE(s) + INSERT;
复制代码
而 MySQL 在主从同步的 binlog 日志中，将 replace 操作转换为 update 操作的条件为：当发生冲突的键是最后一个唯一键，且没有外键约束，且没有触发器，由于我们的测试表中是没有外键约束，也没有触发器的，所以从库接收到的 binlog 日志中转化为 update 的条件即为最后一个唯一键。
这里，我们再进行测试一下（去掉表中的唯一索引 uid）。
（1）创建新表
CREATE TABLE `test_autoincrement_2` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键', `name` varchar(100) NOT NULL DEFAULT 'test' COMMENT '测试名字', PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4
复制代码
（2）插入测试数据
insert into test_autoincrement_2(name) select '孙七';
insert into test_autoincrement_2(name) select '周八';
insert into test_autoincrement_2(name) select '吴九';
#此时主从表结构是一致的，如下：
CREATE TABLE `test_autoincrement_2` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`name` varchar(100) NOT NULL DEFAULT 'test' COMMENT '测试名字',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4
复制代码
（3）replace into 操作验证主库和从库的 AUTO_INCREMENT
MySQL [test2023]> REPLACE INTO test_autoincrement_2 (id,name) values(3,'郑十');Query OK, 2 rows affected (0.08 sec)
复制代码
这里我们把 id=3 的这一行数据对应的 name 修改为’郑十’，可发现上述影响的行数是 2。
再次验证主库和从库的 AUTO_INCREMENT，发现并没有发生变化，还是 4。
CREATE TABLE `test_autoincrement_2` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键', `name` varchar(100) NOT NULL DEFAULT 'test' COMMENT '测试名字', PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4
复制代码
（4）分析 binlog 日志文件
# at 8089#230928 15:52:08 server id 461470011 end_log_pos 8151 CRC32 0xc2ff85bb Update_rows: table id 481 flags: STMT_END_F BINLOG 'qDAVZRM7eYEbRgAAAJkfAAAAAOEBAAAAAAEACHRlc3QyMDIzABR0ZXN0X2F1dG9pbmNyZW1lbnRfMgACAw8CkAEAFSqQxg==qDAVZR87eYEbPgAAANcfAAAAAOEBAAAAAAEAAgAC///8AwAAAAYA5ZC05Lmd/AMAAAAGAOmDkeWNgbuF/8I='/*!*/;### UPDATE `test2023`.`test_autoincrement_2`### WHERE### @1=3 /* INT meta=0 nullable=0 is_null=0 */### @2='吴九' /* VARSTRING(400) meta=400 nullable=0 is_null=0 */### SET### @1=3 /* INT meta=0 nullable=0 is_null=0 */### @2='郑十' /* VARSTRING(400) meta=400 nullable=0 is_null=0 */# at 8151#230928 15:52:08 server id 461470011 end_log_pos 8182 CRC32 0xaa39d2a4 Xid = 699COMMIT/*!*/;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 日志记录的同样是 update 操作。只是当表中除了主键外没有额外的唯一键时，replace into 的操作并不会触发从库的 auto_increment 的异常问题。比如上述的案例 REPLACE INTO test_autoincrement_2 (id,name) values(3,'郑十');，这里仅更改了 name 字段，由‘吴九‘修改为’郑十’。但是主键 id 是没有变化的，当然也就不需要再次使用 auto_increment，这里也可以看到主库的 auto_increment 当然也没有发现变化（当表中除了主键外含有额外的唯一键时，是会触发申请 auto_increment 的），binlog 接收的仍然是 update 操作，所以从库的 auto_increment 也是没有变化的，这样就没法造成 auto_increment 和主库不一致的问题了。
四、解决方案
到这里，我们是明白了 replace into 会造成主从的 auto_increment 不一致，但是怎么去解决呢？
4.1 升级到 MySQL 8.0 版本
在 MySQL 8.0 版本中已将 AUTO_INCREMENT 值做了持久化，且在做更新操作时，会将表上的自增列被更新为比 auto_increment 更大的值，auto_increment 值也将被更新。
4.2 修改 AUTO_INCREMENT 值
线上环境可能已经有很多这种情况，在没有触发业务报错的情况下，一般是很难发现这个隐患，如何在日常巡检中找到这些问题才是关键。
巡检逻辑一：这里可以通过巡检判断从库的 max(id) >= AUTO_INCREMENT 的方式来找出已经存在问题的表信息。然后通过 SQL 语句：ALTER TABLE table_name AUTO_INCREMENT = new_value; 进行修改。
巡检步骤可参考：
（1）仅检测某从节点，包含 auto_increment 属性的表，过滤 SQL 如下：
select TABLE_SCHEMA,TABLE_NAME,AUTO_INCREMENT from information_schema.tables where table_schema not in ('information_schema','mysql','performance_schema','sys') AUTO_INCREMENTis not null \G
复制代码
（2）加锁后读表信息，语句如下：
① 给表加锁
lock tables table_name write;
复制代码
②读取数据和表 auto_increment 值进行比对
MAXID=select max(id) from table_name;AUTO_INCREMENT=select AUTO_INCREMENT from information_schema.tables where TABLE_NAME='t1' ;
复制代码
③ 判断条件
如果 MAXID >= AUTO_INCREMENT , 判断为异常
巡检逻辑二：可以在高可用切换的时候增加 AUTO_INCREMENT 值判断，如果 AUTO_INCREMENT 值不一致，则不发生切换，不过这里的 slave 节点 AUTO_INCREMENT 的值本身可能因为延迟等问题，就会稍落后 maste 主节点，正常的巡检还是有难度的，还有就是当 MySQL 主从切换触发时，如果是因为原主库宕机了，不触发切换也会有问题，所以还是需要提前尽快把这个隐患排除掉。
4.3 禁用 replace into 操作
业务侧禁用 replace into 或 insert ... on duplicate key update ,实现方式可以通过代码逻辑来实现。
4.4 replace into 操作的表不增加其他唯一索引
这里其实实现还是有难度的，自增 id 是不可控的，业务一般是不会使用数据库自带的自增 id。
五、问题总结
1. REPLACE INTO 操作在表存在自增主键且包含唯一索引的情况下，当出现数据冲突的时候，会触发 AUTO_INCREMENT 在主从节点的不一致，一旦主从发生切换，就会造成业务的写入报主键冲突的错误。解决建议：业务更改实现方式，避免使用 replace into，或者使用 MySQL8.0 及以上的版本来解决该问题。
2. 该问题是一个官方的 BUG，不过并没有在 MySQL5.7 的版本中得到修复 。
文章转载自：vivo互联网技术
原文链接：https://www.cnblogs.com/vivotech/p/18118665
体验地址：http://www.jnpfsoft.com/?from=001
评论