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:
None 
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

[30 Oct 2007 23:08] Dominik P.
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
[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.