ProxySQL 3.0.10: SET LOCAL session variable causes hostgroup lock while equivalent SET SESSION is tracked correctly

25 views
Skip to first unread message

Marcello Lupo

unread,
Oct 6, 2026, 4:56:38 AMOct 6
to proxysql
Hello,

I would like to report what appears to be an issue in ProxySQL session variable tracking.

Environment
-----------

- ProxySQL: 3.0.10-426-gf5f1e14
- MariaDB backend: 12.0.2-MariaDB-ubu2404
- Writer hostgroup: 1
- Reader hostgroup: 2
- User default_hostgroup: 1
- transaction_persistent: 1
- Normal SELECT queries are routed to HG2
- Locking SELECTs (FOR UPDATE, FOR SHARE, LOCK IN SHARE MODE) are routed to HG1

Relevant ProxySQL variables
---------------------------

mysql-set_query_lock_on_hostgroup = 1
mysql-session_track_variables     = 1
mysql-set_parser_algorithm        = 2
mysql-enable_server_deprecate_eof = true
mysql-enable_client_deprecate_eof = true
mysql-multiplexing                = true

Minimal query rules
-------------------

^SELECT.*(FOR UPDATE|FOR SHARE|LOCK IN SHARE MODE) -> HG1
^WITH                                              -> HG1
^SELECT                                            -> HG2
default_hostgroup                                  -> HG1

Reproduction
------------

Using a fresh frontend connection:

SET LOCAL innodb_lock_wait_timeout=5;

The statement succeeds.

Immediately afterwards:

PROXYSQL INTERNAL SESSION;

reports, among other fields:

active_transactions: 0
current_hostgroup: 1
locked_on_hostgroup: 1
MultiplexDisabled: true
no_multiplex: true

A subsequent:

SELECT 1;

fails with:

ERROR 9006 (Y0000): ProxySQL Error: connection is locked to hostgroup 1 but trying to reach hostgroup 2

Comparison with SET SESSION
---------------------------

Using a new frontend connection:

SET SESSION innodb_lock_wait_timeout=5;

PROXYSQL INTERNAL SESSION;

shows:

innodb_lock_wait_timeout: "5"
locked_on_hostgroup: -1

and:

SELECT 1;

is correctly routed to HG2 and succeeds.

The same correct behavior is observed with:

SET innodb_lock_wait_timeout=5;

Therefore, the behavior appears to be specific to the SET LOCAL syntax.

Test with the new SET parser
----------------------------

We also tested:

mysql-set_parser_algorithm = 3
mysql-session_track_variables = 1

with a fresh frontend connection.

The result is unchanged:

SET LOCAL innodb_lock_wait_timeout=5;

still results in:

locked_on_hostgroup: 1
MultiplexDisabled: true

and the subsequent:

SELECT 1;

returns:

ERROR 9006 (Y0000): ProxySQL Error: connection is locked to hostgroup 1 but trying to reach hostgroup 2

By contrast, SET SESSION innodb_lock_wait_timeout=5 continues to be tracked correctly.

Application context
-------------------

We initially encountered the issue with phpIPAM.

phpIPAM executes:

SET local innodb_lock_wait_timeout=<value>;
START TRANSACTION;
SELECT ... FOR UPDATE;
...
COMMIT;

The relevant phpIPAM code is in:

functions/classes/class.LockForUpdate.php

and contains:

$this->Database->runQuery("SET local innodb_lock_wait_timeout=$timeout;");

After the transaction completes, the ProxySQL frontend session remains locked to HG1.

Subsequent read queries that our query rules route to HG2 then fail with error 9006.

The issue was observed while opening the phpIPAM customer detail page under:

/tools/customers

The page performs geocoding through the OpenStreetMap/Nominatim code path and then loads customer-related objects.

During a single page load we observed, among others:

SET local innodb_lock_wait_timeout=?
SELECT * FROM `nominatim` WHERE `id`=? FOR UPDATE
UPDATE `locations` SET `lat`=?,`long`=? WHERE `id`=?
COMMIT

followed by read queries such as:

SELECT * FROM `subnets` WHERE `customer_id`=? ...
SELECT * FROM `ipaddresses` WHERE `customer_id`=? ...
SELECT * FROM `vlans` WHERE `customer_id`=? ...
SELECT * FROM `vrf` WHERE `customer_id`=? ...
SELECT * FROM `circuits` WHERE `customer_id`=? ...
SELECT * FROM `racks` WHERE `customer_id`=? ...
SELECT * FROM `routing_bgp` WHERE `customer_id`=? ...

These SELECT statements are normally routed to HG2.

Temporary verification
----------------------

As a temporary verification only, we changed the phpIPAM statement from:

SET LOCAL innodb_lock_wait_timeout=...

to:

SET SESSION innodb_lock_wait_timeout=...

After this change:

- ProxySQL tracks innodb_lock_wait_timeout correctly
- locked_on_hostgroup remains -1
- the customer page loads without any 9006 errors
- the read queries are correctly routed to HG2

This confirms the behavior end-to-end.

Question
--------

Could you please confirm whether the different handling of:

SET LOCAL variable=value

and:

SET SESSION variable=value

is expected behavior?

If not, would it be possible for the SET parser/session tracking logic to treat LOCAL as the equivalent session scope and track the variable instead of locking the frontend session to the current hostgroup?

We would prefer not to modify the application, since the backend accepts SET LOCAL and the intended scope is the current session.

We can provide complete PROXYSQL INTERNAL SESSION output, query rules, query digests, and additional logs if useful.

Thank you.

René Cannaò

unread,
Oct 6, 2026, 5:29:15 AMOct 6
to Marcello Lupo, proxysql
Ciao Marcello,

Thanks for the detailed report: this is a real bug, not intended behavior.
LOCAL is a documented (if rarely used) synonym for SESSION in MySQL's SET syntax.
Worth noting MySQL itself doesn't even list LOCAL in the syntax diagram on its own docs page:

It only mentions it as an aside, which is probably why it slipped through our variable-tracking/query-classification logic.
SET SESSION is correctly recognized and tracked; SET LOCAL is not, and incorrectly falls back to locking the connection to the current hostgroup.

We've opened https://github.com/sysown/proxysql/issues/6397 to track and fix this.
Feel free to follow/subscribe there for updates.

Thanks again for the clear repro, it made this easy to confirm.
Grazie!

--
You received this message because you are subscribed to the Google Groups "proxysql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+u...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/proxysql/7d0d1370-677e-4c7e-9569-58b8cac0a37cn%40googlegroups.com.

Marcello Lupo

unread,
Oct 6, 2026, 7:33:18 AMOct 6
to proxysql
Thank you for the answer.
WIll wait for the fix.
Regards
Marcello
Reply all
Reply to author
Forward
0 new messages