記一次MySQL AUTO_INCREMENT的故障
一、前言
開發(fā)突然緊急的過(guò)來(lái)說(shuō)，他們記錄無(wú)法插入了，有報(bào)重復(fù)鍵錯(cuò)誤
ERROR 1062 (23000): Duplicate entry '2147483647' for key 'PRIMARY'
表名和數(shù)據(jù)都是采用測(cè)試數(shù)據(jù)，結(jié)果和生產(chǎn)的現(xiàn)象是一致的
二、分析
測(cè)試環(huán)境為percona server 5.7.20。首先查看表數(shù)據(jù)和表結(jié)構(gòu)，結(jié)果如下
mysql> select * from t2 order by id desc limit 3;
+------------+------+------+
| id | c1 | c2 |
+------------+------+------+
| 2147483647 | 101 | 101 |
| 100 | 100 | 100 |
| 4 | 4 | 4 |
+------------+------+------+
3 rows in set (0.00 sec)
mysql> show create table t2\G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`c1` int(11) DEFAULT NULL,
`c2` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_c1` (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=2147483647 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
我們可以發(fā)現(xiàn)，其中id 是有符號(hào)的int，已經(jīng)達(dá)到了int的最大值，但是這個(gè)表沒(méi)有相應(yīng)的時(shí)間字段來(lái)記錄這個(gè)id=2147483647 的記錄是何時(shí)插入或者更新的。和開發(fā)聯(lián)系問(wèn)有沒(méi)有手動(dòng)執(zhí)行插入指定ID字段的，開發(fā)回復(fù)沒(méi)有。拿到發(fā)送報(bào)錯(cuò)的時(shí)間點(diǎn)，把時(shí)間點(diǎn)之前的binlog 撈了下，都沒(méi)找到有id=2147483647的插入記錄。查找DDL變更記錄，只找到了該表其他字段的變更，剛好在這個(gè)報(bào)錯(cuò)的時(shí)間點(diǎn)之前，但是沒(méi)有修改auto_increment的值，一時(shí)間陷入了懵逼狀態(tài)。
為了找到原因，豁出去了。用了九牛二虎之力，使用二分查找恢復(fù)了好多份備份，同時(shí)結(jié)合binlog ，終于確認(rèn)這條記錄的具體插入時(shí)間。意外的是，我發(fā)現(xiàn)這條記錄插入的binlog是這樣的
insert into t2(id,c1,c2) values(101,101,101) 也就是說(shuō)，插入的時(shí)候的id 是101，并不是 2147483647，那又是為啥呢？
繼續(xù)解析binlog，我發(fā)現(xiàn)了新大陸
update t2 set id=4147483647,c1=101,c2=101 where id=101;
通過(guò)dml平臺(tái)的日志審計(jì)功能，我們找到了對(duì)應(yīng)的開發(fā)，發(fā)現(xiàn)是開發(fā)誤操作把主鍵更新了，然后溢出，id變成了2147483647
此時(shí)，表的ddl 的 auto_increment 還是等于101，并沒(méi)有變成2147483647。后面的正常業(yè)務(wù)SQL進(jìn)行insert產(chǎn)生的id正常產(chǎn)生，因此可以執(zhí)行成功，直到我們做了一次DDL，加了以個(gè)字段，MySQL重新計(jì)算了auto_incremnt的值，變成了2147483647，新插入的SQL的自增值無(wú)法繼續(xù)分配，主鍵沖突，業(yè)務(wù)開始報(bào)錯(cuò)，才發(fā)現(xiàn)了這個(gè)定時(shí)炸彈。
三、小結(jié)
MySQL如果在指定id 進(jìn)行插入的時(shí)候，如果這個(gè)id大于表的自增值，那么MySQL會(huì)把表的自增值修改為這個(gè)id，并加1，但是如果我們把主鍵更新成更大的值，MySQL并不會(huì)把表的自增值修改為更新后的值，會(huì)埋下一顆定時(shí)炸彈，在某些情況下，如DDL，重啟等之后，業(yè)務(wù)開始報(bào)錯(cuò)，會(huì)誤認(rèn)為DDL或者重啟導(dǎo)致業(yè)務(wù)表的插入故障。
該問(wèn)題在percona 5.6.24 和 percona 5.7.20均有出現(xiàn)，在MySQL 8.0.11 中表現(xiàn)正常。找到BUG發(fā)現(xiàn)2005年就有被提出，因?yàn)樾阅茉蛞约皥?chǎng)景很少?zèng)]有被修復(fù)
參考鏈接：
https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html#innodb-auto-increment-initialization https://bugs.mysql.com/bug.php?id=12434
往期推薦
推薦一個(gè)長(zhǎng)期關(guān)注于
數(shù)據(jù)庫(kù)技術(shù)以及性能優(yōu)化、故障案例分析的公眾號(hào)