Description:
on duplicate key doesn't update LAST_INSERT_ID for INNODB storage engine.
This is tested in mysql 5.0.37 (and some versions below)
How to repeat:
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;
mysql> INSERT INTO words (word,cnt) VALUES ('test',1) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), cnt=cnt+1;
Query OK, 1 row affected (0.01 sec)
mysql> SELECT LAST_INSERT_ID(); +------------------+
| LAST_INSERT_ID() |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)
mysql> INSERT INTO words (word,cnt) VALUES ('test',1) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), cnt=cnt+1;
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> SELECT * from words;
+----+------+-----+
| id | word | cnt |
+----+------+-----+
| 1 | test | 2 |
+----+------+-----+
1 row in set (0.00 sec)
Suggested fix:
An workaround is:
1. use transactions:
BEGIN;
select id from words where word='test' FOR UPDATE; #set lock
INSERT INTO words (word,cnt) VALUES ('test',1) ON DUPLICATE KEY UPDATE cnt=cnt+1;
select id from words where word='test';#get id
COMMIT;#commit and release lock
2. use myisam table type
Description: on duplicate key doesn't update LAST_INSERT_ID for INNODB storage engine. This is tested in mysql 5.0.37 (and some versions below) How to repeat: 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; mysql> INSERT INTO words (word,cnt) VALUES ('test',1) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), cnt=cnt+1; Query OK, 1 row affected (0.01 sec) mysql> SELECT LAST_INSERT_ID(); +------------------+ | LAST_INSERT_ID() | +------------------+ | 1 | +------------------+ 1 row in set (0.00 sec) mysql> INSERT INTO words (word,cnt) VALUES ('test',1) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), cnt=cnt+1; 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> SELECT * from words; +----+------+-----+ | id | word | cnt | +----+------+-----+ | 1 | test | 2 | +----+------+-----+ 1 row in set (0.00 sec) Suggested fix: An workaround is: 1. use transactions: BEGIN; select id from words where word='test' FOR UPDATE; #set lock INSERT INTO words (word,cnt) VALUES ('test',1) ON DUPLICATE KEY UPDATE cnt=cnt+1; select id from words where word='test';#get id COMMIT;#commit and release lock 2. use myisam table type