Operation not allowed after ResultSet closed when using SQLFederationEngine

[wjjboy]

Bug Report

Which version of ShardingSphere did you use?

5.5.0 & 5.5.1

Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

ShardingSphere-JDBC

Expected behavior

Federation SQL executes correctly during concurrent queries

Actual behavior

Federation SQL executes one by one is correctly, but three query one time Exception occurs. Exception Trace Message: More details: java.sql.SQLException: Operation not allowed after ResultSet closed at org.apache.shardingsphere.infra.exception.core.external.sql.ShardingSphereSQLException.toSQLException(ShardingSphereSQLException.java:81) ~[shardingsphere-infra-exception-core-5.5.1.jar!/:5.5.1] at org.apache.shardingsphere.infra.exception.dialect.SQLExceptionTransformEngine.toSQLException(SQLExceptionTransformEngine.java:54) ~[shardingsphere-infra-exception-dialect-core-5.5.1.jar!/:5.5.1] at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.executeQuery(ShardingSpherePreparedStatement.java:192) ~[shardingsphere-jdbc-5.5.1.jar!/:5.5.1] at xxx.ShardingRepository.executeQuery(NodeShardingRepository.java:228) ... 109 common frames omitted Caused by: java.sql.SQLException: Operation not allowed after ResultSet closed at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965) ~[mysql-connector-java-5.1.49.jar!/:5.1.49] at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898) ~[mysql-connector-java-5.1.49.jar!/:5.1.49] at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887) ~[mysql-connector-java-5.1.49.jar!/:5.1.49] at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861) ~[mysql-connector-java-5.1.49.jar!/:5.1.49] at com.mysql.jdbc.ResultSetImpl.checkClosed(ResultSetImpl.java:733) ~[mysql-connector-java-5.1.49.jar!/:5.1.49] at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:6279) ~[mysql-connector-java-5.1.49.jar!/:5.1.49] at com.zaxxer.hikari.pool.HikariProxyResultSet.next(HikariProxyResultSet.java) ~[HikariCP-4.0.3.jar!/:na] at org.apache.shardingsphere.infra.executor.sql.execute.result.query.impl.driver.jdbc.type.stream.JDBCStreamQueryResult.next(JDBCStreamQueryResult.java:54) ~[shardingsphere-infra-executor-5.5.1.jar!/:5.5.1] at org.apache.shardingsphere.sharding.merge.common.IteratorStreamMergedResult.next(IteratorStreamMergedResult.java:41) ~[shardingsphere-sharding-core-5.5.1.jar!/:5.5.1] at org.apache.shardingsphere.sqlfederation.executor.enumerator.JDBCRowEnumerator.moveNext0(JDBCRowEnumerator.java:60) ~[shardingsphere-sql-federation-executor-5.5.1.jar!/:5.5.1] at org.apache.shardingsphere.sqlfederation.executor.enumerator.JDBCRowEnumerator.moveNext(JDBCRowEnumerator.java:56) ~[shardingsphere-sql-federation-executor-5.5.1.jar!/:5.5.1] at org.apache.calcite.linq4j.EnumerableDefaults.groupBy_(EnumerableDefaults.java:979) ~[calcite-linq4j-1.35.0.jar!/:1.35.0] at org.apache.calcite.linq4j.EnumerableDefaults.groupBy(EnumerableDefaults.java:781) ~[calcite-linq4j-1.35.0.jar!/:1.35.0] at org.apache.calcite.linq4j.DefaultEnumerable.groupBy(DefaultEnumerable.java:311) ~[calcite-linq4j-1.35.0.jar!/:1.35.0] at Baz.bind(Unknown Source) ~[na:na] at org.apache.shardingsphere.sqlfederation.engine.SQLFederationEngine.executePlan(SQLFederationEngine.java:236) ~[shardingsphere-sql-federation-core-5.5.1.jar!/:5.5.1] at org.apache.shardingsphere.sqlfederation.engine.SQLFederationEngine.executeQuery(SQLFederationEngine.java:209) ~[shardingsphere-sql-federation-core-5.5.1.jar!/:5.5.1] at org.apache.shardingsphere.driver.executor.engine.DriverExecuteQueryExecutor.executeQuery(DriverExecuteQueryExecutor.java:85) ~[shardingsphere-jdbc-5.5.1.jar!/:5.5.1] at org.apache.shardingsphere.driver.executor.engine.facade.standard.StandardDriverExecutorFacade.executeQuery(StandardDriverExecutorFacade.java:94) ~[shardingsphere-jdbc-5.5.1.jar!/:5.5.1] at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.executeQuery(ShardingSpherePreparedStatement.java:183) ~[shardingsphere-jdbc-5.5.1.jar!/:5.5.1] ... 110 common frames omitted

Reason analyze (If you can)

the HikariProxyResultSet is closed by other thread?

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

Use SpringBoot + ShardingSphere-JDBC + Hikari + Mysql

the DataSource is like below: ` // federation CacheOption executionPlanCache = new CacheOption(1024, 65535L);

    SQLFederationRuleConfiguration federationRuleConfiguration = new SQLFederationRuleConfiguration(true, false, executionPlanCache);

    // config
    Collection<RuleConfiguration> ruleConfigs = Lists.newArrayList();

    ruleConfigs.add(shardingRuleConfig);

    ruleConfigs.add(federationRuleConfiguration);

    // 构建属性配置

    Properties properties = new Properties();

    properties.setProperty("sql-show", "true");

    properties.setProperty("max-connections-size-per-query", "1");

    return ShardingSphereDataSourceFactory.createDataSource(DATABASE_NAME, null, dataSourceMap, ruleConfigs, properties);

`

There has 3DB: Table A stored in DB1.A Table B stored in DB2.B DB3.B

We need sql like this: select t1.id, t2.name, t1.num from ( select id, count(*) as num from B group B.id ) t1 left join ( select id, name from A ) t2 on t1.id = t2.id

Example codes for reproduce this issue (such as a github link).

[strongduanmu]

Hi @wjjboy, thank you for your feedback. Can you provide a demo to help reproduce this exception?

[wjjboy]

Hi @wjjboy, thank you for your feedback. Can you provide a demo to help reproduce this exception?

I will attached later

[wjjboy]

Hi @wjjboy, thank you for your feedback. Can you provide a demo to help reproduce this exception?

Demo is in https://github.com/wjjboy/ShardingSphereTest.git

you should modify ShardingRepository.java for mysql username and password, and the db init sql is in resources dir.

The one by one test is in FederationSuccessTest, The concurrent test is in FederationExceptionTest.

[strongduanmu]

@wjjboy Thank you, I will test it recently.

[wjjboy]

@strongduanmu Hi, any updates on this issue?