MySQL Bugs: #3873: Statement.getGeneratedKeys method returns only 1 result for batched insertions

Bug #3873	Statement.getGeneratedKeys method returns only 1 result for batched insertions
Submitted:	24 May 2004 16:23	Modified:	27 May 2004 19:52
Reporter:	Ceki Gulcu	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	3.0.12-production	OS:	Windows (Windows)
Assigned to:	Mark Matthews	CPU Architecture:	Any

Description:
Hello,

After inserting several rows by using a prepared statement in a batch, retrieving the auto-generated keys by calling statement.getGeneratedKeys returns only the ID of the last insertion.

We have observed that inserting rows in batches results in a very significant boost in performance. Unfortunately, without the getGeneratedKeys method returning all the inserted rows, we cannot use batched statements.

Your help in shedding light on to the matter would be higly appreciated. Thank
you in advance,
 

How to repeat:
  try {
      // Assume we have a valid connection
      connection.setAutoCommit(false);

      // this is the simplified SQL for the purposes of this example
      StringBuffer sql = new StringBuffer();
      sql.append("INSERT INTO logging_event (");
      sql.append("sequence_number)");
      sql.append(" VALUES (??)");
      String insertSQL = sql.toString();

      PreparedStatement insertStatement =
        connection.prepareStatement(insertSQL, Statement.RETURN_GENERATED_KEYS);

      // we have buffered events in a list called "eventsBuffer"
      for (int i = 0; i < eventsBuffer.size(); i++) {
        LoggingEvent event = (LoggingEvent) eventsBuffer.get(i);
                LogLog.info("*** event message is "+event.getRenderedMessage());
        insertStatement.setLong(1, event.getSequenceNumber());
        insertStatement.addBatch();
      }

      int[] r = insertStatement.executeBatch();

      // the correct number of insertions are made    
      if(r != null) {
        for(int x = 0; x < r.length; x++) {
          System.out.println("inserted "+r[x]);
        }
      }

      ResultSet keyRS = insertStatement.getGeneratedKeys();

      // unfortunately only one result is returned instead of 
      // eventsBuffer.size() results.  Actually, only the id of
      // the last inserted row is returned
      int i = 0;
      while (keyRS.next()) {
        int id = keyRS.getInt(1);
        System.out.println("**filling id ="+id);
        
        // eventIDArray is where we keep track of the IDs
        eventIDArray[i++] = id;;
      }

      connection.commit();
    } finally {
      // close the connection...
    }

This is fixed for 3.0.13, but only for prepared statements (it is a little outside the scope of the JDBC spec, as there is no corrolary executeBatch() method in java.sql.Statement that specifies that generated keys should be returned).

See a nightly build of 3.0.x after 00:00 GMT this evening from http://downloads.mysql.com/snapshots.php if you want to test it before the official 3.0.13 release.

Autogenerated Keys for Batch inserts tested with statement and prepared statement

Attachment: TestBatchInsertAutoGeneratedKeys.java (text/java), 2.78 KiB.

I tested the getGeneratedkey for batch inserts on mysql-connector-3.1.14. 

It appears that the feature works correctly through Statement but not through PreparedStatement (Above comment by Mark noted that it will work through PreparedStatement but not through Statement). 

Same behavior was observed on mysql-connector-5.0.5. 

See the attached JUnit test. <a href="http://bugs.mysql.com/file.php?id=6195"> http://bugs.mysql.com/file.php?id=6195 </a>