Bug #101560 rewriteBatchedStatements rewrites batch updates

• Submitted: 11 Nov 2020 9:42
• Modified: 24 Feb 2021 2:18
• Reporter: Andrey Tarashevskiy
• Status: Verified
• Category: Connector / J
• Severity: S3 (Non-critical)
• Version: 8.0.22
• OS: Any
• CPU Architecture: Any

[11 Nov 2020 9:42] Andrey Tarashevskiy

Description:
It's not very clear from documentation (see below) but rewriteBatchedStatements=true also rewrites batch update statements into a single SQL query.

> Should the driver use multiqueries (regardless of the setting of "allowMultiQueries") as well as rewriting of prepared statements for INSERT into multi-value inserts when executeBatch() is called?

That leads to queries like 'UPDATE Foo SET bar=? WHERE baz=?; UPDATE Foo SET bar=? WHERE baz=?;' which can't be used as server prepared statement.

I found out that there is `StringUtils.canHandleAsServerPreparedStatementNoCache` function which checks can query be used or not as a server prepared statement but it works only with allowMultiQueries=true and doesn't check for rewriteBatchedStatements.

How to repeat:
1. Connect to Mysql server with useServerPrepStmts=true
2. Set breakpoint on ConnectionImpl.java:1635 (if(this.emulateUnsupportedPstmts.getValue()))
3. Prepare update statement and add several batches then call executeBatch on statement

Suggested fix:
The similar check should be used for both allowMultiQueries and rewriteBatchedStatements.

[24 Feb 2021 2:18] Filipe Silva

Hi Andrey,

Thanks for your interest in Connector/J and MySQL.

Nice catch. Although all works "fine" I suppose it can be improved. I'm setting this report as verified.