Proxysql: Icinga2 -> ProxySQL -> MariaDB/Galera: MySQL_Session.cpp:3689 [ERROR] Unable to parse query

[xenuser]

Short issue description

ProxySQL throws an error on some of the connections coming from a specific DB client. As a consequence, some DB connections are not established.

Setup description

I configured the following setup:

Host A: Icinga 2.7.2, ProxySQL 1.4.4 (SLES 12 SP2)
Hosts B, C, D: MariaDB 10.2.9 + Galera (RHEL 7.3)

Icinga2 is connecting to localhost:6033 (ProxySQL) which then balances/forwards queries to Hosts B, C D.

Long issue description

When switching from "Icinga2 goes directly to MariaDB on Host B" to "Icinga2 connects to ProxySQL on localhost instead", some of the connections seem to be not established.

Looking at journalctl -f, I see many entries with the following error:

~~~

Dec 10 16:10:20 HOSTA proxysql[3468]: 2017-12-10 16:10:20 MySQL_Session.cpp:3689:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_QUERY_qpo(): [ERROR] Unable to parse query. If correct, report it as a bug: SET SESSION SQL_MODE='STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,ANSI_QUOTES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,ANSI_QUOTES,PIPES_AS_CONCAT,NO_ENGINE_SUBSTITUTION', NAMES utf8;

~~~

When running Icinga2 against ProxySQL, only some of the DB connections reach the three MariaDB/Galera nodes. As a consequence, Icinga2 starts spooling actions which should be runned against a DB. And, in addition, Icinga2 is not working as expected.

Workaround I tried

A co-worker of mine discovered the config variable "mysql-query_digests" which could affect the observed behavior. Our hope was that ProxySQL would stop parsing queries and act as a stupid proxy only until a solution could be found. After activating this setting with...

~~~

set mysql-query_digests = "FALSE";
save mysql variables to disk;
load mysql variables to runtime;

~~~~

... the errors were gone. However, still only few SQL queries went through.

Expected behavior

ProxySQL does not throw an error and makes sure all connections/queries will be handled successfully.

Questions

(a)
In your eyes, is the query I posted above incorrect or is this behavior related to an existing GitHub issue regarding that ProxySQL appends a ";" or something like that?

(b) Is there something I can do to workaround on this problem so I can continue using ProxySQL?

(c) Can you please explain what mysql-query_digests does and how deactivating this setting changes the behavior of ProxySQL?

[renecannao]

Hi @xenuser .
Thank you for reporting this bug.

a) ProxySQL doesn't recognize SET SESSION SQL_MODE=? , NAMES ? .
It does recognize SET SESSION SQL_MODE=? and SET NAMES ? , but not together.
This causes misbehaviors, thus is a bug.

b) Please wait 1.4.4 : I will develop a workaround for this issue: disable multiplexing for SET queries unable to process. The real solution would be to correctly parse it, but I don't want to delay the release of 1.4.4 even further

c) if mysql-query_digests is set to false, ProxySQL dooesn't try to interpret/parse the queries at all, disabling a lot of its features.
Please do no disable mysql-query_digests.

[xenuser]

@renecannao Thanks for the fast reply and also for your awesome work on ProxySQL!

a) Thanks for the explanation.
b) Oh, ok. Does this mean that ProxySQL will be able to handle this specific query in another way or will ProxySQL simply ignore it? I just want to estimate what this means for my use case (e.g. when the query will be thrown away, Icinga2 will not work, so I will have to wait for a fix).
When will 1.4.4 be ready for release? Just want to be prepared, so I don't have to replace ProxySQL with some other tool temporarily etc.
c) Thanks!

[renecannao]

@xenuser : you are welcome!

ProxySQL in branch 1.4.4 is now able to correctly handle SET SQL_MODE and NAMES in the same query.
Even SET SQL_MODE=? , NAMES ? COLLATE ? .
Official release should go out in few days.

[xenuser]

Wow, great!! Thanks a lot.