Bug #45115 InnoDB does not change the auto_increment counter after an UPDATE

• Submitted: 27 May 2009 7:57
• Modified: 27 May 2009 16:39
• Reporter: Paul McCullagh (Basic Quality Contributor) (OCA)
• Status: Duplicate
• Category: MySQL Server: InnoDB storage engine
• Severity: S2 (Serious)
• Version: 5.1.x
• OS: Any
• CPU Architecture: Any
• Tags: auto, auto_increment, increment, innodb, myisam, UPDATE

[27 May 2009 7:57] Paul McCullagh

Description:
When an auto_increment column is updated, MyISAM adjusts the auto_increment counter if the update value is greater than then current auto_increment counter.

InnoDB does not do this, and produces different results when compared with MyISAM.

How to repeat:
Run the following code:

drop table if exists t1;
create table t1(a int auto_increment,b int null,primary key(a)) engine=myisam;
insert into t1(b)values(1);
insert into t1(b)values(2);
insert into t1(b)values(3);
insert into t1(b)values(4);

update t1 set a=300 where b=4;
insert into t1(a,b)values(NULL,5);
insert into t1(a,b)values(NULL,6);
select * from t1 order by a, b;

The result for MyISAM:

+-----+------+
| a   | b    |
+-----+------+
|   1 |    1 | 
|   2 |    2 | 
|   3 |    3 | 
| 300 |    4 | 
| 301 |    5 | 
| 302 |    6 | 
+-----+------+

The result for InnoDB:

+-----+------+
| a   | b    |
+-----+------+
|   1 |    1 | 
|   2 |    2 | 
|   3 |    3 | 
|   5 |    5 | 
|   6 |    6 | 
| 300 |    4 | 
+-----+------+

[27 May 2009 15:21] Calvin Sun

This is a dup of bug#30827.

[27 May 2009 16:39] Paul McCullagh

OK. Sorry, I thought it must be known, but it did not come up in any search.