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

[2 Jun 2010 4:58] Stewart Smith
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.
[2 Jun 2010 6:23] Stewart Smith
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.

[2 Jun 2010 10:13] Sveta Smirnova
Thank you for the report.

This is not a bug. See bug #38839 for explanation.
[2 Jun 2010 23:32] Stewart Smith
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.
[3 Jun 2010 5:41] Sveta Smirnova
Thank you for the feedback.

Makes sense. Moving to documentation category.
[9 May 2012 23:37] John Russell
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.