Bug #53831 Innodb increments auto_increments on failed inserts
Submitted: 20 May 2010 5:04 Modified: 20 Jun 2010 8:15
Reporter: Harun Yayli Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.1.41 OS:Any
Assigned to: CPU Architecture:Any
Tags: auto_increment, innodb, insert

[20 May 2010 5:04] Harun Yayli
Description:
Only on innodb tables auto_increment field increases if inserts fail.
In myisam tables it doesn't increment.

How to repeat:
CREATE TABLE t1 (
  colA bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  colB varchar(10) DEFAULT NULL,
  colC varchar(10) DEFAULT NULL,
  PRIMARY KEY (colA),
  UNIQUE KEY idx (colC)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE t2 (
  colA bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  colB varchar(10) DEFAULT NULL,
  colC varchar(10) DEFAULT NULL,
  PRIMARY KEY (colA),
  UNIQUE KEY idx (colC)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

insert into t1 (colB, colC) VALUES ('a','b')
insert into t1 (colB, colC) VALUES ('a','b')
insert into t2 (colB, colC) VALUES ('a','b')
insert into t2 (colB, colC) VALUES ('a','b')

SHOW TABLE STATUS like 't%';

t1 auto_increment=3
t2 auto_increment=2

Suggested fix:
Unify the behavior like it is in mysql.
failed inserts should not increment auto_increment.
[20 May 2010 8:15] Valeriy Kravchuk
I think this is related to new auto-increment values handling in InnoDB by default. Read http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html and http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_autoinc_lock_m.... 

Check if the same problem happens with innodb_autoinc_lock_mode set to 0 explicitly.
[20 Jun 2010 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".