MySQL Bugs: #54165: InnoDB auto_increment not updated on UPDATE (unlike MyISAM)

Bug #54165	InnoDB auto_increment not updated on UPDATE (unlike MyISAM)
Submitted:	2 Jun 2010 4:58	Modified:	9 May 2012 23:37
Reporter:	Stewart Smith	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1.46	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
See 'how to repeat'. Works on MyISAM, fails on InnoDB.

See also: https://bugs.launchpad.net/drizzle/+bug/314570

How to repeat:
-- source include/have_innodb.inc

create table t1 (a int not null auto_increment primary key, val int) engine=innodb;
insert into t1 (val) values (1);
update t1 set a=2 where a=1;
insert into t1 (val) values (1);
drop table t1;

main.auto_increment_update 'innodb_plugin' [ fail ]
        Test ended at 2010-06-02 14:22:09

CURRENT_TEST: main.auto_increment_update
mysqltest: At line 6: query 'insert into t1 (val) values (1)' failed: 1062: Duplicate entry '2' for key 'PRIMARY'

The result from queries just before the failure was:
create table t1 (a int not null auto_increment primary key, val int) engine=innodb;
insert into t1 (val) values (1);
update t1 set a=2 where a=1;

Suggested fix:
I'll probably have a patch shortly.

Patch (against drizzle, but should be fairly similar) that fixes auto_inc behaviour to be like MyISAM for UPDATE

Attachment: innodb_autoinc_update.patch (text/x-patch), 1.74 KiB.

Thank you for the report.

This is not a bug. See bug #38839 for explanation.

At the very least this should be a documentation bug as *nowhere* is it documented.

Or MyISAM should be fixed to conform to the documentation.

Thank you for the feedback.

Makes sense. Moving to documentation category.

Added this InnoDB-specific note to the tutorial topic in the doc:

For InnoDB tables, be careful if you modify the column containing the
auto-increment value in the middle of a sequence of INSERT
statements. For example, if you use an UPDATE statement to put a new,
larger value in the auto-increment column, a subsequent INSERT could
encounter a "Duplicate entry" error. The test whether an
auto-increment value is already present occurs if you do a DELETE
followed by more INSERT statements, or when you COMMIT the
transaction, but not after an UPDATE statement.