MySQL Bugs: #27033: LAST_INSERT_ID returns 0 on using INSERT... ON DUPLICATE KEY UPDATE ...

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

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).

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>

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.

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

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)

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)

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.

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.

Pushed in 5.0.40, 5.1.17

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.

Bug #29212 was marked as a duplicate of this one.

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