Proxysql: SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED

Created on 16 Aug 2019 · 10Comments · Source: sysown/proxysql

We're running nextcloud on top of proxysql. After updating from proxysql 2.0.5 to 2.0.6 nextcloud just returns status code 503 and proxysql log is full of

/ecs/proxysql ecs/proxysql/6d3d5101-290a-483e-aa99-df8e0909fb1c 2019-08-16 12:15:58 MySQL_Session.cpp:5094:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Setting lock_hostgroup. Please report a bug for future enhancements:SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
/ecs/proxysql ecs/proxysql/6d3d5101-290a-483e-aa99-df8e0909fb1c 2019-08-16 12:15:59 MySQL_Session.cpp:5094:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Setting lock_hostgroup. Please report a bug for future enhancements:SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
/ecs/proxysql ecs/proxysql/6d3d5101-290a-483e-aa99-df8e0909fb1c 2019-08-16 12:16:00 MySQL_Session.cpp:5094:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Setting lock_hostgroup. Please report a bug for future enhancements:SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
/ecs/proxysql ecs/proxysql/32630f59-ede8-4637-a6e6-e3fe939e63d8 2019-08-16 12:16:03 MySQL_Session.cpp:5094:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Setting lock_hostgroup. Please report a bug for future enhancements:SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
/ecs/proxysql ecs/proxysql/32630f59-ede8-4637-a6e6-e3fe939e63d8 2019-08-16 12:16:04 MySQL_Session.cpp:5094:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Setting lock_hostgroup. Please report a bug for future enhancements:SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED

the interesting log message from nextcloud is:

"SQLSTATE[Y0000]: <<Unknown error>>: 9006 ProxySQL Error: connection is locked to hostgroup 0 but trying to reach hostgroup 1"

hostgroup 0 is our write. hostgroup 1 are two readers and also the writer. All backend servers were online.

MySQL [(none)]> select * from runtime_mysql_servers
    -> ;
+--------------+-----------------------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+----------------------------------+
| hostgroup_id | hostname                                                        | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment                          |
+--------------+-----------------------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+----------------------------------+
| 0            | db-div.cluster-cjvvmqw9rhcc.eu-central-1.rds.amazonaws.com      | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              | aurora writer endpoint           |
| 1            | db-div.cluster-ro-cjvvmqw9rhcc.eu-central-1.rds.amazonaws.com   | 3306 | 0         | ONLINE | 100    | 0           | 1000            | 1                   | 0       | 250            | aurora reader endpoint           |
| 1            | db-div-read-replica.cjvvmqw9rhcc.eu-central-1.rds.amazonaws.com | 3306 | 0         | ONLINE | 100    | 0           | 1000            | 1                   | 0       | 250            | rds read replica                 |
| 1            | db-div.cluster-cjvvmqw9rhcc.eu-central-1.rds.amazonaws.com      | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              | aurora writer endpoint as reader |
+--------------+-----------------------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+----------------------------------+

we got just a few query rules for read/write split

    #####################################
    # read / write routing
    # write -> hostgroup 0
    # read -> hostgroup 1
    #####################################
    - name: read split
      proxysql_query_rules:
        login_user: "{{ proxy_sql_admin_user }}"
        login_password: "{{ proxy_sql_admin_password }}"
        match_pattern: "^SELECT"
        destination_hostgroup: "{{ hostgroup_reader }}"
        active: 1
        retries: 3
        state: present
        load_to_runtime: no

    - name: write split
      proxysql_query_rules:
        login_user: "{{ proxy_sql_admin_user }}"
        login_password: "{{ proxy_sql_admin_password }}"
        match_pattern: "^SELECT.*FOR UPDATE$"
        destination_hostgroup: "{{ hostgroup_writer }}"
        active: 1
        retries: 3
        state: present
        load_to_runtime: no

    - name: nextcloud 16 fix
      proxysql_query_rules:
        login_user: "{{ proxy_sql_admin_user }}"
        login_password: "{{ proxy_sql_admin_password }}"
        match_digest: "CREATE.*"
        match_pattern: "ROW_FORMAT = compressed"
        replace_pattern: " "
        destination_hostgroup: "{{ hostgroup_writer }}"
        active: 1
        retries: 3
        state: present
        load_to_runtime: no

    - name: nextcloud 16 fix
      proxysql_query_rules:
        login_user: "{{ proxy_sql_admin_user }}"
        login_password: "{{ proxy_sql_admin_password }}"
        match_digest: "CREATE.*"
        match_pattern: "ROW_FORMAT=compressed"
        replace_pattern: " "
        destination_hostgroup: "{{ hostgroup_writer }}"
        active: 1
        retries: 3
        state: present
        load_to_runtime: no

    - name: load everything to runtime
      proxysql_manage_config:
        login_user: "{{ proxy_sql_admin_user }}"
        login_password: "{{ proxy_sql_admin_password }}"
        action: "LOAD"
        config_settings: "{{ item }}"
        direction: "TO"
        config_layer: "RUNTIME"
      with_items:
        - MYSQL USERS
        - MYSQL SERVERS
        - MYSQL VARIABLES
        - MYSQL QUERY RULES
        - ADMIN VARIABLES

