Proxysql: [WARNING] Unable to parse unknown SET query after upgrade to 2.0.6

Created on 19 Aug 2019  路  22Comments  路  Source: sysown/proxysql

RHEL 8 -> ProxySQL 2.0.5 -> 2.0.61
RHEL 7 -> Master MySQL 5.7.27

After the update from proxysql-2.0.5-1 to proxysql-2.0.6-1, the log file is filling up with :

2019-08-17 14:53:27 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 character_set_results = NULL
etc.

2019-08-17 14:53:38 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 SQL_SELECT_LIMIT=5
2019-08-17 14:53:39 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 SQL_SELECT_LIMIT=DEFAULT

On my master it didn't stop creating new threads, so I downgraded again to 2.0.5-1.

picture nielsalkema
👍3

Most helpful comment

@NielsA try to add rule:

INSERT INTO mysql_query_rules (rule_id, active, match_digest, multiplex) VALUES
(12, 1, '^SET character_set_results', 1),
(13, 1, 'SET SQL_SELECT_LIMIT=', 1),
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

That did the trick for me (except that the , should be a ; just before the load ofcourse). Symptoms that triggered me was an application that couldn't get data through ProxySQL after the upgrade, found the same lines in my proxysql logging.

picture Calypso971 on 19 Aug 2019
👍2

All 22 comments

Same issue here since upgraded to ProxySQL 2.0.6

CentOS7 -> ProxySQL version 2.0.6-73-gc746bf7, codename Truls
CentOS7 -> Server version: 5.7.25-28-57-log Percona XtraDB Cluster

2019-08-19 09:35: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: SET @@session.group_concat_max_len = 8128
2019-08-19 09:38:30 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 collation_connection = 'utf8mb4_unicode_ci';

Also this error is followed up during query:

#9006 - ProxySQL Error: connection is locked to hostgroup 10 but trying to reach hostgroup 11

Strange things, that only block MySQL Workbench or any other tools like this one.

@NielsA , how did you manage the downgrade to 2.0.5 without loosing tables/rules on proxysql side?

joachimjusth picture joachimjusth on 19 Aug 2019

@NielsA try to add rule:

INSERT INTO mysql_query_rules (rule_id, active, match_digest, multiplex) VALUES
(12, 1, '^SET character_set_results', 1),
(13, 1, 'SET SQL_SELECT_LIMIT=', 1),
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
yakirgb picture yakirgb on 19 Aug 2019

@NielsA try to add rule:

INSERT INTO mysql_query_rules (rule_id, active, match_digest, multiplex) VALUES
(12, 1, '^SET character_set_results', 1),
(13, 1, 'SET SQL_SELECT_LIMIT=', 1),
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

Thanks for the tip I can only try it tomorrow and will let you know.

@joachimjusth I had to reload the rules after downgrading.

nielsalkema picture nielsalkema on 19 Aug 2019
👍2

@NielsA try to add rule:

INSERT INTO mysql_query_rules (rule_id, active, match_digest, multiplex) VALUES
(12, 1, '^SET character_set_results', 1),
(13, 1, 'SET SQL_SELECT_LIMIT=', 1),
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

That did the trick for me (except that the , should be a ; just before the load ofcourse). Symptoms that triggered me was an application that couldn't get data through ProxySQL after the upgrade, found the same lines in my proxysql logging.

Calypso971 picture Calypso971 on 19 Aug 2019
👍2

@yakirgb : thank you for the help!

@NielsA , @joachimjusth , @Calypso971 : ProxySQL 2.0.6 introduces a new algorithm to disable multiplexing and routing if unable to parse a SET statement.
This is a safer and more conservative approach than previous versions.
What @yakirgb suggested is a way to instruct proxysql to not disable multiplexing and routing for the specified SET statements.
As error log says, Please report a bug for future enhancements : these cases will be handled out of the box in future releases, many of them since the next one (2.0.7).

Thanks

renecannao picture renecannao on 19 Aug 2019

Hello,

Some feedback regarding this issue.
Since our application routinely uses some of the queries hit by this warning, it produces a log spam of warnings, and those keep showing up with the query rules from above, taking up a lot of space.

In our case it's a slightly different warning (multi-statement).

Our warnings:

2019-09-20 12:16:27 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 innodb_lock_wait_timeout=2;
2019-09-20 12:16:27 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 TRANSACTION ISOLATION LEVEL READ COMMITTED;

Rules:

INSERT INTO mysql_query_rules (rule_id, active, match_digest, multiplex, apply) VALUES
(9000001, 1, '^SET SESSION', 1, 1);
INSERT INTO mysql_query_rules (rule_id, active, match_digest, multiplex, apply) VALUES
(9000002, 1, '^set transaction isolation level', 1, 1);
INSERT INTO mysql_query_rules (rule_id, active, match_digest, multiplex, apply) VALUES
(9000003, 1, '^SET innodb_lock_wait_timeout', 1, 1);

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

Tried with the different values of multiplex too, so I guess the warning isn't supposed to go away.
So we will have to downgrade for the time being.

proxysql --version
ProxySQL version 2.0.6-73-gc746bf7, codename Truls
gAdrev picture gAdrev on 20 Sep 2019

Some errors after upgrading to : ProxySQL version 2.0.7-80-g4dd4ef5, codename Truls

2019-10-04 09:00:02 MySQL_Session.cpp:6929:unable_to_parse_set_statement(): [WARNING] Unable to parse unknown SET query. Setting lock_hostgroup. Please report a bug for future enhancements:SET @@session.wait_timeout = 90

2019-10-04 09:22:53 MySQL_Session.cpp:2914:handler_again___status_CHANGING_CHARSET(): [ERROR] Detected a broken connection during SET NAMES on tstmysql0.foo.ch , 3306 : 2019, Can't initialize character set (null) (path: compiled_in)
2019-10-04 09:22:53 MySQL_Session.cpp:6929:unable_to_parse_set_statement(): [WARNING] Unable to parse unknown SET query. Setting lock_hostgroup. Please report a bug for future enhancements:SET CHARACTER SET utf8
Can't initialize character set (null) (path: compiled_in)
2019-10-04 09:22:53 MySQL_Session.cpp:2914:handler_again___status_CHANGING_CHARSET(): [ERROR] Detected a broken connection during SET NAMES on tstmysql0.foo.ch , 3306 : 2019, Can't initialize character set (null) (path: compiled_in)
2019-10-04 09:22:53 MySQL_Session.cpp:6929:unable_to_parse_set_statement(): [WARNING] Unable to parse unknown SET query. Setting lock_hostgroup. Please report a bug for future enhancements:SET CHARACTER SET utf8
nielsalkema picture nielsalkema on 4 Oct 2019

Some errors after upgrading to : ProxySQL version 2.0.7-80-g4dd4ef5, codename Truls

2019-10-04 09:00:02 MySQL_Session.cpp:6929:unable_to_parse_set_statement(): [WARNING] Unable to parse unknown SET query. Setting lock_hostgroup. Please report a bug for future enhancements:SET @@session.wait_timeout = 90

2019-10-04 09:22:53 MySQL_Session.cpp:2914:handler_again___status_CHANGING_CHARSET(): [ERROR] Detected a broken connection during SET NAMES on tstmysql0.foo.ch , 3306 : 2019, Can't initialize character set (null) (path: compiled_in)
2019-10-04 09:22:53 MySQL_Session.cpp:6929:unable_to_parse_set_statement(): [WARNING] Unable to parse unknown SET query. Setting lock_hostgroup. Please report a bug for future enhancements:SET CHARACTER SET utf8
Can't initialize character set (null) (path: compiled_in)
2019-10-04 09:22:53 MySQL_Session.cpp:2914:handler_again___status_CHANGING_CHARSET(): [ERROR] Detected a broken connection during SET NAMES on tstmysql0.foo.ch , 3306 : 2019, Can't initialize character set (null) (path: compiled_in)
2019-10-04 09:22:53 MySQL_Session.cpp:6929:unable_to_parse_set_statement(): [WARNING] Unable to parse unknown SET query. Setting lock_hostgroup. Please report a bug for future enhancements:SET CHARACTER SET utf8

I am also a similar mistake.

