MySQL Bugs: #116939: LAST_INSERT_ID(0) not visible in OK packet

Bug #116939	LAST_INSERT_ID(0) not visible in OK packet
Submitted:	11 Dec 2024 9:38	Modified:	11 Dec 2024 15:59
Reporter:	Andres Taylor	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Connection Handling	Severity:	S3 (Non-critical)
Version:	8.4.2	OS:	MacOS
Assigned to:		CPU Architecture:	ARM

Description:
When executing a DML statement that uses LAST_INSERT_ID(0), the server does not include the correct last_insert_id value in the returned OK packet. Although LAST_INSERT_ID() calls afterward correctly yield 0, the initial OK packet fails to reflect the expected value.

How to repeat:
create table t(id bigint);
insert into t values (1);
update test.t set id = last_insert_id(42);
update test.t set id = last_insert_id(0);

using wireshark, we can inspect the OK packets for the two updates:

MySQL Protocol - response OK
    Packet Length: 48
    Packet Number: 1
    Response Code: OK Packet (0x00)
    Affected Rows: 1
    Last INSERT ID: 42

MySQL Protocol - response OK
    Packet Length: 48
    Packet Number: 1
    Response Code: OK Packet (0x00)
    Affected Rows: 0

Wireshark capture showing the issue

Attachment: last_insert_id.pcap (application/octet-stream, text), 6.21 KiB.

Hi. Looking at the pcap I think this might be an issue with Wireshark and not an issue with MySQL. There is one byte between the 'Affected Rows' value and the 'Server Status' value. I think this 00 should have been the last insert ID.

Please report an issue for this at https://gitlab.com/wireshark/wireshark/-/issues/new and I'll try to debug and fix this.

However, you likely may have looked into this because of issues with an application or otherwise. Maybe that application, connector, etc. has a similar issue. In that case it *could* be a MySQL related issue.

https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_basic_ok_packet.html has the details of the OK packet

https://gitlab.com/wireshark/wireshark/-/issues/20285

Thank you, Daniel,

This proves that this report is not a bug.

Hence, the status is changed.

Hi Daniël, thanks for the quick response.

We encountered this while working on Vitess to replicate MySQL behavior:
https://github.com/vitessio/vitess/issues/17298

From what we’ve seen, a value of 0 is returned in cases where the last_insert_id is not explicitly changed. For example, running:

UPDATE test.t SET id = 12;

produces an OK packet with last_insert_id=0, even though no last_insert_id was set by this query.

0000   45 00 00 68 00 00 40 00 40 06 00 00 7f 00 00 01
0010   7f 00 00 01 0c ea d1 92 48 3a d5 b6 a1 60 78 4f
0020   80 18 18 e7 fe 5c 00 00 01 01 08 0a fc 4b ea db
0030   48 66 21 34 30 00 00 01 00 01 ->00<- 22 00 00 00 28
0040   52 6f 77 73 20 6d 61 74 63 68 65 64 3a 20 31 20
0050   20 43 68 61 6e 67 65 64 3a 20 31 20 20 57 61 72
0060   6e 69 6e 67 73 3a 20 30

This creates ambiguity: 0 can mean either “no change to the session value” or “the session value was explicitly set to zero.” In Vitess, we’re working around this by ignoring the zero result unless the query clearly includes a last_insert_id(x) call. Without that context, clients cannot reliably distinguish between the two scenarios.

As it stands, this ambiguity seems tied to the current protocol. Unless the protocol is adjusted to differentiate these cases, clients will continue having to rely on the query itself to interpret the returned zero value.

Hi Mr. Taylor,

Thank you for your latest comment.

We understand from your comment that a change in the protocol packets would be required. That is VERY difficult to do and it would necessitate many, many changes. It would all start with a change in the protocol version, so that olde Connectors and API's should have no problem in logging in with the new design of the packet, while also accommodating new Connectors and new API's ....... This would also makes huge problems with replication, since you could have two different protocol packet versions communicating in the same cluster.

Hence, it would be very , very hard to accommodate this change .....

I think the "Not a Bug" status is correct. This just seems to be a protocol limitation.

This behavior probably stems from the link between LAST_INSERT_ID() and AUTO_INCREMENT, where 0 means next-id.

mysql-9.1.0> CREATE TABLE t1(id int AUTO_INCREMENT PRIMARY KEY, c1 int);
Query OK, 0 rows affected (0.02 sec)

mysql-9.1.0> INSERT INTO t1(c1) values (10);
Query OK, 1 row affected (0.01 sec)

mysql-9.1.0> INSERT INTO t1(c1) values (20);
Query OK, 1 row affected (0.01 sec)

mysql-9.1.0> INSERT INTO t1 values (9, 30);
Query OK, 1 row affected (0.01 sec)

mysql-9.1.0> INSERT INTO t1 values (0, 40);
Query OK, 1 row affected (0.01 sec)

mysql-9.1.0> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|               10 |
+------------------+
1 row in set (0.00 sec)

mysql-9.1.0> TABLE t1;
+----+------+
| id | c1   |
+----+------+
|  1 |   10 |
|  2 |   20 |
|  9 |   30 |
| 10 |   40 |
+----+------+
4 rows in set (0.00 sec)

Note that LAST_INSERT_ID() doesn't work for things that are server generated, but are not using AUTO_INCREMENT

mysql-9.1.0> create table t1 (id bigint as (123) stored primary key, c1 int);
Query OK, 0 rows affected (0.02 sec)

mysql-9.1.0> insert into t1(c1) values (10);
Query OK, 1 row affected (0.01 sec)

mysql-9.1.0> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

mysql-9.1.0> table t1;
+-----+------+
| id  | c1   |
+-----+------+
| 123 |   10 |
+-----+------+
1 row in set (0.00 sec)

I think the way forward is to:
- Stop using last_insert_id (the function and the field in the OK packet)
- Support the RETURNING keyword, e.g. `insert into t1(c1) values (10) returning id,c2`. Note that this doesn't need a protocol modification as it is just a normal resultset. This would also make it easier to return other columns that might be generated server side based on column defaults and definitions for generated columns.

Thank you, Daniel .....