Error when inserting multiple records with returning last inserted id (mysql2)

[kir4ik]

Hi

When using the mysql2 driver and executing an INSERT query with returningLastInsertedId, I encounter the following behavior:

• If I insert a single record, the ID is returned as expected.
• However, when attempting to insert multiple records, an error occurs due to the structure of the response from the mysql2 driver.

In the method AbstractQueryRunner.executeInsertReturningMultipleLastInsertedId, the code expects rows to be an array. However, the mysql2 driver returns an object containing the insert information. As a result, I receive the following error:

rows.map is not a function

The screenshot attached shows the response object received from the mysql2 driver in the top left corner.

Steps to Reproduce:

• Use the mysql2 driver.
• Attempt to insert multiple records with returningLastInsertedId.

Expected Behavior: executeInsertReturningMultipleLastInsertedId should correctly handle the response when inserting multiple records.

Actual Behavior: An error occurs because the response is an object, and rows.map fails.

[juanluispaz]

Hi,

I'm reviewing this, and I see the issue.

Some facts first:

• So far, I'm aware MySql doesn't support an insert of multiple values returning the ID of each of them: Last inserted ids? sidorares/node-mysql2#435 Getting the IDs of a bulk insert. mysqljs/mysql#1191
• ts-sql-query offers support to different databases and disallows the use of functions not supported by the database, but in this case, this is not happening; then you are able to call the function returningLastInsertedId over an insert of multiple values when it should not be possible.

Paths I see so far in order to fix this issue:

• Disallow returningLastInsertedId on insert of multiple values
• Emulate this during multiple inserts in a single database call, like in Oracle; I'm not sure about this path; let me know if you have information about it.

[Smert]

@juanluispaz Using of lastInsertId is safe if innodb_autoinc_lock_mode = 0 or 1
stackoverflow, mysql docs

But for innodb_autoinc_lock_mode = 2

Another consideration – which you shouldn’t rely on anyway – is that IDs might not be consecutive with a lock mode of 2. That means you could do three inserts and expect IDs 100,101 and 103, but end up with 100, 102 and 104. For most people, this isn’t a huge deal.
source

It depends on mysql configuration. In any case, you can return lastInsertId of bulk inserts. And next we can decide whether we can generate a sequence