MySQL Bugs: #8181: BatchUpdateException for PreparedStatement.executeBatch()

Bug #8181	BatchUpdateException for PreparedStatement.executeBatch()
Submitted:	28 Jan 2005 16:33	Modified:	1 Feb 2005 21:14
Reporter:	Tal Kormas	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	3.1.6 over MySQL 5.0.2	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
When using PreparedStatement and batching, I get a BatchUpdateException on calling the executeBatch.
This exception comes with one of two messages:

1) When number of columns in the insert commands is small (I've tested with 2), I get the following trace:
java.sql.BatchUpdateException: java.lang.ArrayIndexOutOfBoundsException: 2
        at com.mysql.jdbc.ServerPreparedStatement.executeBatch(ServerPreparedStatement.java:743)
...(and then my code)

2) When the number of columns is larger I get the following trace:
java.sql.BatchUpdateException: General error message from server: "Incorrect arguments to mysql_stmt_execute"
        at com.mysql.jdbc.ServerPreparedStatement.executeBatch(ServerPreparedStatement.java:743)
...(and then my code)

How to repeat:
You can use this code:

try{
      con = DriverManager.getConnection(ConnectionString);
}catch (Exception e){
      System.out.println("Failed Connecting");
      return;  
}
try{
      pstmt = con.prepareStatement("INSERT INTO table (col1,col2) VALUES(?,?)");
      for(int i = 0 ; i < 20 ; i++){
           pstmt.setString(1, "Test "+i);
           pstmt.setInt(2,"Test "+i);
           pstmt.addBatch();
      }
      pstmt.executeBatch();
      pstmt.close();
} catch (SQLException e){
      e.printStackTrace();
}

I tried this example against 3.1.6-ga and mysql 5.0.3-alfa-debug on slackware 10, java 1.5.0-b64 and didn't get any exceptions.
Only change I made - replaced
         pstmt.setInt(2,"Test "+i);
to
         pstmt.setInt(2,i);

(I guess it was just a typo, because it cannot be compiled as is)

 I attached ("Files" section) java source that I used for testing, if you have ideas how to reproduce this error please let us know.

test case

Attachment: Bug8181.java (text/x-java), 846 bytes.

I've used you're code and got:

java.sql.BatchUpdateException: General error message from server: "Incorrect arguments to mysql_stmt_execute"
        at com.mysql.jdbc.ServerPreparedStatement.executeBatch(ServerPreparedStatement.java:743)
        at Bug8181.main(Bug8181.java:22)

More info on my configuration:

1) Linux : Red Hat Enterprise Linux ES release 3 (Taroon Update 4) 
2) Kernal : 2.4.21-27.0.1.ELsmp
3) CPU : i686
4) Java : 1.4.2_05
5) MySQL : 5.0.2 ( RPM based ).
6) Connector : 3.1.6-bin (not ga).

I've created the bug8181 table as : create table bug8181(col1 varchar(20),col2 int);

I just tried this myself, and what you say happens leads me to believe you're using an old driver, even older than 3.1.6 (not ga, which I'm not sure what you mean by, is it a nightly build or something?).

Connector/J 3.1.6 automatically turns off server-side prepared statements for MySQL version 5.0.x, as not all changes/fixes had been completely merged into the server code base when 3.1.6 was released. Therefore, if you're using Connector/J 3.1.6, you shouldn't be seeing server-prepared statement errors such as you're seeing.

