Bug #118055 A potential bugs in Mysql Connector/J

• Submitted: 25 Apr 9:08
• Modified: 28 Apr 17:25
• Reporter: 策 吕
• Status: Not a Bug
• Category: Connector / J
• Severity: S3 (Non-critical)
• Version: mysql-connector-j-9.2.0 && mysql Ver 8.0
• OS: Any
• CPU Architecture: Any

[25 Apr 9:08] 策 吕

Description:
Personally, I think the error reported here is wrong, the description of the exception thrown by MYSQL when executing the second stmt.executeBatch() is personally wrong.

The reason is as follows:

So I have added "DROP TABLE t0" to the batch i.e. addBatch("DROP TABLE t0") before the second stmt.executeBatch() execution.But MYSQL throws an exception: "Exception in thread "main" java.sql.BatchUpdateException: You have an error in your SQL syntax; check the manual that corresponds toyour MySQL server version for the right syntax to use near ';INSERT INTO t0 VALUES (2);INSERT INTO t0 VALUES (3);INSERT INTO t0 VALUES (4)' at line 1."

However, I personally think that a reasonable exception that should be thrown during the second execution of stmt.executeBatch() is "testdb.t0 does not exist" is more reasonable, because t0 has been deleted, so "stmt.addBatch("INSERT INTO t0 VALUES (2)");stmt.addBatch("INSERT INTO t0 VALUES (3)"); stmt.addBatch("INSERT INTO t0 VALUES (4)");" should be invalid because Table t0 cannot be found.

How to repeat:
import java.sql.*;

public class MySQLBatchTest {
    public static void main(String[] args) throws SQLException {
        String url = "jdbc:mysql://localhost:3306/testdb0?user=root&password=1234&rewriteBatchedStatements=true";
        Connection con = DriverManager.getConnection(url);

        try (Statement stmt = con.createStatement()) {
            stmt.execute("DROP TABLE IF EXISTS t0");
        }
        // CREATE TABLE
        try (Statement stmt = con.createStatement()) {
            stmt.execute("CREATE TABLE t0(c0 INT /*PRIMARY KEY*/)");
        }

        // First batch insert
        try (Statement stmt = con.createStatement()) {
            stmt.addBatch("INSERT INTO t0 VALUES (0)");
            stmt.addBatch("INSERT INTO t0 VALUES (1)");
            stmt.addBatch("INSERT INTO t0 VALUES (2)");
            stmt.executeBatch();
        }

        // Second batch: contains duplicate, truncate, and more inserts
        try (Statement stmt = con.createStatement()) {
            stmt.addBatch("INSERT INTO t0 VALUES (1)"); // duplicate
            stmt.addBatch("DROP TABLE t0;");
            //stmt.addBatch("TRUNCATE t0");
            stmt.addBatch("INSERT INTO t0 VALUES (2)");
            stmt.addBatch("INSERT INTO t0 VALUES (3)");
            stmt.addBatch("INSERT INTO t0 VALUES (4)");
            stmt.executeBatch();
        }

        // Query and print results
        try (Statement stmt = con.createStatement()) {
            if (stmt.execute("SELECT * FROM t0")) {
                ResultSet rs = stmt.getResultSet();
                ResultSetMetaData meta = rs.getMetaData();
                int count = meta.getColumnCount();

                while (rs.next()) {
                    StringBuilder sb = new StringBuilder();
                    for (int i = 1; i <= count; i++) {
                        sb.append("* ").append(rs.getString(i)).append(" * ");
                    }
                    System.out.println(sb.toString());
                }
                rs.close();
            } else {
                System.out.println("count: " + stmt.getUpdateCount());
            }
        }
        con.close();
    }
}

[25 Apr 9:44] MySQL Verification Team

Hello 策 吕,

Thank you for the report and feedback.
Verified as described.

regards,
Umesh

[28 Apr 17:25] Axyoan Marcelo

Posted by developer:
 
Hello 策 吕,

Thank you for your report. The error message you are seeing is due to the following line in your code:
stmt.addBatch("DROP TABLE t0;");

Notice the semicolon you added at the end. Because you are using rewriteBatchedStatements, your query is being rewritten into a single one before being sent to the server. This means the actual query being sent is:
INSERT INTO t0 VALUES (1);DROP TABLE t0;;INSERT INTO t0 VALUES (2);INSERT INTO t0 VALUES (3);INSERT INTO t0 VALUES (4);

This query will fail, but not because it's trying to insert into a non existent table. Rather, it's due to the extra semicolon in the middle of the statement.

If you remove this semicolon, then the error message is exactly what you'd expect:
"java.sql.BatchUpdateException: Table 'testcj.t0' doesn't exist"

Let me know if this clears up the confusion. This report will be closed as not a bug.

Regards,
Axyoan