深入探尋mysql自增列導(dǎo)致主鍵重復(fù)問題的原因
廢話少說，進(jìn)入正題。
拿到問題后，首先查看現(xiàn)場，發(fā)現(xiàn)問題表的中記錄的最大值比自增列的值要大，那么很明顯，當(dāng)有記錄進(jì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操作，不會進(jìn)行更新主鍵操作，所以這個解釋行不通。繼續(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約束時，進(jìn)行replace操作；
2.replace在主庫上面實際變化為delete+insert，但binlog記錄的是update；
3.備庫重做update動作，更新主鍵，但由于update動作不會更新自增列值，導(dǎo)致更新后記錄值大于自增列值
問題重現(xià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=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 |
記錄當(dāng)前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更新主鍵的同時也更新自增列值。當(dāng)然了，這個只是純原理分析，具體采用什么方法解這個問題，要根據(jù)mysql內(nèi)部的實現(xiàn)，避免引入新的問題。這個bug我同事已經(jīng)提交到社區(qū)，http://bugs.mysql.com/73563 ，大家可以看看。
相關(guān)文章
這篇文章主要介紹了開啟MySQL的binlog日志的方法步驟，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2021-03-03
修改mysql密碼與忘記mysql密碼的處理方法，有需要的朋友可以參考下2013-02-02
一步步教你利用Mysql存儲過程造百萬級數(shù)據(jù)
因工作需要維護一張中建表數(shù)據(jù)內(nèi)置,所以得造數(shù)據(jù)所以使用存儲過程來造數(shù)據(jù),下面這篇文章主要給大家介紹了關(guān)于如何一步步利用Mysql存儲過程造百萬級數(shù)據(jù)的相關(guān)資料,文中通過圖文介紹的非常詳細(xì),需要的朋友可以參考下2023-03-03
Mac OS10.11下mysql5.7.12 安裝配置方法圖文教程
這篇文章主要為大家詳細(xì)介紹了Mac OS10.11下mysql5.7.12 安裝配置方法圖文教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-01-01
虛擬列是MySQL 5.7開始引入的新特性,本文主要介紹了MySQL 虛擬列和虛擬索引的實現(xiàn),文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2024-08-08
MySQL主從一直是面試常客,里面的知識點雖然基礎(chǔ),但是能回答全的同學(xué)不多,如果我們遇到問如何解決MySQL主從復(fù)制延時問題呢,所以本文給大家講解了MySQL主從復(fù)制延時問題的解決方法,需要的朋友可以參考下2023-12-12
這篇文章主要介紹了mysql免安裝版配置步驟詳解，提供了二個網(wǎng)友的安裝方法，大家可以參考使用2013-12-12
這篇文章主要介紹了MySQL查詢語法的匯總，幫助大家更好的理解和學(xué)習(xí)mysql，感興趣的朋友可以了解下2020-08-08
vscode安裝mysql相關(guān)插件的詳細(xì)步驟
這篇文章主要給大家介紹了關(guān)于vscode安裝mysql相關(guān)插件的詳細(xì)步驟,這將幫助你更高效地管理數(shù)據(jù)庫操作,文中通過圖文介紹的非常詳細(xì),需要的朋友可以參考下2024-07-07
這篇文章主要介紹了mysql中insert與select的嵌套使用方法,代碼功能非常實用,需要的朋友可以參考下2014-07-07