[CONJ-1117] batch insert can not get all GeneratedKeys Created: 2023-10-27  Updated: 2023-12-14  Resolved: 2023-12-14

Status: Closed
Project: MariaDB Connector/J
Component/s: batch
Affects Version/s: 3.2.0, 3.1.4
Fix Version/s: 3.3.2

Type: Bug Priority: Critical
Reporter: hsin Assignee: Diego Dupin
Resolution: Fixed Votes: 0
Labels: generated, key

Attachments: JPEG File v-3-2-0-1.jpg     JPEG File v2-7-9-1.jpg    
Issue Links:
Relates
relates to CONJ-1121 batch insert can not get all Generate... Open

 Description   

org.mariadb.jdbc.Statement#getGeneratedKeys can not get allinsertIds
sql like
insert into table_name (column1,column2) values(row1,row1),(row2,row2)



 Comments   
Comment by hsin [ 2023-10-27 ]

org.mariadb.jdbc.message.server.OkPacket#getAffectedRows
It's not used anywhere

Comment by Diego Dupin [ 2023-10-27 ]

MySQL/MariaDB protocol doesn't permit to know that. Only the first insert id value is known.
There is 2 different way to have all insert id's:

Knowing @@auto_increment_increment :

Statement stmt = sharedConn.createStatement();
    stmt.execute("DROP TABLE IF EXISTS t1");
    stmt.execute("CREATE TABLE t1(t1 int not null primary key auto_increment, t2 DATE NOT NULL)");
    try (ResultSet rsIncrement = stmt.executeQuery("SELECT @@auto_increment_increment");){
      rsIncrement.next();
      int autoIncrementIncrement = rsIncrement.getInt(1);
      int rowCount = stmt.executeUpdate("INSERT INTO t1(t2) VALUES ('2006-04-01'), ('2019-04-11'), ('2020-04-11')", Statement.RETURN_GENERATED_KEYS);
      try (ResultSet rs = stmt.getGeneratedKeys()) {
        if (rs.next()) {
          int firstInsertId = rs.getInt(1);
          System.out.println("firstInsertId = "+ firstInsertId);
          for (int i = 1; i < rowCount; i++) {
            System.out.println("Other insertId = " + (firstInsertId + autoIncrementIncrement * i));
          }
        }
      }
    }

of more simply since MariaDB 10.5.0, using RETURNING value :

    try (ResultSet rs = stmt.executeQuery("INSERT INTO t1(t2) VALUES ('2006-04-01'), ('2019-04-11'), ('2020-04-11') RETURNING t1")) {
      while (rs.next()) {
        System.out.println("insertId = " + rs.getInt(1));
      }
    }

Comment by hsin [ 2023-11-09 ]

the same sql but have two result betwen 2.7.9 and 3.2.0
version-2.7.9 getGeneratedKeys have two ret

version-3.2.0 getGeneratedKeys only one ret

Comment by hsin [ 2023-11-10 ]

@Diego Dupin

Comment by Diego Dupin [ 2023-11-16 ]

This is still an error in 2.x to return multiple id's in that case. Mysql connector will return one value as well.
For multi-value insert only the first id will be returned.

Comment by hsin [ 2023-11-27 ]

But MyBatis requires multiple IDs to be returned in <org.apache.ibatis.executor.keygen.Jdbc3KeyGenerator#processBatch>.It was normal to use this in 2.x, but now there is an exception when upgrading to 3.x

Comment by Diego Dupin [ 2023-12-05 ]

Since breaking MyBatis, a solution can be done to add a compatibility option to have the same behavior than 2.x

Comment by Diego Dupin [ 2023-12-14 ]

new option `returnMultiValuesGeneratedIds` added for connector 2.x compatibility. When enable, getGeneratedKeys() will return all ids of multi-value inserts.

Generated at Thu Feb 08 03:20:45 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.