专栏名称: 高可用架构

高可用架构公众号。

MySQL 主从 AUTO_INCREMENT 不一致问题分析

高可用架构 · 公众号 · · 2024-04-12 12:11

正文

本文介绍了 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

MySQL 主从 AUTO_INCREMENT 不一致问题分析

正文

请到「今天看啥」查看全文