MySQL 主從 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-11
1 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: 0
Master_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 '
keYTZRO4JcACRQAAACYqAAAAAHoAAAAAAAEACHRlc3QyMDIzABJ0ZXN0X2F1dG9pbmNyZW1lbnQA
AwMPAwKQAQCCO6qB
keYTZR+4JcACSQAAAG8qAAAAAHoAAAAAAAEAAgAD///4AQAAAAYA5byg5LiJ6QMAAPgEAAAACQDl
vKDkuInkuLDpAwAAtw/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 = 331
COMMIT/!/;這里可以看到REPLACE INTO操作對應的binlog日志記錄其實是update操作，從庫節點在應用update操作時，發現命中數據時，對應的autoincrement是沒有變化的。
3.2 REPLACE INTO 操作的官方定義是什么？
官方對于 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”.
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)
{
/
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 '
qDAVZRM7eYEbRgAAAJkfAAAAAOEBAAAAAAEACHRlc3QyMDIzABR0ZXN0X2F1dG9pbmNyZW1lbnRf
MgACAw8CkAEAFSqQxg==
qDAVZR87eYEbPgAAANcfAAAAAOEBAAAAAAEAAgAC///8AwAAAAYA5ZC05Lmd/AMAAAAGAOmDkeWN
gbuF/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 = 699
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日志記錄的同樣是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。
五、問題總結
- REPLACE INTO 操作在表存在自增主鍵且包含唯一索引的情況下，當出現數據沖突的時候，會觸發AUTO_INCREMENT在主從節點的不一致，一旦主從發生切換，就會造成業務的寫入報主鍵沖突的錯誤。解決建議：業務更改實現方式，避免使用replace into，或者使用MySQL8.0 及以上的版本來解決該問題。
- 該問題是一個官方的BUG，不過并沒有在MySQL5.7的版本中得到修復 。
https://bugs.mysql.com/bug.php?id=83030
參考文獻：
- https://bugs.mysql.com/bug.php?id=83030
- https://dev.mysql.com/worklog/task/?id=6204
- https://bugs.mysql.com/bug.php?id=20188
