Bug #31971 last_insert_id with on duplicate key still broken
| Submitted: | 30 Oct 2007 23:08 | Modified: | 29 Oct 2019 22:33 |
|---|---|---|---|
| Reporter: | Dominik P. | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 5.0.51 and 5.0.45 | OS: | Any (Debian, FreeBSD) |
| Assigned to: | Evgeny Potemkin | CPU Architecture: | Any |
| Tags: | auto_increment, instert, LAST_INSERT_ID, ON DUPLICATE KEY, primary key, unique | ||
[7 Dec 2007 16:18]
Susanne Ebrecht
Verified as described.
I made the following tests with MySQL 5.0.51 and MySQL 5.1.22
mysql> create table a(id serial,txt varchar(100), used integer, primary key(id), unique(txt)) engine=innodb;
mysql> create table b(id serial,txt varchar(100), used integer, primary key(id), unique(txt)) engine=myisam;
mysql> insert into a(txt,used) values ('abc',1) on duplicate key update used=used+1;
mysql> insert into b(txt,used) values ('abc',1) on duplicate key update used=used+1;
mysql> insert into a(txt,used) values ('abc',1) on duplicate key update used=used+1;
mysql> insert into b(txt,used) values ('abc',1) on duplicate key update used=used+1;
Both tables in both systems have had one row inserted and the used was set to 2.
mysql> select last_insert_id() from a; and after this from b:
Result from MySQL 5.0.51 for Innodb and MyIsam: 2
Result from MySQL 5.1.22 for Innodb and MyIsam: 1
mysql> insert into a(txt,used) values ('abc',1) on duplicate key update used=used+1;
mysql> insert into b(txt,used) values ('abc',1) on duplicate key update used=used+1;
Both tables in both systems have had one row inserted and the used was set to 3.
mysql> select last_insert_id() from a; and after this from b:
Result from MySQL 5.0.51 for Innodb and MyIsam: 2
Result from MySQL 5.1.22 for Innodb and MyIsam: 1
mysql> insert into a(txt,used) values ('def',1) on duplicate key update used=used+1;
mysql> insert into b(txt,used) values ('def',1) on duplicate key update used=used+1;
mysql> select * from a; and after this from b;
MySQL 5.0.51 (Result was the same for Innodb and MyIsam):
+----+------+------+
| id | txt | used |
+----+------+------+
| 1 | abc | 3 |
| 2 | def | 1 |
+----+------+------+
mysql> select last_insert_id() from a; and after this from b;
Result: 2
MySQL 5.1.22:
Innodb;
+----+------+------+
| id | txt | used |
+----+------+------+
| 1 | abc | 3 |
| 4 | def | 1 |
+----+------+------+
MyIsam:
+----+------+------+
| id | txt | used |
+----+------+------+
| 1 | abc | 3 |
| 2 | def | 1 |
+----+------+------+
mysql> select last_insert_id() from a; and after this from b;
Result: 2 for both. This is another bug in my eyes.
Doesn't matter, MySQL 5.0.51 results real_last_insert_id+1 after updating instead of inserting by use of "insert ... on duplicate key upldate".
It doesn't matter, how often you have the duplicate key. When there was an duplicate last_insert_id() shows the next_insert_id() instead of the last one.
Also in MySQL 5.0.51 the behaviour is the same for InnoDB and MyISAM.
[29 Oct 2019 22:33]
Roy Lyseng
Posted by developer: Has likely been fixed earlier.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.041 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: show variables like 'v%'; +-------------------------+------------------------------+ | Variable_name | Value | +-------------------------+------------------------------+ | version | 5.0.45-Dotdeb_0.dotdeb.1-log | | version_comment | Dotdeb Etch backport | | version_compile_machine | i486 | | version_compile_os | pc-linux-gnu | +-------------------------+------------------------------+ 4 rows in set (0.01 sec) select last_insert_id() returns wrong id from last auto_increment value when used with 'insert...on duplicate key update...' action. i hope everything is in place...and nothing twice (my first bug report). How to repeat: CREATE TABLE `ws_pages_descriptions` ( `id` int(10) unsigned NOT NULL auto_increment, `description` varchar(333) NOT NULL, `used` mediumint(8) unsigned NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `description` (`description`) ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.01 sec) INSERT INTO ws_pages_descriptions(id, description, used) VALUES(null, 'mein neuer töst', 1) ON DUPLICATE KEY UPDATE used = used + 1; Query OK, 1 row affected (0.01 sec) select last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 1 | <-- nice +------------------+ 1 row in set (0.00 sec) INSERT INTO ws_pages_descriptions(id, description, used) VALUES(null, 'mein neuer töst', 1) ON DUPLICATE KEY UPDATE used = used + 1; Query OK, 2 rows affected (0.01 sec) select last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 2 | <-- not nice! +------------------+ 1 row in set (0.01 sec) select * from ws_pages_descriptions; +----+-----------------+------+ | id | description | used | +----+-----------------+------+ | 1 | mein neuer töst | 2 | +----+-----------------+------+ 1 row in set (0.00 sec) -- works even without naming id column INSERT INTO ws_pages_descriptions(description, used) VALUES('mein neuer töst', 1) ON DUPLICATE KEY UPDATE used = used + 1; Query OK, 2 rows affected (0.00 sec) mysql> select last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 2 | <-- not nice too +------------------+ 1 row in set (0.00 sec) select * from ws_pages_descriptions; +----+-----------------+------+ | id | description | used | +----+-----------------+------+ | 1 | mein neuer töst | 3 | +----+-----------------+------+ 1 row in set (0.00 sec) Suggested fix: don't know, i'm just a little user