ProxySQL and MySQL backtick make conection to close prematurely

[Fernando Rubio]

Im new to proxysql but i've heard wonderfull things about it to improve mysql loading. So I decided to make a try with a super simple scenario:

php app -> proxysql -> mysql

everything running on the same machine, without routing and replication and none of those things, just a simple proxy sit before the DB to handle cache in a better way that memcache on the original mysql server.

I want to use proxysql mainly as a cache for recurrent selects that the application makes. So the rule is simple as a ^SELECT and a cache_ttl of 10sec

I test this in an old VPS with old everything, so the setup is:

CentOs Linux 7, Php 7.4, ProxySQL 2.7.3-12, MySql 5.7.44

..yeah dont mention about it xD, have you heard the "if isn't broke dont fix it" well here is. (To be honest the client dont want to upgrade their app, just because is extremely customized and dont want to spend 3 years in a new version) I have to spend extra money for keep this running (legacy support), but thats another history.

So the first thing I did was setting the server, the users, the query rules and everything.

I connect to localhost just to discover that proxysql don't cache anything if you connect to localhost, seems the localhost use the .sock so proxysql dont do anything with cache... this was the first surprise and is not documented anywhere... So I connect to 127.0.0.1 and now yes, proxysql cache everything according to my simple SELECT rule.

So I connect my app, and here a bunch of errors appear, after A LOT of debugging with those ugly 500 errors that every one hates, I narrow the error down to SQL Syntax parsing on proxysql.

For instance: SELECT * FROM my_table; or SELECT my_colum FROM my_table; works like a charm

But

SELECT `my_ugly_backtick_column_name` FROM my_table;

Fails, closing the connection to mysql prematurely with the error

Fatal error: Uncaught Exception: Error: MySQL server has gone away

