Disabling mysql-set_query_lock_on_hostgroup effects

[Jose Eduardo Hernandez Paredes]

Hi,

When using Navicat to make a Query to a MySQL cluster with a ProxySQL server in front, I got this

[Err] 9006 - ProxySQL Error: connection is locked to hostgroup 1 but trying to reach hostgroup 2

Also it happens on the query SELECT * FROM information_schema.CHARACTER_SETS when loading PHPMyAdmin connected to the ProxySQL. 

It begun happening after I made the read/write splitting on the proxy. Hostgroup 1 is write (master), and 2 is read only (2 slaves). I looked about in many places and this solution keep coming up from users:

mysql-set_query_lock_on_hostgroup=0

This is the ProxySQL explaining on this variable: When active (default from 2.0.6), if SET statement is used in multi-statements commands or if the parsing of SET statement is not successful, both multiplexing and query routing is disabled. The client will remain bound to a single backend connection. Any SET statement that ProxySQL doesn’t understand will disable multiplexing and routing.

What is this error happening in the first place? Is this solution right? What does it do? Will it affect the normal functioning of the ProxySQL and the read/write splitting?

[Gillian Gunson]

I was able to reproduce the problem, using phpMyAdmin 5.0.2 and the dockerized proxysql test cluster (https://github.com/sysown/docker-mysql-proxysql). After logging in with my mysql credentials:

The error message shows the error happening on the query: 

SELECT `CHARACTER_SET_NAME` AS `Charset`, `DEFAULT_COLLATE_NAME` AS `Default collation`, `DESCRIPTION` AS `Description`, `MAXLEN` AS `Maxlen` FROM `information_schema`.`CHARACTER_SETS`

And error "#9006 - ProxySQL Error: connection is locked to hostgroup 0 but trying to reach hostgroup 1". If I start clicking around the same error pops up again for other charset-related queries.

The general log on the writeable mysql server (hostgroup 0 in this case) shows, when I logged into phpMyAdmin:

2020-07-09T04:41:31.299340Z   214 Query SET NAMES utf8mb4 COLLATE 'utf8mb4_general_ci'

2020-07-09T04:41:31.300974Z   214 Query SET lc_messages = 'en_US'

2020-07-09T04:41:31.343739Z   214 Change user root@docker-mysql-proxysql_proxysql1_1.docker-mysql-proxysql_back on information_schema using TCP/IP

2020-07-09T04:41:31.438745Z   214 Query SET NAMES utf8mb4 COLLATE 'utf8mb4_general_ci'

2020-07-09T04:41:31.439325Z   214 Query SET lc_messages = 'en_US'

2020-07-09T04:41:31.474327Z   214 Query SET collation_connection = 'utf8mb4_unicode_ci'

(repeated a few more times)

Looking at the ProxySQL logs:

2020-07-09 04:32:46 MySQL_Session.cpp:4895:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse multi-statements command with SET statement: setting lock hostgroup . Command: SET lc_messages = 'en_US';

So it's failing on the "SET lc_messages = 'en_US';" command that phpMyAdmin sends and then locks to the default hostgroup. In this case it's complaining about the trailing ';' (which is to be fixed in 2.0.13), but if I use the mysql client to login via proxysql and run the same SET, I get:

2020-07-09 05:02:26 MySQL_Session.cpp:6483:unable_to_parse_set_statement(): [WARNING] Unable to parse unknown SET query from client 172.18.0.1:33644. Setting lock_hostgroup. Please report a bug for future enhancements:set lc_messages='en_US'

So it would still error even if the trailing semicolon bug is fixed, because ProxySQL doesn't recognize "lc_messages".

[Gillian Gunson]

I've reported the unknown lc_messages variable bug: https://github.com/sysown/proxysql/issues/2937

[Gillian Gunson]

As for Navicat (which I missed the first time), I replicated the problem there too. I ran a SELECT NOW() and it returned with the error, and the ProxySQL log shows:

2020-07-09 10:28:22 MySQL_Session.cpp:6483:unable_to_parse_set_statement(): [WARNING] Unable to parse unknown SET query from client 172.18.0.1:35204. Setting lock_hostgroup. Please report a bug for future enhancements:SET PROFILING = 1

So in Navicat's case it's `SET PROFILING` that's the problem. This has been reported in a few places in the proxysql repo (e.g. https://github.com/sysown/proxysql/issues/2474). May need to see whether to open a new issue or add to an existing one. 

[hpjo...@gmail.com]

Thank you very much for the attention!

[Gillian Gunson]

I forgot to update, in terms of the Navicat problem with "SET PROFILING" being run automatically, I'm trying to compile a workaround to put in the docs. Current version is in https://github.com/sysown/proxysql/issues/2943#issuecomment-656865473 which should allow Navicat to let you run a query. Might be something to iterate on if other problems appear.

ProxySQL isn't meant to be connected to by these GUI SQL clients, so the general directive is to connect to the database server(s) directly and these workarounds are just for getting around common problems when people use these clients anyways.