| Bug #109377 | rewriteBatchedStatements doesn't work when parenthesis are found in values | ||
|---|---|---|---|
| Submitted: | 14 Dec 2022 13:36 | Modified: | 20 Mar 16:29 |
| Reporter: | Carl Nyströmer | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / J | Severity: | S3 (Non-critical) |
| Version: | 8.0.30, 8.0.31 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[15 Dec 2022 13:22]
MySQL Verification Team
Hello Carl Nyströmer, Thank you for the report and feedback. regards, Umesh
[20 Mar 16:29]
Daniel So
Posted by developer: 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."
Description: If a right parenthesis (')') is found within the value clause of any INSERT statement in a prepared statement batch, followed by a bindable parameter ('?'), the batch won't be rewritten. This is due to com.mysql.cj.QueryInfo not parsing the prepared statement sql string correctly when asserting if a prepared statement is "rewritableAsMultiValues". How to repeat: Execute an arbitrary insert statement with a SQL-function in the values part of the statement, followed by a bindable parameter. PreparedStatement ps = c.prepareStatement(""" INSERT INTO users ( user_id, random_string, ) VALUES( UUID_TO_BIN(?), ? ) """) ps.setString(1, UUID.randomUUID().toString()); ps.setString(2, "foo"); ps.addBatch(); ps.setString(1, UUID.randomUUID().toString()); ps.setString(2, "bar"); ps.addBatch(); ps.executeBatch(); Suggested fix: Here are the problematic parts of the code: valueClauseEnd is set if a right parenthesis is found when in the valueClause (regardless if it is the end or not). https://github.com/mysql/mysql-connector-j/blob/27603148f10a5f47467bec7ad26a5ca28da63c72/s... If valueClauseEnd is set, and '?' is reached while parsing, rewritableAsMultiValues is set to false, and the batched statement won't be rewritten. https://github.com/mysql/mysql-connector-j/blob/27603148f10a5f47467bec7ad26a5ca28da63c72/s...