MySQL Bugs: #97061: MySqlCommand.LastInsertedId returns 0 after executing multiple statements

Bug #97061	MySqlCommand.LastInsertedId returns 0 after executing multiple statements
Submitted:	30 Sep 2019 7:15	Modified:	28 Nov 2022 18:14
Reporter:	Niklas Föcking	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	8.0.17	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
In my C# program, I want to do two insert statements by executing MySqlCommand.ExecuteNonQuery() only a single time. So, in MySqlCommand.CommandText, I separate these two statements with a semicolon. After executing ExecuteNonQuery(), LastInsertedId returns always 0, but it should return the value of the autoincremeted id. 

How to repeat:
First, I created two tables with the following statements:

Create Table TestTable(
    id BIGINT NOT NULL AUTO_INCREMENT,
    column1 CHAR(100),
    Primary Key(id)
);

Create Table TestTableWithForeignKey(
    foreign_id BIGINT NOT NULL,
    column2 CHAR(100),
    Foreign Key(foreign_id) REFERENCES TestTable(id)
);

After that, I insert an entry into the first table (which is named 'TestTable'). This table contains a primary key (id) which is incremeted automatically. In a second step, I insert an entry into the second table ('TestTableWithForeignKey') which inserts the id of the first table as an foreign key. For these two insertions, I use the following statements:

INSERT INTO TestTable (column1) VALUES ('hello'); 
INSERT INTO TestTableWithForeignKey (foreign_id, column2) VALUES(LAST_INSERT_ID(), 'test');

At the end, I call MySqlCommand.LastInsertedId, because I want to save this ID in a datastructure, but it always returns 0. I wrote the following program to show the issue with only a few lines of code:

        static void Main(string[] args)
        {
            ExecuteInsertStatements();
            Console.ReadKey();
        }

        private static MySqlConnection GetConnection()
        {
            var factory = MySqlClientFactory.Instance;

            if (!(factory.CreateConnection() is MySqlConnection connection))
                throw new Exception("Could not get database connection from client factory");

            connection.ConnectionString = "Server=localhost; Database=Test; UID=root; PWD=secretsecret";
            connection.Open();
            return connection;
        }

        private static void ExecuteInsertStatements()
        {
            using (var connection = GetConnection())
            {
                using (var command = connection.CreateCommand())
                {
                    var trans = connection.BeginTransaction();
                    
                    try
                    {
                        command.Transaction = trans;
                        command.CommandText = "INSERT INTO TestTable (column1) VALUES ('hello'); INSERT INTO TestTableWithForeignKey (foreign_id, column2) VALUES(LAST_INSERT_ID(), 'test');";
                        command.ExecuteNonQuery();
                        Console.WriteLine($"Inserted Entry with ID {command.LastInsertedId}");

                        trans.Commit();
                    }
                    catch (Exception e)
                    {
                        Console.WriteLine(e);
                    }
                }
            }
        }
    }

The output of 'Console.WriteLine($"Inserted Entry with ID {command.LastInsertedId}");' in method ExecuteInsertStatements() is always 'Inserted Entry with ID 0'.

Suggested fix:
The call of MySqlCommand.LastInsertedId should work like the call of 'Select last_insert_id()'. It should return the last autoincremented primary key instead of 0.

Hello Niklas Föcking,

Thank you for the report.

regards,
Umesh

Same bug MySqlCommand.LastInsertedId returned 0, if command contained more then one INSERT.
Connector/NET(MySql.Data.dll) 8.0.18.0

Posted by developer:
 
Verified using Connector/Net version 8.0.30

Posted by developer:
 
When executing multiple Insert statements in one single MySQLCommand, the LastInsertedId property was set to zero since Connector/NET was reading an extra OK packet from the server that doesn't contain the LastInsertedId. The fix was to avoid overriding the value of the LastInsertedId property if this has been already set.

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

When executing multiple Insert statements in one MySQLCommand call, the
LastInsertedId property was set to zero because the connector read an
extra OK packet from the server that omitted LastInsertedId. Now,
Connector/NET avoids overriding the value if it is set

Thank you for the bug report.