FlyingZC opened a new issue, #24070:
URL: https://github.com/apache/shardingsphere/issues/24070
## Bug Report
In JDBC adapter,the statement cache is not cleared when the connection is
closed, and a connection close error is reported.
### Which version of ShardingSphere did you use?
Master.
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
JDBC
### Expected behavior
Clear the statement cache when conection is closed.
### Actual behavior
In JDBC adapter,the statement cache is not cleared when the connection is
closed, and a connection close error is reported.
```
java.sql.SQLException: Connection is closed
at
com.zaxxer.hikari.pool.ProxyConnection$ClosedConnection.lambda$getClosedConnection$0(ProxyConnection.java:515)
at com.sun.proxy.$Proxy6.getMetaData(Unknown Source)
at
com.zaxxer.hikari.pool.ProxyConnection.getMetaData(ProxyConnection.java:380)
at
com.zaxxer.hikari.pool.HikariProxyConnection.getMetaData(HikariProxyConnection.java)
at
org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:85)
at
org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:69)
at
org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.syncExecute(ExecutorEngine.java:135)
at
org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.serialExecute(ExecutorEngine.java:121)
at
org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.execute(ExecutorEngine.java:115)
at
org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutor.execute(JDBCExecutor.java:67)
at
org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutor.execute(JDBCExecutor.java:51)
at
org.apache.shardingsphere.driver.executor.DriverJDBCExecutor.doExecute(DriverJDBCExecutor.java:152)
at
org.apache.shardingsphere.driver.executor.DriverJDBCExecutor.execute(DriverJDBCExecutor.java:141)
at
org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.useDriverToExecute(ShardingSpherePreparedStatement.java:481)
at
org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.execute(ShardingSpherePreparedStatement.java:414)
at
org.apache.shardingsphere.test.e2e.transaction.cases.temp.BenchmarkInOnePhaseTransaction.oltpReadWrite(BenchmarkInOnePhaseTransaction.java:65)
at
org.apache.shardingsphere.test.e2e.transaction.case3.jdbc.SimpleSysbenchLocalTransTestCase.testOnePhaseTransaction(SimpleSysbenchLocalTransTestCase.java:36)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at
org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:59)
at
org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at
org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:56)
at
org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
at
org.junit.runners.BlockJUnit4ClassRunner$1.evaluate(BlockJUnit4ClassRunner.java:100)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:366)
at
org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:103)
at
org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:63)
at org.junit.runners.ParentRunner$4.run(ParentRunner.java:331)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:79)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:329)
at org.junit.runners.ParentRunner.access$100(ParentRunner.java:66)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:293)
at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
at org.junit.runners.ParentRunner.run(ParentRunner.java:413)
at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
at
com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:69)
at
com.intellij.rt.junit.IdeaTestRunner$Repeater$1.execute(IdeaTestRunner.java:38)
at
com.intellij.rt.execution.junit.TestsRepeater.repeat(TestsRepeater.java:11)
at
com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:35)
at
com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:235)
at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:54)
2023-02-09 10:43:08.143 [main] DEBUG com.atomikos.icatch.jta.TransactionImp
- enlistResource (
org.apache.shardingsphere.transaction.xa.spi.SingleXAResource@78743dd8 ) with
transaction 127.0.0.1.tm167591058813000004
2023-02-09 10:43:08.143 [main] DEBUG
com.atomikos.icatch.imp.TransactionStateHandler - addParticipant (
XAResourceTransaction: XID:
3132372E302E302E312E746D313637353931303538383133303030303034:3132372E302E302E312E746D36
) for transaction 127.0.0.1.tm167591058813000004
2023-02-09 10:43:08.143 [main] DEBUG
com.atomikos.datasource.xa.XAResourceTransaction - XAResource.start ( XID:
3132372E302E302E312E746D313637353931303538383133303030303034:3132372E302E302E312E746D36
, XAResource.TMNOFLAGS ) on resource 2-ds_0 represented by XAResource instance
org.apache.shardingsphere.transaction.xa.spi.SingleXAResource@78743dd8
2023-02-09 10:43:08.143 [main] DEBUG
com.atomikos.icatch.imp.CompositeTransactionImp - registerSynchronization (
com.atomikos.icatch.jta.Sync2Sync@2986427e ) for transaction
127.0.0.1.tm167591058813000004
```
### Reason analyze (If you can)
<img width="946" alt="image"
src="https://user-images.githubusercontent.com/19788130/217712684-58bc213d-2d49-4a48-97aa-8894427d9548.png";>
<img width="1317" alt="image"
src="https://user-images.githubusercontent.com/19788130/217713506-5cc19dfc-a044-4f28-b4c5-490ed19b4c90.png";>
When the xa transaction is opened on the jdbc side, all previous physical
connections corresponding to the current logical connection of
`ShardingSphereConnection` will be closed,but the statement cache is not
cleared, and the previous statement may be obtained during subsequent
execution. When the connection is obtained through statment, a connection
closed exception is reported.
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule
configuration, when exception occur etc.
```yaml
databaseName: sharding_db
dataSources:
ds_0:
dataSourceClassName: com.zaxxer.hikari.HikariDataSource
driverClassName: com.mysql.jdbc.Driver
jdbcUrl:
jdbc:mysql://localhost:3306/demo_ds_0?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
username: root
password: x
ds_1:
dataSourceClassName: com.zaxxer.hikari.HikariDataSource
driverClassName: com.mysql.jdbc.Driver
jdbcUrl:
jdbc:mysql://localhost:3306/demo_ds_1?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
username: root
password: x
rules:
- !TRANSACTION
defaultType: XA
providerType: Atomikos
- !SHARDING
tables:
sbtest1:
actualDataNodes: ds_${0..1}.sbtest1_${0..1}
databaseStrategy:
standard:
shardingColumn: id
shardingAlgorithmName: database_id_inline
tableStrategy:
standard:
shardingColumn: id
shardingAlgorithmName: table_id_inline
defaultTableStrategy:
none:
shardingAlgorithms:
database_id_inline:
type: INLINE
props:
algorithm-expression: ds_${id % 2}
table_id_inline:
type: INLINE
props:
algorithm-expression:
sbtest1_${Math.floorMod(Math.floorDiv(id.longValue(), 2L), 2L)}
keyGenerators:
snowflake:
type: SNOWFLAKE
mode:
type: Standalone
```
Run `BenchmarkInOnePhaseTransaction` code:
```java
public void oltpReadWrite() throws Exception {
try {
connection.setAutoCommit(false);
int tableNum = random.nextInt(TABLES);
int randomIdNum = random.nextInt(TABLE_SIZE - 20);
for (PreparedStatement each : reads) {
each.setInt(1, randomIdNum);
each.execute();
}
PreparedStatement indexUpdate = indexUpdates[tableNum];
indexUpdate.setInt(1, randomIdNum + 4);
indexUpdate.execute();
PreparedStatement nonIndexUpdate = nonIndexUpdates[tableNum];
nonIndexUpdate.setString(1, randomString(120));
nonIndexUpdate.setInt(2, randomIdNum + 4 * 2);
nonIndexUpdate.execute();
PreparedStatement delete = deletes[tableNum];
delete.setInt(1, randomIdNum + 4 * 3);
delete.execute();
PreparedStatement insert = inserts[tableNum];
insert.setInt(1, randomIdNum + random.nextInt(TABLE_SIZE) * 40);
insert.setInt(2, random.nextInt(Integer.MAX_VALUE));
insert.setString(3, randomString(120));
insert.setString(4, randomString(60));
insert.execute();
connection.commit();
} catch (Exception e) {
e.printStackTrace();
}
}
```
### Example codes for reproduce this issue (such as a github link).
--
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.
To unsubscribe, e-mail:
[email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
![https://user-images.githubusercontent.com/19788130/217712684-58bc213d-2d49-4a48-97aa-8894427d9548.png"](https://user-images.githubusercontent.com/19788130/217712684-58bc213d-2d49-4a48-97aa-8894427d9548.png"){kind=link}
![https://user-images.githubusercontent.com/19788130/217713506-5cc19dfc-a044-4f28-b4c5-490ed19b4c90.png"](https://user-images.githubusercontent.com/19788130/217713506-5cc19dfc-a044-4f28-b4c5-490ed19b4c90.png"){kind=link}