So I realized that ANY mysql syntax containing (`) fails.

I have tried almost everything, of course the first thing was get ride of the backticks at all, but this ofcourse fails for some reserved names like type or ssl that sadly the application use as columns, then I set

SET sql_mode=(SELECT CONCAT(@@session.sql_mode,",ANSI_QUOTES"));

as a mysql-init-connect variable and then using the regex to change the queries from backtick to double quotes. This works for almost all sintax, but of course there are edge cases that make it fails, because the application use different libraries so there is a huge mix of syntax styling.

I have expend 3 days without sleeping trying different things and aproachs, but now i'm burned.

So I ask the community, how can I do this.

I can't belive that proxysql cant handle backticks due parser error or protocol failures on the wire, because backticks are common and widely used, so the most probable thing is that I miss some super simple configuration here or there.

PD: There is no * wait_timeout * large packet problem here, the connection get close only with the backtick character, I have tried different encoding too utf8, utfmb4, etc. with no success.

[René Cannaò]

Hi Fernando,

Can you share the ProxySQL error logs?

Thanks,

René

--
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 visit https://groups.google.com/d/msgid/proxysql/76889a33-a46b-48b4-bd34-7484eecb0258n%40googlegroups.com.

[René Cannaò]

Hi Fernando,

Thanks for the extra info. I'll keep this on the public list so others can benefit : please send future replies to the group, not to me directly.

The smoking gun in your log is the idle time. Look at this line:

  [ERROR] ... last_used 199256246ms ago : 2027, Received malformed packet

In ProxySQL, `last_used` is printed in milliseconds (lib/MySQL_Session.cpp:8755-8756 — `monotonic_time()` returns microseconds, divided by 1000 before printing). So that value is:

  199256246 ms = 199256 s = 55.35 hours

Your backend connection sat idle for 55 hours. MySQL's default `wait_timeout` is 8 hours, so MySQL killed that connection 47 hours before your query tried to use it. The plain query "works" only because it happened to be routed to a different (still-alive) connection in ProxySQL's pool.

Error 2027 is `CR_MALFORMED_PACKET` from libmariadbclient — a TCP-level problem, not a parser problem. The 0x60 backtick byte is irrelevant.

To check whether ProxySQL handles backticks correctly, I built a minimal test with the same versions you reported (ProxySQL 2.7.3-12, MySQL 5.7.44, PHP 7.4) and your exact init (set_charset("utf8") + query("SET SQL_MODE = ''")). It runs 15 backtick query patterns:

  https://gist.github.com/renecannao/31690a8399b53273c50d1c359eaea7e7

In a clean environment all 12 cases with valid test data return rows correctly and the connection never drops. The 3 remaining cases in the script have assertions that don't match the seeded data — they are script bugs, not backtick-handling failures.

If you can reproduce the failure on your machine, please post the exact steps, the full ProxySQL log, and your ProxySQL configuration (proxysql.cnf) to the list. A minimal, deterministic reproducer is what I need to identify whether this is a ProxySQL bug or an environment issue.

Best,
René

[Fernando Rubio]

Hi Rene

In order to reproduce the issue, I have saved my current configuration to proxysql.cnf  

( sudo mysql -u admin -padmin -h 127.0.0.1 -P6032 -e "SELECT CONFIG FILE" > /tmp/proxysql.cnf)

I have removed all the mysql_rules to keep the config minimal just as you did. By doing this, I have discovered that some errors I thought were backtick-related actually weren´t, and I have isolated the error to only one keyword in backticks.

I prepared dbtest1.php and run some queries that are part of the original app, calling the different steps. Before running any steps, I  emptied the proxysql logs each time.

Here are my results:

TEST1

dbtest1.php?s=1

Returns DB data as expected acording to SELECT * FROM extension

ProxySQL Error logs is empty (No errors)

TEST2

dbtest1.php?s=2

Returns DB data as expected acording to SELECT * FROM extension  and SELECT * FROM extension WHERE type = 'total'

ProxySQL Error logs is empty (No errors)

TEST3

dbtest1.php?s=3

Returns DB data as expected acording to SELECT * FROM extension  and SELECT * FROM extension WHERE type = 'total' 

but now fails on SELECT * FROM extension WHERE `type` = 'total'

PHP ERROR:

Fatal error: Uncaught Exception: Error: MySQL server has gone away<br />Error No: 2006<br />SELECT * FROM extension WHERE `type` = 'total' in /home/dbtest.php:43 Stack trace: #0 /home/dbtest.php(83): db->query('SELECT * FROM e...') #1 {main} thrown in /home/dbtest.php on line 43

ProxySQL Error logs now show

2026-06-14 15:54:48 MySQL_Session.cpp:4837:handler_minus1_ClientLibraryError(): [ERROR] Detected a broken connection while running query on (1,127.0.0.1,3306,706895) , FD (Conn:42 , MyDS:42) , mysqluser , last_used 1ms ago : 2027, Received malformed packet
2026-06-14 15:54:48 MySQL_Session.cpp:4851:handler_minus1_ClientLibraryError(): [WARNING] Retrying query.
2026-06-14 15:54:48 MySQL_Session.cpp:4837:handler_minus1_ClientLibraryError(): [ERROR] Detected a broken connection while running query on (1,127.0.0.1,3306,706896) , FD (Conn:42 , MyDS:42) , mysqluser , last_used 255360280ms ago : 2027, Received malformed packet

On test3, I deliberately ran Test1 and Test2 as well to track the error within the same connection. Here appear something that I don't understand, the log says that the connection was broken during the query containing `type` on it, reporting last_used 1ms ago (I assume this is because Test2 was sent right before Test3). Then the query was retried, but now the connection is broken again, but the last_used reported is 255360280ms (This is about 70 hours ago). Does this mean that the retry connections had been idle for 70 hours and is different from the current connection? I'm kinda lost with that.

TEST4

dbtest1.php?s=4

Returns DB data as expected acording to SELECT * FROM extension  and SELECT * FROM extension WHERE type = 'total'

and (this is a surprise) according to SELECT * FROM extension WHERE `code` = 'total',  so here we noted that the problem is not the backtick by itself.

Error logs is empty (No errors)

TEST5

dbtest1.php?s=5

Returns DB data as expected acording to SELECT * FROM extension  and SELECT * FROM extension WHERE type = 'total'

and according to SELECT * FROM store WHERE REPLACE(`ssl`, 'www.', '') = 'https://domain.cl/', (This query used to fail before due to my workaround mysq_rule that removed backticks completely)

Error logs is empty (No errors)

So, in summary,  the only expression causing the connection to drop so far in the test script is: 

SELECT * FROM extension WHERE `type` = 'total'

For some reason, `type` wrapped in backticks triggers the connection closure.

Also, please keep in mind that if I use localhost instead of 127.0.0.1, everything works flawlessly—the entire app runs without issues—but, of course, I lose the ability to use the cache this way.

AFTER making this test, I tried to implement my workaround again with the following rule:

INSERT INTO mysql_query_rules (rule_id, active, match_pattern, replace_pattern, apply) VALUES (10, 1, '`type`', 'type', 1);

LOAD MYSQL QUERY RULES TO RUNTIME; 

SAVE MYSQL QUERY RULES TO DISK;

When running the isolated test script (dbtest1.php), tests 1 to 5 now succeed. Common sense suggested the application would work fine now, but Murphy's Law kicked in: the main application still fails, surprisingly when executing the expression from TEST 5.

Testing the full application now throws:

PHP ERROR

Fatal error: Uncaught Exception: Error: MySQL server has gone away<br />Error No: 2006<br />SELECT * FROM store WHERE REPLACE(`ssl`, 'www.', '') = 'https://domain.cl/'

PROXYSQL ERROR LOG

2026-06-14 16:45:31 [INFO] Received command SET sql_quote_show_create = 1, autocommit = 1
2026-06-14 16:45:31 [INFO] Received command SET sql_quote_show_create = 1, autocommit = 1
2026-06-14 16:45:31 [INFO] Received LOAD MYSQL QUERY RULES TO RUNTIME command
2026-06-14 16:45:31 [INFO] Computed checksum for 'LOAD MYSQL QUERY RULES TO RUNTIME' was '0x50FB58CEC5EF0023', with epoch '1781469931'
2026-06-14 16:45:31 [INFO] Received SAVE MYSQL QUERY RULES TO DISK command

2026-06-14 17:41:39 MySQL_Session.cpp:4837:handler_minus1_ClientLibraryError(): [ERROR] Detected a broken connection while running query on (1,127.0.0.1,3306,726038) , FD (Conn:42 , MyDS:42) , user mysqluser , last_used 1ms ago : 2027, Received malformed packet
2026-06-14 17:41:39 MySQL_Session.cpp:4851:handler_minus1_ClientLibraryError(): [WARNING] Retrying query.
2026-06-14 17:41:39 MySQL_Session.cpp:4837:handler_minus1_ClientLibraryError(): [ERROR] Detected a broken connection while running query on (1,127.0.0.1,3306,726039) , FD (Conn:42 , MyDS:42) , user mysqluser , last_used 261771737ms ago : 2027, Received malformed packet
2026-06-14 17:41:45 MySQL_Session.cpp:4837:handler_minus1_ClientLibraryError(): [ERROR] Detected a broken connection while running query on (1,127.0.0.1,3306,726053) , FD (Conn:42 , MyDS:42) , user mysqluser , last_used 1ms ago : 2027, Received malformed packet
2026-06-14 17:41:45 MySQL_Session.cpp:4851:handler_minus1_ClientLibraryError(): [WARNING] Retrying query.
2026-06-14 17:41:45 MySQL_Session.cpp:4837:handler_minus1_ClientLibraryError(): [ERROR] Detected a broken connection while running query on (1,127.0.0.1,3306,726054) , FD (Conn:42 , MyDS:42) , user mysqluser , last_used 261777152ms ago : 2027, Received malformed packet
2026-06-14 17:41:46 MySQL_Session.cpp:4837:handler_minus1_ClientLibraryError(): [ERROR] Detected a broken connection while running query on (1,127.0.0.1,3306,726062) , FD (Conn:42 , MyDS:42) , user mysqluser , last_used 1ms ago : 2027, Received malformed packet
2026-06-14 17:41:46 MySQL_Session.cpp:4851:handler_minus1_ClientLibraryError(): [WARNING] Retrying query.
2026-06-14 17:41:46 MySQL_Session.cpp:4837:handler_minus1_ClientLibraryError(): [ERROR] Detected a broken connection while running query on (1,127.0.0.1,3306,726063) , FD (Conn:42 , MyDS:42) , user mysqluser , last_used 261778848ms ago : 2027, Received malformed packet
2026-06-14 17:41:47 MySQL_Session.cpp:4837:handler_minus1_ClientLibraryError(): [ERROR] Detected a broken connection while running query on (1,127.0.0.1,3306,726065) , FD (Conn:42 , MyDS:42) , user mysqluser , last_used 2ms ago : 2027, Received malformed packet
2026-06-14 17:41:47 MySQL_Session.cpp:4851:handler_minus1_ClientLibraryError(): [WARNING] Retrying query.
2026-06-14 17:41:47 MySQL_Session.cpp:4837:handler_minus1_ClientLibraryError(): [ERROR] Detected a broken connection while running query on (1,127.0.0.1,3306,726066) , FD (Conn:42 , MyDS:42) , user mysqluser , last_used 261779039ms ago : 2027, Received malformed packet
2026-06-14 17:41:49 MySQL_Session.cpp:4837:handler_minus1_ClientLibraryError(): [ERROR] Detected a broken connection while running query on (1,127.0.0.1,3306,726082) , FD (Conn:42 , MyDS:42) , user mysqluser , last_used 1ms ago : 2027, Received malformed packet
2026-06-14 17:41:49 MySQL_Session.cpp:4851:handler_minus1_ClientLibraryError(): [WARNING] Retrying query.
2026-06-14 17:41:49 MySQL_Session.cpp:4837:handler_minus1_ClientLibraryError(): [ERROR] Detected a broken connection while running query on (1,127.0.0.1,3306,726083) , FD (Conn:42 , MyDS:42) , user mysqluser , last_used 261781698ms ago : 2027, Received malformed packet
2026-06-14 17:41:50 MySQL_Session.cpp:4837:handler_minus1_ClientLibraryError(): [ERROR] Detected a broken connection while running query on (1,127.0.0.1,3306,726084) , FD (Conn:42 , MyDS:42) , user mysqluser , last_used 1ms ago : 2027, Received malformed packet
2026-06-14 17:41:50 MySQL_Session.cpp:4851:handler_minus1_ClientLibraryError(): [WARNING] Retrying query.
2026-06-14 17:41:50 MySQL_Session.cpp:4837:handler_minus1_ClientLibraryError(): [ERROR] Detected a broken connection while running query on (1,127.0.0.1,3306,726085) , FD (Conn:42 , MyDS:42) , user mysqluser , last_used 261781937ms ago : 2027, Received malformed packet
2026-06-14 17:41:51 MySQL_Session.cpp:4837:handler_minus1_ClientLibraryError(): [ERROR] Detected a broken connection while running query on (1,127.0.0.1,3306,726091) , FD (Conn:42 , MyDS:42) , user mysqluser , last_used 1ms ago : 2027, Received malformed packet
2026-06-14 17:41:51 MySQL_Session.cpp:4851:handler_minus1_ClientLibraryError(): [WARNING] Retrying query.
2026-06-14 17:41:51 MySQL_Session.cpp:4837:handler_minus1_ClientLibraryError(): [ERROR] Detected a broken connection while running query on (1,127.0.0.1,3306,726092) , FD (Conn:42 , MyDS:42) , user mysqluser , last_used 261783764ms ago : 2027, Received malformed packet
2026-06-14 17:41:53 MySQL_Session.cpp:4837:handler_minus1_ClientLibraryError(): [ERROR] Detected a broken connection while running query on (1,127.0.0.1,3306,726094) , FD (Conn:42 , MyDS:42) , user mysqluser , last_used 0ms ago : 2027, Received malformed packet
2026-06-14 17:41:53 MySQL_Session.cpp:4851:handler_minus1_ClientLibraryError(): [WARNING] Retrying query.
2026-06-14 17:41:53 MySQL_Session.cpp:4837:handler_minus1_ClientLibraryError(): [ERROR] Detected a broken connection while running query on (1,127.0.0.1,3306,726095) , FD (Conn:42 , MyDS:42) , user mysqluser , last_used 261784939ms ago : 2027, Received malformed packet
2026-06-14 17:41:54 MySQL_Session.cpp:4837:handler_minus1_ClientLibraryError(): [ERROR] Detected a broken connection while running query on (1,127.0.0.1,3306,726099) , FD (Conn:42 , MyDS:42) , user mysqluser , last_used 2ms ago : 2027, Received malformed packet
2026-06-14 17:41:54 MySQL_Session.cpp:4851:handler_minus1_ClientLibraryError(): [WARNING] Retrying query.
2026-06-14 17:41:54 MySQL_Session.cpp:4837:handler_minus1_ClientLibraryError(): [ERROR] Detected a broken connection while running query on (1,127.0.0.1,3306,726100) , FD (Conn:42 , MyDS:42) , user mysqluser , last_used 261786153ms ago : 2027, Received malformed packet

So the backtick ghost appear again.

The application without any rules, fails on Test3, just as the dbtest1.php script does.

I am not entirely sure if it is genuinely failing on `ssl`, as the log shows multiple malformed packets. The error might just be manifesting during that specific expression while the root cause lies elsewhere. Interestingly, this exact expression does not fail when executed inside dbtest1.php, even after applying the new mysql_rule.

Maybe the issue here is something related to timeouts with just a surprising correlation with backticks, but not backticks per-se.

I Hope that my proxysql.cnf give some clues about what I did wrong after following the install steps.

[Fernando Rubio]

BTW I have tested the word type in backtick again with this odd result

TEST3

SELECT * FROM extension WHERE `type` = 'total'

2026-06-15 13:49:29 MySQL_Session.cpp:4837:handler_minus1_ClientLibraryError(): [ERROR] Detected a broken connection while running query on (1,127.0.0.1,3306,941076) , FD (Conn:46 , MyDS:46) , user mysqluser , last_used 0ms ago : 2027, Received malformed packet
2026-06-15 13:49:29 MySQL_Session.cpp:4851:handler_minus1_ClientLibraryError(): [WARNING] Retrying query.
2026-06-15 13:49:29 MySQL_Session.cpp:4837:handler_minus1_ClientLibraryError(): [ERROR] Detected a broken connection while running query on (1,127.0.0.1,3306,941277) , FD (Conn:46 , MyDS:46) , user mysqluser , last_used 334242016ms ago : 2027, Received malformed packet

The error persist, it should show 10 rows out of 46, it's a small table with a few columns.

#   name                               type    charset Null default  extra

1 extension_id Primary int(11) No None AUTO_INCREMENT
2 type varchar(32) utf8_general_ci No None
3 code varchar(32) utf8_general_ci No None

TEST6

EXPLAIN SELECT * FROM extension WHERE `type` = 'total'

No errors :P

object(stdClass)#4 (3) { ["num_rows"]=> int(1) ["row"]=> array(12) { ["id"]=> string(1) "1" ["select_type"]=> string(6) "SIMPLE" ["table"]=> string(12) "extension" ["partitions"]=> NULL ["type"]=> string(3) "ALL" ["possible_keys"]=> NULL ["key"]=> NULL ["key_len"]=> NULL ["ref"]=> NULL ["rows"]=> string(2) "46" ["filtered"]=> string(5) "10.00" ["Extra"]=> string(11) "Using where" } ["rows"]=> array(1) { [0]=> array(12) { ["id"]=> string(1) "1" ["select_type"]=> string(6) "SIMPLE" ["table"]=> string(12) "extension" ["partitions"]=> NULL ["type"]=> string(3) "ALL" ["possible_keys"]=> NULL ["key"]=> NULL ["key_len"]=> NULL ["ref"]=> NULL ["rows"]=> string(2) "46" ["filtered"]=> string(5) "10.00" ["Extra"]=> string(11) "Using where" } } }

So we can't say that the problem is with the word `type`, instead the error appears sometimes on syntax that contains a backtick.

[René Cannaò]

I can't reproduce the issue.

And at this point, I guess the backtick has nothing to do with the issue you are experiencing.

I reread the original email, and I must be honest about it: I read quickly, focusing only on the backticks.

Now that I reread it (you talk about caching a lot) the question I have is: do you have MySQL query cache enabled on the backend?

Unrelated, this statement is false:

> I connect to localhost just to discover that proxysql don't cache anything if you connect to localhost, seems the localhost use the .sock so proxysql dont do anything with cache... this was the first surprise and is not documented anywhere... 

ProxySQL query cache doesn't care if you connect via UDS or TCP . You probably aren't connecting to ProxySQL ...

To view this discussion visit https://groups.google.com/d/msgid/proxysql/211f5486-29af-4374-ad58-941d36a024bdn%40googlegroups.com.

[Fernando Rubio]

do you have MySQL query cache enabled on the backend?

Yes, MySQL server have memcached enabled.

About the difference between localhost and 127.0.0.1:

Considering the only rule:
(rule_id,active,username,schemaname,flagIN,client_addr,proxy_addr,proxy_port,digest,match_digest,match_pattern,negate_match_pattern,re_modifiers,flagOUT,replace_pattern,destination_hostgroup,cache_ttl,cache_empty_result,cache_timeout,reconnect,timeout,retries,delay,next_query_flagIN,mirror_flagOUT,mirror_hostgroup,error_msg,OK_msg,sticky_conn,multiplex,gtid_from_hostgroup,log,apply,attributes,comment) =>

(1,1,NULL,NULL,0,NULL,NULL,NULL,NULL,NULL,^SELECT,0,CASELESS,NULL,NULL,1,10000,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,,NULL)

1) If I use localhost:6033 when connect the client I got:

SELECT rule_id, hits FROM stats.stats_mysql_query_rules

no records

SELECT * FROM stats_mysql_global AS stats WHERE Variable_Name LIKE 'Query_Cache%'

Query_Cache_Memory_bytes
0
Query_Cache_count_GET
0
Query_Cache_count_GET_OK
0
Query_Cache_count_SET
0
Query_Cache_bytes_IN
0
Query_Cache_bytes_OUT
0
Query_Cache_Purged
0
Query_Cache_Entries
0

2) If I use 127.0.0.1:6033 when connect the client I got:

SELECT rule_id, hits FROM stats.stats_mysql_query_rules

rule_id,hits
1,6

SELECT * FROM stats_mysql_global AS stats WHERE Variable_Name LIKE 'Query_Cache%'

Query_Cache_Memory_bytes
31050
Query_Cache_count_GET
15
Query_Cache_count_GET_OK
2
Query_Cache_count_SET
12
Query_Cache_bytes_IN
25493
Query_Cache_bytes_OUT
730
Query_Cache_Purged
7
Query_Cache_Entries
5

That's why I thought that sock and tcp was different, but if you state that isn't, so here is the root of the problem maybe.

[Fernando Rubio]

After a lot of tries, the only thing that works is

UPDATE mysql_users SET fast_forward=1 where username='mysqluser';

LOAD MYSQL USERS TO RUNTIME; 

SAVE MYSQL USERS TO DISK;

But I can't use any cache features this way right?

[René Cannaò]

I am pretty confident you are hitting a MySQL bug.

https://bugs.mysql.com/bug.php?id=84639

You are likely hitting this bug because you have old clients not using CLIENT_DEPRECATE_EOF and ProxySQL configured to use it.

You can disable all ProxySQL variables related to client deprecate EOF .

About localhost: when using localhost your client is likely connecting to MySQL directly, not to ProxySQL.

Nowhere you are specifying ProxySQL socket: clients are connecting to default socket = mysql directly 

To view this discussion visit https://groups.google.com/d/msgid/proxysql/334dcadb-2b1a-4ffc-b190-b48385ab18d1n%40googlegroups.com.

[Fernando Rubio]

Finally, after a lot of debugging, I have solved this...

I will go right to the solution:

UPDATE global_variables
SET variable_value='false'
WHERE variable_name='mysql-enable_client_deprecate_eof';

-- 2. Desactivar el flag de optimización extendido para el lado servidor
UPDATE global_variables
SET variable_value='false'
WHERE variable_name='mysql-enable_server_deprecate_eof';

-- 3. Cargar las variables globales en caliente a la memoria activa
LOAD MYSQL VARIABLES TO RUNTIME;

-- 4. Guardar los cambios de forma persistente en el disco
SAVE MYSQL VARIABLES TO DISK;

Now everythings works as it should.

Thank you Rene for your time!