MySQL Bugs: #110503: rewriteBatchedStatements=true leads to unexpected result

Bug #110503	rewriteBatchedStatements=true leads to unexpected result
Submitted:	26 Mar 2023 8:25	Modified:	17 Nov 2023 19:23
Reporter:	Wenqian Deng	Email Updates:
Status:	Verified	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	8.0.32	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
During my testing, I found that setting rewriteBatchedStatements=true would affect the execution result of executeBatch. 
Please take a look at my test case, which returns three records: `-751078476`, `538116271`, `1217233624`, when rewriteBatchedStatements=true is set. This seems to indicate that both the TRUNCATE and subsequent INSERT operations have failed. 
However, when I connect to the MySQL using MariaDB Connector J or set rewriteBatchedStatements=false, this error does not occur. Instead, it returns `-751078476`, `-671105839`, `1722235442`.

How to repeat:
@Test
public void test() throws SQLException {
    String url = "jdbc:mysql://localhost:3306?user=root&password=123456&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();
    }
}

Hello Wenqian Deng,

Thank you for the report and test case.

regards,
Umesh

Posted by developer:
 
The observed result is expected.

This is so because when rewriteBatchedStatements=true, Connector/J tries to avoid submitting queries individually to the server. So, when one of the query fails, subsequent queries are not executed. On the other hand, with rewriteBatchedStatements=false, all queries are executed regardless of failures.

When setting rewriteBatchedStatements=true one must be aware of such differences and make their choice consciously.

I think that if this behavior is intended as a feature, it should be more clearly documented. Users may not expect that setting "rewriteBatchedStatements" would result in inconsistent behavior of the connector.

I have another confused point according to your comment "So, when one of the query fails, subsequent queries are not executed. On the other hand, with rewriteBatchedStatements=false, all queries are executed regardless of failures.".
In the test case, with rewriteBatchedStatements=true, 
when the second batch is: executeBatch(con, new String[]{"INSERT INTO t0 VALUES (1217233624)", "TRUNCATE t0", "INSERT INTO t0 VALUES (1722235442)"}); the result shows "TRUNCATE t0" executed successfully, 
but when the second batch is executeBatch(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)"}); the result shows "TRUNCATE t0" executed failed. 
It means with rewriteBatchedStatements=true, sometimes subsequent queries are not executed but sometimes they are executed. I think it is a bug and please open it.

Well, yes, it could be documented better.

Regarding your last observation, this is so because statement re-writing only kicks in if the batch has at least 5 queries.

Note that this feature was designed having in mind that batched statements are typically INSERTs or UPDATEs (as the documentation for Statement#addBatch() states) and the goal is to improve performance when executing a large number of them. It is kind of implicit that in theses cases the input comes from a "clean" source and no data consistency exceptions are expected while executing those queries. If you aren't sure the queries you need to execute won't throw a data consistency exception, then you shouldn't use "rewriteBatchedStatements=true".

Normal applications have little to no gain in setting "rewriteBatchedStatements=true", actually, quite the opposite. You can see this as a feature to use when importing CSV files to a database or similar.

I'm setting the bug as "Verified" but just from the perspective of improving the documentation of the connection property "rewriteBatchedStatements".

Duplicated by Bug#118138