ProxySQL Error: connection is locked to hostgroup 1 but trying to reach hostgroup 2

[Geoff Maddock]

Friends -

I recently deployed a proxysql cluster ( proxysql/proxysql:2.3.2), and in general things are working well.  Except in one case where I find I'm getting an odd error for a specific user when doing SELECTs.

The database user being used by the connection is ` go_vmware`

An example query that I'm running directly against the proxysql instance:
SELECT * FROM `virtual_cluster`

Which returns
SELECT * FROM `virtual_cluster`
> 9006 - ProxySQL Error: connection is locked to hostgroup 1 but trying to reach hostgroup 2
> Time: 0.028s

My main question is what is locking the connection to hostgroup 1?

Based on the config (see below), it should use the rule to connect to hostgroup 2, and that should be the end of it.    It's not super clear what could lead to this case.

- Geoff


My proxysql.cnf looks like this:

datadir="/var/lib/proxysql"
 
admin_variables=
{
    refresh_interval=2000
    cluster_check_status_frequency=0
    restapi_enabled=true
    restapi_port=6070
    prometheus_memory_metrics_interval=61
}
 
mysql_variables=
{
    threads=4
    max_connections=2048
    default_query_delay=0
    default_query_timeout=36000000
    have_compress=true
    poll_timeout=2000
    interfaces="0.0.0.0:6033;/tmp/proxysql.sock"
    default_schema="information_schema"
    stacksize=1048576
    connect_timeout_server=10000
    monitor_history=60000
    monitor_connect_interval=200000
    monitor_ping_interval=200000
    ping_interval_server_msec=10000
    ping_timeout_server=200
    commands_stats=true
    sessions_sort=true
    monitor_username="proxysql"
    monitor_password="nnhcedahJz3J!Vmxm%"
    auditlog_filename="proxysql_auditlog"
    log_unhealthy_connections=false
}

mysql_query_rules =
(
    { rule_id=1, active=1, match_digest="^SELECT.*FOR UPDATE$", destination_hostgroup=1, apply=1 },
    { rule_id=2, active=1, match_digest="^SELECT", destination_hostgroup=2, apply=1 },
)
 
mysql_servers =
(
    { address="acm-db-aux-1.ldap.mydomain.local" , port=3306 , hostgroup_id=2 , max_connections=100 },
)
 
mysql_replication_hostgroups =
(
    { writer_hostgroup=1, reader_hostgroup=2, check_type="read_only" },
)

mysql_users =
(
    { username = "go_vmware", password = "password", default_hostgroup = 1, transaction_persistent = 1, active = 1 },
    { username = "report", password = "password", default_hostgroup = 2, transaction_persistent = 0, active = 1 },
)

[René Cannaò]

> My main question is what is locking the connection to hostgroup 1?

Have you checked the error log?

--
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 on the web visit https://groups.google.com/d/msgid/proxysql/CAOJqdZFzyDYs036bpkzRV3EGMT8531ZvtM%3Dei%3DVvk6RgK3ifwg%40mail.gmail.com.

[Geoff Maddock]

Taking a look in the logs, I'm seeing this:

2021-10-21 18:44:13 MySQL_Session.cpp:7093:unable_to_parse_set_statement(): [WARNING] Unable to parse unknown SET query from client 10.42.5.60:59752. Setting lock_hostgroup. Please report a bug for future enhancements:SET PROFILING = 1

Not clear on what would be doing the SET query.   I am making my sql select from inside of a navicat client - maybe this is doing some kind of set?

- Geoff 

[René Cannaò]

Navicat is likely to be the one executing that query.

You can create a query rule to completely filter that SET query.

INSERT INTO mysql_query_rules (active, match_digest,OK_msg) VALUES (1,'^SET PROFILING','');

LOAD MYSQL QUERY RULES TO RUNTIME;

SAVE MYSQL QUERY RULES TO DISK;