MySQL Bugs: #30827: Autoincrement counter does not update on record update

Bug #30827	Autoincrement counter does not update on record update
Submitted:	5 Sep 2007 11:50	Modified:	5 Sep 2007 12:34
Reporter:	Nikolay Pelov	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.0.41	OS:	Any
Assigned to:	Heikki Tuuri	CPU Architecture:	Any
Tags:	autoincrement, innodb, UPDATE

Description:
When an autoincrement primary key field of a record is updated to a value grater or equal than next autoincrement value the autoincrement counter does not change and next insert fails. This bug is not present on MyISAM and not tested on BDB. 

How to repeat:
CREATE TABLE `ai` (
`id` INT NOT NULL AUTO_INCREMENT ,
`val` VARCHAR( 32 ) NOT NULL ,
PRIMARY KEY ( `id` )
) TYPE = innodb;

insert into ai values(NULL,'first record');
select * from ai;
+----+--------------+
| id | val          |
+----+--------------+
|  1 | first record |
+----+--------------+
show table status like 'ai';
+------+-~-+----------------+-~-+
| Name | ~ | Auto_increment | ~ |
+------+-~-+----------------+-~-+
| ai   | ~ |              2 | ~ |
+------+-~-+----------------+-~-+

update ai set id=2 where id=1;
show table status like 'ai';
+------+-~-+----------------+-~-+
| Name | ~ | Auto_increment | ~ |
+------+-~-+----------------+-~-+
| ai   | ~ |              2 | ~ |
+------+-~-+----------------+-~-+

insert into ai values(NULL,'second row');
ERROR 1062 (23000): Duplicate entry '2' for key 1

Suggested fix:
Workaround: in a transaction:
BEGIN;
<update id records>
select max(id) for update;
ALTER TABLE tbl AUTO_INCREMENT = <result from previous query>;
COMMIT;

Nikolay, an UPDATE does not affect the auto-inc counter in InnoDB. That is how it has always been, and it is documented.

The behavior of MyISAM might be less logical.

Regards,

Heikki

The documentation is a little vague on this point. Perhaps an explicit statement could be made on http://dev.mysql.com/doc/refman/5.0/en/innodb-auto-increment-handling.html noting that only inserts, and not updates, affect the InnoDB auto increment counter.