MySQL Bugs: #72246: executeBatch insert count broken by rewriteBatchedStatements=true

Bug #72246	executeBatch insert count broken by rewriteBatchedStatements=true
Submitted:	4 Apr 2014 22:13	Modified:	9 Apr 2014 7:45
Reporter:	Justin Cranford	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	5.1.58-1~dotdeb.0-log (Debian)	OS:	Linux (Debian x64 v5.0.5 (Lenny))
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
I upgraded Connector/J from 5.1.24 to 5.1.30, and added rewriteBatchedStatements=true to my JDBC connection properties for Apache Cmmons DBCP.

This changed the behaviour of executeBatch when doing batch insert. Instead of getting 1 back for each element of the returned int[] array, all of the values are not N.

I am wondering if this is the expected behavior of executeBatch with inserts, or if it possibly broken by a change introduced in 5.1.27. 

***

http://dev.mysql.com/doc/relnotes/connector-j/en/news-5-1-27.html

When rewriteBatchedStatements=true and useAffectedRows=true were set, executeBatch() did not return the number of affected rows as expected. (Bug #16442951, Bug #68562)

How to repeat:
Java 7u45 x32
Tomcat 7.0.50
Apache Commons DBCP 1.4
mysql-connector-java-5.1.30-bin.jar
Server: 5.1.58-1~dotdeb.0-log (Debian)

        PreparedStatement prep = null;
        try {
            prep = this.connection.prepareStatement(INSERT_SQL);
            for (int i=0; i<10; i++) {
                prep.setInt(1, i);
                prep.setString(2, "string");
                prep.addBatch();
            }
	        int[] rcs = prep.executeBatch();
	        for (int i=0; i<10; i++) {
	        	System.out.println("rc[" + i + "]=" + rcs[i] + " (Expected=1)");
	        }
        } catch (SQLException sqle) {
        	sqle.printStackTrace();
        } finally {
        	prep.close();
        }

Suggested fix:
If rewriteBatchedStatements=true breaks JDBC API compliance for PreparedStatement.executeBatch(), then throw an expection or log a warning to that effect.

Also document this in Connector/J connection properties to say rewriteBatchedStatements=true breaks JDBC API compliance for doing batch inserts with PreparedStatement.executeBatch().

Correction: Instead of getting 1 back for each element of the returned int[] array, all of the values are *NOW* N.

Hi Justin,

If you give us more info about errors, maybe provide stacktrace, we could be more precise in our analysis.

According to given information this problem might be the same Cody Lerum reported for Hibernate where compliance was broken by Bug#72246 patch, return values greater than 2 (insert ... on duplicate) are not accepted by some frameworks. So the proposal here, and patch in progress in related Bug#61213, is to use JDBC compliant value Statement.SUCCESS_NO_INFO instead of total count per each rewritten statement result.

UPD: "...where compliance was broken by Bug#68562 patch..."

I have no stack trace to post beyond the error logged by my example code. The error I get is specific to my application. Connector/J does not throw an exception, it is proprietary application logic that logged the error.

Justin,

I close this report as duplicate of Bug#68562. Please, feel free to reopen it if you find that your situation is different.

Please note that fix will be introduced in bounds of Bug#61213