記一次MySQL AUTO_INCREMENT的故障
點擊上方“服務(wù)端思維”，選擇“設(shè)為星標”
回復(fù)”669“獲取獨家整理的精選資料集
回復(fù)”加群“加入全國服務(wù)端高端社群「后端圈」
一、前言
開發(fā)突然緊急的過來說，他們記錄無法插入了，有報重復(fù)鍵錯誤
ERROR 1062 (23000): Duplicate entry '2147483647' for key 'PRIMARY'
表名和數(shù)據(jù)都是采用測試數(shù)據(jù)，結(jié)果和生產(chǎn)的現(xiàn)象是一致的
二、分析
測試環(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 是有符號的int，已經(jīng)達到了int的最大值，但是這個表沒有相應(yīng)的時間字段來記錄這個id=2147483647 的記錄是何時插入或者更新的。和開發(fā)聯(lián)系問有沒有手動執(zhí)行插入指定ID字段的，開發(fā)回復(fù)沒有。拿到發(fā)送報錯的時間點，把時間點之前的binlog 撈了下，都沒找到有id=2147483647的插入記錄。查找DDL變更記錄，只找到了該表其他字段的變更，剛好在這個報錯的時間點之前，但是沒有修改auto_increment的值，一時間陷入了懵逼狀態(tài)。
為了找到原因，豁出去了。用了九牛二虎之力，使用二分查找恢復(fù)了好多份備份，同時結(jié)合binlog ，終于確認這條記錄的具體插入時間。意外的是，我發(fā)現(xiàn)這條記錄插入的binlog是這樣的
insert into t2(id,c1,c2) values(101,101,101) 也就是說，插入的時候的id 是101，并不是 2147483647，那又是為啥呢？
繼續(xù)解析binlog，我發(fā)現(xiàn)了新大陸
update t2 set id=4147483647,c1=101,c2=101 where id=101;
通過dml平臺的日志審計功能，我們找到了對應(yīng)的開發(fā)，發(fā)現(xiàn)是開發(fā)誤操作把主鍵更新了，然后溢出，id變成了2147483647
此時，表的ddl 的 auto_increment 還是等于101，并沒有變成2147483647。后面的正常業(yè)務(wù)SQL進行insert產(chǎn)生的id正常產(chǎn)生，因此可以執(zhí)行成功，直到我們做了一次DDL，加了以個字段，MySQL重新計算了auto_incremnt的值，變成了2147483647，新插入的SQL的自增值無法繼續(xù)分配，主鍵沖突，業(yè)務(wù)開始報錯，才發(fā)現(xiàn)了這個定時炸彈。
三、小結(jié)
MySQL如果在指定id 進行插入的時候，如果這個id大于表的自增值，那么MySQL會把表的自增值修改為這個id，并加1，但是如果我們把主鍵更新成更大的值，MySQL并不會把表的自增值修改為更新后的值，會埋下一顆定時炸彈，在某些情況下，如DDL，重啟等之后，業(yè)務(wù)開始報錯，會誤認為DDL或者重啟導(dǎo)致業(yè)務(wù)表的插入故障。
該問題在percona 5.6.24 和 percona 5.7.20均有出現(xiàn)，在MySQL 8.0.11 中表現(xiàn)正常。找到BUG發(fā)現(xiàn)2005年就有被提出，因為性能原因以及場景很少沒有被修復(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
— 本文結(jié)束 —
● 漫談設(shè)計模式在 Spring 框架中的良好實踐
關(guān)注我，回復(fù) 「加群」 加入各種主題討論群。
對「服務(wù)端思維」有期待，請在文末點個在看
喜歡這篇文章，歡迎轉(zhuǎn)發(fā)、分享朋友圈
