深入探尋mysql自增列導(dǎo)致主鍵重復(fù)問題的原因
廢話少說，進入正題。
拿到問題后，首先查看現(xiàn)場，發(fā)現(xiàn)問題表的中記錄的最大值比自增列的值要大，那么很明顯，當有記錄進行插入時，自增列產(chǎn)生的值就有可能與已有的記錄主鍵沖突，導(dǎo)致出錯。首先想辦法解決問題，通過人工調(diào)大自增列的值，保證大于表內(nèi)已有的主鍵即可，調(diào)整后，導(dǎo)數(shù)據(jù)正常。問題是解決了，接下來要搞清楚問題原因，什么操作導(dǎo)致了這種現(xiàn)象的發(fā)生呢？
這里有一種可能，即業(yè)務(wù)邏輯包含更新自增主鍵的代碼，由于mysql的update動作不會同時更新自增列值，若更新主鍵值比自增列大，也會導(dǎo)致上述現(xiàn)象：記錄最大值比自增主鍵值大。但開發(fā)反饋說這張表僅僅存在load data infile操作，不會進行更新主鍵操作，所以這個解釋行不通。繼續(xù)分析，表中含有唯一約束，會不會和唯一約束有關(guān)，線下實驗?zāi)M沒有重現(xiàn)。后來想想會不會和主備切換有關(guān)系，因為前兩天做過一次主備切換。于是乎，配合主備環(huán)境作了測試，果然和主備切換有關(guān)系，一切問題的來源都清晰了。
問題發(fā)生的前置條件：
1.mysql復(fù)制基于row模式
2.innodb表
3.表含有自增主鍵，并且含有唯一約束
4.load data infile 采用replace into語法插入數(shù)據(jù)【遇到重復(fù)唯一約束，直接覆蓋】
問題發(fā)生的原理：
1.主庫遇到重復(fù)unique約束時，進行replace操作；
2.replace在主庫上面實際變化為delete+insert，但binlog記錄的是update；
3.備庫重做update動作，更新主鍵，但由于update動作不會更新自增列值，導(dǎo)致更新后記錄值大于自增列值
問題重現(xià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=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 |
查看表數(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 |
記錄當前binlog位點， 后續(xù)可以查看replace動作產(chǎn)生的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 |
查看表數(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工具分析日志，查詢從庫執(zhí)行的update語句 |
Pos | Event_type ---------+--------------- 59242888 | Query 59242957 | Table_map 59243013 |Update_rows_v1 59243072 | Xid |
|
|
8 |
查看自增列值 Show create table |
此時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 |
經(jīng)過第8步操作后，若發(fā)生主備切換，slave提供服務(wù)，此時通過自增列插入主鍵6的記錄，就會發(fā)生主鍵沖突。
如何解決這個bug？對于replace操作，生成binlog時也生成delete和insert兩個事件而非一個update事件；或者在執(zhí)行update更新主鍵的同時也更新自增列值。當然了，這個只是純原理分析，具體采用什么方法解這個問題，要根據(jù)mysql內(nèi)部的實現(xiàn)，避免引入新的問題。這個bug我同事已經(jīng)提交到社區(qū)，http://bugs.mysql.com/73563 ，大家可以看看。
相關(guān)文章
本文提供使用Linux的Shell腳本定時處理MySQL超時Locked進程腳本2013-11-11
MySQL中的數(shù)據(jù)用各種不同的技術(shù)存儲在文件（或內(nèi)存）中，這些技術(shù)中的每一種技術(shù)都使用不同的存儲機制，索引技巧，鎖定水平并且最終提供廣泛的不同功能和能力。在MySQL中將這些不同的技術(shù)及配套的相關(guān)功能稱為存儲引擎。2016-01-01
這篇文章主要介紹了MySQL PHP 語法詳解及實例代碼的相關(guān)資料,需要的朋友可以參考下2017-01-01
這篇文章主要介紹了MySQL刪除表的三種方式(小結(jié))，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2020-09-09
其實MYSQL的對數(shù)據(jù)庫的操作與其它的SQL類數(shù)據(jù)庫大同小異，您最好找本將SQL的書看看。2009-06-06
這篇文章主要介紹了服務(wù)器上的mysql實現(xiàn)讀取sql文件方式，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2023-03-03
索引是一個數(shù)據(jù)結(jié)構(gòu),索引是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)(有序),這篇文章主要給大家介紹了關(guān)于MySQLJSON索引用法的相關(guān)資料,需要的朋友可以參考下2024-01-01
本篇文章是對關(guān)于mysql 5.5字符集的問題進行了詳細的分析介紹，需要的朋友參考下2013-06-06
MYSQL無法啟動提示： Default storage engine (InnoDB) is not availabl
自己用的MYSQL都是用MYISAM數(shù)據(jù)庫，還沒涉及到需要INNODB，因此打算直接不加載INNODB引擎。2011-05-05
Mysql查詢很慢卡在sending data的原因及解決思路講解
今天小編就為大家分享一篇關(guān)于Mysql查詢很慢卡在sending data的原因及解決思路講解，小編覺得內(nèi)容挺不錯的，現(xiàn)在分享給大家，具有很好的參考價值，需要的朋友一起跟隨小編來看看吧2019-04-04