Unable to parse query. If correct, report it as a bug: SET SESSION wsrep_sync_wait=?

[glbyers]

Using ProxySQL 2.6.2 on RHEL 8.9 in front of a Galera cluster to route queries for IcingaDB, logs are flooded with the message;

2024-04-21 05:52:32 MySQL_Session.cpp:6574:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [ERROR] Unable to parse query. If correct, report it as a bug: SET SESSION wsrep_sync_wait=?

It appears wsrep_sync_wait is a tracked session variable in proxysql 2.6.2 & it expects an integer value, but in this case, the variable is set via a prepared statement.

Is it still the case that ProxySQL doesn't support the parsing and tracking of SET statements using the binary protocol (prepared statements).?

I have raised this with IcingaDB developers (see first link above).

[renecannao]

Hi @glbyers .
Thank you for the report!

Yes, it is still the case that ProxySQL doesn't support the parsing and tracking of SET statements using the binary protocol.
The reason is that with prepared statements the parameter is send on a different command (during the execution of the prepared statement), thus the tracking becomes way more complex and costly in term of performance.
For example, every time a client executes a prepared statement ProxySQL would need to internally lookup for the original prepared statement and identify if it was a SET statement for a variable it is able to track. Furthermore, if is able to track it, executing the SET statement as a prepared statement would be a performance cost, thus proxysql it would switch to text protocol when executing on the backend. As said, extra complexity and performance cost.

I see your patch on Icinga/icingadb#750 (comment) . Not only your patch has less code, but it is also around 3 times faster: it sends only 1 command (a COM_QUERY) instead of 3 commands (COM_STMT_PREPARE, COM_STMT_EXECUTE and COM_STMT_CLOSE) for simply settings a session variable.

[glbyers]

Thank you @renecannao. I'll close this one now.