溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊(cè)×
其他方式登錄
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶(hù)服務(wù)條款》

自增列導(dǎo)致主鍵重復(fù)

發(fā)布時(shí)間：2020-07-23 16:38:34 來(lái)源：網(wǎng)絡(luò) 閱讀：2206 作者：Stereor 欄目：MySQL數(shù)據(jù)庫(kù)

有記錄進(jìn)行插入時(shí)，自增列產(chǎn)生的值就有可能與已有的記錄主鍵沖突，導(dǎo)致出錯(cuò)。首先想辦法解決問(wèn)題，通過(guò)人工調(diào)大自增列的值，保證大于表內(nèi)已有的主鍵即可，調(diào)整后，導(dǎo)數(shù)據(jù)正常


問(wèn)題發(fā)生的前置條件：

       1.mysql復(fù)制基于row模式

       2.innodb表

       3.表含有自增主鍵，并且含有唯一約束

       4.load data infile 采用replace into語(yǔ)法插入數(shù)據(jù)【遇到重復(fù)唯一約束，直接覆蓋】

問(wèn)題發(fā)生的原理：

        1.主庫(kù)遇到重復(fù)unique約束時(shí)，進(jìn)行replace操作；

        2.replace在主庫(kù)上面實(shí)際變化為delete+insert，但binlog記錄的是update；

        3.備庫(kù)重做update動(dòng)作，更新主鍵，但由于update動(dòng)作不會(huì)更新自增列值，導(dǎo)致更新后記錄值大于自增列值

問(wèn)題重現(xiàn)實(shí)驗(yàn)：


準(zhǔn)備工作

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條記錄后，自增列值變?yōu)?

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=InnoDB AUTO_INCREMENT=6 DEFAULT 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=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

 

3

查看表數(shù)據(jù)


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

記錄當(dāng)前binlog位點(diǎn)，

后續(xù)可以查看replace動(dòng)作產(chǎn)生的binlog事件

mysql-bin.000038

59242888


5

replace操作

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

影響兩條記錄，主庫(kù)replace=

delete+insert

 

Query OK, 2 rows affected

(0.00 sec)


 

 

6

查看表數(shù)據(jù)


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;

也可以通過(guò)mysqlbinlog工具分析日志，查詢(xún)從庫(kù)執(zhí)行的update語(yǔ)句

Pos      | Event_type   

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

59242888 | Query        

59242957 | Table_map    

59243013 |Update_rows_v1

59243072 | Xid          


8

查看自增列值

Show create table  test_autoinc\G;

此時(shí)master的自增列為7，而slave的自增列為6，與表內(nèi)最大值相同

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

9手工調(diào)大自增主鍵 Show create table  test_autoinc\G;

手工調(diào)大自增id

alter table test_autoinc auto_increment=12;


Show create table  test_autoinc\G;



alter table test_autoinc auto_increment=12;

Show create table  test_autoinc\G;


發(fā)現(xiàn)master和slave的自增id一致





向AI問(wèn)一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀(guān)點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手