Bug #55140 Autoincrement increases on failed query with contraints...

• Submitted: 9 Jul 2010 22:53
• Modified: 10 Jul 2010 6:06
• Reporter: L U
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.1.47
• OS: Any
• CPU Architecture: Any

[9 Jul 2010 22:53] L U

Description:
A bit of an issue my coder was having. I don't know what he was trying to do and I'm not sure if his method of trying to get something to work is wrong, but it produces an unusual event...

How to repeat:
CREATE TABLE IF NOT EXISTS `a` (
  `id` INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  `value` INT UNSIGNED
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `b` (
  `id` INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  `a_id` INT UNSIGNED NOT NULL,
  CONSTRAINT FOREIGN KEY (a_id) REFERENCES a(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO a(value) VALUES(1);
INSERT INTO b(a_id) VALUES(2);
INSERT INTO b(a_id) VALUES(1);

Try to do an insert into b with a value that does not exist in a. Then do an insert into b with a value that exists in a. If you then do 

select * from b; you will see that the row that successfully was inserted has id 2

[10 Jul 2010 6:06] Valeriy Kravchuk

This is not a bug. Please, read the manual, http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html:

"In all lock modes (0, 1, and 2), if a transaction that generated auto-increment values rolls back, those auto-increment values are “lost.” Once a value is generated for an auto-increment column, it cannot be rolled back, whether or not the “INSERT-like” statement is completed, and whether or not the containing transaction is rolled back. Such lost values are not reused. Thus, there may be gaps in the values stored in an AUTO_INCREMENT column of a table."