MySQL Bugs: #39207: update does not always change auto

Bug #39207	update does not always change auto_increment value
Submitted:	3 Sep 2008 9:58	Modified:	9 Sep 2008 10:46
Reporter:	Mattias Jonsson	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.1+	OS:	Any
Assigned to:	Sunny Bains	CPU Architecture:	Any

Description:
InnoDB does not update the auto_increment value in certain insert/update sequences (see below)

Is the statement given in bug#12434 still true (This is not a bug), even after the rework of auto_increment locking in InnoDB? (then it is just confusing since if one does insert '3' instead of NULL before the update, it will work as expected.)

How to repeat:
create table t1 (a int not null auto_increment, primary key (a)) engine innodb;
insert into t1 values (2);
insert into t1 values (NULL);
update t1 set a = 10 where a = 2;
insert into t1 values (NULL);
select * from t1;
a
3
4
10

I would expect 3, 10, 11 here (as if myisam or memory)
If one updates to 4 instead of 10, it will fail with:
query 'insert into t1 values (NULL)' failed: 1062: Duplicate entry '4' for key 'PRIMARY'

Suggested fix:
If table->found_next_number_field->val_int() is higher than current auto_inc value, update it. (when update, the table->next_number_field is not always set).

Thank you for the bug report. Verified as described.

Assigning to Sunny for consideration. Please note that 
1- W1 proved to be  wrong as there's a workaround of DELETE/INSERT
2- the internal tag means 'triage not completed yet'
3- if the generally unsound UPDATE on autoinc column results in a permanent 
inability to insert, this is indeed a problem

and I do thank Mattias for the prompt answer to my questions

InnoDB's behavior has always been that an UPDATE does not change the auto-inc counter value.

In 2001, I thought this to be more logical than the MyISAM behavior.

If you need to raise the counter value, do an INSERT followed by a DELETE.

Marking this as not a bug.