MySQL Bugs: #61371: Duplicate Key when inserting new auto increment value

Bug #61371	Duplicate Key when inserting new auto increment value
Submitted:	1 Jun 2011 10:26	Modified:	2 Jun 2011 12:16
Reporter:	Florian Bantner	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S1 (Critical)
Version:	5.0.32-7etch12, 5.1.54-1ubuntu4	OS:	Linux (Ubuntu 11.04)
Assigned to:		CPU Architecture:	Any
Tags:	auto_increment, duplicate key, innodb

Description:
When updating a auto_increment key to the next value which would be generated an error is shown: ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'

This only affects InnoDB table but not MyISAM.

How to repeat:
create table tmp (id int primary key auto_increment) engine=InnoDB;

insert into tmp values();

select * from tmp;
+----+
| id |
+----+
|  1 |
+----+

update tmp set id=2 where id=1;

select * from tmp;
+----+
| id |
+----+
|  2 |
+----+

insert into tmp values();
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'

Innodb calculates next autoincrement value as max(id) only at server startup. 

After that at  any inserts -- innodb just increases stored value. This behaviour is described in the documentation - 
http://dev.mysql.com/doc/refman/5.0/en/innodb-auto-increment-handling.html

I see. Thank you. 

So you're talking most likely about this:

"After the auto-increment counter has been initialized, if a user does not explicitly specify a value for an AUTO_INCREMENT column, InnoDB increments the counter by one and assigns the new value to the column. If the user inserts a row that explicitly specifies the column value, and the value is bigger than the current counter value, the counter is set to the specified column value."

I did read this before posting this bug-report but it seems I did miss the point that the increment is only done on inserts with or without value but not on updates (which seems totaly counter-intuitive to me). Perhaps it would be a good idea to add a sentence like: "Note that updates on the AUTO_INCREMENT column don't update the counter (as they would in a MyISAM table) (so you should avoid them)." to make it more clear.