MySQL 主從 AUTO_INCREMENT 不一致問題分析
一、問題描述
1.1 問題現(xiàn)象
在 MySQL 5.7 版本中，REPLACE INTO 操作在表存在自增主鍵的情況下，可能會出現(xiàn)表的auto_increment值主從不一致現(xiàn)象，如果在此期間發(fā)生主從故障切換，當(dāng)原來的slave節(jié)點(diǎn)變成了新的master節(jié)點(diǎn)，由于表的auto_increment值是小于原主庫的，當(dāng)業(yè)務(wù)繼續(xù)寫入時，就會收到主鍵沖突的報(bào)錯提示。
相關(guān)報(bào)錯信息如下：
! 報(bào)錯提示
ERROR 1062 (23000): Duplicate entry 'XXX' for key 'PRIMARY'
1.2 影響評估
在業(yè)務(wù)邏輯中使用了Replace into,或者INSERT...ON DUPLICATE KEY UPDATE。
一旦出現(xiàn)了表的auto_increment值主從不一致現(xiàn)象，在出現(xiàn)MySQL主從故障切換后，業(yè)務(wù)的正常寫入會報(bào)主鍵沖突的錯誤，當(dāng)auto_increment相差不多，或許在業(yè)務(wù)重試的時候會跳過報(bào)錯，但是auto_increment相差較多時，會超出業(yè)務(wù)重試的次數(shù)，這樣造成的影響會更大。
二、問題復(fù)現(xiàn)
2.1 環(huán)境搭建
這里在測試環(huán)境中，搭建MySQL社區(qū)版 5.7 版本，一主一從的架構(gòu)。
【OS】：CentOS Linux release 7.3
【MySQL】：社區(qū)版本 5.7
【主從架構(gòu)】：一主一從
【庫表信息】：庫名：test2023
表名：test_autoincrement
表結(jié)構(gòu)如下：
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 準(zhǔn)備測試數(shù)據(jù)
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正常情況下，插入一行數(shù)據(jù)，影響的行數(shù)是1。
此時查看主從節(jié)點(diǎn)表的autoincrement值，可以看到此時主從的AUTO_INCREMENT是一致的，都是4，即自增主鍵下一次申請的值是4。
2.3 問題復(fù)現(xiàn)模擬
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字段的值更改為"張三豐",可發(fā)現(xiàn)此時影響的行數(shù)是2?，F(xiàn)在我們再次查看主從節(jié)點(diǎn)表的autoincrement值。
此時出現(xiàn)了主從節(jié)點(diǎn)表的AUTO_INCREMENT不一致現(xiàn)象。
2.3.2 模擬主從切換
由于是在測試環(huán)境，這里就直接進(jìn)行了主從關(guān)系的更改。
（1）停止當(dāng)前slave節(jié)點(diǎn)的復(fù)制線程
MySQL [test2023]> stop slave;
Query OK, 0 rows affected (0.08 sec)（2）查看當(dāng)前slave節(jié)點(diǎn)的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）重做主從關(guān)系
MySQL [test2023]> CHANGE MASTER TO MASTER_HOST = '原slave節(jié)點(diǎn)的IP地址', MASTER_USER = '復(fù)制賬戶', 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 模擬業(yè)務(wù)正常寫入
MySQL [test2023]> insert into test_autoincrement(name,uid) select '趙六',1004; ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'
到這里我們看到了預(yù)期的報(bào)錯現(xiàn)象，如果是正常業(yè)務(wù)系統(tǒng)，這里的主從節(jié)點(diǎn)表的AUTO_INCREMENT可能會相差非常大，業(yè)務(wù)的正常插入就會持續(xù)報(bào)錯了。
意味著真實(shí)的操作是先做delete操作，然后再進(jìn)行insert。
三、原因分析
3.1 為什么從庫節(jié)點(diǎn)的 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操作對應(yīng)的binlog日志記錄其實(shí)是update操作，從庫節(jié)點(diǎn)在應(yīng)用update操作時，發(fā)現(xiàn)命中數(shù)據(jù)時，對應(yīng)的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操作會判斷原有的數(shù)據(jù)行是否存在，如果存在的話，就先刪除舊的數(shù)據(jù)，然后進(jìn)行insert操作，如果不存在的話，就和insert操作時一樣的。
第二段也提到了INSERT ... ON DUPLICATE KEY UPDATE Statement ，其實(shí)這個操作也會造成上面的主從autoincrement不一致現(xiàn)象，這里就不展開討論了。
! 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進(jìn)行分析。
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 操作其實(shí)是insert 或者 delete + insert
The manual defines the REPLACE semantics that it is either an INSERT or DELETE(s) + INSERT;而 MySQL 在主從同步的binlog日志中，將replace操作轉(zhuǎn)換為update操作的條件為：當(dāng)發(fā)生沖突的鍵是最后一個唯一鍵，且沒有外鍵約束，且沒有觸發(fā)器，由于我們的測試表中是沒有外鍵約束，也沒有觸發(fā)器的，所以從庫接收到的binlog日志中轉(zhuǎn)化為update的條件即為最后一個唯一鍵。
這里，我們再進(jìn)行測試一下（去掉表中的唯一索引uid）。
（1）創(chuàng)建新表
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）插入測試數(shù)據(jù)
insert into test_autoincrement_2(name) select '孫七';
insert into test_autoincrement_2(name) select '周八';
insert into test_autoincrement_2(name) select '吳九';
#此時主從表結(jié)構(gòu)是一致的，如下：
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 操作驗(yàn)證主庫和從庫的AUTO_INCREMENT
MySQL [test2023]> REPLACE INTO test_autoincrement_2 (id,name) values(3,'鄭十');
Query OK, 2 rows affected (0.08 sec)這里我們把id=3的這一行數(shù)據(jù)對應(yīng)的name修改為’鄭十’，可發(fā)現(xiàn)上述影響的行數(shù)是2。
再次驗(yàn)證主庫和從庫的AUTO_INCREMENT，發(fā)現(xiàn)并沒有發(fā)生變化，還是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/;總結(jié)：可發(fā)現(xiàn)binlog日志記錄的同樣是update 操作。只是當(dāng)表中除了主鍵外沒有額外的唯一鍵時，replace into的操作并不會觸發(fā)從庫的auto_increment的異常問題。比如上述的案例REPLACE INTO test_autoincrement_2 (id,name) values(3,'鄭十');，這里僅更改了name字段，由‘吳九‘修改為’鄭十’。但是主鍵id是沒有變化的，當(dāng)然也就不需要再次使用auto_increment，這里也可以看到主庫的auto_increment當(dāng)然也沒有發(fā)現(xiàn)變化（當(dāng)表中除了主鍵外含有額外的唯一鍵時，是會觸發(fā)申請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 值
線上環(huán)境可能已經(jīng)有很多這種情況，在沒有觸發(fā)業(yè)務(wù)報(bào)錯的情況下，一般是很難發(fā)現(xiàn)這個隱患，如何在日常巡檢中找到這些問題才是關(guān)鍵。
巡檢邏輯一：這里可以通過巡檢判斷從庫的max(id) >= AUTO_INCREMENT的方式來找出已經(jīng)存在問題的表信息。然后通過SQL語句：ALTER TABLE table_name AUTO_INCREMENT = new_value; 進(jìn)行修改。
巡檢步驟可參考：
（1）僅檢測某從節(jié)點(diǎn)，包含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;②讀取數(shù)據(jù)和表auto_increment值進(jìn)行比對
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值不一致，則不發(fā)生切換，不過這里的slave節(jié)點(diǎn)AUTO_INCREMENT的值本身可能因?yàn)檠舆t等問題，就會稍落后maste主節(jié)點(diǎn)，正常的巡檢還是有難度的，還有就是當(dāng)MySQL主從切換觸發(fā)時，如果是因?yàn)樵鲙戾礄C(jī)了，不觸發(fā)切換也會有問題，所以還是需要提前盡快把這個隱患排除掉。
4.3 禁用 replace into 操作
業(yè)務(wù)側(cè)禁用replace into 或 insert ... on duplicate key update ,實(shí)現(xiàn)方式可以通過代碼邏輯來實(shí)現(xiàn)。
4.4 replace into操作的表不增加其他唯一索引
這里其實(shí)實(shí)現(xiàn)還是有難度的，自增id是不可控的，業(yè)務(wù)一般是不會使用數(shù)據(jù)庫自帶的自增id。
五、問題總結(jié)
- REPLACE INTO 操作在表存在自增主鍵且包含唯一索引的情況下，當(dāng)出現(xiàn)數(shù)據(jù)沖突的時候，會觸發(fā)AUTO_INCREMENT在主從節(jié)點(diǎn)的不一致，一旦主從發(fā)生切換，就會造成業(yè)務(wù)的寫入報(bào)主鍵沖突的錯誤。解決建議：業(yè)務(wù)更改實(shí)現(xiàn)方式，避免使用replace into，或者使用MySQL8.0 及以上的版本來解決該問題。
- 該問題是一個官方的BUG，不過并沒有在MySQL5.7的版本中得到修復(fù) 。
https://bugs.mysql.com/bug.php?id=83030
參考文獻(xiàn)：
- https://bugs.mysql.com/bug.php?id=83030
- https://dev.mysql.com/worklog/task/?id=6204
- https://bugs.mysql.com/bug.php?id=20188
