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

[slowtech]

ProxySQL Version:

ProxySQL> select @@version;
+-------------------+
| @@version         |
+-------------------+
| 2.0.8-67-g877cab1 |
+-------------------+
1 row in set (0.00 sec)

MySQL Version: 8.0.19

Here is the config info.

ProxySQL> select username,active,default_hostgroup,default_schema,schema_locked,transaction_persistent from runtime_mysql_users;
+------------+--------+-------------------+----------------+---------------+------------------------+
| username   | active | default_hostgroup | default_schema | schema_locked | transaction_persistent |
+------------+--------+-------------------+----------------+---------------+------------------------+
| split_user | 1      | 10                |                | 0             | 1                      |
| split_user | 1      | 10                |                | 0             | 1                      |
+------------+--------+-------------------+----------------+---------------+------------------------+
2 rows in set (0.00 sec)

ProxySQL> select hostgroup_id,hostname,port,status from runtime_mysql_servers;
+--------------+----------------+------+--------+
| hostgroup_id | hostname       | port | status |
+--------------+----------------+------+--------+
| 10           | 192.168.244.10 | 3306 | ONLINE |
| 20           | 192.168.244.20 | 3306 | ONLINE |
+--------------+----------------+------+--------+
2 rows in set (0.01 sec)

ProxySQL> select rule_id,active,match_digest,destination_hostgroup,multiplex,apply from runtime_mysql_query_rules;
+---------+--------+----------------------+-----------------------+-----------+-------+
| rule_id | active | match_digest         | destination_hostgroup | multiplex | apply |
+---------+--------+----------------------+-----------------------+-----------+-------+
| 1       | 1      | ^SELECT.*FOR UPDATE$ | 10                    | NULL      | 1     |
| 2       | 1      | ^SELECT              | 20                    | NULL      | 1     |
+---------+--------+----------------------+-----------------------+-----------+-------+
2 rows in set (0.00 sec)

ProxySQL> select * from runtime_mysql_replication_hostgroups;
+------------------+------------------+-----------------+--------------+
| writer_hostgroup | reader_hostgroup | check_type      | comment      |
+------------------+------------------+-----------------+--------------+
| 10               | 20               | super_read_only | repl_group_1 |
+------------------+------------------+-----------------+--------------+
1 row in set (0.00 sec)

ProxySQL> select * from global_variables where variable_name like '%multiplexing%';
+-----------------------------------+----------------------+
| variable_name                     | variable_value       |
+-----------------------------------+----------------------+
| mysql-multiplexing                | true                 |
| mysql-keep_multiplexing_variables | tx_isolation,version |
+-----------------------------------+----------------------+
2 rows in set (0.00 sec)

But the test for multiplexing doesn't work as expected. Here is the test. As the document says, multiplexing is disabled when queries contain user variables or temporary tables.

test1

# mysql -h192.168.244.128 -usplit_user -psplit_pass -P6033
mysql> set @name='slowtech';
Query OK, 0 rows affected (0.11 sec)

mysql> select @name;
ERROR 9006 (Y0000): ProxySQL Error: connection is locked to hostgroup 10 but trying to reach hostgroup 20

test2

# mysql -h192.168.244.128 -usplit_user -psplit_pass -P6033

mysql> create temporary table slowtech.t5(id int);
Query OK, 0 rows affected (0.79 sec)

mysql> insert into slowtech.t5 values(5);
Query OK, 1 row affected (0.27 sec)

mysql> select * from slowtech.t5;
ERROR 1146 (42S02): Table 'slowtech.t5' doesn't exist

Here is the repeat SQL

delete from mysql_servers;
insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.244.10',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values(20,'192.168.244.20',3306);
load mysql servers to runtime;
save mysql servers to disk;

delete from mysql_query_rules;
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) values (1,1,'^SELECT.*FOR UPDATE$',10,1);
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) values (2,1,'^SELECT',20,1);
load mysql query rules to runtime;
save mysql query rules to disk;

delete from mysql_replication_hostgroups;
insert into mysql_replication_hostgroups values (10,20,'super_read_only','repl_group_1');
load mysql servers to runtime;

