Bug #78934 Failed INSERT(Dup entry) still change LAST_INSERT_ID() for auto_increment col

• Submitted: 23 Oct 2015 4:14
• Modified: 26 Oct 2015 13:37
• Reporter: Su Dylan
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.7.8
• OS: Any
• CPU Architecture: Any

[23 Oct 2015 4:14] Su Dylan

Description:
Output:
=======
mysql> drop table if exists t1;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1(c1 int primary key auto_increment);
insert into t1 values (null);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values (null);
selecQuery OK, 1 row affected (0.00 sec)

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

mysql> insert into t1 values (null), (1), (null);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.8-rc  |
+-----------+
1 row in set (0.00 sec)

Problem:
========
Since the second insert statement fails with "Duplicate entry '1' for key 'PRIMARY'", it should not change LAST_INSERT_ID() value.

How to repeat:

drop table if exists t1;
create table t1(c1 int primary key auto_increment);
insert into t1 values (null);
select last_insert_id();
insert into t1 values (null), (1), (null);
select last_insert_id();

Suggested fix:
After the second insert statement fails with "Duplicate entry '1' for key 'PRIMARY'", "SELECT LAST_INSERT_ID()" should return 1.

[26 Oct 2015 13:37] MySQL Verification Team

Hello Su Dylan,

Thank you for the report.
Imho if you insert multiple rows using a single INSERT statement, LAST_INSERT_ID() returns the value generated for the first inserted row only. Also, for transactional tables, if the statement is rolled back due to an error, the value of LAST_INSERT_ID() is left undefined. For manual ROLLBACK, the value of LAST_INSERT_ID() is not restored to that before the transaction; it remains as it was at the point of the ROLLBACK.

Please see https://dev.mysql.com/doc/refman/5.7/en/information-functions.html#function_last-insert-id

“Lost” auto-increment values and sequence gaps in
https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-configurable.html

Thanks,
Umesh