Description:
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/...
Symptoms:
## 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...
Suggestion:
```
this.serverSideStatementCache.remove(new CompoundCacheKey(this.database, sql, resultSetType));
```
Or set the resultSetType after the cached pStmt has been returned.