2019-10-10 11:35:09 MySQL_Session.cpp:4153:handler(): [WARNING] Error during query on (10,172.18.1.156,3206): 1267, Illegal mix of collations (utf8mb4_0900_as_cs,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='
2019-10-10 11:35:13 MySQL_Session.cpp:4153:handler(): [WARNING] Error during query on (30,172.18.1.157,3206): 1146, Table 'mysql.proc' doesn't exist
2019-10-10 11:35:17 MySQL_Session.cpp:6929:unable_to_parse_set_statement(): [WARNING] Unable to parse unknown SET query. Setting lock_hostgroup. Please report a bug for future enhancements:SET PROFILING = 1

5sdba picture 5sdba on 10 Oct 2019

Hi all!
With version 2.0.8-67-g877cab1
In my case, I getting this error:
MySQL_Session.cpp:5370: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 @max_dimension = '', @med_dimension = '', @min_dimension = '';

This come from this php code:
$dbAdapter->query("SET @max_dimension = '', @med_dimension = '', @min_dimension = '';");

I tried with version 2.0.5 and runs without issues.

101Artur picture 101Artur on 3 Dec 2019

A few more errors : proxysql-2.0.8-1.x86_64

2020-01-13 17:09:26 MySQL_Session.cpp:7033:unable_to_parse_set_statement(): [WARNING] Unable to parse unknown SET query from client 10.2.16.23:53926. Setting lock_hostgroup. Please report a bug for future enhancements:/*!80000 SET SESSION information_schema_stats_expiry=0 */
2020-01-16 06:12:06 MySQL_Session.cpp:7033:unable_to_parse_set_statement(): [WARNING] Unable to parse unknown SET query from client 10.2.28.158:33004. Setting lock_hostgroup. Please report a bug for future enhancements:set optimizer_switch='semijoin=off'

nielsalkema picture nielsalkema on 16 Jan 2020

@renecannao when this will be fixed? Are you planning to fix this in future releases? Due to this issue we could not able to upgrade to 2.0.10 .

umagmrit picture umagmrit on 11 Mar 2020

@umagmrit : what is your issue?

For a lot of the above (if not all) I would say that this behavior is not a bug.
ProxySQL is safer now than before.
You can disable the algorithm and let proxysql run as in 2.0.5 , but it is less safe.
The other option is to configure query rules, like suggested by @yakirgb previously.

renecannao picture renecannao on 11 Mar 2020

@renecannao After set mysql-set_query_lock_on_hostgroup=0, working fine but getting warnings like not able to parse SET statements Please report a bug for future enhancements.

umagmrit picture umagmrit on 11 Mar 2020

Cool, that is working fine that is good.
Now, depending from the query time, you can either let proxysql disable multiplexing (that is what it does by default), or create rules like @yakirgb explained. But you need to make sure it makes sense to re-enable multiplexing.

renecannao picture renecannao on 11 Mar 2020

@renecannao I cannot create rules like @yakirgb explained. because my proxysql configured with two galera clusters and queries will route by my own query rules. If I use one galera cluster and default query rules I can simply create rules explained by @yakirgb.Please see my proxysql configuration.

mysql_servers = ( { address="zmc-galera-0-0.zmc-galera-0" , port=3306 , hostgroup=10, max_connections=100 }, { address="zmc-galera-0-1.zmc-galera-0" , port=3306 , hostgroup=10, max_connections=100 }, { address="zmc-galera-0-2.zmc-galera-0" , port=3306 , hostgroup=10, max_connections=100 }, { address="zmc-galera-1-0.zmc-galera-1" , port=3306 , hostgroup=100, max_connections=100 }, { address="zmc-galera-1-1.zmc-galera-1" , port=3306 , hostgroup=100, max_connections=100 }, { address="zmc-galera-1-2.zmc-galera-1" , port=3306 , hostgroup=100, max_connections=100 } )
`mysql_query_rules =
(
{
rule_id=10
active=1
match_pattern="^INSERT {1,}INTO {1,}\bmboxgroup1\b.|^UPDATE {1,}\bmboxgroup1\b.|^REPLACE {1,}INTO {1,}\bmboxgroup1\b.|^DELETE..FROM {1,}\bmboxgroup1\b.|^CREATE..\bmboxgroup1\b."
destination_hostgroup=10
log=1
apply=1
},
{
rule_id=11
active=1
match_pattern="^SELECT..FROM {1,}\bmboxgroup1\b.|^SELECT..FROM . WHERE {1,}schema_name {0,}= {0,}'mboxgroup1'"
destination_hostgroup=30
log=1
apply=1
},

    {
    rule_id=12
    active=1
    match_pattern="^INSERT {1,}INTO {1,}\bmboxgroup2\b.*|^UPDATE {1,}\bmboxgroup2\b.*|^REPLACE {1,}INTO {1,}\bmboxgroup2\b.*|^DELETE.*.FROM {1,}\bmboxgroup2\b.*|^CREATE.*.\bmboxgroup2\b.*|^mboxgroup2 {1,}"
    destination_hostgroup=100
    log=1
    apply=1
},
{
    rule_id=13
    active=1
    match_pattern="^SELECT.*.FROM {1,}\bmboxgroup2\b.*|^SELECT.*.FROM *.* WHERE {1,}schema_name {0,}= {0,}'mboxgroup2'"
    destination_hostgroup=300
    log=1
    apply=1
}

)
`

umagmrit picture umagmrit on 11 Mar 2020

It is not clear to me:
a) why you say you can't create more rules
b) what are the SET statements that cause hostgroup locking

You should start from point B

renecannao picture renecannao on 11 Mar 2020

@renecannao The below SET statements are cause hostgroup locking.
SET SQL_QUOTE_SHOW_CREATE=1
set optimizer_switch='semijoin=off'
set optimizer_switch=default
SET FOREIGN_KEY_CHECKS=0

I can create rules for above SET statements but my doubt is that, rules for above SET statements hit which hostgroup. I am using two galera clusters in proxySQL, so there will two write host groups and two read host groups.

umagmrit picture umagmrit on 11 Mar 2020

