程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> 關於MYSQL數據庫 >> 深入探尋mysql自增列導致主鍵重復問題的原因

深入探尋mysql自增列導致主鍵重復問題的原因

編輯:關於MYSQL數據庫

廢話少說,進入正題。

     拿到問題後,首先查看現場,發現問題表的中記錄的最大值比自增列的值要大,那麼很明顯,當有記錄進行插入時,自增列產生的值就有可能與已有的記錄主鍵沖突,導致出錯。首先想辦法解決問題,通過人工調大自增列的值,保證大於表內已有的主鍵即可,調整後,導數據正常。問題是解決了,接下來要搞清楚問題原因,什麼操作導致了這種現象的發生呢?

      這裡有一種可能,即業務邏輯包含更新自增主鍵的代碼,由於mysql的update動作不會同時更新自增列值,若更新主鍵值比自增列大,也會導致上述現象:記錄最大值比自增主鍵值大。但開發反饋說這張表僅僅存在load data infile操作,不會進行更新主鍵操作,所以這個解釋行不通。繼續分析,表中含有唯一約束,會不會和唯一約束有關,線下實驗模擬沒有重現。後來想想會不會和主備切換有關系,因為前兩天做過一次主備切換。於是乎,配合主備環境作了測試,果然和主備切換有關系,一切問題的來源都清晰了。

問題發生的前置條件:

       1.mysql復制基於row模式

       2.innodb表

       3.表含有自增主鍵,並且含有唯一約束

       4.load data infile 采用replace into語法插入數據【遇到重復唯一約束,直接覆蓋】

問題發生的原理:

        1.主庫遇到重復unique約束時,進行replace操作;

        2.replace在主庫上面實際變化為delete+insert,但binlog記錄的是update;

        3.備庫重做update動作,更新主鍵,但由於update動作不會更新自增列值,導致更新後記錄值大於自增列值

問題重現實驗:

准備工作

Create table test_autoinc(id int auto_increment, c1 int,c2 varchar(100),primary key(id),unique key(c1));

insert into test_autoinc(c1,c2) values(1,'abc');

insert into test_autoinc(c1,c2) values(2,'abc');

insert into test_autoinc(c1,c2) values(3,'abcdd');

insert into test_autoinc(c1,c2) values(4,'abcdd');

insert into test_autoinc(c1,c2) values(5,'abcdd');

1

操作

備注

Master

slave

2

查看自增列值

Show create table

test_autoinc\G

插入5條記錄後,自增列值變為6

CREATE TABLE `test_autoinc` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `c1` int(11) DEFAULT NULL,

  `c2` varchar(100) DEFAULT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `c1` (`c1`)

) ENGINE=InnoDBAUTO_INCREMENT=6DEFAULT CHARSET=utf8

CREATE TABLE `test_autoinc` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `c1` int(11) DEFAULT NULL,

  `c2` varchar(100) DEFAULT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `c1` (`c1`)

) ENGINE=InnoDBAUTO_INCREMENT=6DEFAULT CHARSET=utf8

 

3

查看表數據

 

id | c1   | c2  

---+------+------

 1 |    1 | abc 

 2 |    2 | abc 

 3 |    3 | abcdd

 4 |    4 | abcdd

 5 |    5 | abcdd

id | c1   | c2  

---+------+------

 1 |    1 | abc 

 2 |    2 | abc 

 3 |    3 | abcdd

 4 |    4 | abcdd

 5 |    5 | abcdd

4

查看binlog位置

show master status\G

記錄當前binlog位點,

後續可以查看replace動作產生的binlog事件

mysql-bin.000038

59242888

 

5

replace操作

replace into test_autoinc(c1,c2) values(2,'eeee');

影響兩條記錄,主庫replace=

delete+insert

 

Query OK, 2 rows affected

(0.00 sec)

 

 

 

6

查看表數據

 

id | c1   | c2   

---+------+-------

 1 |    1 | abc  

 3 |    3 | abcdd

 4 |    4 | abcdd

 5 |    5 | abcdd

 6 |    2 | eeee 

id | c1   | c2   

---+------+-------

 1 |    1 | abc  

 3 |    3 | abcdd

 4 |    4 | abcdd

 5 |    5 | abcdd

 6 |    2 | eeee 

7

查看binlog事件

show binlog events in 'mysql-bin.000038' from 59242888;

也可以通過mysqlbinlog工具分析日志,查詢從庫執行的update語句

Pos      | Event_type   

---------+---------------

59242888 | Query        

59242957 | Table_map    

59243013 |Update_rows_v1

59243072 | Xid          

 

8

查看自增列值

Show create table

此時master的自增列為7,而slave的自增列為6,與表內最大值相同

CREATE TABLE `test_autoinc` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `c1` int(11) DEFAULT NULL,

  `c2` varchar(100) DEFAULT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `c1` (`c1`)

) ENGINE=InnoDBAUTO_INCREMENT=7

CREATE TABLE `test_autoinc` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `c1` int(11) DEFAULT NULL,

  `c2` varchar(100) DEFAULT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `c1` (`c1`)

) ENGINE=InnoDBAUTO_INCREMENT=6

經過第8步操作後,若發生主備切換,slave提供服務,此時通過自增列插入主鍵6的記錄,就會發生主鍵沖突。

     如何解決這個bug?對於replace操作,生成binlog時也生成delete和insert兩個事件而非一個update事件;或者在執行update更新主鍵的同時也更新自增列值。當然了,這個只是純原理分析,具體采用什麼方法解這個問題,要根據mysql內部的實現,避免引入新的問題。這個bug我同事已經提交到社區,http://bugs.mysql.com/73563 ,大家可以看看。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved