Bug #101560 rewriteBatchedStatements rewrites batch updates
Submitted: 11 Nov 2020 9:42 Modified: 24 Feb 2021 2:18
Reporter: Andrey Tarashevskiy Email Updates:
Status: Verified Impact on me:
Category:Connector / J Severity:S3 (Non-critical)
Version:8.0.22 OS:Any
Assigned to: CPU Architecture:Any

[11 Nov 2020 9:42] Andrey Tarashevskiy
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.