I thought 3.1.6 is a release, as the file name is mysql-connector-java-3.1.6.zip and jar name is mysql-connector-java-3.1.6-bin.jar (The test made on comment #2 said it's with 3.1.6-ga).
Anyway, I've checked and made a search and this is the only driver version I have on that computer, so I don't see how is it possible for the computer to pickup older driver.

Tal,

I'm just at a loss how you can have 3.1.6 using server-side prepared statements with MySQL-5.0.2, that's all.

If you take a look at com.mysql.jdbc.Connection.initializePropsFromServer(), you'll see the following code which _turns_off_ server-side prepared statements:

            if (versionMeetsMinimum(5, 0, 0)) {
            	this.useServerPreparedStmts = false; // 4.1.2+ style prepared statements
            		                                 // don't work on these versions
            }

Also, line 743 in ServerPreparedStatement() in version 3.1.6 (taken from your stacktrace) is an end brace to an if statment, so I don't know how an exception is being thrown there.

Usually when things seem this far off from what a user 'sees', and what we can/can't duplicate in the lab, we find that somewhere, in some dark dusty corner of their operating system, the user has an old version of the JDBC driver they didn't even know was there.

What does a "find / -name "mysql-connector-java*" (or the equivalent on your operating system) say? You should be able to find a version that you don't think is there, lurking in some jre/lib/ext or WEB-INF/lib directory.

A) It only list 1 file which is mysql-connector-java-3.1.6-bin.jar.
B) I've also tried connecting from Windows to this server and I get the same trace.
C) This driver and program works on MySQL server 4.1

If this is the case, then I suggest you try downloading it from mysql.com again, or testing a nightly snapshot, because the file you've installed really doesn't appear to be the 3.1.6 we shipped :(

I've download 3.1 again from mysql.com, and got the same error.

I've added 2 lines to the Bug8181.java:

          System.out.println(((Driver) Class.forName("com.mysql.jdbc.Driver").newInstance()).getMajorVersion());
          System.out.println(((Driver) Class.forName("com.mysql.jdbc.Driver").newInstance()).getMinorVersion());          

And got the output:
3
1
java.sql.BatchUpdateException: General error message from server: "Incorrect arguments to mysql_stmt_execute"
        at com.mysql.jdbc.ServerPreparedStatement.executeBatch(ServerPreparedStatement.java:743)
        at Bug8181.main(Bug8181.java:24)

I've tried with 3.2 alpha, and got the same output. When I open the look at the com.mysql.jdbc.Driver file on the 3.2 jar it does states that it's a 3.1 version...

Any idea?

Any other idea?

> I've download 3.1 again from mysql.com, and got the same error.
> 
> I've added 2 lines to the Bug8181.java:
> 
>           System.out.println(((Driver)
> Class.forName("com.mysql.jdbc.Driver").newInstance()).getMajorVersion());
>           System.out.println(((Driver)
> Class.forName("com.mysql.jdbc.Driver").newInstance()).getMinorVersion());       
>   
> 
> And got the output:
> 3
> 1
> java.sql.BatchUpdateException: General error message from server: "Incorrect
> arguments to mysql_stmt_execute"
>         at
> com.mysql.jdbc.ServerPreparedStatement.executeBatch(ServerPreparedStatement.java
> :743)
>         at Bug8181.main(Bug8181.java:24)
> 
> I've tried with 3.2 alpha, and got the same output. When I open the look at the
> com.mysql.jdbc.Driver file on the 3.2 jar it does states that it's a 3.1
> version...

I just downloaded it, ran it, and I get '3'/'2', so I really think you have a classpath problem somewhere, despite what you say :(

I've just tested 3.1.6 as well with your testcase, and added one line:

System.out.println(pstmt.getClass().getName());

The testcase passes, and also shows that ServerPreparedStatement _is_not_ being used:

com.mysql.jdbc.PreparedStatement

I can not explain how you're getting ServerPreparedStatements enabled in your environment, because they should _not_ be because they _do_not_ currently work between the version of the JDBC driver you have, and the version of the server have, so the driver is supposed to disable them. Since I do not have access to your environment, the only further advice I can give to you is to forcefully disable them and wait for MySQL-5.0.3 by using the 'useServerPrepStmts=false' flag in your JDBC URL.

Guy, sorry I was wrong. I've added the line:

System.out.println(prop.getProperty("java.class.path"));

And then went over all the jars in this classpath. One of the jars was an archive file containing J/Connected 3.0.11 and as it was before the J/Connector jar it picked it up from this path...

Thanks you for you fast help!