版本2.1.2
jdbc url 如下
jdbc:mysql://****:9030/test?useServerPrepStmts=true&useLocalSessionState=true&rewriteBatchedStatements=true&cachePrepStmts=true&prepStmtCacheSqlLimit=9999999&prepStmtCacheSize=9999999
java 代码如下
public static void main( String[] args ) throws SQLException, IOException {
String jdbcUrl = args[0];
String username = args[1];
String password = args[2];
try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password)) {
conn.createStatement().execute("set group_commit = async_mode;");
String query = "insert into test1" + " values(?, ?, ?,?)";
try (PreparedStatement stmt = conn.prepareStatement(query)) {
for (int j = 0; j < 3; j++) {
long start = System.currentTimeMillis();
// 10 rows per insert
for (int i = 0; i < 17000; i++) {
stmt.setInt(1, i);
stmt.setString(2, i +"");
stmt.setString(3, "name" + i);
stmt.setTimestamp(4, Timestamp.valueOf(LocalDateTime.now()));
stmt.addBatch();
}
int[] result = stmt.executeBatch();
long end = System.currentTimeMillis();
System.out.println("insert " + result.length + " rows" + " cost time: " + (end - start) + "ms");
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
在测试中每个批次超过 17000后会出发报错
java.lang.ArrayIndexOutOfBoundsException: 2464
at com.mysql.cj.NativeQueryBindings.getBinding(NativeQueryBindings.java:191)
at com.mysql.cj.NativeQueryBindings.setFromBindValue(NativeQueryBindings.java:198)
at com.mysql.cj.jdbc.ClientPreparedStatement.setOneBatchedParameterSet(ClientPreparedStatement.java:591)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchWithMultiValuesClause(ClientPreparedStatement.java:675)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchInternal(ClientPreparedStatement.java:409)
at com.mysql.cj.jdbc.StatementImpl.executeBatch(StatementImpl.java:795)
at com.dlink.App.main(App.java:40)
排查了一下好像是数据库侧的问题,jdbc驱动中ServerPreparedQuery构建的时候向服务端发送了一个请求,请求中包含了批量插入改写后的sql,服务端返回了参数个数,当批次数比较小时返回的参数个数时正常的,比如sql
insert into test1 values(?,?,?,?)
这个sql中包含4个参数,当发送每批发送15000时,服务端返回的参数个数时60000,这时候是合适的,当每批次发送17000时,预期返回的值应该时68000,实际却返回了2464,客户端根据服务端返回的数据构建了长度为2464的queryBindings,因为实际写入参数个数时68000,当超过2464时就会报错。驱动中相关代码如下: