Bug #27033 LAST_INSERT_ID returns 0 on using INSERT... ON DUPLICATE KEY UPDATE ...
Submitted: 11 Mar 2007 14:09 Modified: 20 Mar 2007 17:43
Reporter: Markus Wolters Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.37 OS:Windows (Windows 2003/Linux)
Assigned to: Evgeny Potemkin CPU Architecture:Any
Tags: insert, LAST_INSERT_ID, ON DUPLICATE KEY, regression

[11 Mar 2007 14:09] Markus Wolters
Description:
Since the latest GA of Community Server 5.0.37 the following function does not work anymore: INSERT ... ON DUPLICATE KEY UPDATE `Pk` = LAST_INSERT_ID(`Pk`)

After that statement LAST_INSERT_ID() should return the last autoincrement id or in case of existing row the id of that. But now it returns '0' in case of an existing (duplicate key) row.

How to repeat:
CREATE TABLE  `applications` (
  `ApplicationId` int unsigned NOT NULL auto_increment,
  `ApplicationName` varchar(255) NOT NULL,
  PRIMARY KEY  (`ApplicationId`),
  UNIQUE KEY `ApplicationName` (`ApplicationName`)
);

INSERT Applications (ApplicationName) VALUES ('Test')
ON DUPLICATE KEY UPDATE ApplicationId = LAST_INSERT_ID(ApplicationId);
Query OK, 1 row affected (0.06 sec)

SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |      <----- OKAY! Newly inserted!
+------------------+
1 row in set (0.00 sec)

INSERT Applications (ApplicationName) VALUES ('Test')
ON DUPLICATE KEY UPDATE ApplicationId = LAST_INSERT_ID(ApplicationId);
Query OK, 0 row affected (0.00 sec)

SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                0 |      <----- WRONG! Should return '1'!
+------------------+
1 row in set (0.00 sec)

Suggested fix:
Go back to Version 5.0.27 or work around by first INSERTing and after that SELECTing (more expensive).
[11 Mar 2007 16:59] MySQL Verification Team
Thank you for the bug report. Verified as described:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.27-community-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test
Database changed
mysql> CREATE TABLE  `applications` (
    ->   `ApplicationId` int unsigned NOT NULL auto_increment,
    ->   `ApplicationName` varchar(255) NOT NULL,
    ->   PRIMARY KEY  (`ApplicationId`),
    ->   UNIQUE KEY `ApplicationName` (`ApplicationName`)
    -> );
Query OK, 0 rows affected (0.88 sec)

mysql> INSERT Applications (ApplicationName) VALUES ('Test')
    -> ON DUPLICATE KEY UPDATE ApplicationId = LAST_INSERT_ID(ApplicationId);