SQL_QUOTE_SHOW_CREATE=1 : this is the default, maybe you can just create a rule to return OK.
FOREIGN_KEY_CHECKS=0 : for this, you can create a rule as suggested by @yakirgb . Note, proxysql will disable multiplexing anyway, but it won't lock on hostgroup.

set optimizer_switch : this is a bit ore complex.
Do you really need to change this setting? Why the application is changing back and forth?

renecannao picture renecannao on 11 Mar 2020

The optimizer_switch issue just snagged me as well. This is a somewhat problematic scenario - the current default handling of this totally makes sense and is "safe". However, consider the scenario where there is read-write splitting and proxysql locks on to a read-only slave - any manipulation queries will fail.

I do not think it is reasonable to expect proxysql to track state of all the various "set"tings which can alter connection state - in some super-common cases like "set names" it does perhaps.

It would be really nice here I think if we could specify at the user-level what connection state we want. I am not sure what that might look like - perhaps something as simple as having a column in mysql_users called state_commands or something along those lines where we could specify what we want for optimizer_switch or foreign_key_checks or any of the numerous settings which exist for a connection. Queries/settings stored here would not need to be identified/handled by proxysql since they would be applied uniformly for all connections for that user.

Anyways, just an idea - it just seems like there are no good workarounds for a case where you want read-write splitting but also want specific settings always applied at user-level (i.e. like optimizer_switch and/or numerous others) - but perhaps there is and I am just missing it. Obviously, we can go change the code to do stuff like send all writes over a different connection, but its nice that proxysql mostly does not require this.

caffeinejolt picture caffeinejolt on 25 May 2020

Just upgraded from 1.4.12 to 2.0.12 and seeing similar logs when running tests. 

Unable to parse multi-statements command with SET statement: setting lock hostgroup . Command: SET FOREIGN_KEY_CHECKS=0;

I'm happy that this is working as expected, but is it possible to make the logs less verbose?

bryanhorstmann picture bryanhorstmann on 10 Jun 2020

Moving first steps with ProxySQL today and I came across the same issue

2020-07-16 11:18:23 MySQL_Session.cpp:6536:unable_to_parse_set_statement(): [WARNING] Unable to parse unknown SET query from client 192.168.0.51:47154. Setting lock_hostgroup. Please report a bug for future enhancements:SET FOREIGN_KEY_CHECKS=0

with version 2.0.13 during some tests.

Creating a rule as suggested fixed the warning, but I had a lot of " Duplicate entry " errors on queries right after the SET FOREIGN_KEY_CHECKS=0 statement (this is due to poor application design I guess, but never happened on a standalone MySQL) , so I assume the "SET FOREIGN_KEY_CHECKS=0" is actually not executed on the node ?

gennaris picture gennaris on 16 Jul 2020
👍1

Moving first steps with ProxySQL today and I came across the same issue

2020-07-16 11:18:23 MySQL_Session.cpp:6536:unable_to_parse_set_statement(): [WARNING] Unable to parse unknown SET query from client 192.168.0.51:47154. Setting lock_hostgroup. Please report a bug for future enhancements:SET FOREIGN_KEY_CHECKS=0

with version 2.0.13 during some tests.

Creating a rule as suggested fixed the warning, but I had a lot of " Duplicate entry " errors on queries right after the SET FOREIGN_KEY_CHECKS=0 statement (this is due to poor application design I guess, but never happened on a standalone MySQL) , so I assume the "SET FOREIGN_KEY_CHECKS=0" is actually not executed on the node ?

me too, same error, with 2.0.13:

2020-07-22 11:26:08 MySQL_Session.cpp:6536:unable_to_parse_set_statement(): [WARNING] Unable to parse unknown SET query from client 127.0.0.1:61186. Setting lock_hostgroup. Please report a bug for future enhancements:SET FOREIGN_KEY_CHECKS=0
2020-07-22 11:26:08 MySQL_Session.cpp:6536:unable_to_parse_set_statement(): [WARNING] Unable to parse unknown SET query from client 127.0.0.1:61268. Setting lock_hostgroup. Please report a bug for future enhancements:SET FOREIGN_KEY_CHECKS=0
2020-07-22 11:26:09 MySQL_Session.cpp:6536:unable_to_parse_set_statement(): [WARNING] Unable to parse unknown SET query from client 127.0.0.1:61410. Setting lock_hostgroup. Please report a bug for future enhancements:SET FOREIGN_KEY_CHECKS=0
rickytato picture rickytato on 22 Jul 2020
Was this page helpful?
0 / 5 - 0 ratings

Related issues

lazzyfu picture lazzyfu  路  19Comments
ghost picture ghost  路  20Comments
ethaniel picture ethaniel  路  18Comments
asharpaev picture asharpaev  路  15Comments
izzyquestion picture izzyquestion  路  15Comments