Bug #107577 rewriteBatchedStatements not work when table column name contains 'value' string
Submitted: 16 Jun 2022 8:38 Modified: 30 Nov 2023 12:03
Reporter: Charles Yang Email Updates:
Status: Closed Impact on me:
Category:Connector / J Severity:S2 (Serious)
Version:8.0.29 OS:Any
Assigned to: CPU Architecture:Any

[16 Jun 2022 8:38] Charles Yang
setting rewriteBatchedStatements to true, and create a table with column name contains 'value', such like 'p_value' and using insert sql `insert into tableName(p_value) values (?)` to insert data into table, rewriteBatchedStatements not working correct.

How to repeat:
1. create a table with any column name contains 'value', such like 'p_value',
2. setting rewriteBatchedStatements to true
3. use preparedstatement to insert data, such like `insert into tableName(p_value) values (?)`

Suggested fix:
in com.mysql.cj.QueryInfo, strInspector.matchesIgnoreCase(VALUE_CLAUSE) should skip column names
[16 Jun 2022 20:40] MySQL Verification Team

Are you getting the same result with server side prepared statement set to true and to false ?

[20 Jun 2022 1:40] Charles Yang
thanks for your replay.
we tested these cases:
1. column name not contains 'value',
2. on column name contains 'value'
and check the server side log, the final sql is:
for case 1: insert into table_name ('p_val') values ('1'),('2'),('3')
for case 2: insert into table_name ('p_value') values ('1');insert into table_name ('p_value') values ('2');insert into table_name ('p_value') values ('3');
so i don't think the the bug is cased by the server side parameter setting.

according by the comments of https://github.com/mysql/mysql-connector-j/blob/8.0.29/src/main/core-api/java/com/mysql/cj..., 

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.
    withinValuesClause = true;
    strInspector.indexOfNextChar(); // Position on the first values list character.
    valuesClauseBegin = strInspector.getPosition();

    if (rewritableAsMultiValues) {
        valuesEndpointStart = valuesClauseBegin;


if a sql like `insert into table_name(id, p_value) values(?,?)`, the if block will be executed, i think it is not correct, am i wrong?
[20 Jun 2022 4:19] MySQL Verification Team

That part is supposed to be ok as com.mysql.cj.util.* should know to search outside `` and outside /* */ and before -- and only between whitespaces etc etc .. but looks someone forgot something as I can reproduce the issue so it is def. there :( 

Thank you for your report
[13 Feb 2023 17:06] Filipe Silva
Related to Bug#109864.
[30 Nov 2023 12:03] Filipe Silva
Added the following entry to the Connector/J 8.0.33 changelog: 
"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 
are 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."
[30 Nov 2023 12:11] MySQL Verification Team
Bug #109818 marked as duplicate of this one
[30 Nov 2023 12:30] MySQL Verification Team
Bug #108978 marked as duplicate of this one