MySQL Bugs: #46788: While using ON DUPLICATE KEY UPDATE on a batchRewriteStatements I get exception

Bug #46788	While using ON DUPLICATE KEY UPDATE on a batchRewriteStatements I get exception
Submitted:	18 Aug 2009 14:58	Modified:	16 Sep 2009 13:42
Reporter:	Dan Dumitriu	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	5.1.8	OS:	Linux (Fedora 10)
Assigned to:	Mark Matthews	CPU Architecture:	Any
Tags:	exception, ON DUPLICATE KEY UPDATE, Parameter index out of range

Description:
I am using the ON DUPLICATE KEY UPDATE functionality together with the option of rewriteBatchedStatements set to true. when I try to execute the prepared statement I get the following exception:

the prepared statement looks like this:

insert into config_table (modified,id_) values (?,?) ON DUPLICATE KEY UPDATE modified=?

java.sql.SQLException: Parameter index out of range (3 > number of parameters, which is 2).
	at com.sag.etl.job.processors.JdbcInsertProcessor.flush(JdbcInsertProcessor.java:135)
......
Caused by: java.sql.SQLException: Parameter index out of range (3 > number of parameters, which is 2).
	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.PreparedStatement.checkBounds(PreparedStatement.java:3657)
	at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3641)
	at com.mysql.jdbc.PreparedStatement.setBytesNoEscapeNoQuotes(PreparedStatement.java:3391)
	at com.mysql.jdbc.PreparedStatement.setOneBatchedParameterSet(PreparedStatement.java:4203)
	at com.mysql.jdbc.PreparedStatement.executeBatchedInserts(PreparedStatement.java:1759)
	at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1441)
	at com.sag.etl.job.processors.JdbcInsertProcessor.flush(JdbcInsertProcessor.java:131)
	... 16 more

problem does not appear if I use the 5.1.7 connector or any connector which is older.
this seems like a newly introduced bug.

How to repeat:
create a table having a primary key id, and some value e.g. name.

thePreparedStmt = theConnection.prepareStatement("insert into config_table (modified,id_) values (?,?) ON DUPLICATE KEY UPDATE modified=?");

thePreparedStmt.setString(1, "theID");
thePreparedStmt.setString(2, "Hello_world_");
thePreparedStmt.setString(3, "Hello_world_");

when you create the connection set the properties:

        Properties myProperties = new Properties();
        myProperties.put("user", theUser);
        myProperties.put("password", thePassword);
        myProperties.put("cacheServerConfiguration", "true");
        
        myProperties.put("useLocalSessionState", "true");
        myProperties.put("elideSetAutoCommits", "true");
        myProperties.put("alwaysSendSetIsolation", "true");
        
        myProperties.put("rewriteBatchedStatements", "true");
        
        myProperties.put("cachePrepStmts", "true");
        myProperties.put("prepStmtCacheSize", "512");
        
        myProperties.put("maintainTimeStats", "false");
        myProperties.put("cacheResultSetMetadata", "true");
        
        myProperties.put("maintainTimeStats", "false");
        myProperties.put("cacheResultSetMetadata", "true");
        
        myProperties.put("useUnbufferedIO", "false");
        myProperties.put("useReadAheadInput", "false");

execute the insert statement:

thePreparedStmt.executeBatch();

you will get the above exception. please elt me know if you need more details on reproducing it

Suggested fix:
check how you rewrite the statement, I guess you rewrite it up where the ON DUPLICATE KEY UPDATE part of the statement begins....
I have no better suggestion

Versions prior to 5.1.8 wouldn't attempt to re-write a query of this form as a multi-value INSERT. 5.1.8 will attempt to re-write this query as a multi-value INSERT.

It seems we'll need a check that there are no parameters after the ON DUPLICATE KEY UPDATE clause, because it's not possible to re-write the query into a multi-value INSERT. Instead it should be sent as a multi-query (as it was prior to 5.1.8).

Hi,
Actually I was hopping I will get this fix and still aim for some performance improvement. As I found in a presentation (ConnectorJ Performance) by someone called Mark Matthews ;)

A Rewritten INSERT
 INSERT INTO foo VALUES (...),(...),(...) up to
max_allowed_packet
 Starting in 5.1.8, ON DUPLICATE KEY UPDATE
rewritten into multi-value as well!
 except for case where you use LAST_INSERT_ID()

I do not know if that is technically possible or not.
Dan

The issue is that the query can not be re-written to a form like:

INSERT INTO foo VALUES (...), (...), (...) ON DUPLICATE KEY UPDATE ... because after the update the query has parameters. There is no form of multi-value INSERT that works for that case. The entire batch could be re-written to a form like:

INSERT INTO foo VALUES (...) ON DUPLICATE KEY UPDATE bar=1; INSERT INTO foo VALUES (...) ON DUPLICATE KEY UPDATE bar=2; 

Which is what the fix will have to be.

You might consider if the query can be re-written to not require a parameter in the UPDATE clause, i.e. using the VALUES function, see http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

Fixed for 5.1.9.

An entry was added to the 5.1.9 changelog:

When using the ON DUPLICATE KEY UPDATE functionality together with the rewriteBatchedStatements option set to true, an exception was generated when trying to execute the prepared statement:

INSERT INTO config_table (modified,id_) VALUES (?,?) ON DUPLICATE KEY UPDATE modified=?

The exception generated was:

java.sql.SQLException: Parameter index out of range (3 > number of parameters, which is
2).
	at com.sag.etl.job.processors.JdbcInsertProcessor.flush(JdbcInsertProcessor.java:135)
......
Caused by: java.sql.SQLException: Parameter index out of range (3 > number of parameters,
which is 2).
	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.PreparedStatement.checkBounds(PreparedStatement.java:3657)
	at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3641)
	at
com.mysql.jdbc.PreparedStatement.setBytesNoEscapeNoQuotes(PreparedStatement.java:3391)
	at
com.mysql.jdbc.PreparedStatement.setOneBatchedParameterSet(PreparedStatement.java:4203)
	at com.mysql.jdbc.PreparedStatement.executeBatchedInserts(PreparedStatement.java:1759)
	at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1441)
	at com.sag.etl.job.processors.JdbcInsertProcessor.flush(JdbcInsertProcessor.java:131)
	... 16 more