wengsy150943
commented
9 months ago This bug still occurs in v7.5.0. And we find it will cause an unexpected connection close.
If we try to catch the expection in the case above and them rollback this transaction. The last rollback will fail because connection is closed.
We add a try-catch block in executeParamQueryAsync in the raw case. Here is the new case.
package org.example;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.concurrent.CompletableFuture;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
public class Main {
private static final String TIDB_HOST = "49.52.27.20";
private static final ExecutorService executor = Executors.newSingleThreadExecutor();
private static final int TIDB_PORT = 4000;
private static final long TIMEOUT = 1010;
public static void main(String[] args) throws ClassNotFoundException, SQLException, InterruptedException {
Connection connection1 = null;
Connection connection2 = null;
Class.forName("com.mysql.cj.jdbc.Driver");
try {
// JDBC connection parameters
String jdbcUrl = "jdbc:mysql://" + TIDB_HOST + ":" + TIDB_PORT + "/test";
String username = "root";
String password = "";
// Establish the connections
connection1 = DriverManager.getConnection(jdbcUrl, username, password);
connection1.setAutoCommit(false);
connection2 = DriverManager.getConnection(jdbcUrl, username, password);
connection2.setAutoCommit(false);
// Init database
executeQuery(connection1, "drop table if exists t1");
executeQuery(connection1, "create table t1 (a int, b int,primary key(a))");
executeQuery(connection1, "insert into t1 values (3, 2),(2, 3)");
executeQuery(connection1, "begin");
executeQuery(connection2, "begin");
// Parameterized delete operations
System.out.println("c1 delete: " + executeQuery(connection1, "delete from t1 where a = 3"));
System.out.println("c2 delete: " + executeQuery(connection2, "delete from t1 where a = 2"));
// conn1 delete the record async
CompletableFuture<String> res = null;
System.out.println("c1 delete: " + (res=executeParamQueryAsync(connection1, "delete from t1 where a = 2")));
// keep conn2 commit at the same time when conn1's delete is timeout
Thread.sleep(TIMEOUT);
System.out.println("c2 commit: " + executeQuery(connection2, "commit"));
res.get();
} catch (SQLException | InterruptedException | ExecutionException e) {
e.printStackTrace();
} finally {
Thread.sleep(2000);
// Close resources in the reverse order of their creation
connection1.close();
connection2.close();
executor.shutdown();
}
}
private static String executeQuery(Connection connection, String query) throws SQLException {
Statement statement = connection.createStatement();
statement.execute(query);
return "Success" ;
}
private static CompletableFuture<String> executeParamQueryAsync(Connection connection, String query) {
return CompletableFuture.supplyAsync(() -> {
try (PreparedStatement preparedStatement = connection.prepareStatement(query)) {
preparedStatement.setQueryTimeout(1);
return preparedStatement.executeUpdate() == 0 ? "Failure" : "Success";
} catch (SQLException e) {
e.printStackTrace();
try {
connection.commit();
} catch (Exception e1) { // HERE we catch the unexpected exception thrown by tidb, and rollback this transaction
e1.printStackTrace();
try {
connection.rollback();
System.out.println("successfully rollback");
} catch (SQLException ex) {
throw new RuntimeException(ex);
}
}
System.out.println("c1 delete end");
return "Failure";
}
}, executor);
}
}The last rollback fails and throws a exception because connection is closed.
java.util.concurrent.ExecutionException: java.lang.RuntimeException: java.sql.SQLNonTransientConnectionException: No operations allowed after connection closed.
at java.base/java.util.concurrent.CompletableFuture.reportGet(CompletableFuture.java:396)
at java.base/java.util.concurrent.CompletableFuture.get(CompletableFuture.java:2073)
at org.example.Main.main(Main.java:59)
Caused by: java.lang.RuntimeException: java.sql.SQLNonTransientConnectionException: No operations allowed after connection closed.
at org.example.Main.lambda$executeParamQueryAsync$0(Main.java:94)
at java.base/java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1768)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642)
at java.base/java.lang.Thread.run(Thread.java:1583)
Caused by: java.sql.SQLNonTransientConnectionException: No operations allowed after connection closed.
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:110)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:73)
at com.mysql.cj.jdbc.ConnectionImpl.rollback(ConnectionImpl.java:1846)
at org.example.Main.lambda$executeParamQueryAsync$0(Main.java:91)
... 4 moreSince the case causes unexpected connection close, it may be more dangerous than a compatibility bug.
The TiDB version is:
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version() |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v7.5.0
Edition: Community
Git Commit Hash: 069631e2ecfedc000ffb92c67207bea81380f020
Git Branch: heads/refs/tags/v7.5.0
UTC Build Time: 2023-11-24 08:50:14
GoVersion: go1.21.3
Race Enabled: false
Check Table Before Drop: false
Store: tikv |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
dveeden
Bug Report
1. Minimal reproduce step (Required)
Running the case below by JAVA.
Schema:
Then run the case by two connections parallel.
We also provide our code which triggers this case. Note that the static value
TIMEOUTmaybe need to be changed based on the testing environment to keep conn2 commits at the same time when conn1's delete is timeout.And the jdbc version is:
2. What did you expect to see? (Required)
There is only one exception in the whole execution, i.e., the timeout exception of conn1's delete.
That's what MySQL8.0 does. Staggering the time of conn1's delete and conn2's commit also leads to this result, i.e., setting TIMEOUT to 2000.
Then the output is shown below.
3. What did you see instead (Required)
The commit/rollback after conn1's delete also fails and throws other exceptions(Communications link failure), as shown below.
This phenomenon is confusing because it's hard to check whether the transaction of conn1is finished.(In fact it is finished.)
And I think it is unexpected because:
4. What is your TiDB version? (Required)