[mysql] MySQL with ProxySQL in front reports errors and stalls

[neoeinstein]

We have a ProxySQL proxy fronting our MySQL database. When connecting to the database and beginning to execute queries, the first query may execute successfully, but subsequent queries stall out and do not proceed.

These are the logs from ProxySQL:

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 sql_mode=(SELECT CONCAT(@@sql_mode, ',PIPES_AS_CONCAT,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE'));
SET time_zone = '+00:00';
SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;

When I try to execute the next query, the execution seems to stall out in the middle of the query and it never resolves to either an error or a success. I'm doing this inside of a Warp server, and because the future never resolves, it hangs the HTTP connection. Cancelling the HTTP request drops the future, and the next query executes correctly, but on a new MySQL connection. If I gracefully shut down the Warp server instead, it will hang waiting for the request to finish until the timeout expires.

I could add some timeouts surrounding the queries, but that wouldn't solve the underlying issue of every other query failing to resolve.

Some times, when starting up, I will receive an error back from the ProxySQL instance:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SET time_zone = '+00:00';
      SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci' at line 2

This may just require issuing each SET command as a separate execute rather than all in one, as is currently done.

Issue #263 may be related.

[mehcode]

What's your MySQL version? Can you try master and see if the changes there fix your problem?

[neoeinstein]

I pulled up master, and the stalling issue is not present, but the error returned by ProxySQL continues to show up on the connection being established:

error returned from database: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SET time_zone = '+00:00';
SET NAMES utf8mb4 COLLATE utf8mb4_unico...' at line 2

I went ahead and pulled down the sqlx source and compiled it with a small change, just separating the initial SETs in the MySQL connection into 3 separate executes. When compiling against this modification, I can no longer reproduce the error. I'll post up a pull request with the change for you to consider.

[neoeinstein]

Oh, and for the version, ProxySQL is reporting: Server version: 10.4.1 (ProxySQL). These errors are only happening while traversing ProxySQL (handling multiple SETs within one execution apparently requires special handling). When executing against MySQL directly, there is no issue.