maybe something is wrong with this release changelog

* If SET statement is used in multi-statements commands or if parsing of SET statement it is not successful, both multiplexing and query routing is disabled. The client remains bind to a single backend connections.

ProxySQL version 2.0.6-73-gc746bf7f, codename Truls is running in a docker container using ubuntu:18.04

Source

markuman

Most helpful comment

Hi I am using v2.0.12 and still have this issue:

2020-07-02 11:13:24 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 SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; INSERT INTODistributedLock(Resource, CreatedAt) SELECT 'locks:schedulepoller', timestamp('2020-07-02 09:13:24.185814') FROM dual WHERE NOT EXISTS ( SELECT * FROMDistributedLockWHERE Resource = 'locks:schedulepoller' AND CreatedAt > timestamp('2020-07-02 09:12:24.185814'));

ipAnything on 2 Jul 2020

👍2

All 10 comments

Thank you for the report.
As a temporary workaround, create a rule like this one:

INSERT INTO mysql_query_rules (rule_id, active, match_digest, multiplex) VALUES
(1, 1, '^SET SESSION TRANSACTION ISOLATION', 1),

Thanks

renecannao on 16 Aug 2019

👍1

I notices a lot of similar SET warnings with 2.0.6 too. E.g. when dumping from a db and pipe into proxysql

2019-08-20 08:37:07 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET @saved_cs_client     = @@character_set_client */
2019-08-20 08:37:07 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET character_set_client = utf8 */
2019-08-20 08:37:08 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET character_set_client = @saved_cs_client */
2019-08-20 08:37:08 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET @saved_cs_client     = @@character_set_client */
2019-08-20 08:37:08 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET character_set_client = utf8 */
2019-08-20 08:37:08 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET character_set_client = @saved_cs_client */
2019-08-20 08:37:09 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET @saved_cs_client     = @@character_set_client */
2019-08-20 08:37:09 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET character_set_client = utf8 */
2019-08-20 08:37:09 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET character_set_client = @saved_cs_client */
2019-08-20 08:37:10 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET @saved_cs_client     = @@character_set_client */
2019-08-20 08:37:10 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET character_set_client = utf8 */
2019-08-20 08:37:10 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET character_set_client = @saved_cs_client */
2019-08-20 08:37:10 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET @saved_cs_client     = @@character_set_client */
2019-08-20 08:37:10 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET character_set_client = utf8 */
2019-08-20 08:37:11 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET character_set_client = @saved_cs_client */
2019-08-20 08:37:11 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET @saved_cs_client     = @@character_set_client */
2019-08-20 08:37:11 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET character_set_client = utf8 */
2019-08-20 08:37:12 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET character_set_client = @saved_cs_client */
2019-08-20 08:37:12 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET @saved_cs_client     = @@character_set_client */
2019-08-20 08:37:12 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET character_set_client = utf8 */
2019-08-20 08:37:12 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET character_set_client = @saved_cs_client */
2019-08-20 08:37:13 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET @saved_cs_client     = @@character_set_client */
2019-08-20 08:37:13 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET character_set_client = utf8 */
2019-08-20 08:37:13 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET character_set_client = @saved_cs_client */
2019-08-20 08:37:14 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET @saved_cs_client     = @@character_set_client */
2019-08-20 08:37:14 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET character_set_client = utf8 */
2019-08-20 08:37:14 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET character_set_client = @saved_cs_client */
2019-08-20 08:37:15 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET @saved_cs_client     = @@character_set_client */
2019-08-20 08:37:15 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET character_set_client = utf8 */
2019-08-20 08:37:15 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET character_set_client = @saved_cs_client */
2019-08-20 08:37:16 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET @saved_cs_client     = @@character_set_client */
2019-08-20 08:37:16 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET character_set_client = utf8 */
2019-08-20 08:37:16 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET character_set_client = @saved_cs_client */
2019-08-20 08:37:17 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET @saved_cs_client     = @@character_set_client */
2019-08-20 08:37:17 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET character_set_client = utf8 */
2019-08-20 08:37:17 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET character_set_client = @saved_cs_client */
2019-08-20 08:37:18 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET @saved_cs_client     = @@character_set_client */
2019-08-20 08:37:18 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET character_set_client = utf8 */
2019-08-20 08:37:18 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET character_set_client = @saved_cs_client */
2019-08-20 08:37:19 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET @saved_cs_client     = @@character_set_client */
2019-08-20 08:37:19 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET character_set_client = utf8 */
2019-08-20 08:37:19 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET character_set_client = @saved_cs_client */
2019-08-20 08:37:20 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET @saved_cs_client     = @@character_set_client */
2019-08-20 08:37:20 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET character_set_client = utf8 */
2019-08-20 08:37:20 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET character_set_client = @saved_cs_client */
2019-08-20 08:37:21 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET @saved_cs_client     = @@character_set_client */
2019-08-20 08:37:21 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET character_set_client = utf8 */
2019-08-20 08:37:21 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET character_set_client = @saved_cs_client */
2019-08-20 08:37:22 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET @saved_cs_client     = @@character_set_client */
2019-08-20 08:37:22 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET character_set_client = utf8 */
2019-08-20 08:37:22 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET character_set_client = @saved_cs_client */
2019-08-20 08:37:23 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET @saved_cs_client     = @@character_set_client */
2019-08-20 08:37:23 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET character_set_client = utf8 */
2019-08-20 08:37:23 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET character_set_client = @saved_cs_client */
2019-08-20 08:37:24 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET @saved_cs_client     = @@character_set_client */
2019-08-20 08:37:24 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET character_set_client = utf8 */
2019-08-20 08:37:24 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET character_set_client = @saved_cs_client */
2019-08-20 08:37:24 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET @saved_cs_client     = @@character_set_client */
2019-08-20 08:37:24 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET character_set_client = utf8 */
2019-08-20 08:37:25 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET character_set_client = @saved_cs_client */
2019-08-20 08:37:25 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET @saved_cs_client     = @@character_set_client */
2019-08-20 08:37:25 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET character_set_client = utf8 */
2019-08-20 08:37:25 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET character_set_client = @saved_cs_client */
2019-08-20 08:38:30 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */
2019-08-20 08:38:30 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */
2019-08-20 08:38:30 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */
2019-08-20 08:38:30 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */
2019-08-20 08:38:30 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */
2019-08-20 08:38:30 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */

markuman on 20 Aug 2019

related and explained in https://github.com/sysown/proxysql/issues/2202#issuecomment-522513844

markuman on 20 Aug 2019

2019-08-26 12:56:54 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: SET profiling_history_size = 0
2019-08-26 12:56:54 MySQL_Session.cpp:5102:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [WARNING] Unable to parse unknown SET query. Not setting lock_hostgroup because already set. Please report a bug for future enhancements: SET profiling_history_size = 15

i add a rule:

INSERT INTO mysql_query_rules (rule_id, active, match_digest, multiplex,destination_hostgroup) VALUES(103, 1, '^SET session profiling_history_size', 1,30);
[email protected][(none)] >select rule_id,active,match_digest,destination_hostgroup,multiplex from runtime_mysql_query_rules;
+---------+--------+-------------------------------------+-----------------------+-----------+
| rule_id | active | match_digest | destination_hostgroup | multiplex |
+---------+--------+-------------------------------------+-----------------------+-----------+
| 100 | 1 | .@. | 10 | NULL |
| 101 | 1 | ^SELECT.*FOR UPDATE$ | 10 | NULL |
| 102 | 1 | ^SELECT | 30 | NULL |
| 103 | 1 | ^SET session profiling_history_size | 30 | 1 |

[email protected][(none)] >select * from runtime_mysql_servers;
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 30 | 172.18.1.157 | 3206 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | cluster |
| 10 | 172.18.1.156 | 3206 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | cluster |
| 30 | 172.18.1.158 | 3206 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | cluster |
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
alse not ok

5sdba on 26 Aug 2019

Thank you for the report.
As a temporary workaround, create a rule like this one:
INSERT INTO mysql_query_rules (rule_id, active, match_digest, multiplex) VALUES
(1, 1, '^SET SESSION TRANSACTION ISOLATION', 1),
Thanks

I've also the error with:

2019-08-27 11:57:05 MySQL_Session.cpp:4720: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 @@SESSION.sql_mode = '';

I've also tried to add a query rule for all "^SET " but, I still have the problem

decanet on 27 Aug 2019

I've also the error with:

2019-08-27 11:57:05 MySQL_Session.cpp:4720: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 @@SESSION.sql_mode = '';

I've also tried to add a query rule for all "^SET " but, I still have the problem

We have the exact same problem. tested the following and it didn't help:

SET @@SESSION.sql_mode
SET @@SESSION\.sql_mode
^SET @@SESSION\.sql_mode
^SET.*sql_mode
SET.*sql_mode

we temporarily fixed this by commenting out the query from the mysqli library we are using and setting the sql_mode globally

 SET @@global.sql_mode = '';

But this is not the real solution for this problem.
I hope we get the right solution soon!

tkopic on 30 Aug 2019

We decided to downgrade to 2.0.5
as soon as we fix one query another shows up and messes everything up
it's not worth the hassle

tkopic on 30 Aug 2019

closing, because SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED works with 2.0.7 now.

markuman on 20 Oct 2019

Hi I am using v2.0.12 and still have this issue:

ipAnything on 2 Jul 2020

👍2

Hi, we are using ProxySQL version 2.0.15-20-g32bb92c, codename Truls but a similar issue exists.
MySQL_Session.cpp:6595:unable_to_parse_set_statement(): [WARNING] Unable to parse unknown SET query from client abcdef. Setting lock_hostgroup. Please report a bug for future enhancements:SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

We applied one of the workarounds, it seems ok but still annoying. This bug should be re-opened.