Bug #108978 The already prepared statement cannot be rewrited into a multi-values clause
Submitted: 3 Nov 2022 8:08 Modified: 30 Nov 2023 12:29
Reporter: David Bing Email Updates:
Status: Duplicate Impact on me:
None 
Category:Connector / J Severity:S5 (Performance)
Version:8.0.31 OS:Any
Assigned to: CPU Architecture:Any
Tags: REWRITEBATCHEDSTATEMENTS

[3 Nov 2022 8:08] David Bing
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
[30 Nov 2023 12:29] MySQL Verification Team
Hello David Bing,

Thank you for the report and feedback.
IMHO this is duplicate of Bug #107577 and per change log this is fixed in 8.0.33. Thank you.

Quoting from 8.0.33 change log:

Rewriting of batched statements failed when a closing parenthesis was found within a VALUES clause. It was because QueryInfo failed to parse the prepared statement properly in that case. With this fix, the parser of VALUES clauses is improved, so that Connector/J is now able to recognize rewritability of statements that contain function calls or multiple VALUES lists, and it also handles well the cases when the string "value" is part of a table name, a column name, or a keyword. (Bug #109377, Bug #34900156, Bug #107577, Bug #34325361)

Please refer https://dev.mysql.com/doc/relnotes/connector-j/8.0/en/news-8-0-33.html

regards,
Umesh