Tks

[Amixp]

+1

[renecannao]

You seem to be confusing two distinct features:

• multiplexing : the ability to share the same backend connection between multiple frontend connections
• query routing

Disabling multiplexing does not disable query routing

[michaeldg]

I think you are reporting two separate issues here. I have duplicated one of your issues in #2569 - perhaps you can clarify your other issue here and make them separate?

[slowtech]

I think you are reporting two separate issues here. I have duplicated one of your issues in #2569 - perhaps you can clarify your other issue here and make them separate?

they are two of the conditions where multiplexing is disabled

[michaeldg]

Two different issues:

Issue 1: Multiplexing is disabled on user-defined session variables (set @name='slowtech';), but SELECT query is still routed to backend for which access is denied, Issue 2: Multiplexing is NOT disabled on temporary table creation, as the SELECT from that table is still routes to the other node (where the table does not exist).

As said, I have reported issue 1 in #2569.

[slowtech]

Temporary tables If CREATE TEMPORARY TABLE is executed, multiplexing is disabled and is never enabled again on the connection.

https://github.com/sysown/proxysql/wiki/Multiplexing

[michaeldg]

Yes, therefore the next query should not be executed on a different connection.

[michaeldg]

@renecannao can you elaborate on how multiplexing works?

In my opinion, if multiplexing gets disabled on a certain connection (for example due to starting a transaction or creating a temporary table), that client should be tied to that server connection, the client should in no case be moved to a different server connection as it would loose the transaction or temporary table in the process.

[u2bo]

have the same error. used navicat exec : select 1 .and get error [Err] 9006 - ProxySQL Error: connection is locked to hostgroup 10 but trying to reach hostgroup 11

it sames like used exits write connection for this . how to resolve this? my config is normal @renecannao

[abdidarmawan007]

i have the same error lol proxysql version = 2.0.10 mysql client = sqlyog, stitchdata

proxysql error connection is locked to hostgroup 0 but trying to reach hostgroup 1

[abdidarmawan007]

fix with this command

login to proxysql admin

set mysql-set_query_lock_on_hostgroup=0; load mysql variables to runtime; save mysql variables to disk;

restart your proxysql service (ubuntu)

sudo service proxysql restart

[rsidhaarth]

Hi,

I am getting the same error when I am trying to deploy magento files.

https://prnt.sc/tj2r6a

What exactly this "set mysql-set_query_lock_on_hostgroup=0;" this command do?

Thanks

[jtomaszon]

@rsidhaarth Check the link with the documentation mysql-set_query_lock_on_hostgroup Basically, after 2.0.6, when proxySQL does not understand a SET command, will disable routing and multiplexing, setting that to Zero disable that functionality.

In my case of testing, using django app I see this on the logs:

2020-07-25 03:57:56 MySQL_Session.cpp:4943: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 sql_mode='STRICT_TRANS_TABLES', innodb_strict_mode=1;SET character_set_connection=utf8mb4;SET collation_connection=utf8mb4_unicode_ci;SET NAMES 'utf8mb4';

which is pretty specific on why is happening

[tylerhains]

Alternatively, if you execute a manual COMMIT after the set, the thread is freed from the lock and can pick up another read thread.

[ibelarouci]

I need solution please

[jermlinden]

fwiw I experienced this when I started the mysql client in safe mode and then sent a USE command, e.g.

` mysql -h mysql.server -u mysqluser -pthisisntmypassword --safe-updates Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 46 Server version: 5.7.36 (ProxySQL)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> USE the_datbase_i_want Database changed MySQL [the_datbase_i_want]> show tables; ERROR 9006 (Y0000): ProxySQL Error: connection is locked to hostgroup 0 but trying to reach hostgroup 63 `

vs

` mysql -h mysql.server -u mysqluser -pthisisntmypassword --safe-updates -D the_datbase_i_want Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 47 Server version: 5.7.36 (ProxySQL)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [the_datbase_i_want]> show tables; +------------------------------+ | Tables_in_the_datbase_i_want | +------------------------------+ | a_random_table | +------------------------------+ `

[renecannao]

@jermlinden : does this happens also with a mysql client? (You are using a mariadb client, not a mysql client).

Also, please check and share proxysql error log

[jermlinden]

same thing happens with mysql --version mysql Ver 8.0.32 for macos13.0 on x86_64 (Homebrew)

proxysql version is 2.4.2

2023-02-24 20:15:50 MySQL_Session.cpp:7401:unable_to_parse_set_statement(): [WARNING] Unable to parse unknown SET query from client 10.136.6.8:53755. Setting lock_hostgroup. Please report a bug for future enhancements:SET SQL_SAFE_UPDATES=1,SQL_SELECT_LIMIT=1000,MAX_JOIN_SIZE=1000000 2023-02-24 20:15:50 MySQL_Variables.cpp:320:validate_charset(): [WARNING] Server doesn't support collation (255) utf8mb4_0900_ai_ci. Replacing it with the configured default (33) utf8_general_ci. Client 10.136.6.8:53755

[renecannao]

@jermlinden : I created a new issue specifically for --safe-updates : #4137 . This is a regression bug. Thanks for the report.

[Hitesh-Agrawal]

Any update on this issue, we are still seeing this same behaviour in proxysql 2.5.5 ( without --safe-updates) , after using set mysql-set_query_lock_on_hostgroup=0; we are still seeing this issue. Update: It did work with proxysql : - 2.5.3 without the above settings (i.e mysql-set_query_lock_on_hostgroup=1 by default in 2.5.3).

[urbaman]

I can confirm seeing this with mysql-set_query_lock_on_hostgroup=1, using mariadb driver to mariadb 11.0.3, in some clients (cloudbeaver). Had to set mysql-set_query_lock_on_hostgroup=0.

[yuriy-zhilovets2]

The same with the Perl DBD::Mariadb driver:

[WARNING] Unable to parse unknown SET query from client 127.0.0.1:60910. Setting lock_hostgroup. Please report a bug for future enhancements:SET character_set_server = 'utf8mb4'

[Sushovan55]

Getting the same error again and again for multiple flows. using proxysql_2.0.10-debian9_amd64 hostgroup numbers are changing time to time along with the query. Means the issue happening for any hostgroups and with any query

"log": "2024-01-30T17:33:04.461Z {\"level\": \"error\", \"message\": \"Global Error Handler\", \"data\": undefined, \"error\": {\"message\":\"ProxySQL Error: connection is locked to hostgroup 22 but trying to reach hostgroup 3\",\"errno\":9006,\"sql\":\"/undefined/\n\n SELECT c.t AS token, CAST(AES_DECRYPT(d.mn, '*****') AS CHAR) AS mobile FROM schema_name.c\n LEFT OUTER JOIN schema_name.d on d.c_id = c.id\n INNER JOIN schema_name.cl ON c.c_id = cl.id\n WHERE c.id = ? AND c.s = 1 AND cl.cl_name = ?\",\"sqlState\":\"Y0000\",\"sqlMessage\":\"ProxySQL Error: connection is locked to hostgroup 22 but trying to reach hostgroup 3\",\"name\":\"Error\",\"stack\":\"Error: ProxySQL Error: connection is locked to hostgroup 22 but trying to reach hostgroup 3\n at PromiseConnection.execute (/usr/app/node_modules/mysql2/promise.js:111:22)\n at /usr/app/build/libs/db-manager.js:67:52\n at Generator.next ()\n at fulfilled (/usr/app/build/libs/db-manager.js:28:58)\n at propagateAslWrapper (/usr/app/node_modules/async-listener/index.js:504:23)\n at /usr/app/node_modules/async-listener/glue.js:188:31\n at /usr/app/node_modules/async-listener/index.js:541:70\n at /usr/app/node_modules/async-listener/glue.js:188:31\n at runMicrotasks ()\n at processTicksAndRejections (internal/process/task_queues.js:93:5)\"} }"

[ByJacob]

Probably we should wait for 2.6.0, when release new set algorithm - https://github.com/sysown/proxysql/issues/4137