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
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