Bug #118138 A potential bugs in Mysql Connector/J

• Submitted: 8 May 11:54
• Modified: 16 Jul 20:46
• Reporter: 策 吕
• Status: Duplicate
• Category: Connector / J
• Severity: S3 (Non-critical)
• Version: 9.2.0, 9.3.0
• OS: Any
• CPU Architecture: Any

[8 May 11:54] 策 吕

Description:
When I was testing I found something that confused me.

When I set the URL with rewriteBatchedStatements=true, it produced the following results:
* 1 ** 0 ** 0 *
* 2 ** 1 ** 1 *
* 3 ** 1 ** 1 *
* 5 ** 1 ** 1 *
* 7 ** 1 ** 1 *

But when rewriteBatchedStatements=false, the following results are produced:
* 1 ** 0 ** 0 *
* 2 ** 1 ** 1 *
* 3 ** 1 ** 1 *
* 5 ** 1 ** 1 *
* 7 ** 1 ** 1 *
* 101 ** 1 ** 1 *
* 103 ** 1 ** 1 *

I don't think this makes sense, when using MySQL Connector/J, enabling rewriteBatchedStatements=true causes some of the inserted data to be lost, while when disabled (rewriteBatchedStatements=false) the inserted data is correct.This issue occurs during bulk inserts and only affects certain records.

How to repeat:
import java.sql.*;

public class reportBug {
    public static void main(String[] args) throws SQLException {
        //rewriteBatchedStatements=true
        String url1 = "jdbc:mysql://localhost:3306/test?user=root&password=1234&allowMultiQueries=false&rewriteBatchedStatements=false";
        Connection con = DriverManager.getConnection(url1);
        Statement stmt = con.createStatement();
        stmt.execute("DROP TABLE IF EXISTS t;");
        stmt.execute(
                "CREATE TABLE t(" +
                        "  id INT PRIMARY KEY," +
                        "  value1 BIT," +
                        "  value2 BIT" +
                        ");"
        );

        stmt.addBatch("INSERT INTO t VALUES(1,0,0)");
        stmt.addBatch("INSERT INTO t VALUES(5,1,1)");
        stmt.addBatch("INSERT INTO t VALUES(7,1,1)");
        stmt.addBatch("INSERT INTO t VALUES(2,1,1)");
        stmt.addBatch("INSERT INTO t VALUES(3,1,1)");
        stmt.addBatch("INSERT INTO t VALUES(1,1,1)");
        stmt.addBatch("INSERT INTO t VALUES(101,1,1)");
        stmt.addBatch("INSERT INTO t VALUES(103,1,1)");

        try{
            stmt.executeBatch();
        } catch (Exception e) {
            System.out.println(e);
        }
        executeAndPrint(con, "SELECT * FROM t");
    }

    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();
                StringBuilder sb = new StringBuilder();

                while (rs.next()) {
                    sb.setLength(0);
                    for (int i = 1; i <= count; i++) {
                        sb.append("* ").append(rs.getString(i)).append(" *");
                    }
                    System.out.println(sb);
                }
            }
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

[9 May 6:43] MySQL Verification Team

Hello 策 吕,

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

regards,
Umesh

[16 Jul 20:46] Axyoan Marcelo

Hi 策 吕,
The behavior you are seeing is not really a bug, I encourage you to read the following bug report and the conversation contained in it: Bug#110503, as it relates closely to your testcase.
In short, however, setting rewriteBatchedStatements to true, "bundles" all batched queries into a single one before sending it to the server.
Because you are trying to insert a duplicate key midway through your code, the server stops at that point and no longer continues to process the rest of the query.
If you set rewriteBatchedStatements to false, all queries execute independently of one another, so even if one fails due to a duplicate key, the rest execute without issue.
This report will be closed as duplicate of Bug#110503

Regards,
Axyoan