自增列導(dǎo)致主鍵重復(fù)
有記錄進(jìn)行插入時(shí)，自增列產(chǎn)生的值就有可能與已有的記錄主鍵沖突，導(dǎo)致出錯(cuò)。首先想辦法解決問題，通過人工調(diào)大自增列的值，保證大于表內(nèi)已有的主鍵即可，調(diào)整后，導(dǎo)數(shù)據(jù)正常
托里網(wǎng)站制作公司哪家好，找創(chuàng)新互聯(lián)！從網(wǎng)頁(yè)設(shè)計(jì)、網(wǎng)站建設(shè)、微信開發(fā)、APP開發(fā)、響應(yīng)式網(wǎng)站建設(shè)等網(wǎng)站項(xiàng)目制作，到程序開發(fā)，運(yùn)營(yíng)維護(hù)。創(chuàng)新互聯(lián)成立與2013年到現(xiàn)在10年的時(shí)間，我們擁有了豐富的建站經(jīng)驗(yàn)和運(yùn)維經(jīng)驗(yàn)，來保證我們的工作的順利進(jìn)行。專注于網(wǎng)站建設(shè)就選創(chuàng)新互聯(lián)。
問題發(fā)生的前置條件：
1.MySQL復(fù)制基于row模式
2.innodb表
3.表含有自增主鍵，并且含有唯一約束
4.load data infile 采用replace into語法插入數(shù)據(jù)【遇到重復(fù)唯一約束，直接覆蓋】
問題發(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)致更新后記錄值大于自增列值
問題重現(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; | 也可以通過mysqlbinlog工具分析日志，查詢從庫(kù)執(zhí)行的update語句 | 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一致 |
本文名稱：自增列導(dǎo)致主鍵重復(fù)
標(biāo)題URL：http://m.jiaotiyi.com/article/ggecdg.html