記一次Auto Increment故障

故障的來龍去脈大致是這樣的：在一個月黑風高的晚上，苦逼的程序員被一陣急促的報警短信聲驚醒，原來是數據庫的某個表出問題了，雖然查詢操作都正常，但創建操作卻都失敗了，經過調試，發現原因是表被插入了一行問題數據，其自增字段的值被顯式的設置為整型的最大值，導致後續缺省插入的數據不能獲取到一個合法的主鍵值。

我們不妨創建一個測試表說明問題：

CREATE TABLE IF NOT EXISTS `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

然後插入一行問題數據：

INSERT INTO test (id, name) VALUES (2147483647, 'x');

結果導致不能執行正常的插入語句：

INSERT INTO test (name) VALUES ('y');

此時數據庫會報錯：

#1062 – Duplicate entry ‘2147483647’ for key ‘PRIMARY’

換句話說，InnoDB 表類型會在內部維護一個 Auto Increment 字段的計數器，以便為後續的插入提供一個必要的唯一標識。每當有新數據插入的時候，計數器的值就會被更新為 MAX + 1，如果插入唯一標識為 2147483647 的數據，那麼計數器將無法正常更新，因為它已經達到了 INT 數據類型的上限。

既然理清了問題的原因，那麼第一感覺就是把 Auto Increment 字段的計數器復位，設置到一個合理的值，我們可以在刪除問題數據後嘗試執行如下語句：

ALTER TABLE test AUTO_INCREMENT = 123;

此方法無疑能夠達到目的，但有一個缺點：在 MySQL 中，當 ALTER 一個表時，實際上相當於重新創建了一次表！如果原本數據就很大的話，這個過程將非常緩慢。

讓我們再來考慮考慮其它方法，既然問題出在唯一標識的隱式設置上，那麼我們不妨換個思路，顯式設置唯一標識，比如說通過「SELECT MAX … FOR UPDATE」的方式直接查詢得到唯一標識符，並在稍後執行的 INSERT 語句中顯式設置。

不過這種方法不夠透明，需要修改業務代碼，更透明的方法是使用觸發器：

CREATE TABLE seq ( 
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `created` timestamp NOT NULL,
   PRIMARY KEY (`id`)
) Engine=InnoDB;

DELIMITER |;
CREATE TRIGGER test_seq BEFORE INSERT ON test
FOR EACH ROW
BEGIN
    INSERT INTO seq (created) VALUES (NOW());
    SET NEW.id = LAST_INSERT_ID();
END;
|;

問題到這裡似乎已經解決了，不過在咨詢了 @linux流浪貓 之後，意外得到了一個很簡單的答案：只要刪除問題數據後，重啟一下服務即可。實際上文檔裡有相關描述：

If you specify an AUTO_INCREMENT column for an InnoDB table, the table handle in the InnoDB data dictionary contains a special counter called the auto-increment counter that is used in assigning new values for the column. This counter is stored only in main memory, not on disk.

…

A server restart also cancels the effect of the AUTO_INCREMENT = N table option in CREATE TABLE and ALTER TABLE statements, which you can use with InnoDB tables to set the initial counter value or alter the current counter value.

正所謂：踏破鐵鞋無覓處，得來全不費工夫。