You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
LAST_INSERT_ID behaviour differs between MySQL and Vitess for INSERT ... ON DUPLICATE KEY UPDATE ... queries.
From my testing, MySQL 8.0 behaves like this:
If no rows are inserted or updated, LAST_INSERT_ID will be 0.
If no rows are inserted, but some rows are updated, LAST_INSERT_ID will be equal to the AUTO_INCREMENT column's value of the last row that was updated. "Last row updated" is based on the order of VALUES tuples in the INSERT statements.
If at least one row is inserted, LAST_INSERT_ID will be equal to the highest inserted AUTO_INCREMENT column's value.
Inserts into tables that use a sequence for auto increment value generation in Vitess (v16+, haven't tested earlier version) will always return the highest reserved sequence value for the auto increment column, whether an actual insert happened or not. This breaks applications that depend on the behaviour of MySQL.
Reproduction Steps
N/A
Binary Version
v16 and later
Operating System and Environment details
N/A
Log Fragments
N/A
The text was updated successfully, but these errors were encountered:
Thinking about this more, I actually don't believe Vitess can match MySQLs behaviour in all of the cases described above.
When we send an INSERT ... ON DUPLICATE KEY UPDATE ... statement down to a shard, we can't rely on whatever last_insert_id value comes back. The reason for that is that the autoIncrement column defined in the VSchema might either not be flagged as AUTO_INCREMENT in the MySQL schema, or might even be a different column than the one defined as AUTO_INCREMENT in the MySQL schema.
So the only information we can use is the rows_affected value sent back from MySQL. But that does not really help us a lot either, as we can only infer from it whether:
no rows were modified (rows_affected=0)
only one row was inserted (rows_affected=1)
all rows were updated (rows_affected=2*n, where n is the number of rows we tried to insert)
a mix of rows was either inserted or updated, or a few inserts were made without updates, or a few updates were made without inserts (rows_affected between 1 and 2*n)
We can handle the case where no rows were changed correctly, by setting last_insert_id to 0.
For all the cases where there's at least one insert, this is also correctly handled. last_insert_id is set to the first generated/inserted auto increment value. (This is the current behaviour already implemented by Vitess).
For cases where there's no inserts but some updates, we have no way to get ahold of the column values for the columns defined as autoIncrement in the VSchema. We also don't get enough information back from MySQL to understand which row might have been the last one that got updated to run an additional query to fetch the auto increment column value. We also can't use LAST_INSERT_ID in the ON DUPLICATE KEY UPDATE ... part of the query to "fetch" the value of the auto increment column, because that would be executed independent of whether the row was actually modified or not.
Overview of the Issue
LAST_INSERT_ID
behaviour differs between MySQL and Vitess forINSERT ... ON DUPLICATE KEY UPDATE ...
queries.From my testing, MySQL 8.0 behaves like this:
LAST_INSERT_ID
will be0
.LAST_INSERT_ID
will be equal to theAUTO_INCREMENT
column's value of the last row that was updated. "Last row updated" is based on the order ofVALUES
tuples in theINSERT
statements.LAST_INSERT_ID
will be equal to the highest insertedAUTO_INCREMENT
column's value.Inserts into tables that use a sequence for auto increment value generation in Vitess (v16+, haven't tested earlier version) will always return the highest reserved sequence value for the auto increment column, whether an actual insert happened or not. This breaks applications that depend on the behaviour of MySQL.
Reproduction Steps
N/A
Binary Version
Operating System and Environment details
Log Fragments
The text was updated successfully, but these errors were encountered: