Sequel Ace SET command interfering with ProxySQL setup

[multipleofzero]

• Sequel Ace Version: Version 4.0.16, Build 20066
• Sequel Ace Source: Homebrew
• macOS Version: 14.4.1
• Processor Type: Apple M2
• MySQL Version: 8.0.28
• ProxySQL Version: 2.5.5-percona-1.2

Is Issue Present in Latest Beta?
Yes

Description
ProxySQL tracks all the session variables configured by the client using SET statements. It then forwards these settings to (potentially different) backends as required.

When it encounters a variable it does not know about, it cannot track it and must decide what to do about this. The setting mysql-set_query_lock_on_hostgroup is governing this.

In newer versions of ProxySQL the default for mysql-set_query_lock_on_hostgroup is 1 which means that a connection is locked to a hostgroup when a SET for an unknown/untrackable variable is issued.
It can then happen (subject to the cluster/ProxySQL configuration) that SELECT (read-only) queries should be routed to a different host group than the one the connection was initially established with/for. Usually that is a r/w group, it seems (which is sensible, I guess). This is called "query routing" in ProxySQL.

This seems to be exactly what is happening in my case. When I disable mysql-set_query_lock_on_hostgroup (set it to 0, see for example "9006 ProxySQL Error: connection is locked to hostgroup XX but trying to reach hostgroup YY" on SO), I can successfully issue a query. I cannot change this setting on our production server though.

Most likely, the SET information_schema_stats_expiry=0 mentioned in #1241 is the culprit, as it seems to be the only SET statement involved.

It would be necessary to be able to prevent this statement to be issued for a connection to ProxySQL (or ProxySQL should learn to track this variable) for Sequel Ace to be usable against (such) Proxy SQL setups.

Steps To Reproduce

1. Connect to a ProxySQL Instance
2. Select a database
3. Execute a (SELECT) query
4. Sequel Ace shows an error message similar to the one shown in 3.4.1 introduces a SET command that can not be disabled (incompatible with ProxySQL) #1241, something along the lines of "ProxySQL Error: connection is locked to hostgroup 1 but trying to reach hostgroup 2" instead of the data

Expected Behaviour
The query should execute and return data.

Related Issues
#1241 is the exact same issue but was apparently not really followed up

Additional Context
This is related to the behaviour of ProxySQL (when it sees a SET for an unknown variable) and the specific MySQL cluster setup (using host groups to differentiate between read-write and read-only nodes). It is not easily testable without a quite complex cluster setup.

There is also an open discussion in ProxySQL's Github repo.

[Jason-Morcos]

Although ProxySQL isn't officially supported by Sequel Ace, I'd be happy to review and approve a PR that fixes this issue without breaking MySQL/MariaDB connections.
Thank you for the detailed writeup here!

[multipleofzero]

I have never worked with Objective-C, so I am not qualified to tackle this. But maybe someone from the community has the required skills and can shave off some time for this?