Unable to parse unknown SET query Setting lock_hostgroup. Please report a bug for future enhancements:SET @@innodb_lock_wait_timeout = 1

[jdlawrie]

Reproduced on both 2.7.3-0-g08ae969_DEBUG and 2.7.3-3 non-debug

[renecannao]

Hi @jdlawrie .
Please provide the steps to reproduce this , and error log.

SET @@innodb_lock_wait_timeout = 1 works since ProxySQL 2.4.0 , so the issue is of different nature.

Also, note that 2.7.3-0-g08ae969 is not latest 2.7 release.
Latest ProxySQL 2.7 is version 2.7.3-12-g50b7f85

[jdlawrie]

Hi @renecannao - checked on a newer version and we're still seeing the same:

proxysql> select @@version;
+-------------------+
| @@version         |
+-------------------+
| 2.7.3-12-g50b7f85 |
+-------------------+
1 row in set (0.000 sec)

proxysql> SELECT * FROM mysql_query_rules;
Empty set (0.000 sec)

MySQL [information_schema]> SET @@innodb_lock_wait_timeout = 1;
Query OK, 0 rows affected (0.000 sec)

MySQL [information_schema]> SELECT @@innodb_lock_wait_timeout;
+----------------------------+
| @@innodb_lock_wait_timeout |
+----------------------------+
|                         50 |
+----------------------------+
1 row in set (0.001 sec)

This doesn't give me any errors, but at the same time I see dev logs:
proxysql-0 proxysql 2025-05-21 09:50:23 MySQL_Session.cpp:8427:unable_to_parse_set_statement(): [WARNING] Unable to parse unknown SET query from client 10.151.92.152:45086. Setting lock_hostgroup. Please report a bug for future enhancements:SET @@innodb_lock_wait_timeout = 1-- context: {"user_id": "802e6aa8-979a-45af-8f31-bbec62797b34"}

The annoying thing is that setting the lock_wait_timeout sometimes works, and sometimes doesn't. This environment only has one backend MySQL server:
| 10 | db.development | 3306 | 0 | ONLINE | 1 | 0 | 100 | 0 | 0 | 0 | |

I've tried using query rules to set multiplexing to 1 but it didn't help:

| 4       | 1      | NULL     | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | NULL         | ^SET @@innodb_lock_wait_timeout = \d      | 0                    | CASELESS     | NULL    | NULL            | NULL                  | NULL      | NULL               | NULL          | NULL      | NULL    | NULL    | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | 0         | NULL                | NULL | 0     |            | NULL    |

On a side note, should this be set to multiplexing 0 1 or 2? Documentation says @@ disables multiplexing anyway, but previous bug reports suggest to rewrite them to 1.

[renecannao]

Hi @jdlawrie .
-- context: {"user_id": "802e6aa8-979a-45af-8f31-bbec62797b34"} : what is that ?

[jdlawrie]

Hi @renecannao it's sent by the app as a context comment, and admittedly should have been in the title/initial bug report. The SET with the comment seems to work against mysql directly

[jdlawrie]

Output from the debug build (2.7.3-0-g08ae969_DEBUG)

1747828569862659(20): 13:MySQL_Session.cpp:1230:handler_SetAutocommit(): MOD#11#debug_mysql_query_processor LVL#5 : Parsing SET command = SET @@innodb_lock_wait_timeout = 1-- context: {"user_id": "0d8df1be-166a-41a9-862f-3b0f79ce8cc8"}
1747828569862670(11): 13:MySQL_Session.cpp:6489:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): MOD#11#debug_mysql_query_processor LVL#5 : Parsing SET command = SET @@innodb_lock_wait_timeout = 1-- context: {"user_id": "0d8df1be-166a-41a9-862f-3b0f79ce8cc8"}
1747828569862770(100): 13:MySQL_Session.cpp:6533:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): MOD#4#debug_mysql_com LVL#5 : Parsing SET command SET @@innodb_lock_wait_timeout = 1-- context: {"user_id": "0d8df1be-166a-41a9-862f-3b0f79ce8cc8"}
1747828569862775(5): 13:MySQL_Session.cpp:6534:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): MOD#11#debug_mysql_query_processor LVL#5 : Parsing SET command = SET @@innodb_lock_wait_timeout = 1-- context: {"user_id": "0d8df1be-166a-41a9-862f-3b0f79ce8cc8"}
1747828569862897(122): 13:set_parser.cpp:391:parse1v2(): MOD#11#debug_mysql_query_processor LVL#4 : SET parsing: v1='' , v2='' , v3='' , v4='innodb_lock_wait_timeout' , v5='1'
1747828569863137(240): 13:MySQL_Session.cpp:8418:unable_to_parse_set_statement(): MOD#11#debug_mysql_query_processor LVL#5 : Locking hostgroup for query SET @@innodb_lock_wait_timeout = 1-- context: {"user_id": "0d8df1be-166a-41a9-862f-3b0f79ce8cc8"}
1747828569863143(6): 13:MySQL_Session.cpp:8422:unable_to_parse_set_statement(): MOD#11#debug_mysql_query_processor LVL#5 : SET query to cause setting lock_hostgroup: SET @@innodb_lock_wait_timeout = 1-- context: {"user_id": "0d8df1be-166a-41a9-862f-3b0f79ce8cc8"}
2025-05-21 11:56:09 MySQL_Session.cpp:8427:unable_to_parse_set_statement(): [WARNING] Unable to parse unknown SET query from client 10.141.9.5:58808. Setting lock_hostgroup. Please report a bug for future enhancements:SET @@innodb_lock_wait_timeout = 1-- context: {"user_id": "0d8df1be-166a-41a9-862f-3b0f79ce8cc8"}

[jdlawrie]

Submitted a PR #4959

[jdlawrie]

Hey @renecannao I just wanted to follow up and say that this is a bigger issue than I thought. We had another issue where if we aborted a transaction using sqlalchemy and had been using auto_commit = 0 with proxysql, it would sometimes commit before rollback, and sometimes cause part of the transaction to be committed.

Pushing a rewrite rule fix for this SET (while we wait for the above PR or a similar one to be merged) fixed both issues - I guess it was something to do with multiplexing but it's a ugess.

[renecannao]

Hi @jdlawrie .
Thank you for the follow up.
Can you please provide a full test case?
We are working on a complete rewrite of SetParser , and we want to make sure to cover this case too