《Mysql必讀深入探尋mysql自增列導(dǎo)致主鍵重復(fù)問(wèn)題的原因》要點(diǎn)：
本文介紹了Mysql必讀深入探尋mysql自增列導(dǎo)致主鍵重復(fù)問(wèn)題的原因，希望對(duì)您有用。如果有疑問(wèn)，可以聯(lián)系我們。
廢話少說(shuō),進(jìn)入正題.MYSQL教程
???? 拿到問(wèn)題后,首先查看現(xiàn)場(chǎng),發(fā)現(xiàn)問(wèn)題表的中記錄的最大值比自增列的值要大,那么很明顯,當(dāng)有記錄進(jìn)行插入時(shí),自增列產(chǎn)生的值就有可能與已有的記錄主鍵沖突,導(dǎo)致出錯(cuò).首先想辦法解決問(wèn)題,通過(guò)人工調(diào)大自增列的值,保證大于表內(nèi)已有的主鍵即可,調(diào)整后,導(dǎo)數(shù)據(jù)正常.問(wèn)題是解決了,接下來(lái)要搞清楚問(wèn)題原因,什么操作導(dǎo)致了這種現(xiàn)象的發(fā)生呢?MYSQL教程
????? 這里有一種可能,即業(yè)務(wù)邏輯包含更新自增主鍵的代碼,由于mysql的update動(dòng)作不會(huì)同時(shí)更新自增列值,若更新主鍵值比自增列大,也會(huì)導(dǎo)致上述現(xiàn)象：記錄最大值比自增主鍵值大.但開(kāi)發(fā)反饋說(shuō)這張表僅僅存在load data infile操作,不會(huì)進(jìn)行更新主鍵操作,所以這個(gè)解釋行不通.繼續(xù)分析,表中含有唯一約束,會(huì)不會(huì)和唯一約束有關(guān),線下實(shí)驗(yàn)?zāi)M沒(méi)有重現(xiàn).后來(lái)想想會(huì)不會(huì)和主備切換有關(guān)系,因?yàn)榍皟商熳鲞^(guò)一次主備切換.于是乎,配合主備環(huán)境作了測(cè)試,果然和主備切換有關(guān)系,一切問(wèn)題的來(lái)源都清晰了.MYSQL教程
問(wèn)題發(fā)生的前置條件：MYSQL教程
?????? 1.mysql復(fù)制基于row模式MYSQL教程
?????? 2.innodb表MYSQL教程
?????? 3.表含有自增主鍵,并且含有唯一約束MYSQL教程
?????? 4.load data infile 采用replace into語(yǔ)法插入數(shù)據(jù)【遇到重復(fù)唯一約束,直接覆蓋】MYSQL教程
問(wèn)題發(fā)生的原理：MYSQL教程
??????? 1.主庫(kù)遇到重復(fù)unique約束時(shí),進(jìn)行replace操作；MYSQL教程
??????? 2.replace在主庫(kù)上面實(shí)際變化為delete+insert,但binlog記錄的是update；MYSQL教程
??????? 3.備庫(kù)重做update動(dòng)作,更新主鍵,但由于update動(dòng)作不會(huì)更新自增列值,導(dǎo)致更新后記錄值大于自增列值MYSQL教程
問(wèn)題重現(xiàn)實(shí)驗(yàn)：MYSQL教程
|
準(zhǔn)備工作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條記錄后,自增列值變?yōu)?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=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教程 |
查看表數(shù)據(jù)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教程 |
記錄當(dāng)前binlog位點(diǎn),MYSQL教程 后續(xù)可以查看replace動(dòng)作產(chǎn)生的binlog事件MYSQL教程 |
mysql-bin.000038MYSQL教程 59242888MYSQL教程 |
?MYSQL教程 |
|
5MYSQL教程 |
replace操作MYSQL教程 replace into test_autoinc(c1,c2) values(2,'eeee');MYSQL教程 |
影響兩條記錄,主庫(kù)replace=MYSQL教程 delete+insertMYSQL教程 |
?MYSQL教程 Query OK,?2 rows affectedMYSQL教程 (0.00 sec)MYSQL教程 |
?MYSQL教程 |
|
?MYSQL教程 ?MYSQL教程 6MYSQL教程 |
查看表數(shù)據(jù)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教程 |
也可以通過(guò)mysqlbinlog工具分析日志,查詢(xún)從庫(kù)執(zhí)行的update語(yǔ)句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教程 |
此時(shí)master的自增列為7,而slave的自增列為6,與表內(nèi)最大值相同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教程 |
經(jīng)過(guò)第8步操作后,若發(fā)生主備切換,slave提供服務(wù),此時(shí)通過(guò)自增列插入主鍵6的記錄,就會(huì)發(fā)生主鍵沖突.MYSQL教程
???? 如何解決這個(gè)bug?對(duì)于replace操作,生成binlog時(shí)也生成delete和insert兩個(gè)事件而非一個(gè)update事件；或者在執(zhí)行update更新主鍵的同時(shí)也更新自增列值.當(dāng)然了,這個(gè)只是純?cè)矸治?具體采用什么方法解這個(gè)問(wèn)題,要根據(jù)mysql內(nèi)部的實(shí)現(xiàn),避免引入新的問(wèn)題.這個(gè)bug我同事已經(jīng)提交到社區(qū),http://bugs.mysql.com/73563 ,大家可以看看.MYSQL教程
轉(zhuǎn)載請(qǐng)注明本頁(yè)網(wǎng)址：
http://www.snjht.com/jiaocheng/2170.html