Query OK, 1 row affected (0.09 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

mysql> INSERT Applications (ApplicationName) VALUES ('Test')
    -> ON DUPLICATE KEY UPDATE ApplicationId = LAST_INSERT_ID(ApplicationId);
Query OK, 2 rows affected (0.08 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

mysql>

c:\build\5.0>bin\mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.38 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test
Database changed
mysql> CREATE TABLE  `applications` (
    ->   `ApplicationId` int unsigned NOT NULL auto_increment,
    ->   `ApplicationName` varchar(255) NOT NULL,
    ->   PRIMARY KEY  (`ApplicationId`),
    ->   UNIQUE KEY `ApplicationName` (`ApplicationName`)
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT Applications (ApplicationName) VALUES ('Test')
    -> ON DUPLICATE KEY UPDATE ApplicationId = LAST_INSERT_ID(ApplicationId);
Query 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 Applications (ApplicationName) VALUES ('Test')
    -> ON DUPLICATE KEY UPDATE ApplicationId = LAST_INSERT_ID(ApplicationId);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

mysql>
[12 Mar 2007 13:29] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/21727

ChangeSet@1.2476, 2007-03-12 15:56:58+03:00, evgen@moonbone.local +4 -0
  Bug#27033: Wrong LAST_INSERT_ID() value after INSERT .. ON DUPLICATE if no
  rows were actually changed.
  
  The LAST_INSERT_ID() is reset to 0 if no rows were inserted or changed.
  This is the case when an INSERT ... ON DUPLICATE KEY UPDATE updates a row
  with the same values as the row contains.
  
  Now the LAST_INSERT_ID() values is reset to 0 only if there were no rows
  successfully inserted or updated.
  The new 'touched' field is added to the COPY_INFO structure. It holds the
  number of rows that were updated no matter whether they were actually
  changed or not.
[13 Mar 2007 23:35] Andrei Nazarenko
Actually, after installing MySQL 5.0.37 NONE of my "ON DUPLICATE KEY UPDATE" statements work properly when there is a duplicate key encountered.

It does not matter whether I have LAST_INSERT_ID() in my statement or not.

Does the fix also resolve the issue I am experiencing or is it only for those cases where LAST_INSERT_ID() is being used?

Here is my example:

CREATE TABLE `test` (
  `hostname` varchar(10) NOT NULL,
  `last_polled` datetime NOT NULL,
  `ticks` int(10) unsigned NOT NULL,
  `status` enum('OK','ERR') NOT NULL,
  PRIMARY KEY  (`exrouter`)
) ENGINE=MyISAM ;

INSERT INTO `test` VALUES ('host1', NOW(), 1282953802, 'OK');

INSERT INTO `test` VALUES ('host1', NOW(), 1282953802, 'OK') ON DUPLICATE KEY UPDATE `last_polled` = NOW();

Thanks
[15 Mar 2007 0:09] Emmanuel Courreges
For me the ON DUPLICATE KEY UPDATE works on the master but is not transmitted to the slave. I guess it can be related to the "rows affected" which is always wrong. It is always 0, and it used to be 1 in case of insert and 2 in case of update.

Now I get this:

mysql> INSERT userServers (id,status) VALUES (8,1) ON DUPLICATE KEY UPDATE status=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> INSERT userServers (id,status) VALUES (8,0) ON DUPLICATE KEY UPDATE status=0;
Query OK, 0 rows affected, 1 warning (0.00 sec)
[15 Mar 2007 0:30] Emmanuel Courreges
If it helps, something tells me that it has been introduced in 5.0.36 by doing this fix:

If the duplicate key value was present in the table, INSERT ... ON DUPLICATE KEY UPDATE reported a row count indicating that a record was updated, even when no record actually changed due to the old and new values being the same. Now it reports a row count of zero. (Bug#19978)
[15 Mar 2007 15:36] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/22022

ChangeSet@1.2476, 2007-03-15 18:34:44+03:00, evgen@moonbone.local +4 -0
  Bug#27033: 0 as LAST_INSERT_ID() after INSERT .. ON DUPLICATE if rows were
  touched but not actually changed.
  
  The LAST_INSERT_ID() is reset to 0 if no rows were inserted or changed.
  This is the case when an INSERT ... ON DUPLICATE KEY UPDATE updates a row
  with the same values as the row contains.
  
  Now the LAST_INSERT_ID() values is reset to 0 only if there were no rows
  successfully inserted or updated.
  The new 'touched' field is added to the COPY_INFO structure. It holds the
  number of rows that were touched no matter whether they were actually
  changed or not.
[15 Mar 2007 20:23] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/22059

ChangeSet@1.2476, 2007-03-15 23:21:29+03:00, evgen@moonbone.local +4 -0
  Bug#27033: 0 as LAST_INSERT_ID() after INSERT .. ON DUPLICATE if rows were
  touched but not actually changed.
  
  The LAST_INSERT_ID() is reset to 0 if no rows were inserted or changed.
  This is the case when an INSERT ... ON DUPLICATE KEY UPDATE updates a row
  with the same values as the row contains.
  
  Now the LAST_INSERT_ID() values is reset to 0 only if there were no rows
  successfully inserted or touched.
  The new 'touched' field is added to the COPY_INFO structure. It holds the
  number of rows that were touched no matter whether they were actually
  changed or not.
[17 Mar 2007 17:58] Alexey Botchkov
Pushed in 5.0.40, 5.1.17
[20 Mar 2007 17:43] Paul DuBois
Noted in 5.0.40, 5.1.17 changelogs.

For INSERT ... ON DUPLICATE KEY UPDATE statements on tables
containing AUTO_INCREMENT columns, LAST_INSERT_ID() was reset to 0 if
no rows were successfully inserted or changed. Not changed includes
the case where a row was updated to its current values, but in that
case, LAST_INSERT_ID() should not be reset to 0. Now LAST_INSERT_ID()
is reset to 0 only if no rows were successfully inserted or touched,
whether or not touched rows were changed.
[20 Jun 2007 5:01] Valeriy Kravchuk
Bug #29212 was marked as a duplicate of this one.
[5 Nov 2011 17:45] HC Jehg
Still present in 5.0.92 with a twist. LAST_INSERT_ID wrong if data comes from select and no change is made. Note this example has not got the cnt=cnt+1 in the first couple of queries.

mysql> CREATE TABLE `words` (
    ->   `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   `word` VARCHAR(32) NOT NULL,
    ->   `cnt` INT UNSIGNED NOT NULL,
    ->   PRIMARY KEY (`id`),
    ->   UNIQUE KEY `word` (`word`)
    -> )TYPE = innodb;
Query OK, 0 rows affected, 1 warning (0,00 sec)

mysql> INSERT INTO words (word,cnt) SELECT 'test',1 ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id); SELECT LAST_INSERT_ID();
Query OK, 1 row affected (0,00 sec)
Records: 1  Duplicates: 0  Warnings: 0

+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |  <--- nice
+------------------+
1 row in set (0,00 sec)

mysql> INSERT INTO words (word,cnt) SELECT 'test',1 ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id); SELECT LAST_INSERT_ID();
Query OK, 0 rows affected (0,01 sec)
Records: 1  Duplicates: 0  Warnings: 0

+------------------+
| LAST_INSERT_ID() |
+------------------+
|                0 |  <-- NOT NICE
+------------------+
1 row in set (0,00 sec)

Original bug seems solved:

mysql> INSERT INTO words (word,cnt) VALUES ('test',1) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id); SELECT LAST_INSERT_ID();
Query OK, 0 rows affected (0,01 sec)

+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |  <-- nice
+------------------+
1 row in set (0,00 sec)

If we put in a change (which kind of defeats the purpose of the id=LAST_INSERT_ID(id) trick) then it seems to work, even with the select.

mysql> INSERT INTO words (word,cnt) SELECT 'test',1 ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), cnt=cnt+1; SELECT LAST_INSERT_ID();
Query OK, 2 rows affected (0,00 sec)
Records: 1  Duplicates: 1  Warnings: 0

+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+
1 row in set (0,00 sec)

Did I miss something?

This is on Mandrive 2008.1 for i586 compiled from source.

HC