• 新闻资讯
  • MySQL 主從AUTO_INCREMENT 不一致問題分析

MySQL 主從AUTO_INCREMENT 不一致問題分析

2024.04.07

一、問題描述

1.1 問題現象

在MySQL 5.7 版本中，REPLACE INTO 操作在表存在自增主鍵的情況下，可能會出現表的auto_increment值主從不一致現象，如果在此期間發生主從故障切換，當原來的slave節點變成了新的master節點，由於表的auto_increment值是小於原始主庫的，當業務繼續寫入時，就會收到主鍵衝突的報錯提示。

相關報錯資訊如下：

! 報錯提示

錯誤 1062 (23000)：鍵「PRIMARY」的條目「XXX」重複

1.2 影響評估

在業務邏輯中使用了Replace into,或INSERT...ON DUPLICATE KEY UPDATE。

一旦出現了表的auto_increment值主從不一致現象，在出現MySQL主從故障切換後，業務的正常寫入會報主鍵衝突的錯誤，當auto_increment相差不多，或許在業務重試的時候會跳過報錯，但是auto_increment相差較多時，會超出業務重試的次數，這樣造成的影響會更大。

二、問題復現

2.1 環境搭建

這裡在測試環境中，搭建MySQL社群版5.7 版本，一主一從的架構。

【作業系統】：CentOS Linux 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;

  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.

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.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.

正常情況下，插入一行數據，影響的行數是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)
  • 1.
  • 2.

這裡透過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)
  • 1.
  • 2.

（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)
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.

（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)
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.
  • 25.
  • 26.
  • 27.
  • 28.
  • 29.
  • 30.
  • 31.
  • 32.
  • 33.
  • 34.
  • 35.
  • 36.
  • 37.
  • 38.
  • 39.
  • 40.
  • 41.
  • 42.
  • 43.
  • 44.
  • 45.
  • 46.
  • 47.
  • 48.
  • 49.
  • 50.
  • 51.
  • 52.
  • 53.
  • 54.
  • 55.
  • 56.
  • 57.
  • 58.
  • 59.
  • 60.
  • 61.
  • 62.
  • 63.
  • 64.
  • 65.

2.3.3 模擬業務正常寫入

MySQL [test2023]> insert into test_autoincrement(name,uid) select '趙六',1004;錯誤 1062 (23000)：鍵“PRIMARY”的重複條目“4”

到這裡我們看到了預期的報錯現象，如果是正常業務系統，這裡的主從節點表的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/!/;
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.

這裡可以看到REPLACE INTO操作對應的binlog日誌記錄其實是update操作，從函式庫節點在應用update操作時，發現命中資料時，對應的autoincrement是沒有變化的。

3.2 REPLACE INTO 操作的官方定義是什麼？

官方對於REPLACE INTO 的定義如下：

摘選自

https://dev.mysql.com/doc/refman/5.7/en/replace.html

REPLACE 的工作方式與 INSERT 完全相同，只不過如果表中的舊行與 PRIMARY KEY 或 UNIQUE 索引的新行具有相同的值，則在插入新行之前會刪除舊行。請參閱第 13.2.5 節「INSERT 語句」。

REPLACE 是 MySQL 對 SQL 標準的擴充。它要么插入，要么刪除並插入。對於標準 SQL 的另一個 MySQL 擴充功能（插入或更新），請參閱第 13.2.5.2 節「INSERT ... ON DUPLICATE KEY UPDATE 語句」。

這裡可以看到一張表包含主鍵或唯一鍵的情況下，replace操作會判斷原有的數據行是否存在，如果存在的話，就先刪除舊的數據，然後進行insert操作，如果不存在的話，就和insert操作時一樣的。

第二段也提到了INSERT ... ON DUPLICATE KEY UPDATE Statement ，其實這個操作也會造成上面的主從autoincrement不一致現象，這裡就不展開討論了。

！筆記

只有當表具有 PRIMARY KEY 或 UNIQUE 索引時，REPLACE 才有意義。否則，它相當於 INSERT，因為沒有索引可用於確定新行是否與另一行重複。

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
    {...
}
...
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.
  • 25.
  • 26.
  • 27.
  • 28.
  • 29.
  • 30.
  • 31.
  • 32.
  • 33.
  • 34.
  • 35.
  • 36.
  • 37.
  • 38.
  • 39.
  • 40.
  • 41.
  • 42.
  • 43.
  • 44.
  • 45.
  • 46.
  • 47.
  • 48.
  • 49.
  • 50.
  • 51.
  • 52.
  • 53.
  • 54.
  • 55.
  • 56.
  • 57.
  • 58.
  • 59.
  • 60.
  • 61.

上述原始碼中可以看到在主庫中replace 操作其實是insert 或delete + insert

The manual defines the REPLACE semantics that it is either an INSERT or DELETE(s) + INSERT;
  • 1.

而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
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.

（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
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.

（3）replace into 操作驗證主庫和從庫的AUTO_INCREMENT

MySQL [test2023]> REPLACE INTO test_autoincrement_2 (id,name) values(3,'郑十');
Query OK, 2 rows affected (0.08 sec)
  • 1.
  • 2.

這裡我們把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
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.

（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/;
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.

總結：可發現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
  • 1.

（2）加鎖後讀表訊息，語句如下：

① 將錶加鎖

lock tables table_name write;
  • 1.

②讀取資料和表auto_increment值進行比對

MAXID=select max(id) from table_name;
AUTO_INCREMENT=select AUTO_INCREMENT from information_schema.tables where TABLE_NAME='t1' ;
  • 1.
  • 2.

③ 判斷條件

如果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的版本中修復。

https://bugs.mysql.com/bug.php?id=83030