Bug #109683 MySqlCommand.LastInsertedId is incorrect if multiple rows are inserted
Submitted: 18 Jan 14:28 Modified: 19 Jan 3:11
Reporter: Bradley Grainger (OCA) Email Updates:
Status: Verified Impact on me:
Category:Connector / NET Severity:S2 (Serious)
Version:8.0.32 OS:Any
Assigned to: CPU Architecture:Any

[18 Jan 14:28] Bradley Grainger
When multiple rows are inserted by a single MySqlCommand, the MySqlCommand.LastInsertedId property returns the ID of the first row that was inserted. The expected behaviour is to return the ID of the last row that was inserted, just as LAST_INSERT_ID() does.

This is a breaking change from v8.0.31.

How to repeat:
using var connection = new MySqlConnection("...");

using var command = new MySqlCommand(@"drop table if exists test;
create table test(rowid integer not null primary key auto_increment, text varchar(100) not null);
", connection);

command.CommandText = @"
INSERT INTO test (text) VALUES ('test1');
INSERT INTO test (text) VALUES ('test2');";

Console.WriteLine(command.ExecuteNonQuery()); // Prints 2
Console.WriteLine(command.LastInsertedId); // Prints 1

// LAST_INSERT_ID() is correct but LastInsertedId is wrong
command.CommandText = @"
INSERT INTO test (text) VALUES ('test3');
INSERT INTO test (text) VALUES ('test4');

using var reader = command.ExecuteReader();
Console.WriteLine(reader.GetInt64(0)); // Prints 4
Console.WriteLine(command.LastInsertedId); // Prints 3

Suggested fix:
Revert this change: https://github.com/mysql/mysql-connector-net/commit/b65554971800996770d7453784ce425c9b7048...
[19 Jan 3:11] MySQL Verification Team
Hello Bradley,

Thank you for the report and feedback.