Bug #35089 LAST_INSERT_ID returns 0 on using INSERT... ON DUPLICATE KEY UPDATE ...

• Submitted: 5 Mar 2008 19:55
• Modified: 5 Mar 2008 21:48
• Reporter: Greg Smyth
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.1.22-rc-community-log
• OS: Any
• CPU Architecture: Any
• Tags: insert, LAST_INSERT_ID, ON DUPLICATE KEY, regression

[5 Mar 2008 19:55] Greg Smyth

Description:
Bug #27033 has resurfaced in 5.1.22, despite being pushed to 5.1.17?

How to repeat:
mysql> create table blah (a int, PRIMARY KEY(a));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into blah values (1),(2),(3),(4),(5),(6),(7);
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> select * from blah;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
+---+
7 rows in set (0.00 sec)

mysql> insert into blah set a=5 on duplicate key update a=99;
Query OK, 2 rows affected (0.00 sec)

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                0 |                  <------ The correct answer here is 5.
+------------------+
1 row in set (0.00 sec)               

mysql> select * from blah;
+----+
| a  |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  6 |
|  7 |
| 99 |
+----+
7 rows in set (0.00 sec)

mysql> show variables like 'version%';
+-------------------------+-------------------------------+
| Variable_name           | Value                         |
+-------------------------+-------------------------------+
| version                 | 5.1.22-rc-community-log       |
| version_comment         | MySQL Community Edition (GPL) |
| version_compile_machine | i686                          |
| version_compile_os      | redhat-linux-gnu              |
+-------------------------+-------------------------------+
4 rows in set (0.00 sec)

mysql>

[5 Mar 2008 21:48] Sveta Smirnova

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

According to http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_last-insert-id:

"For MySQL 5.1.12 and later, LAST_INSERT_ID() (no arguments) returns the first  *automatically* generated value successfully inserted for an AUTO_INCREMENT column as a result of the most recently executed INSERT statement."

In your case value inserted not automatically, but explicitly. Same behavior for regular INSERT:

create table blah (a int, PRIMARY KEY(a));
insert into blah values (1),(2),(3),(4),(5),(6),(7);
select last_insert_id();
last_insert_id()
0