MySQL Bugs: #96628: mysql batch insert generatedKeys not accord expected

Bug #96628	mysql batch insert generatedKeys not accord expected
Submitted:	23 Aug 2019 3:21	Modified:	24 Aug 2019 2:23
Reporter:	xxz xxz	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S1 (Critical)
Version:	8.0.17	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
mysql batch insert values generatedKeys not expected.

sql:
insert into test1(id, name) values(?, ?), (null, ?), (?, ?), (null, ?)

parameters:
1, "11", "22", 10, "1010", "44"

generatedKeys:
expected: 1, 2, 10, 11
actual: 2, 3, 4, 5

How to repeat:
create table test1(id bigint auto_increment primary key, name varchar(45)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Connection con = getConnection();
PreparedStatement ps = con.prepareStatement("insert into test1(id, name) values(?, ?), (null, ?), (?, ?), (null, ?)");
int i = 0;
ps.setObject(++i, 1);
ps.setObject(++i, "11");
ps.setObject(++i, "22");
ps.setObject(++i, 10);
ps.setObject(++i, "1010");
ps.setObject(++i, "44");
ps.addBatch();
ps.executeBatch();

ResultSet rs = ps.getGeneratedKeys();
List<Integer> list = new ArrayList<Integer>();
while(rs.next()) {
    int aInt = rs.getInt(1);
    list.add(aInt);
}
System.out.println(list);
rs.close();
ps.close();
con.close();

Hello!

Thank you for the report and test case.

regards,
Umesh

I hope you confirm the issue. if the issue is bug, please tell me resolve plan.

Hi,

It's not a bug actually it's a limitation of MySQL protocol.

C/J collects generated keys from last_insert_id values returned by server in OK packet (https://dev.mysql.com/doc/internals/en/packet-OK_Packet.html). And there is always only one value per query. So in case of multi-value INSERT you use server returns last_insert_id=2, and that's all c/J knows after all, and there is no other choice than try to guess that all INSERTs are identical and just increment this last_insert_id for remaining number of parameters according to AUTO_INCREMENT, as described here https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_last-insert-id.

So, if you need to mix explicit key values with generated ones you should not use multi-value inserts. This one works as expected:

ps = con.prepareStatement("insert into testBug96628(id, name) values(?, ?)", Statement.RETURN_GENERATED_KEYS);
ps.setObject(1, 1);
ps.setObject(2, "11");
ps.addBatch();
ps.setObject(1, null);
ps.setObject(2, "22");
ps.addBatch();
ps.setObject(1, 10);
ps.setObject(2, "1010");
ps.addBatch();
ps.setObject(1, null);
ps.setObject(2, "44");
ps.addBatch();
ps.executeBatch();

whether convert the sql on mysql server. let it be with you provider case return result consistency.

best regards
xxz