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

Insert Ignore LastInsertedId is -1 when Ignored #1147

Closed
mkehoe opened this issue Feb 24, 2022 · 3 comments
Closed

Insert Ignore LastInsertedId is -1 when Ignored #1147

mkehoe opened this issue Feb 24, 2022 · 3 comments
Assignees
Labels

Comments

@mkehoe
Copy link

mkehoe commented Feb 24, 2022

Software versions
MySqlConnector version: 2.1.7
Server type (MySQL, MariaDB, Aurora, etc.) and version: MySql 5.7
.NET version: 6.0

Describe the bug
When using an insert ignore statement with ExecuteNonQueryAsync(), LastInsertedId is -1 (expected 0) if the insert is ignored. I believe this is changed behavior from an earlier version.

Code sample

await connection.OpenAsync();

var sql = @"insert ignore into test.test_table (id, foo) values (@id, @foo)";
await using var cmd = new MySqlCommand(sql, connection);

cmd.Parameters.AddWithValue("@id", 2);
cmd.Parameters.AddWithValue("@foo", "Test");

cmd.CommandType = System.Data.CommandType.Text;
await cmd.ExecuteNonQueryAsync();

Expected behavior
MySqlConnector docs for LastInsertedId reference MySql LAST_INSERTED_ID() and they state:

If you use INSERT IGNORE and the row is ignored, the LAST_INSERT_ID() remains unchanged from the current value (or 0 is returned if the connection has not yet performed a successful INSERT)

This statement leads me to believe that 0 for LastInsertedId is correct in this scenario.

@bgrainger
Copy link
Member

I believe this is changed behavior from an earlier version.

It looks like the value of cmd.LastInsertedId (in this example) changed from 0 to -1 between 1.3.14 and 2.0.0.

@bgrainger
Copy link
Member

This may have been a regression introduced by the fix for #1026.

@bgrainger
Copy link
Member

Fixed in 2.1.8.

okramarenko added a commit to memsql/SingleStoreNETConnector that referenced this issue Mar 27, 2023
Summary:
This diff grabs changes from original connector that were made in 2.1.7 and 2.1.8 releases:
- Only insert a semicolon when concatenating SQL. Fixes mysql-net#1133
- Parse multiple TLS versions correctly. Fixes mysql-net#1138
- Document return value of ExecuteNonQuery. Fixes mysql-net#1136
- Enable all .NET analyzers
- Add support for new types to SingleStoreBulkCopy. Fixes mysql-net#1143
- Update SingleStoreParameter types documentation
- Update StyleCop to 1.2.0-beta.406
- Reset LastInsertedId to 0 between commands, not -1. Fixes mysql-net#1147
- Fix BulkCopy for DateOnly and TimeOnly. Fixes mysql-net#1146

Test Plan: https://app.circleci.com/pipelines/github/memsql/SingleStoreNETConnector/249/workflows/3100503c-8a0f-48e6-99a3-635efe59384f

Reviewers: pmishchenko-ua

Reviewed By: pmishchenko-ua

Subscribers: engineering-list

JIRA Issues: PLAT-6408

Differential Revision: https://grizzly.internal.memcompute.com/D61685
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