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();
}
}
}