Bug Report: result mismatch on LAST_INSERT_ID() between Vitess and MySQL

[mcrauwel]

Overview of the Issue

In some cases using the LAST_INSERT_ID() function through Vitess (vtgate) yields a different result than running this against vanilla MySQL.

Got a reproducible vitess-testertest case

Test case output:

➜  vitess-tester git:(main) ✗ ./vt tester --number-of-shards 1 --vtexplain-vschema t/dw_17047.vschema.json t/dw_17047.test
starting sharded keyspace: 'sharded' with shards [-]
E1129 13:56:50.659161   18267 vtorc_process.go:95] configuration - {
	"Debug": true,
	"ListenAddress": ":7710",
	"RecoveryPeriodBlockSeconds": 1
}
t/dw_17047.test: ok! Ran 11 queries, 10 successfully and 1 failures take time 0.222961125 s

Error: some tests failed 😭
see errors in errors

Reproduction Steps

Error output

➜  vitess-tester git:(main) ✗ cat errors/t/dw_17047.test/28
Error log for query on line 28:
SELECT LAST_INSERT_ID();

Query (SELECT LAST_INSERT_ID();) results mismatched.
Vitess Results:
[UINT64(1)]
Vitess RowsAffected: 0
MySQL Results:
[UINT64(0)]
MySQL RowsAffected: 0
query plan:
{
	"OperatorType": "Projection",
	"Expressions": [
		":__lastInsertId as LAST_INSERT_ID()"
	],
	"Inputs": [
		{
			"OperatorType": "SingleRow"
		}
	]
}

Vitess test-case files

dw_17047.test.txt
dw_17047.vschema.json

Binary Version

➜  vitess-tester git:(main) ✗ vtgate --version
vtgate version Version: 22.0.0-SNAPSHOT (Git revision 68b25b30f210511eda149fe96871704ec800ab18 branch 'main') built on Fri Nov 29 13:55:38 CET 2024 by [email protected] using go1.23.3 darwin/arm64

Operating System and Environment details

Macbook M1 Pro
Mac OS Sequoia 15.1.1

Log Fragments

No response

[systay]

The issue seems to be a bug in MySQL. Using a network sniffer we could see that MySQL is not sending back the last_insert_id value when it's set to 0. It seems to work fine for other numbers, but when we set it to zero, MySQL doesn't send the last_insert_id value.

[harshit-gangal]

Here is the packet output for different query which does not differ for when last_insert_id(<arg>) is used with 0 and when it is not present.

mysql> insert into t3 values (last_insert_id(0));
Query OK, 1 row affected (0.01 sec)

mysql> insert into t3 values (7);
Query OK, 1 row affected (0.01 sec)

with a non-zero last_insert_id argument

mysql> insert into t3 values (last_insert_id(1));
Query OK, 1 row affected (0.01 sec)

[harshit-gangal]

In Vitess, we only update the session state when last_insert_id is non-zero.

From the above test, it is hard to determine when the session state be updated for zero.

A possible workaround for the application would be to use a different default than 0 for last_insert_id

[yydoow]

Yes, I would agree some change in the application, Would it possible to have some error? when last_insert_id(0) is executed given session state is updated only when last_insert_id is non-zero.

[systay]

I've opened a bug report for this:
https://bugs.mysql.com/bug.php?id=116939