Description:
The already prepared statement cannot be rewrited into a multi-values clause when rewriteBatchedStatements is true and the sql contains "value", such as param_value1.
How to repeat:
The code:
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
dataSource.setUrl(jdbcUrl);
dataSource.setUsername("xxx");
dataSource.setPassword("xxx");
StopWatch stopWatch = new StopWatch();
Connection connection = null;
PreparedStatement ps = null;
try {
connection = dataSource.getConnection();
connection.setAutoCommit(false);
String sql = "INSERT INTO test_batch (id, param_value1) VALUES (?, ?)";
ps = connection.prepareStatement(sql);
for (int i = 0; i < 10000; i++) {
ps.setInt(1, i);
ps.setString(2, RandomStringUtils.randomAlphanumeric(30));
ps.addBatch();
if ((i + 1) % 10000 == 0) {
ps.executeBatch();
ps.clearBatch();
}
}
connection.commit();
} catch (SQLException e) {
LOGGER.error("系统错误", e);
} finally {
try {
if (ps != null) {
ps.close();
}
if (connection != null) {
connection.close();
}
dataSource.close();
} catch (SQLException e) {
LOGGER.error("error", e);
} finally {
stopWatch.stop();
LOGGER.info("take time：{}", stopWatch.getLastTaskTimeMillis());
}
}
Suggested fix:
com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchInternal():
if (!this.batchHasPlainStatements && this.rewriteBatchedStatements.getValue()) {
if (getQueryInfo().isRewritableWithMultiValuesClause()) {
return executeBatchWithMultiValuesClause(batchTimeout);
}
if (!this.batchHasPlainStatements && this.query.getBatchedArgs() != null
&& this.query.getBatchedArgs().size() > 3 /* cost of option setting rt-wise */) {
return executePreparedBatchAsMultiStatement(batchTimeout);
}
}
return executeBatchSerially(batchTimeout);
-------------------------------------------------------------
The conditional expression "getQueryInfo().isRewritableWithMultiValuesClause()" will return false if insertSql contains the word "value1" other than keyword "value" and "value". For example, the column name is param_value1 or param2.
--------------------------------------------------------------------
com.mysql.cj.QueryInfo:
if (valuesClauseBegin == -1 && strInspector.matchesIgnoreCase(VALUE_CLAUSE) != -1) { // VALUE(S) clause found.
strInspector.incrementPosition(VALUE_CLAUSE.length()); // Advance to the end of "VALUE".
if (strInspector.matchesIgnoreCase("S") != -1) { // Check for the "S" in "VALUE(S)" and advance 1 more character if needed.
strInspector.incrementPosition();
}
withinValuesClause = true;
strInspector.indexOfNextChar(); // Position on the first values list character.
valuesClauseBegin = strInspector.getPosition();
if (rewritableAsMultiValues) {
valuesEndpointStart = valuesClauseBegin;
}
}
--------------------------------------------------------
The code has missing judgment conditions:
1. check if the char immediately preceding VALUES may be part of the table name
2. check if the char immediately following VALUES may be whitespace or open parenthesis
Description: The already prepared statement cannot be rewrited into a multi-values clause when rewriteBatchedStatements is true and the sql contains "value", such as param_value1. How to repeat: The code: DruidDataSource dataSource = new DruidDataSource(); dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver"); dataSource.setUrl(jdbcUrl); dataSource.setUsername("xxx"); dataSource.setPassword("xxx"); StopWatch stopWatch = new StopWatch(); Connection connection = null; PreparedStatement ps = null; try { connection = dataSource.getConnection(); connection.setAutoCommit(false); String sql = "INSERT INTO test_batch (id, param_value1) VALUES (?, ?)"; ps = connection.prepareStatement(sql); for (int i = 0; i < 10000; i++) { ps.setInt(1, i); ps.setString(2, RandomStringUtils.randomAlphanumeric(30)); ps.addBatch(); if ((i + 1) % 10000 == 0) { ps.executeBatch(); ps.clearBatch(); } } connection.commit(); } catch (SQLException e) { LOGGER.error("系统错误", e); } finally { try { if (ps != null) { ps.close(); } if (connection != null) { connection.close(); } dataSource.close(); } catch (SQLException e) { LOGGER.error("error", e); } finally { stopWatch.stop(); LOGGER.info("take time：{}", stopWatch.getLastTaskTimeMillis()); } } Suggested fix: com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchInternal(): if (!this.batchHasPlainStatements && this.rewriteBatchedStatements.getValue()) { if (getQueryInfo().isRewritableWithMultiValuesClause()) { return executeBatchWithMultiValuesClause(batchTimeout); } if (!this.batchHasPlainStatements && this.query.getBatchedArgs() != null && this.query.getBatchedArgs().size() > 3 /* cost of option setting rt-wise */) { return executePreparedBatchAsMultiStatement(batchTimeout); } } return executeBatchSerially(batchTimeout); ------------------------------------------------------------- The conditional expression "getQueryInfo().isRewritableWithMultiValuesClause()" will return false if insertSql contains the word "value1" other than keyword "value" and "value". For example, the column name is param_value1 or param2. -------------------------------------------------------------------- com.mysql.cj.QueryInfo: if (valuesClauseBegin == -1 && strInspector.matchesIgnoreCase(VALUE_CLAUSE) != -1) { // VALUE(S) clause found. strInspector.incrementPosition(VALUE_CLAUSE.length()); // Advance to the end of "VALUE". if (strInspector.matchesIgnoreCase("S") != -1) { // Check for the "S" in "VALUE(S)" and advance 1 more character if needed. strInspector.incrementPosition(); } withinValuesClause = true; strInspector.indexOfNextChar(); // Position on the first values list character. valuesClauseBegin = strInspector.getPosition(); if (rewritableAsMultiValues) { valuesEndpointStart = valuesClauseBegin; } } -------------------------------------------------------- The code has missing judgment conditions: 1. check if the char immediately preceding VALUES may be part of the table name 2. check if the char immediately following VALUES may be whitespace or open parenthesis