Bug #118201 A potential bug in Mysql Connector/J

• Submitted: 15 May 2:37
• Modified: 7 Jul 22:05
• Reporter: 策 吕
• Status: Closed
• Category: Connector / J
• Severity: S3 (Non-critical)
• Version: 9.0.0
• OS: Any
• CPU Architecture: Any

[15 May 2:37] 策 吕

Description:
The following inconsistent result occurs when using MYSQL Connector, but I don't have the following problem when using OceanBase Connector.

1. When allowMultiQueries is set to true, the result is printed:
----------------
 * 267492252 * 
----------------

2. When allowMultiQueries is set to false, the result is printed:
----------------
* 622 * 
* 267492252 * 
----------------

How to repeat:
import java.sql.*;

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

        Statement stmt = con.createStatement();
        
        // First: create table
        stmt.execute("DROP TABLE IF EXIST t0");
        stmt.execute("CREATE TABLE t0(c0 INT PRIMARY KEY NOT NULL)");
        
        // Second: randomly insert an integer of type INT
        stmt.execute("INSERT INTO t0 VALUES (267492252)");

        // Third: Inserting random integers using batch operations
        Statement bstmt = con.createStatement();
        bstmt.addBatch("INSERT INTO t0 VALUES (267492252)");
        bstmt.addBatch("INSERT INTO t0 VALUES (622)");

        // ..., Here you can add some more addBatch(String sql) methods.

        try {
            bstmt.executeBatch();
        } catch (Exception e) {
           
        }
        
        // Fourth: execute "SELECT * FROM t0" and print the result.
        executeAndPrint(con, "SELECT * FROM t0");
    }
    public static 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();
                while (rs.next()) {
                    for (int i = 1; i <= count; i++) {
                        System.out.print("* " + rs.getString(i) + " * ");
                    }
                    System.out.println();
                }
            } else {
                System.out.println("Update count: " + statement.getUpdateCount());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

[19 May 5:34] 策 吕

Sorry, there was a spelling error in the code, didn't notice it on commit:
stmt.execute("DROP TABLE IF EXIST t0");
Fix it as
stmt.execute("DROP TABLE IF EXISTS t0");

[20 May 10:29] Filipe Silva

Thank you for your interest in MySQL Connector/J and for taking the time to file this bug report.

Regardless what the documentation says about the connection property `allowMultiQueries`, it does affect batched statements. This is the behavior since the early days of the driver. What remains to see if whether this should be fixed in the documentation or if it makes sense to change the behavior of this property.

Note that two other connection properties have the power to affect how batched statements are executed: `rewriteBatchedStatements` and `continueBatchOnError`.

For these reasons, I'm setting this report as "verified".

[7 Jul 22:05] Daniel So

Posted by developer:
 
Added an entry to the Connector/J 9.4.0 changelog: 

"Enabling allowMultiQueries without also enabling rewriteBatchedStatements caused statements to be rewritten, which was not the expected behavior. With this fix, allowMultiQueries only allows users to execute multiple queries in a single Statement, whereas batching, including statement rewriting, is governed exclusively by rewriteBatchedStatements."

[16 Jul 19:52] Axyoan Marcelo

Duplicated by Bug#118167