MySQL Bugs: #31971: last_insert_id with on duplicate key still broken

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

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

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.

Posted by developer:
 
Has likely been fixed earlier.