MySQL Bugs: #12434: Update auto_increment column to last ID + 1 causes error on next insert

Bug #12434	Update auto_increment column to last ID + 1 causes error on next insert
Submitted:	8 Aug 2005 12:57	Modified:	8 Aug 2005 19:02
Reporter:	Taco van den Broek	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.0.7-beta-nt	OS:	Any (any)
Assigned to:		CPU Architecture:	Any

Description:
When updating an auto_increment column of a row to the next auto_increment number will prevent future auto incrementing. This bug can also be found in MySQL version 4.1.7.

How to repeat:
-- Create a table with auto_increment column
CREATE TABLE `foo` (
  `foo_id` int(11) NOT NULL auto_increment,
  `foo_title` varchar(32) default NULL,
  PRIMARY KEY  (`foo_id`)
) ENGINE=InnoDB;

-- Insert some rows
INSERT INTO foo (foo_title) VALUES ('First'),('Second'),('Third');

-- Update the auto_increment column to the next value
UPDATE foo SET foo_id = 4 WHERE foo_id = 1;

-- Insert a new row, just like before
INSERT INTO foo (foo_title) VALUES ('Fourth');

Verified with 5.0.11 and 4.1.14

Hi!

This is the documented behavior of the InnoDB auto-increment.

Why would you update the value of the auto-increment column?

Workaround: use DELETE + INSERT.

Regards,

Heikki

Thanks for the quick reply!

I've found this behavior while trying out the 'INSERT ... ON DUPLICATE KEY UPDATE ...' syntax. In the on duplicate key clause I accidentaly updated an auto-increment column like described in this bug report.

I've searched for this behavior on the innodb site, but couldn't find it? Nevertheless I still think it is unwanted behavior that a table can become useless after a valid update query unless you manually reset the auto-increment counter after each update in which this behavior might occur.

Taco

This behavior could be fixed by acquiring an auto-increment lock in UPDATE statements. However, it would reduce concurrency and possibly make deadlocks occur more often.

As there is very little need to update an auto-increment column in real-world applications, we have chosen performance over ease-of-use.