You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2022/03/23 03:34:05 UTC
[GitHub] [shardingsphere] TeslaCN opened a new issue #16294: Operation not allowed after ResultSet closed occasionally happens in ShardingSphere-Proxy MySQL
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TeslaCN edited a comment on issue #16294: Operation not allowed after ResultSet closed occasionally happens in ShardingSphere-Proxy MySQL
Posted by GitBox <gi...@apache.org>.
TeslaCN edited a comment on issue #16294:
URL: https://github.com/apache/shardingsphere/issues/16294#issuecomment-1080198856
Closing 100 prepared statements once is easier to reproduce this issue.
```
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 < 100; i++) {
PreparedStatement[] preparedStatements = new PreparedStatement[100];
for (int i1 = 0; i1 < preparedStatements.length; i1++) {
preparedStatements[i1] = connection.prepareStatement("select * from bmsql_item where i_id = ?");
preparedStatements[i1].setInt(1, i + 1);
try (ResultSet resultSet = preparedStatements[i1].executeQuery()) {
while (resultSet.next()) {
resultSet.getInt(1);
}
}
}
for (PreparedStatement each : preparedStatements) {
each.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");
// return DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/bmsql?useSSL=false&useServerPrepStmts=true&cachePrepStmts=false", "root", "root");
}
}
```
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] terrymanu closed issue #16294: Operation not allowed after ResultSet closed occasionally happens in ShardingSphere-Proxy MySQL
Posted by GitBox <gi...@apache.org>.
terrymanu closed issue #16294:
URL: https://github.com/apache/shardingsphere/issues/16294
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TeslaCN edited a comment on issue #16294: Operation not allowed after ResultSet closed occasionally happens in ShardingSphere-Proxy MySQL
Posted by GitBox <gi...@apache.org>.
TeslaCN edited a comment on issue #16294:
URL: https://github.com/apache/shardingsphere/issues/16294#issuecomment-1080198856
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TeslaCN edited a comment on issue #16294: Operation not allowed after ResultSet closed occasionally happens in ShardingSphere-Proxy MySQL
Posted by GitBox <gi...@apache.org>.
TeslaCN edited a comment on issue #16294:
URL: https://github.com/apache/shardingsphere/issues/16294#issuecomment-1078926151
> My solution:
>
> 1. Create thread for per frontend connection in MySQL Proxy just like how PostgreSQL Proxy does.
> 2. Remove `ConnectionStatus`. After 1 done, tasks of a client will be executed serially, so the lock in `ConnectionStatus` is no longer required.
This solution degraded performance.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TeslaCN commented on issue #16294: Operation not allowed after ResultSet closed occasionally happens in ShardingSphere-Proxy MySQL
Posted by GitBox <gi...@apache.org>.
TeslaCN commented on issue #16294:
URL: https://github.com/apache/shardingsphere/issues/16294#issuecomment-1078926151
> My solution:
>
> 1. Create thread for per frontend connection in MySQL Proxy just like how PostgreSQL Proxy does.
> 2. Remove `ConnectionStatus`. After 1 done, tasks of a client will be executed serially, so the lock in `ConnectionStatus` is no longer required.
This solution will degrade performance.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TeslaCN commented on issue #16294: Operation not allowed after ResultSet closed occasionally happens in ShardingSphere-Proxy MySQL
Posted by GitBox <gi...@apache.org>.
TeslaCN commented on issue #16294:
URL: https://github.com/apache/shardingsphere/issues/16294#issuecomment-1080198856
Closing 100 prepared statements once is easier to reproduce this issue.
```
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 < 100; i++) {
PreparedStatement[] preparedStatements = new PreparedStatement[100];
for (int i1 = 0; i1 < preparedStatements.length; i1++) {
preparedStatements[i1] = connection.prepareStatement("select * from bmsql_item where i_id = ?");
preparedStatements[i1].setInt(1, i + 1);
try (ResultSet resultSet = preparedStatements[i1].executeQuery()) {
while (resultSet.next()) {
resultSet.getInt(1);
}
}
}
for (PreparedStatement each : preparedStatements) {
each.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");
// return DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/bmsql?useSSL=false&useServerPrepStmts=true&cachePrepStmts=false", "root", "root");
}
}
```
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TeslaCN commented on issue #16294: Operation not allowed after ResultSet closed occasionally happens in ShardingSphere-Proxy MySQL
Posted by GitBox <gi...@apache.org>.
TeslaCN commented on issue #16294:
URL: https://github.com/apache/shardingsphere/issues/16294#issuecomment-1076465696
My solution:
1. Create thread for per frontend connection in MySQL Proxy just like how PostgreSQL Proxy does.
1. Remove `ConnectionStatus`. After 1 done, tasks of a client will be executed serially, so the lock in `ConnectionStatus` is no longer required.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TeslaCN commented on issue #16294: Operation not allowed after ResultSet closed occasionally happens in ShardingSphere-Proxy MySQL
Posted by GitBox <gi...@apache.org>.
TeslaCN commented on issue #16294:
URL: https://github.com/apache/shardingsphere/issues/16294#issuecomment-1076368646
This issue is easier to reproduce in transaction. If in transaction, the task will not `waitUntilConnectionRelease`, which increases the probability of running 2 tasks from the same client concurrently.
https://github.com/apache/shardingsphere/blob/3958c811dd6e1c4729a86d741ef095d43bf1cdd4/shardingsphere-proxy/shardingsphere-proxy-backend/src/main/java/org/apache/shardingsphere/proxy/backend/communication/jdbc/connection/JDBCBackendConnection.java#L179-L182
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org