MySQL Bugs: #36918: Exception when rewriteBatchedStatements and useServerPrepStmts are used together

Bug #36918	Exception when rewriteBatchedStatements and useServerPrepStmts are used together
Submitted:	23 May 2008 15:06	Modified:	13 Apr 2015 7:26
Reporter:	Boris Kobilkovsky	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / J	Severity:	S1 (Critical)
Version:	5.1.6	OS:	Any
Assigned to:	Alexander Soklakov	CPU Architecture:	Any

Description:
Consistently get the following exception when use is something like:

jdbc:mysql://localhost/exp?characterEncoding=UTF-8&useServerPrepStmts=true&rewriteBatchedStatements=true

The exception:

java.sql.SQLException: Parameter index out of bounds. 4 is not between valid values of 1 and 3
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
	at com.mysql.jdbc.ServerPreparedStatement.getBinding(ServerPreparedStatement.java:925)
	at com.mysql.jdbc.ServerPreparedStatement.setOneBatchedParameterSet(ServerPreparedStatement.java:2870)
	at com.mysql.jdbc.PreparedStatement.executeBatchedInserts(PreparedStatement.java:1411)
	at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1071)
	at com.quest.nitro.MySqlTesting.main(MySqlTesting.java:32)

How to repeat:
Here is the code reproducing the problem:

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.springframework.jdbc.datasource.SingleConnectionDataSource;

public class MySqlTesting {

	public static void main(String[] args) {
		SingleConnectionDataSource ds = new SingleConnectionDataSource();
		ds.setDriverClassName("com.mysql.jdbc.Driver");
		ds.setUrl("jdbc:mysql://localhost/exp?" +
				"characterEncoding=UTF-8&useServerPrepStmts=true" +
				"&rewriteBatchedStatements=true");
		ds.setUsername("root");
		ds.setPassword("root");
		
		
		Connection c;
		try {
			c = ds.getConnection();
			PreparedStatement s = c.prepareStatement("insert into test (vl) values (?)");
			long ts = System.currentTimeMillis();
			for (int i = 0; i<3; i++) {
				s.setString(1, "value"+i);
				s.addBatch();
			}
			
			s.executeBatch();
			
			ts =  System.currentTimeMillis() - ts;
			System.out.println("Completed in: " +ts);
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}
}

Suggested fix:
Well I did some debugging and it appears that the problem is somehow related to 0 based index versus 1 based index (the exception kind of hints on that).

Hi Boris and thanks for your report.

I am sorry but I can't repeat it. We have regression test case based on Bug#30493 for this (just a bit modified):

	Connection rewriteConn = null;
        Properties props = new Properties(); // For "faulty" case
        props.put("rewriteBatchedStatements","true");
        props.put("useServerPrepStmt","true");
		
	try {
		String ddl = "(autoIncId INT NOT NULL PRIMARY KEY AUTO_INCREMENT, uniqueTextKey VARCHAR(255) UNIQUE KEY)";
		createTable("testBug30493", ddl);
			
		String [] sequence = {"c", "a", "d", "b"};
		String sql = "insert into testBug30493 (uniqueTextKey) values (?) on duplicate key UPDATE autoIncId = last_insert_id( autoIncId )";
		String tablePrimeSql = "INSERT INTO testBug30493 (uniqueTextKey) VALUES ('a'), ('b'), ('c'), ('d')";
			
		this.stmt.executeUpdate(tablePrimeSql); //This stmt does not use Properties but my standard connection string.
		this.pstmt = this.conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			
		for (int i = 0; i < sequence.length; i++) {
			this.pstmt.setString(1, sequence[i]);
			this.pstmt.addBatch();
		}
		this.pstmt.executeBatch();
		ResultSet nonRewrittenRsKeys = this.pstmt.getGeneratedKeys();
	
		createTable("testBug30493", ddl);
		this.stmt.executeUpdate(tablePrimeSql);
			
		rewriteConn = getConnectionWithProps(props); //Should fail
		this.pstmt = rewriteConn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			
		for (int i = 0; i < sequence.length; i++) {
			this.pstmt.setString(1, sequence[i]);
			this.pstmt.addBatch();
		}
		this.pstmt.executeBatch();
		ResultSet rewrittenRsKeys = this.pstmt.getGeneratedKeys();
			
		assertResultSetsEqual(nonRewrittenRsKeys, rewrittenRsKeys);
	} finally {
		closeMemberJDBCResources();
		if (rewriteConn != null) {
			rewriteConn.close();
		}
	}

I am using JRE 1.5.0_12-b04 with latest c/J sources on WinXP Pro SP2 and mysql-5-0-64-pb1103-win32. If you have c/J source repo checked, you can try yourself.

So, if you can please provide more info or something that can help me reproduce, please do. Otherwise I'll close the report.

Hi Tonci, 

Thanks for forwarding your testing code.

The explanation of the mystery is very easy...

The regression test case code never uses rewriteBatchedStatements and useServerPrepStmts together, because of the simple typo.

Take a look at the line putting the useServerPrepStmts property. The property name misses the 's' character at the end.

* useServerPrepStmts - a proper name for this property
* useServerPrepStmt - the name used in the test case code.

       Connection rewriteConn = null;
       Properties props = new Properties(); // For "faulty" case
       props.put("rewriteBatchedStatements","true");
       props.put("useServerPrepStmt","true");

I corrected the typo and reproduced the problem immediately on the HEAD of the trunk as well as the original 5.1.6 version.

Hope this helps in your analisys.

Your help is much appreciated.

Best Regards,
Boris Kobilkovsky

Boris, alas, I have only myself to blame, spelling error was in my addition to test case...

Anyway, verified just as described with test case provided (mind to add "s" to props.put("useServerPrepStmts","true");).

MySQL server 5.0.64-pb1103-log on WinXP Pro SP2 localhost with latest c/J 5.1 sources.
java.vm.version         : 1.5.0_12-b04
java.vm.vendor          : Sun Microsystems Inc.
java.runtime.version    : 1.5.0_12-b04

This functionality has been overhauled in the HEAD of 5.1, and I can no longer reproduce your issue. Can you try with a nightly snapshot or HEAD build?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".