Batch delete leads to error

[antonialatalo]

This is the at least for me a reproducible bug for ProxySQL.

From our application, I'm sending a batch delete query. The application is written in java and we are using the latest MySQL driver.
While sending the batch request I continuously get the error:
2021-04-01 07:35:35 MySQL_Session.cpp:3897:handler_minus1_LogErrorDuringQuery(): [WARNING] Error during query on (2,127.0.0.1,3307,10097): 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 'delete from some_table where id = 10 AND some_fi' at line 1
As you can see statement is truncated in the middle of the query. Truncated place differs depending on how long batch request is.

When looking to stats table i see complete statement.
select digest_text from stats_mysql_query_digest where digest_text like 'delete%';
The result is:
delete from some_table where id = ? AND some_field = ?;delete from some_table where id = ? AND some_field = ?;delete from some_table where id = ? AND some_field = ?;delete from some_table where id = ? AND some_field = ?;delete from some_table where id = ? AND some_field = ?;delete from some_table where id = ? AND some_field = ?;delete from some_table where id = ? AND some_field = ?;delete from some_table where id = ? AND some_field = ?;delete from some_table where id = ? AND some_field = ?;delete from some_table where id = ? AND some_field = ?;delete from some_table where id = ? AND some_field = ?;delete from some_table where id = ? AND some_field = ?;delete from some_table where id = ? AND some_field = ?;delete from some_table where id = ? AND some_field = ?;delete from some_table where id = ? AND some_field = ?;delete from some_table where id = ? AND some_field = ?;delete from some_table where id = ? AND some_field = ?;delete from some_table where id = ? AND some_field = ?;delete from some_table where id = ? AND some_field = ?;delete from some_table where id = ? AND some_field = ?;delete from some_table where id = ? AND some_field = ?;delete from some_table where id = ? AND some_field = ?;delete from some_table where id = ? AND some_field = ?;delete from some_table where id = ? AND some_field = ?;delete from some_table where id = ? AND some_field = ?;delete from some_table where id = ? AND some_field = ?;delete from some_table where id = ? AND some_field = ?

I did try to change default values to be longer for mysql-query_digests_max_digest_length, mysql-query_digests_max_query_length but this has no affect.

Everything starts to work when changing mysql-query_digests to be false. But with digest enabled this batch delete doesn't work.

Another annoing error is:
2021-04-01 07:35:35 MySQL_Session.cpp:6872:unable_to_parse_set_statement(): [WARNING] Unable to parse unknown SET query from client 127.0.0.1:45190. Setting lock_hostgroup. Please report a bug for future enhancements:SET @audit_userid = 1
This happen from time to time. It does not prevent any functionality and is sent to MySQL server correctly.

ProxySQL version 2.1.0-544-g17a4b4a7, codename Truls
OS: Linux Mint 20.1 cinnamon

Thank you!
Antoni Alatalo

[bskllzh]

@antonialatalo , can you provide scripts or methods that can be reproduced?

[antonialatalo]

After a very deep investigation, found that the packet sent to ProxySQL is somehow corrupted. Couldn't find what is broken. but seems that it's not in ProxySQL but in our code.