《Mysql必讀深入探尋mysql自增列導致主鍵重復問題的原因》要點：
本文介紹了Mysql必讀深入探尋mysql自增列導致主鍵重復問題的原因，希望對您有用。如果有疑問，可以聯系我們。
廢話少說,進入正題.MYSQL教程
???? 拿到問題后,首先查看現場,發現問題表的中記錄的最大值比自增列的值要大,那么很明顯,當有記錄進行插入時,自增列產生的值就有可能與已有的記錄主鍵沖突,導致出錯.首先想辦法解決問題,通過人工調大自增列的值,保證大于表內已有的主鍵即可,調整后,導數據正常.問題是解決了,接下來要搞清楚問題原因,什么操作導致了這種現象的發生呢?MYSQL教程
????? 這里有一種可能,即業務邏輯包含更新自增主鍵的代碼,由于mysql的update動作不會同時更新自增列值,若更新主鍵值比自增列大,也會導致上述現象：記錄最大值比自增主鍵值大.但開發反饋說這張表僅僅存在load data infile操作,不會進行更新主鍵操作,所以這個解釋行不通.繼續分析,表中含有唯一約束,會不會和唯一約束有關,線下實驗模擬沒有重現.后來想想會不會和主備切換有關系,因為前兩天做過一次主備切換.于是乎,配合主備環境作了測試,果然和主備切換有關系,一切問題的來源都清晰了.MYSQL教程
問題發生的前置條件：MYSQL教程
?????? 1.mysql復制基于row模式MYSQL教程
?????? 2.innodb表MYSQL教程
?????? 3.表含有自增主鍵,并且含有唯一約束MYSQL教程
?????? 4.load data infile 采用replace into語法插入數據【遇到重復唯一約束,直接覆蓋】MYSQL教程
問題發生的原理：MYSQL教程
??????? 1.主庫遇到重復unique約束時,進行replace操作；MYSQL教程
??????? 2.replace在主庫上面實際變化為delete+insert,但binlog記錄的是update；MYSQL教程
??????? 3.備庫重做update動作,更新主鍵,但由于update動作不會更新自增列值,導致更新后記錄值大于自增列值MYSQL教程
問題重現實驗：MYSQL教程
|
準備工作MYSQL教程 |
Create table test_autoinc(id int auto_increment, c1 int,c2 varchar(100),primary key(id),unique key(c1));MYSQL教程 insert into test_autoinc(c1,c2) values(1,'abc');MYSQL教程 insert into test_autoinc(c1,c2) values(2,'abc');MYSQL教程 insert into test_autoinc(c1,c2) values(3,'abcdd');MYSQL教程 insert into test_autoinc(c1,c2) values(4,'abcdd');MYSQL教程 insert into test_autoinc(c1,c2) values(5,'abcdd');MYSQL教程 |
|||
|
1MYSQL教程 |
操作MYSQL教程 |
備注MYSQL教程 |
MasterMYSQL教程 |
slaveMYSQL教程 |
|
2MYSQL教程 |
查看自增列值MYSQL教程 Show create tableMYSQL教程 test_autoinc\GMYSQL教程 |
插入5條記錄后,自增列值變為6MYSQL教程 |
CREATE TABLE `test_autoinc` (MYSQL教程 ? `id` int(11) NOT NULL AUTO_INCREMENT,MYSQL教程 ? `c1` int(11) DEFAULT NULL,MYSQL教程 ? `c2` varchar(100) DEFAULT NULL,MYSQL教程 ? PRIMARY KEY (`id`),MYSQL教程 ? UNIQUE KEY `c1` (`c1`)MYSQL教程 ) ENGINE=InnoDBAUTO_INCREMENT=6DEFAULT CHARSET=utf8 |
CREATE TABLE `test_autoinc` (MYSQL教程 ? `id` int(11) NOT NULL AUTO_INCREMENT,MYSQL教程 ? `c1` int(11) DEFAULT NULL,MYSQL教程 ? `c2` varchar(100) DEFAULT NULL,MYSQL教程 ? PRIMARY KEY (`id`),MYSQL教程 ? UNIQUE KEY `c1` (`c1`)MYSQL教程 ) ENGINE=InnoDBAUTO_INCREMENT=6DEFAULT CHARSET=utf8MYSQL教程 ?MYSQL教程 |
|
3MYSQL教程 |
查看表數據MYSQL教程 |
?MYSQL教程 |
id | c1?? | c2??MYSQL教程 ---+------+------MYSQL教程 ?1 |??? 1 | abc?MYSQL教程 ?2 |??? 2 | abc?MYSQL教程 ?3 |??? 3 | abcddMYSQL教程 ?4 |??? 4 | abcddMYSQL教程 ?5 |??? 5 | abcddMYSQL教程 |
id | c1?? | c2??MYSQL教程 ---+------+------MYSQL教程 ?1 |??? 1 | abc?MYSQL教程 ?2 |??? 2 | abc?MYSQL教程 ?3 |??? 3 | abcddMYSQL教程 ?4 |??? 4 | abcddMYSQL教程 ?5 |??? 5 | abcddMYSQL教程 |
|
4MYSQL教程 |
查看binlog位置MYSQL教程 show master status\GMYSQL教程 |
記錄當前binlog位點,MYSQL教程 后續可以查看replace動作產生的binlog事件MYSQL教程 |
mysql-bin.000038MYSQL教程 59242888MYSQL教程 |
?MYSQL教程 |
|
5MYSQL教程 |
replace操作MYSQL教程 replace into test_autoinc(c1,c2) values(2,'eeee');MYSQL教程 |
影響兩條記錄,主庫replace=MYSQL教程 delete+insertMYSQL教程 |
?MYSQL教程 Query OK,?2 rows affectedMYSQL教程 (0.00 sec)MYSQL教程 |
?MYSQL教程 |
|
?MYSQL教程 ?MYSQL教程 6MYSQL教程 |
查看表數據MYSQL教程 |
?MYSQL教程 |
id | c1?? | c2???MYSQL教程 ---+------+-------MYSQL教程 ?1 |??? 1 | abc??MYSQL教程 ?3 |??? 3 | abcddMYSQL教程 ?4 |??? 4 | abcddMYSQL教程 ?5 |??? 5 | abcddMYSQL教程 ?6 |??? 2 | eeee?MYSQL教程 |
id | c1?? | c2???MYSQL教程 ---+------+-------MYSQL教程 ?1 |??? 1 | abc??MYSQL教程 ?3 |??? 3 | abcddMYSQL教程 ?4 |??? 4 | abcddMYSQL教程 ?5 |??? 5 | abcddMYSQL教程 ?6 |??? 2 | eeee?MYSQL教程 |
|
7MYSQL教程 |
查看binlog事件MYSQL教程 show binlog events in 'mysql-bin.000038' from 59242888;MYSQL教程 |
也可以通過mysqlbinlog工具分析日志,查詢從庫執行的update語句MYSQL教程 |
Pos????? | Event_type???MYSQL教程 ---------+---------------MYSQL教程 59242888 | Query????????MYSQL教程 59242957 | Table_map????MYSQL教程 59243013 |Update_rows_v1MYSQL教程 59243072 | Xid??????????MYSQL教程 |
?MYSQL教程 |
|
8MYSQL教程 |
查看自增列值MYSQL教程 Show create tableMYSQL教程 |
此時master的自增列為7,而slave的自增列為6,與表內最大值相同MYSQL教程 |
CREATE TABLE `test_autoinc` (MYSQL教程 ? `id` int(11) NOT NULL AUTO_INCREMENT,MYSQL教程 ? `c1` int(11) DEFAULT NULL,MYSQL教程 ? `c2` varchar(100) DEFAULT NULL,MYSQL教程 ? PRIMARY KEY (`id`),MYSQL教程 ? UNIQUE KEY `c1` (`c1`)MYSQL教程 ) ENGINE=InnoDBAUTO_INCREMENT=7MYSQL教程 |
CREATE TABLE `test_autoinc` (MYSQL教程 ? `id` int(11) NOT NULL AUTO_INCREMENT,MYSQL教程 ? `c1` int(11) DEFAULT NULL,MYSQL教程 ? `c2` varchar(100) DEFAULT NULL,MYSQL教程 ? PRIMARY KEY (`id`),MYSQL教程 ? UNIQUE KEY `c1` (`c1`)MYSQL教程 ) ENGINE=InnoDBAUTO_INCREMENT=6MYSQL教程 |
經過第8步操作后,若發生主備切換,slave提供服務,此時通過自增列插入主鍵6的記錄,就會發生主鍵沖突.MYSQL教程
???? 如何解決這個bug?對于replace操作,生成binlog時也生成delete和insert兩個事件而非一個update事件；或者在執行update更新主鍵的同時也更新自增列值.當然了,這個只是純原理分析,具體采用什么方法解這個問題,要根據mysql內部的實現,避免引入新的問題.這個bug我同事已經提交到社區,http://bugs.mysql.com/73563 ,大家可以看看.MYSQL教程
