Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

MySqlCommand.LastInsertedId #1026

Closed
dkoehler69 opened this issue Aug 23, 2021 · 8 comments
Closed

MySqlCommand.LastInsertedId #1026

dkoehler69 opened this issue Aug 23, 2021 · 8 comments
Labels
Milestone

Comments

@dkoehler69
Copy link

dkoehler69 commented Aug 23, 2021

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;)

@bgrainger bgrainger added the bug label Aug 24, 2021
@dkoehler69
Copy link
Author

dkoehler69 commented Aug 24, 2021

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
Copy link
Member

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:

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
Copy link
Author

dkoehler69 commented Aug 24, 2021

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
Copy link
Member

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

@bgrainger
Copy link
Member

bgrainger commented Aug 25, 2021

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
Copy link
Member

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

@dkoehler69
Copy link
Author

dkoehler69 commented Aug 25, 2021

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
Copy link
Member

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Development

No branches or pull requests

2 participants