Bug #102520 serverSideStatementCache ignores resultSetType
Submitted: 8 Feb 2021 4:11 Modified: 21 Dec 2022 18:44
Reporter: Max Wolffe Email Updates:
Status: Closed Impact on me:
Category:Connector / J Severity:S3 (Non-critical)
Version:8.0.23 OS:Any
Assigned to: CPU Architecture:Any

[8 Feb 2021 4:11] Max Wolffe
Main issue:
It appears that the PreparedStatement returned from ConnectionImpl prepareStatement can contain an incorrect resultSetType when cachePrepStmts is true. https://github.com/mysql/mysql-connector-j/blame/d64b664fa93e81296a377de031b8123a67e6def2/...


## What I'm doing
Running the same sql query `SELECT * FROM testdb.Cars` twice:
1. Once as is, via eclipselink entity manager. 
2. The second time with `setFirstResult` set to index 2 - which should return results starting from index 2. 

## What I expect to happen
The behaviour from MySQL Connector 8.0.19. 

First query returns results 1-10
Second query returns results 2-10

## What is happening
The second query uses `.setFirstResult()` causes the following RuntimeException:
Internal Exception: java.sql.SQLException: Operation not allowed for a result set of type ResultSet.TYPE_FORWARD_ONLY.
Error Code: 0
This is new as of mysql-connector-java 8.0.20, when a runtime check for the resultSetType was added to certain resultSetType operations (absolute, first, etc).

It appears to be caused by an older issue in which preparedStatements were returned from the cache with an old resultSetType. 

I've created a small project to demonstrate this bug - https://github.com/maxwolffe/mysql-connector-bug-example 
MySQL Server Version - Server version: 5.7.29 MySQL Community Server (GPL)
Java Version - 1.8.0_275
OS - Mac OS X 10.15.7
EclipseLink version - 2.7.8 (latest 2.x.x)
MySQL connector version - 8.0.23 (latest)
Relevant jdbc connector settings - eclipselink.jdbc.property.cachePrepStmts=true

How to repeat:
I've created a small project to demonstrate this bug - https://github.com/maxwolffe/mysql-connector-bug-example 

Suggested fix:
Could we add the resultSetType to the serverSideStatementCache compound cache key? So that we're not overwriting the second query's resultSetType?

At this line - https://github.com/mysql/mysql-connector-j/blob/d64b664fa93e81296a377de031b8123a67e6def2/s...

this.serverSideStatementCache.remove(new CompoundCacheKey(this.database, sql, resultSetType));

Or set the resultSetType after the cached pStmt has been returned.
[8 Feb 2021 7:26] MySQL Verification Team
Hello Max Wolffe,

Thank you for the report and test case.

[21 Dec 2022 18:44] Daniel So
Posted by developer:
Added the following entry to the Connector/J 8.0.32 changelog: 

"With prepared statement cache enabled, a server-side prepared statement obtained from the cache always maintained its old result set type even after that had been changed in a new Connection.prepareStatement() call. This patch fixes that behavior by resetting properly the result set type value of the cached statement for each new query."