Bug #118138 A potential bugs in Mysql Connector/J
Submitted: 8 May 11:54 Modified: 9 May 6:43
Reporter: 策 吕 Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:9.2.0, 9.3.0 OS:Any
Assigned to: 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