Dapper.Contrib InsertAsync w/ mysql has problems with inserted ID (auto increment key column) and high concurrency

[mitchcapper]

Using the latest MySQL driver 7.07-m61 without transactions I was running into issues where multiple inserts at the same time (on different MySQL connections but from the same pool) were getting the wrong (or no/0) values back for primary key auto increment integer columns. This was only an issue in high frequently inserts. By wrong I mean the insert ID for a different recently inserted item was assigned to another item. This was with single inserts (not multiple in one call). In theory last_insert_id should apple to the current connection however something is clearly amiss. I fixed this temporarily with a hack to use DBCommand .LastInsertedId (as on the exact command it can't miss) but not a great solution. Will be filing a feature request that may allow for lastinsertedid solution using the oncompleted feature.

Also this is not extremely high concurrency maybe only a 6-12 items in a second or two, but over a somewhat slow link which might exacerbate the problem. Could look at dual statement queries to do it in the insert query itself (like done in some other sql drivers) otherwise.

[Etrimus]

Hello. There is any information about this issue? It is actual for for me in Dapper 1.50.4 and Dapper.Contrib 1.50.4.

[mitchcapper]

@Etrimus not that I am aware I am using a modified dapper library that gets it off the command itself and returns that for insert queries (rather than affected rows) which works well.

[bgrainger]

It sounds like DapperLib/Dapper#949 describes the underlying cause, which means you may be able to work around this by opening the connection before calling InsertAsync.