MySQL Bugs: #96623: batch update with rewriteBatchedStatements&useServerPrepStmts send fail request

Bug #96623	batch update with rewriteBatchedStatements&useServerPrepStmts send fail request
Submitted:	22 Aug 2019 17:17	Modified:	7 Oct 2024 22:34
Reporter:	robi su	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S5 (Performance)
Version:	8.0.15, 8.0.17	OS:	Linux
Assigned to:		CPU Architecture:	x86

Description:
running batch update with rewriteBatchedStatements=true&useServerPrepStmts=true&useConfigs=maxPerformance&allowMultiQueries=false always sending always failed com_stmt_prepare request.

it seems can canHandleAsServerPreparedStatement only take care allowMultiQueries and miss the situation that rewriteBatchedStatements will make update/delete to multi-query.

How to repeat:
use jdbc url with 

rewriteBatchedStatements=true&useServerPrepStmts=true&useConfigs=maxPerformance&allowMultiQueries=false

run this code

        // create table t(id int, v int);
        // insert into t(id, v) values(1, 1),(2, 2), (3, 3), (4, 4);
        try (Connection connection = dataSource.getConnection();
             final PreparedStatement inPstmt = connection.prepareStatement("update t set v = ? where id = ?")) {
            inPstmt.setInt(1, 1);
            inPstmt.setInt(2, 4);
            inPstmt.addBatch();
            inPstmt.setInt(1, 2);
            inPstmt.setInt(2, 3);
            inPstmt.addBatch();
            inPstmt.setInt(1, 3);
            inPstmt.setInt(2, 2);
            inPstmt.addBatch();
            inPstmt.setInt(1, 4);
            inPstmt.setInt(2, 1);
            inPstmt.addBatch();
            int[] result = inPstmt.executeBatch();
            System.out.println(Arrays.toString(result));
        }

and using wireshark to pcap packet will see 

client send a com_stmt_prepare with 

`update t set v = ? where id = ?;update t set v = ? where id = ?;update t set v = ? where id = ?;update t set v = ? where id = ?` 

but it will be failed with 

Error 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'update t set v = ? where id = ?;update t set v = ? where id = ?;update t set v =' at line 1

then jdbc using com_query to retry send batch update and success...

if we change allowMultiQueries=false to allowMultiQueries=true

wireshark should not see any fail com_stmt_prepare.

Suggested fix:
for mysql-server, it seems cannot prepare multi-query 
 https://github.com/mysql/mysql-server/blob/6a0b1a1342722a207a3d67d3ddbdbe3789a1d970/sql/sq..., if mysql-server support, current code will works.

but maybe mysql-server have some reason can not support that, so maybe a quick fix, we can do is let canHandleAsServerPreparedStatement take care batch rewrite.

how about add new flag in NativeServerSession and control this flag by enableMultiQueries/disableMultiQueries, then canHandleAsServerPreparedStatement can check this new flag plus allowMultiQueries :D

this will send useless request and affect performance if user use those configuration

hi I have file a PR in github https://github.com/mysql/mysql-connector-j/pull/42

Hello Li Su,

Thank you for the report and feedback.

regards,
Umesh

Please note that in order to submit contributions you must first sign the Oracle Contribution Agreement (OCA).
For additional information please check http://www.oracle.com/technetwork/community/oca-486395.html.
If you have any questions, please contact the MySQL community team.

Duplicated by Bug#101560.

Posted by developer:
 
Added the following entry to the C/J 9.1.0 changelog: 

"When rewriteBatchedStatements was set to true, Connector/J failed to detect some cases in which server-side PreparedStatements could not be used, resulting in errors for the statement preparations. With this fix, Connector/J detected the issues and uses client-side PreparedStatements instead in those situations."