记一次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.

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

记一次Auto Increment故障》上有2条评论

发表评论

电子邮件地址不会被公开。 必填项已用*标注