MySqlCommand.LastInsertedId

[dkoehler69]

MySqlConnector version: 1.3.11
Environment: .NET Framework 4.7.2 under Windows 10

MySqlCommand.LastInsertedId gives an incorrect result, when using multiple SQL-statements in the command.

This is the method that exposes the problem:

protected long NewEntryTransactional(string qry, params MySqlParameter[] parameters)
{
  using MySqlConnection dbConnection = CreateDatabaseConnection();
  using MySqlCommand cmd = CreateNewEntryCmd(dbConnection, qry, parameters); // Builds the cmd
  dbConnection.Open();
  try
  {
	MySqlTransaction tr = dbConnection.BeginTransaction();
	try
	{
	  cmd.Transaction = tr;
	  cmd.ExecuteNonQuery();
	  long id = cmd.LastInsertedId;
	  tr.Commit();
	  return id;
	}
	catch (Exception)
	{
	  tr.Rollback();
	  throw;
	}
  }
  finally
  {
	dbConnection.Close();
  }
}

When I call this method with qry containing a SELECT statement followed by an INSERT statement, then cmd.LastInsertedId returns -1 instead of the ID of the inserted row. I have also tried to moved long id = cmd.LastInsertedId; behind tr.Commit(); But this still returns -1. (The SELECT statement defines a variable that is used in the INSERT statement.)

Running the code with Connector/NET returns the ID. (EDIT: My original Connector/NET code did not include the line cmd.Transaction = tr;)

[dkoehler69]

I looked a bit deeper into it: cmd.LastInsertedId does not work with a single INSERT statement outside of a transaction, either. This time it returns 0, even though a row with a new auto increment value had been inserted. (Checked with the InnoDB engine.)

[bgrainger]

Thanks for the detailed bug report; a fix is underway.

cmd.LastInsertedId does not work with a single INSERT statement outside of a transaction, either

There are tests that verify that this does work:

MySqlConnector/tests/SideBySide/InsertTests.cs

Lines 33 to 37 in 20ddc50

	await m_database.Connection.OpenAsync();
	using var command = new MySqlCommand("INSERT INTO insert_ai (text) VALUES (@text);", m_database.Connection);
	command.Parameters.Add(new() { ParameterName = "@text", Value = "test" });
	await command.ExecuteNonQueryAsync();
	Assert.Equal(1L, command.LastInsertedId);

If you have a consistent repro, please post it.

[dkoehler69]

cmd.LastInsertedId does not work with a single INSERT statement outside of a transaction, either.

I was able to narrow down the problem. A simple INSERT statement does indeed work. But when I add a comment sign (#) directly after the semicolon, then LastInsertedId returns 0.

The problem also shows up, when I surround the INSERT statement with a LOCK TABLE ... WRITE; before and an UNLOCK TABLE; instruction afterwards (in the same MySqlCommand object).

Checked with MySqlConnector version: 1.3.11.

[bgrainger]

I haven't tested yet, but those will likely be fixed by the commit already linked to this case.

[bgrainger]

The problem also shows up, when I surround the INSERT statement with a LOCK TABLE ... WRITE; before and an UNLOCK TABLE; instruction afterwards (in the same MySqlCommand object).

I see 0 being returned from Connector/NET. OTOH, I could be convinced that's a bug (that MySqlConnector should fix); there's an INSERT command in that compound SQL statement, and LastInsertedId should return the first ID inserted from the last INSERT command (which would not be 0). What are your thoughts?

[bgrainger]

Looks like Connector/NET's behaviour is already filed as a bug: https://bugs.mysql.com/bug.php?id=97061

[dkoehler69]

I checked what MySQL itself is returning:

CREATE TABLE `test` ( `id` INT NOT NULL AUTO_INCREMENT , `foo` INT NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;
LOCK TABLE test WRITE;
INSERT INTO test (foo) VALUES (42);
UNLOCK TABLE;
SELECT LAST_INSERT_ID(); 

This returns 1, and subsequent executions of LOCK TABLE ... UNLOCK TABLE; SELECT LAST_INSERT_ID(); return 2, 3, ...

So yes, it seems to be a bug.

[bgrainger]

Added some additional tests (for the above examples) in 76357a1.