MySQL Bugs: #43795: Autoincrement increased by 2 on duplicate key condition

Bug #43795	Autoincrement increased by 2 on duplicate key condition
Submitted:	23 Mar 2009 5:28	Modified:	23 Mar 2009 6:15
Reporter:	Susan Cai	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.1.32-community	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	autoincrement, DUPLICATE KEY UPDATE, unique key

Description:
Use the following insert statement, the next autoincrement ID (primary key) is increased by 2 for the next data insertion after having updated duplicate key.  

Insert into tablename (id, field1, field2, ...)
values (...)
ON DUPLICATE KEY UPDATE ... ;

primary key & auto-increment - id
unique key - field1 + field2

How to repeat:
1) create table by :

DROP TABLE IF EXISTS `test`.`test`;
CREATE TABLE  `test`.`test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `a` char(3) NOT NULL,
  `b` char(3) NOT NULL,
  `c` int(10) unsigned NOT NULL,
  `d` float NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `Index_uk` (`a`,`b`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

2) insert data:

INSERT INTO test (a, b, c, d)
values ('a1', 'b1', 1, 0.1)
ON DUPLICATE KEY UPDATE c = 11, d= 1.1 ;

mysql> select * from test;
+----+----+----+---+-----+
| id | a  | b  | c | d   |
+----+----+----+---+-----+
|  1 | a1 | b1 | 1 | 0.1 |
+----+----+----+---+-----+
1 row in set (0.00 sec)

INSERT INTO test (a, b, c, d)
values ('a1', 'b1', 2, 0.3)
ON DUPLICATE KEY UPDATE c = 12, d= 1.2 ;

2 rows affected by the last command, no result returned.

mysql> select * from test;
+----+----+----+----+-----+
| id | a  | b  | c  | d   |
+----+----+----+----+-----+
|  1 | a1 | b1 | 12 | 1.2 |
+----+----+----+----+-----+
1 row in set (0.00 sec)

Things looked like fine, but when insert a new record as follow, the id should be 2 rather than 3.

INSERT INTO test (a, b, c, d)
values ('a2', 'b2', 2, 0.2)
ON DUPLICATE KEY UPDATE c = 22, d= 2.2 ;

1 row affected by the last command, no result returned.

mysql> select * from test;
+----+----+----+----+-----+
| id | a  | b  | c  | d   |
+----+----+----+----+-----+
|  1 | a1 | b1 | 12 | 1.2 |
|  3 | a2 | b2 |  2 | 0.2 |
+----+----+----+----+-----+
2 rows in set (0.00 sec)

3)More cases, after updating 2 records, the next id inserted was 6 not 4. (as following)

INSERT INTO test (a, b, c, d)
values ('a1', 'b1', 111, 11.3)
ON DUPLICATE KEY UPDATE c = 1110, d= 111.02 ;

2 rows affected by the last command, no result returned.

INSERT INTO test (a, b, c, d)
values ('a2', 'b2', 202, 202.2)
ON DUPLICATE KEY UPDATE c = 2220, d= 222.02 ;

2 rows affected by the last command, no result returned.

INSERT INTO test (a, b, c, d)
values ('a3', 'b3', 3, 3.3)
ON DUPLICATE KEY UPDATE c = 33, d= 33.3 ;

1 row affected by the last command, no result returned.

mysql> select * from test;
+----+----+----+------+--------+
| id | a  | b  | c    | d      |Last_update_ID
+----+----+----+------+--------+
|  1 | a1 | b1 | 1110 | 111.02 | 1
|  3 | a2 | b2 | 2220 | 222.02 | 3
|  6 | a3 | b3 |    3 |    3.3 | 6
+----+----+----+------+--------+
3 rows in set (0.00 sec)

Suggested fix:
the next autoincrement id should be LAST_INSERT_ID(id) + 1.

Sorry, but this is not a bug. This behavior looks intended and is somehow documented. Read http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html:

"A similar situation exists if you use INSERT ... ON DUPLICATE KEY UPDATE. This statement is also classified as a “mixed-mode insert” since an auto-increment value is not necessarily generated for each row. Because InnoDB allocates the auto-increment value before the insert is actually attempted, it cannot know whether an inserted value will be a duplicate of an existing value and thus cannot know whether the auto-increment value it generates will be used for a new row."

Afternoon all.. I'm a little confused by this issue and perhaps someone from the MySQL team could clarify.

On this issue you state that this is not a bug but is being caused by the system not knowing if the row already exists before the auto-increment is updated.. I can understand this logic and was prepared to leave it at that until I found this issue raised in June last year

http://bugs.mysql.com/bug.php?id=37560

It reports the same issue but says that it was fixed sometime before 5.0.51b (see results from Miguel's test, numbers incremented as expected).

I've run his test here on 5.1.31-community and see the same problem initially reported by Nathan.

Could someone please clarify if this is a bug or not?

Thanks