MySQL Bugs: #6822: Statement.executeBatch() does not throw BatchUpdateException

Bug #6822	Statement.executeBatch() does not throw BatchUpdateException
Submitted:	25 Nov 2004 7:24	Modified:	30 Nov 2004 20:57
Reporter:	Rohithaksha Kalluraya	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	3.0.16-ga	OS:	Any (All)
Assigned to:		CPU Architecture:	Any

Description:
In batch update operations, if one of the operation fails (because of key constraint or so) this function is throwing SQLException instead of BatchUpdateException.

How to repeat:
Create a database table with atleast one primary key. Connect to it through JDBC and add rows into the batch using Statement.addBatch(String sql).
Make one of the middle row (say 3rd row out of 10 rows) to fail by making duplicate key constraint (existing primary key). Now execute the batch by calling Statement.executeBatch(). This throws SQLException. It should have thrown BatchUpdateException.

Suggested fix:
Change in the driver.

Look at the code for Statement.executeBatch() and you will see it's not possible for it to throw a generic SQL exception while doing updates. (This behavior is actually tested in the testsuite for the driver).

BatchUpdateExceptions _are_ SQLExceptions, so maybe you're not seeing it the way you want, or the message is confusing you?

I am creating PreparedStatement object and inserting SQL queries using Statement.addBatch(String query). Is this type of usage not allowed ?

Anyway thanks a lot,

Rohit

Please post an example of your code, your comment doesn't make it clear _exactly_ what you are doing.

This program has 6 insert queries. 3rd query fails because of primary key constraint (column Id). Remaining queries are valid.

Attachment: BatchEx.java (application/octet-stream, text), 2.55 KiB.

Uploaded the example to files section.
In that I am inserting 6 INSERT queries into the batch out of which 3rd one fails because of key constriant (Please assume that column "Id" is my primary key column). So remaining 3 queries are getting rejected though they are valid inserts.

Thanks,
Rohit

This behavior is by design. The JDBC specification allows a driver to either stop at the first error encountered (which still throws a BatchUpdateException), or process the entire batch and throw a batch update exception at the end.

We're one of the few drivers that allow you to decide which behavior you want. We default to the first (stop immediately, because in most cases it doesn't make sense to continue the batch, and requires more work to undo if you continue), but if you set 'continueBatchOnError=true' in your JDBC URL as a configuration property, you get the second behavior (which is what you expect).

The testcase that Eric and I put together and posted in BUG#6823 shows that this feature works as intended in both cases (when continueBatchOnError is 'true' or 'false').

We've tested this against Connector/J 3.0 and 3.1. 

If you can post an example of _your_ code showing that this indeed doesn't work, then we'd appreciate it. Otherwise we have to consider this bug as not-repeatable (and we've currently marked it as such).

Please feel free to ammend  this bug if you have a concrete example that demonstrates the issue in your use case.

I am very sorry for dragging the issue. It is very easy to simulate the problem.

Create a PreapredStatement object (by calling Connection.prepareStatement(String sql)), add SQL queries by calling Statement.addBatch(String sql) (Please note that I am calling addBatch of the super class, "Statement" from PreparedStatement object reference). Make sure one of the SQL query fails.
Now if you run this program, it throws SQLException instead of BatchUpdateException.

     Connection conn;
     Statement pstmt;

	  Class.forName(driver);
	  conn = DriverManager.getConnection(url, user, passwd);
	  conn.setAutoCommit(false);
          
	  pstmt = conn.prepareStatement(some_query);
	  
	  pstmt.addBatch(valid_query);
	  
	  pstmt.addBatch(valid_query);
	  
	  // Make sure this query fails because of key constraints.
	  pstmt.addBatch(valid_query);
	  
	  pstmt.addBatch(valid_query);
	  
	  pstmt.addBatch(valid_query);
	  pstmt.executeBatch();
	  conn.commit();

This throws SQLException instead of BatchUpdateException.
java.sql.SQLException: Duplicate entry '1' for key 1
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2816)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1518)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1609)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:2228)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:2159)
        at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1243)
        at com.mysql.jdbc.ServerPreparedStatement.executeBatch(ServerPreparedStatement.java:763)
        at BatchEx.<init>(BatchEx.java:59)
        at BatchEx.main(BatchEx.java:85)

Thanks,
Rohit

Remove parameter rewriteBatchedStatements=true will solve this problem