Bug #54165 InnoDB auto_increment not updated on UPDATE (unlike MyISAM)

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.