When running a SET query in ProxySQL, you may come across a warning as shown below in /var/log/proxysql.log for example:
2023-12-21 12:59:27 MySQL_Session.cpp:7093:unable_to_parse_set_statement(): [WARNING] Unable to parse unknown SET query from client 172.16.2.213:57126. Setting lock_hostgroup. Please report a bug for future enhancements:SET SESSION innodb_lock_wait_timeout = 20
This is due to the introduction of a new algorithm starting from ProxySQL version 2.0.6 to disable multiplexing if a SET statement could not be parsed, which causes the warning to appear when the SET SESSION query is run.
To workaround this issue and prevent the warning from appearing, a rule should be added to explicitly allow any SET SESSION query to be multiplexed using the queries shown below:
INSERT INTO mysql_query_rules (rule_id, active, match_digest, multiplex) VALUES (1, 1, '^SET SESSION', 1);
NOTE1: the rule above is only for allowing SET SESSION queries.
NOTE2: make sure that you are using a unique identifier for rule_id.
Here is another example rule to allow more specific SET queries, such as "SET character_set_results" and "SET SQL_SELECT_LIMIT=":
INSERT INTO mysql_query_rules (rule_id, active, match_digest, multiplex) VALUES
(12, 1, '^SET character_set_results', 1),
(13, 1, 'SET SQL_SELECT_LIMIT=', 1);
After INSERT the new rule, it must be loaded to runtime for the changes to take affect immediately:
LOAD MYSQL QUERY RULES TO RUNTIME;
It is also advisable to save the updated rules to the disk to avoid them from reverting when the service is restarted:
SAVE MYSQL QUERY RULES TO DISK;