TeslaCN opened a new issue #16294: URL: https://github.com/apache/shardingsphere/issues/16294
## Bug Report This issue is not like https://github.com/apache/shardingsphere/issues/7549. ### Which version of ShardingSphere did you use? master - d7223362a6ec23819ca5dfc240177974ddfc86bb ### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy? ShardingSphere-Proxy MySQL ### Example codes for reproduce this issue (such as a github link). ```java package icu.wwj.hello.jdbc; import lombok.SneakyThrows; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Statement; import java.util.concurrent.CyclicBarrier; import java.util.concurrent.ExecutorService; import java.util.concurrent.Executors; public class MySQLProxyPreparedStatement { public static void main(String[] args) throws Exception { int threads = 32; CyclicBarrier barrier = new CyclicBarrier(threads); Runnable runnable = () -> { try (Connection connection = getConnection(); Statement statement = connection.createStatement()) { connection.setAutoCommit(false); barrier.await(); for (int i = 0; i < 10000; i++) { PreparedStatement preparedStatement = connection.prepareStatement("select * from bmsql_item where i_id = ?"); preparedStatement.setInt(1, i + 1); try (ResultSet resultSet = preparedStatement.executeQuery()) { while (resultSet.next()) { resultSet.getInt(1); } } preparedStatement.close(); try (ResultSet resultSet = statement.executeQuery("select 1")) { while (resultSet.next()) { resultSet.getInt(1); } } } connection.rollback(); } catch (Exception ex) { ex.printStackTrace(); } }; ExecutorService executor = Executors.newFixedThreadPool(threads); for (int i = 0; i < threads; i++) { executor.execute(runnable); } executor.shutdown(); } @SneakyThrows private static Connection getConnection() { return DriverManager.getConnection("jdbc:mysql://127.0.0.1:13306/bmsql_sharding?useSSL=false&useServerPrepStmts=true&cachePrepStmts=false", "root", "root"); // Connecting to MySQL directly is normal. // return DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/bmsql?useSSL=false&useServerPrepStmts=true&cachePrepStmts=false", "root", "root"); } } ``` ### Expected behavior No error occurs. ### Actual behavior Client: ``` java.sql.SQLException: Operation not allowed after ResultSet closed at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3978) at com.mysql.jdbc.MysqlIO.nextRowFast(MysqlIO.java:2118) at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:1992) at com.mysql.jdbc.MysqlIO.readSingleRowSet(MysqlIO.java:3403) at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:471) at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:3115) at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:2344) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2739) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2491) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2449) at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1381) at icu.wwj.hello.jdbc.MySQLProxyPreparedStatement.lambda$main$0(MySQLProxyPreparedStatement.java:32) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) at java.base/java.lang.Thread.run(Thread.java:833) ``` ShardingSphere-Proxy: ``` [ERROR] 2022-03-23 11:20:43.535 [ShardingSphere-Command-38] o.a.s.p.f.c.CommandExecutorTask - Exception occur: java.lang.NullPointerException: null [ERROR] 2022-03-23 11:20:43.948 [ShardingSphere-Command-22] o.a.s.p.f.c.CommandExecutorTask - Exception occur: java.sql.SQLException: Operation not allowed after ResultSet closed at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861) at com.mysql.jdbc.ResultSetImpl.checkClosed(ResultSetImpl.java:743) at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:6289) at com.zaxxer.hikari.pool.HikariProxyResultSet.next(HikariProxyResultSet.java) at org.apache.shardingsphere.infra.executor.sql.execute.result.query.impl.driver.jdbc.type.stream.JDBCStreamQueryResult.next(JDBCStreamQueryResult.java:51) at org.apache.shardingsphere.sharding.merge.dql.iterator.IteratorStreamMergedResult.next(IteratorStreamMergedResult.java:41) at org.apache.shardingsphere.proxy.backend.communication.DatabaseCommunicationEngine.next(DatabaseCommunicationEngine.java:195) at org.apache.shardingsphere.proxy.backend.text.data.impl.UnicastDatabaseBackendHandler.next(UnicastDatabaseBackendHandler.java:98) at org.apache.shardingsphere.proxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutor.next(MySQLComQueryPacketExecutor.java:96) at org.apache.shardingsphere.proxy.frontend.mysql.command.MySQLCommandExecuteEngine.writeQueryData(MySQLCommandExecuteEngine.java:84) at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:102) at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:69) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) at java.base/java.lang.Thread.run(Thread.java:833) [ERROR] 2022-03-23 11:20:44.157 [ShardingSphere-Command-29] o.a.s.p.f.c.CommandExecutorTask - Exception occur: java.sql.SQLException: Operation not allowed after ResultSet closed at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861) at com.mysql.jdbc.ResultSetImpl.checkClosed(ResultSetImpl.java:743) at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:6289) at com.zaxxer.hikari.pool.HikariProxyResultSet.next(HikariProxyResultSet.java) at org.apache.shardingsphere.infra.executor.sql.execute.result.query.impl.driver.jdbc.type.stream.JDBCStreamQueryResult.next(JDBCStreamQueryResult.java:51) at org.apache.shardingsphere.sharding.merge.dql.iterator.IteratorStreamMergedResult.next(IteratorStreamMergedResult.java:41) at org.apache.shardingsphere.proxy.backend.communication.DatabaseCommunicationEngine.next(DatabaseCommunicationEngine.java:195) at org.apache.shardingsphere.proxy.backend.text.data.impl.UnicastDatabaseBackendHandler.next(UnicastDatabaseBackendHandler.java:98) at org.apache.shardingsphere.proxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutor.next(MySQLComQueryPacketExecutor.java:96) at org.apache.shardingsphere.proxy.frontend.mysql.command.MySQLCommandExecuteEngine.writeQueryData(MySQLCommandExecuteEngine.java:84) at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:102) at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:69) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) at java.base/java.lang.Thread.run(Thread.java:833) ``` ### Reason analyze (If you can) The command `COM_STMT_CLOSE` requires no response. Refer to https://dev.mysql.com/doc/internals/en/com-stmt-close.html So the next command sent by the client is closely followed the `COM_STMT_CLOSE`. And the command handler in Proxy is a shared thread pool, which means Proxy will handle 2 commands concurrently and the error may occur.  -- 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]
