Description:
The behavior of INSERT... ON DUPLICATE KEY UPDATE... changed between 5.0.34 and 5.0.36 due to a bug fix for INSERT... IGNORE behavior.
Current behavior does not conform to Documented behavior for 5.0.36 or 5.1.16
How to repeat:
In mysql-enterprise-gpl-5.0.34.rhel.x86_64:
CREATE TABLE Circuit (
CIRCUIT_ID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
CIRCUIT VARCHAR(256) NOT NULL UNIQUE
) ENGINE=InnoDB;
mysql> INSERT INTO Circuit SET CIRCUIT = "foo" ON DUPLICATE KEY UPDATE CIRCUIT_ID=LAST_INSERT_ID(CIRCUIT_ID); select LAST_INSERT_ID();
Query OK, 1 row affected (0.01 sec)
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)
mysql> INSERT INTO Circuit SET CIRCUIT = "foo" ON DUPLICATE KEY UPDATE CIRCUIT_ID=LAST_INSERT_ID(CIRCUIT_ID); select LAST_INSERT_ID();
Query OK, 0 rows affected (0.00 sec)
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)
=========================================================================
In mysql-enterprise-gpl-5.0.36.rhel.x86_64:
CREATE TABLE Circuit (
CIRCUIT_ID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
CIRCUIT VARCHAR(256) NOT NULL UNIQUE
) ENGINE=InnoDB;
mysql> INSERT INTO Circuit SET CIRCUIT = "foo" ON DUPLICATE KEY UPDATE CIRCUIT_ID=LAST_INSERT_ID(CIRCUIT_ID); select LAST_INSERT_ID();
Query OK, 1 row affected (0.01 sec)
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)
mysql> INSERT INTO Circuit SET CIRCUIT = "foo" ON DUPLICATE KEY UPDATE CIRCUIT_ID=LAST_INSERT_ID(CIRCUIT_ID); select LAST_INSERT_ID();
Query OK, 0 rows affected (0.00 sec)
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 0 |
+------------------+
1 row in set (0.00 sec)
