• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

JDBC executeBatch BatchUpdateException finding bad sql

 
Aadil Shaik
Greenhorn
Posts: 25
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,

I am using JDBC Statement's executeBatch() for running bunch of sql statements in a batch mode.
If there are any bad sql's it throws BatchUpdateException. I want to use this exception to find the actual bad sql.
I use mysql database.



but i see all negative values in the updates array.
[-1, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3, -3]


How to find the bad sql?
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Which database are you using?

Unfortunately, different JDBC drivers might implement this functionality differently. If you're lucky, the documentation of the JDBC driver you're using will contain a section on batch update error handling.

If you're using a specific database/JDBC driver, you might create a specific handling of the BatchUpdateException tailored to the JDBC driver in question. If you don't know in advance which JDBC driver is going to be used, your best bet is to log as much information as possible and use it when the exception happens to figure out what went wrong.
 
Aadil Shaik
Greenhorn
Posts: 25
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you. i use MySQL Connector.
 
Paul Clapham
Sheriff
Posts: 21559
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The documentation here: http://docs.oracle.com/javase/8/docs/api/constant-values.html#java.sql.Statement.EXECUTE_FAILED tells you how to translate the numbers you see in that array into the named constants which the docs talk about. Most of your results are -3 which is EXECUTE_FAILED.

In practice you can't expect to get the failing SQL code. You can't even extract SQL code from a Statement which is supposed to execute that SQL code, in fact. But there's nothing stopping you from putting the SQL code, or some proxy for it, into a parallel array or list or something as you add it to the batch.
 
Aadil Shaik
Greenhorn
Posts: 25
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
All i need to know is the index for the failed SQL.
I have a parallel array that keeps track of the sql's i am adding to the batch.
But it looks like there is some issue with the mysql driver.
If i use rewriteBatchedStatements=true in the connection string then i get -1 and all -3s.
If i don't use the rewriteBatchedStatements=true then i get these codes.
[1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, -3, 1, -3, 1, -3, -3, -3, 1, -3, 1, -3, 1, -3, -3, -3, 1, -3, 1, -3, 1, -3, -3, -3, 1, -3, 1, -3, 1, -3, -3, -3]
For my testing i only added one bad SQL. not sure why i get multiple -3s.
 
Dave Tolls
Ranch Hand
Posts: 2095
15
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think (and i'm sure you could confirm this with the MySQL docs) that the rewrite will result in all rows in a particular INSERT sent to the db failing if one of them has an issue.
In essence the driver turns the individual INSERT statements into one (or as few as possible) INSERT statements each with data for multiple rows.
If one of those rows has, for example, a duplicate key then the whole INSERT will fail.

But I expect that after that rewrite has been done that MySQL will be unable to identify which particular row is the issue as it is now part of a larger statement.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think Dave is right about the batch rewrite.

If you don't use the rewrite, does the index of the first -3 (ie. Statement.EXECUTE_FAILED) in the array correspond to the index of the bad statement? If this is so, you could obtain the first failing statement this way and disregard anything that happened afterwards (you'll rollback the transaction).

However, without the rewrite, there's probably no significant speedup using batches with the Connector/J driver. And without batches, the situation would be much easier to handle.

Generally speaking, your application should only attempt to execute SQL statements that are expected to go through. In this case, if something fails, you'd log as much about the problem as possible and have the situation handled as an incident.

If you want to use the database to identify problems with the data you're writing to it, and the end user to be able to identify and resolve the issue, that's a much, much more complex situation to handle (even without using update batching).
 
Aadil Shaik
Greenhorn
Posts: 25
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"If you don't use the rewrite, does the index of the first -3 (ie. Statement.EXECUTE_FAILED) in the array correspond to the index of the bad statement? If this is so, you could obtain the first failing statement this way and disregard anything that happened afterwards (you'll rollback the transaction)."

Unfortunately the index of the first -3 doesn't correspond to the index of the bad statement. The bad statement is at the every end. Not sure why the driver sends -3 for the correct statements.

I think the driver should be able to identify the bad statement. First try with the rewrite, if the large insert goes through without issues its a happy path. When the large insert fails the driver should try with individual statements and return the bad index back.

For now i am thinking about looking for some other alternative instead of depending on the driver's capabilities. Thank you all.

 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave Tolls wrote:I think (and i'm sure you could confirm this with the MySQL docs) that the rewrite will result in all rows in a particular INSERT sent to the db failing if one of them has an issue.
In essence the driver turns the individual INSERT statements into one (or as few as possible) INSERT statements each with data for multiple rows.
If one of those rows has, for example, a duplicate key then the whole INSERT will fail.

That's exactly what happens! Confirmed in the Configuration Properties for Connector/J.
Configuration Properties for Connector/J wrote:rewriteBatchedStatements

Should the driver use multiqueries (irregardless of the setting of "allowMultiQueries") as well as rewriting of prepared statements for INSERT into multi-value inserts when executeBatch() is called? Notice that this has the potential for SQL injection if using plain java.sql.Statements and your code doesn't sanitize input correctly. Notice that for prepared statements, server-side prepared statements can not currently take advantage of this rewrite option, and that if you don't specify stream lengths when using PreparedStatement.set*Stream(), the driver won't be able to determine the optimum number of parameters per batch and you might receive an error from the driver that the resultant packet is too large. Statement.getGeneratedKeys() for these rewritten statements only works when the entire batch includes INSERT statements. Please be aware using rewriteBatchedStatements=true with INSERT .. ON DUPLICATE KEY UPDATE that for rewritten statement server returns only one value as sum of all affected (or found) rows in batch and it isn't possible to map it correctly to initial statements; in this case driver returns 0 as a result of each batch statement if total count was 0, and the Statement.SUCCESS_NO_INFO as a result of each batch statement if total count was > 0.

Default: false

Since version: 3.1.13


This article and this one clearly illustrate that using rewriteBatchedStatements=true (with MySQL) improves the performance of parametrized batch writing (332% faster for inserts and 179% faster for updates).

Hope it helps!
Kind regards,
Roel
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Aadil Shaik wrote:If i use rewriteBatchedStatements=true in the connection string then i get -1 and all -3s.

According to the javadoc of BatchUpdateException, the -1 seems to be a bug as the only (expected) return values should be update count, -2 (Statement.SUCCESS_NO_INFO), and -3 (Statement.EXECUTE_FAILED). This issue is mentioned here as well (and a bug report would be reported but I can't find it in the MySQL bugs overview).
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic