Description:
The issue arises when executing batch statements with the rewriteBatchedStatements=true parameter set in the database URL. Specifically, the behavior of the TRUNCATE statement within a batch of SQL commands is inconsistent.
In the first scenario, where the second batch is {"INSERT INTO t0 VALUES (1217233624)", "TRUNCATE t0", "INSERT INTO t0 VALUES (1722235442)"}, the TRUNCATE statement executes successfully. In contrast, in the second scenario with a longer batch: {"INSERT INTO t0 VALUES (1217233624)", "TRUNCATE t0", "INSERT INTO t0 VALUES (-751078476)", "INSERT INTO t0 VALUES (-671105839)", "INSERT INTO t0 VALUES (1722235442)"}, the TRUNCATE statement fails to execute.
According to the developers, "When one of the queries fails, subsequent queries are not executed." The expected behavior is for the TRUNCATE statement to consistently fail regardless of the number of statements in the batch.
How to repeat:
@Test
public void test() throws SQLException {
String url = "jdbc:mysql://localhost:3306?user=user&password=password&rewriteBatchedStatements=true";
Connection con = DriverManager.getConnection(url);
execute(con, "DROP DATABASE IF EXISTS test");
execute(con, "CREATE DATABASE test");
execute(con, "USE test");
execute(con, "CREATE TABLE t0(c0 INT UNIQUE)");
batchExecute(con, new String[]{"INSERT INTO t0 VALUES (538116271)", "INSERT INTO t0 VALUES (1217233624)", "INSERT INTO t0 VALUES (-751078476)"});
batchExecute(con, new String[]{"INSERT INTO t0 VALUES (1217233624)", "TRUNCATE t0", "INSERT INTO t0 VALUES (-751078476)", "INSERT INTO t0 VALUES (-671105839)", "INSERT INTO t0 VALUES (1722235442)"});
executeAndPrint(con, "SELECT * FROM t0");
}
public void execute(Connection con, String sql) {
try {
Statement statement = con.createStatement();
statement.execute(sql);
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void executeAndPrint(Connection con, String sql) {
try {
Statement statement = con.createStatement();
if (statement.execute(sql)) {
ResultSet rs = statement.getResultSet();
ResultSetMetaData rsMetaData = rs.getMetaData();
int count = rsMetaData.getColumnCount();
StringBuffer sb = new StringBuffer();
while (rs.next()) {
sb.setLength(0);
for (int i = 1; i <= count; i++) {
sb.append("* " + rs.getString(i) + " *");
}
System.out.println(sb);
}
}
else {
System.out.println("count: " + statement.getUpdateCount());
}
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
Description: The issue arises when executing batch statements with the rewriteBatchedStatements=true parameter set in the database URL. Specifically, the behavior of the TRUNCATE statement within a batch of SQL commands is inconsistent. In the first scenario, where the second batch is {"INSERT INTO t0 VALUES (1217233624)", "TRUNCATE t0", "INSERT INTO t0 VALUES (1722235442)"}, the TRUNCATE statement executes successfully. In contrast, in the second scenario with a longer batch: {"INSERT INTO t0 VALUES (1217233624)", "TRUNCATE t0", "INSERT INTO t0 VALUES (-751078476)", "INSERT INTO t0 VALUES (-671105839)", "INSERT INTO t0 VALUES (1722235442)"}, the TRUNCATE statement fails to execute. According to the developers, "When one of the queries fails, subsequent queries are not executed." The expected behavior is for the TRUNCATE statement to consistently fail regardless of the number of statements in the batch. How to repeat: @Test public void test() throws SQLException { String url = "jdbc:mysql://localhost:3306?user=user&password=password&rewriteBatchedStatements=true"; Connection con = DriverManager.getConnection(url); execute(con, "DROP DATABASE IF EXISTS test"); execute(con, "CREATE DATABASE test"); execute(con, "USE test"); execute(con, "CREATE TABLE t0(c0 INT UNIQUE)"); batchExecute(con, new String[]{"INSERT INTO t0 VALUES (538116271)", "INSERT INTO t0 VALUES (1217233624)", "INSERT INTO t0 VALUES (-751078476)"}); batchExecute(con, new String[]{"INSERT INTO t0 VALUES (1217233624)", "TRUNCATE t0", "INSERT INTO t0 VALUES (-751078476)", "INSERT INTO t0 VALUES (-671105839)", "INSERT INTO t0 VALUES (1722235442)"}); executeAndPrint(con, "SELECT * FROM t0"); } public void execute(Connection con, String sql) { try { Statement statement = con.createStatement(); statement.execute(sql); statement.close(); } catch (SQLException e) { e.printStackTrace(); } } public void executeAndPrint(Connection con, String sql) { try { Statement statement = con.createStatement(); if (statement.execute(sql)) { ResultSet rs = statement.getResultSet(); ResultSetMetaData rsMetaData = rs.getMetaData(); int count = rsMetaData.getColumnCount(); StringBuffer sb = new StringBuffer(); while (rs.next()) { sb.setLength(0); for (int i = 1; i <= count; i++) { sb.append("* " + rs.getString(i) + " *"); } System.out.println(sb); } } else { System.out.println("count: " + statement.getUpdateCount()); } statement.close(); } catch (SQLException e) { e.printStackTrace(); } }