Bug #109683 MySqlCommand.LastInsertedId is incorrect if multiple rows are inserted

• Submitted: 18 Jan 2023 14:28
• Modified: 6 Mar 2023 17:13
• Reporter: Bradley Grainger (OCA)
• Status: Closed
• Category: Connector / NET
• Severity: S2 (Serious)
• Version: 8.0.32
• OS: Any
• CPU Architecture: Any

[18 Jan 2023 14:28] Bradley Grainger

Description:
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("...");
connection.Open();

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.ExecuteNonQuery();

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');
SELECT LAST_INSERT_ID();";

using var reader = command.ExecuteReader();
reader.Read();
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 2023 3:11] MySQL Verification Team

Hello Bradley,

Thank you for the report and feedback.

regards,
Umesh

[6 Mar 2023 17:13] Christine Cole

Posted by developer:
 
Fixed as of the upcoming MySQL Connector/NET 8.0.33 release, and here's the proposed changelog entry from the documentation team:

When Connector/NET inserted multiple rows using a single MySqlCommand, the
MySqlCommand.LastInsertedId property returned the ID of the first row that
was inserted rather than the last row.

Thank you for the bug report.