博客

记一次Auto Increment故障

  • Post author:
  • Post category:IT
  • Post comments:0评论

实际上本次故障的素材来自于朋友的朋友，虽然我并不是故障的亲身经历者，但即便只是作为旁观者，依然感觉有所收获，于是乎记录下来以馈读者。

故障的来龙去脉大致是这样的：在一个月黑风高的晚上，苦逼的程序员被一阵急促的报警短信声惊醒，原来是数据库的某个表出问题了，虽然查询操作都正常，但创建操作却都失败了，经过调试，发现原因是表被插入了一行问题数据，其自增字段的值被显式的设置为整型的最大值，导致后续缺省插入的数据不能获取到一个合法的主键值。

我们不妨创建一个测试表说明问题：

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.

正所谓：踏破铁鞋无觅处，得来全不费工夫。

发表回复