Bug #92536 Updateing server side PrepStmts ResultSet fail

• Submitted: 23 Sep 2018 13:59
• Modified: 14 Dec 2018 0:29
• Reporter: Daniel Busche
• Status: Closed
• Category: Connector / J
• Severity: S3 (Non-critical)
• Version: 8.0.12
• OS: Windows
• CPU Architecture: x86

[23 Sep 2018 13:59] Daniel Busche

Description:
I created a MysqlDataSource and call #setUseServerPrepStmts(true). Bug does not ocur with #setUseServerPrepStmts(false)

When I try to update an ResultSet of an "select ... for update" PreparedStatement for a table with an primary key column and I update a column not within the primary key the updateRow() call leads to the failure

{{{
Exception in thread "main" java.sql.SQLException: Statement parameter 1 not set.
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:85)
	at com.mysql.cj.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:633)
	at com.mysql.cj.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:414)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1025)
	at com.mysql.cj.jdbc.result.UpdatableResultSet.refreshRow(UpdatableResultSet.java:1130)
	at com.mysql.cj.jdbc.result.UpdatableResultSet.refreshRow(UpdatableResultSet.java:1060)
	at com.mysql.cj.jdbc.result.UpdatableResultSet.updateRow(UpdatableResultSet.java:1706)
	at general.Main.main(Main.java:38)
Caused by: com.mysql.cj.exceptions.WrongArgumentException: Statement parameter 1 not set.
	at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:488)
	at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61)
	at com.mysql.cj.ServerPreparedQueryBindings.checkParameterSet(ServerPreparedQueryBindings.java:117)
	at com.mysql.cj.AbstractQueryBindings.checkAllParametersSet(AbstractQueryBindings.java:134)
	at com.mysql.cj.ServerPreparedQuery.prepareExecutePacket(ServerPreparedQuery.java:241)
	at com.mysql.cj.ServerPreparedQuery.serverExecute(ServerPreparedQuery.java:208)
	at com.mysql.cj.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:631)
	... 6 more
}}}

How to repeat:
1. Create a table
 {{{
CREATE TABLE `testupdate` (`key` VARCHAR(45) NOT NULL, `value` BIGINT(20) NOT NULL,  PRIMARY KEY (`key`));
}}}
2. Insert a('key',0)
 {{{
INSERT INTO `testupdate` VALUES ('key', 0)
}}}
3. 
{{{
MysqlDataSource datasource = new MysqlDataSource();
datasource.setUseServerPrepStmts(true);
Connection connection = datasource.getConnection(...);
try (PreparedStatement stmt = connection.prepareStatement("SELECT `key`, `value` FROM `testupdate` WHERE `key`=? FOR UPDATE", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE)) {
 stmt.setString(1, "key");
 try (ResultSet res = stmt.executeQuery()) {
  res.next();
  res.updateLong("value", 1);
  res.updateRow();  // fails
 }
}

[24 Sep 2018 7:07] MySQL Verification Team

Hello Daniel,

Thank you for the report!

regards,
Umesh

[13 Oct 2018 13:34] N.S. Cutler

Any update here?

This is really a show stopper with respect to adopting Connector/J 8.0

[15 Oct 2018 7:41] Alexander Soklakov

We'll try to fix it ASAP.

[14 Dec 2018 0:29] Daniel So

Posted by developer:
 
Added the following entry to the Connector/J 8.0.14 changelog:

"Updating a result set returned by a server-side prepared statement with SELECT ... FOR UPDATE resulted in a SQLException."