xiaguangme opened a new issue #7955:
URL: https://github.com/apache/shardingsphere/issues/7955
When we use c3p0 connection pool library and ShardingSphere, we can not
query result with decrypted column normally.
Becasuse ShardingSphere called PreparedStatement.getResultSet twice in
EncryptPreparedStatement, trigger a c3p0 Exception.
### Which version of ShardingSphere did you use?
```xml
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-core</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-namespace</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5.4</version>
</dependency>
```
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
`ShardingSphere-JDBC`
### Expected behavior
When we use c3p0 connection pool library and ShardingSphere, we should query
result with decrypted column normally
### Actual behavior
Trigger a c3p0 Exception:
java.lang.InternalError: Marking a ResultSet inactive that we did not know
was opened!
Exception stack as folllow:
```java
java.lang.InternalError: Marking a ResultSet inactive that we did not know
was opened!
at
com.mchange.v2.c3p0.impl.NewPooledConnection.markInactiveResultSetForStatement(NewPooledConnection.java:353)
at
com.mchange.v2.c3p0.impl.NewProxyResultSet.close(NewProxyResultSet.java:844)
at
com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.close(NewProxyPreparedStatement.java:1693)
at
org.apache.shardingsphere.shardingjdbc.jdbc.adapter.executor.ForceExecuteTemplate.execute(ForceExecuteTemplate.java:42)
at
org.apache.shardingsphere.shardingjdbc.jdbc.adapter.AbstractStatementAdapter.close(AbstractStatementAdapter.java:50)
...
```
### Reason analyze (If you can)
Call getResultSet method twice
```java
// --ShardingSphere--
//
org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.EncryptPreparedStatement
private EncryptResultSet createEncryptResultSet(final PreparedStatement
preparedStatement) throws SQLException {
return null == preparedStatement.getResultSet() ? null : new
EncryptResultSet(runtimeContext, sqlStatementContext, this,
preparedStatement.getResultSet());
}
```
NewProxyPreparedStatement.getResultSet method can not reentry, if you call
the method twice, it will trigger a c3p0 Exception when close Resultset and
PreparedStatement.
```java
// --c3p0--
// com.mchange.v2.c3p0.impl.NewProxyPreparedStatement
public final ResultSet getResultSet() throws SQLException
{
try
{
maybeDirtyTransaction();
ResultSet innerResultSet = inner.getResultSet();
if (innerResultSet == null) return null;
parentPooledConnection.markActiveResultSetForStatement(
inner, innerResultSet );
NewProxyResultSet out = new NewProxyResultSet(
innerResultSet, parentPooledConnection, inner, this );
synchronized ( myProxyResultSets ) {
myProxyResultSets.add( out ); }
return out;
}
//.....
}
// getResultSet in c3p0 can not be called twice, we can refer
[here](https://github.com/swaldman/c3p0/issues/104)
```
So far, it is bug of c3p0 or bug of ShardingSphere??
Continue the analysis,
we find some comment on JDK Statement getResultSet method:
**This method should be called only once per result**
So, c3p0 seems to be no mistake, it is very sad...
```java
//--JDK--
// java.sql.Statement super class of java.sql.PreparedStatement
/**
* Retrieves the current result as a <code>ResultSet</code> object.
* This method should be called only once per result.
*
* @return the current result as a <code>ResultSet</code> object or
* <code>null</code> if the result is an update count or there are no
more results
* @exception SQLException if a database access error occurs or
* this method is called on a closed <code>Statement</code>
* @see #execute
*/
ResultSet getResultSet() throws SQLException;
```
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule
configuration, when exception occur etc.
Use c3p0 connection pool library to query encrypted column.
### Example codes for reproduce this issue (such as a github link).
Plase see the part: Which version of ShardingSphere did you use
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
[email protected]
