Invalid resultset when using query cache and mixing clients using and not using CLIENT_DEPRECATE_EOF

[renecannao]

• A clear description of the issue

If a client connects to ProxySQL not using CLIENT_DEPRECATE_EOF and it caches a resultset, when a client using CLIENT_DEPRECATE_EOF executes the same query it will get an invalid resultset and the client will disconnect.
The data in the resultset is correct, but proxysql skips a sequence id thus the client assumes it is corrupted.

• ProxySQL version

From 2.1.0 to 2.3.2

• OS version

Any.

• The steps to reproduce the issue

Have two mysql clients, one not using CLIENT_DEPRECATE_EOF and one using CLIENT_DEPRECATE_EOF .
For example, using mysql client binary for 5.6 and 8.0 .
We can use dbdeployer for this.

dbdeployer downloads get mysql-5.6.44.tar.xz
dbdeployer unpack mysql-5.6.44.tar.xz
dbdeployer downloads get mysql-8.0.22-linux-glibc2.17-x86_64-minimal.tar.xz
dbdeployer unpack mysql-8.0.22-linux-glibc2.17-x86_64-minimal.tar.xz

Have a very basic proxysql installation with 1 backend and 1 user, and create a query rule to enable query caching (for simplicity, on every SELECT):

mysql -u admin -padmin -h 127.0.0.1 -P6032 -e "INSERT INTO mysql_query_rules (rule_id, active, match_digest, cache_ttl) VALUES (1,1,'^SELECT', 600000); LOAD MYSQL QUERY RULES TO RUNTIME;"

Run a SELECT 1 using an old mysql client (not using CLIENT_DEPRECATE_EOF), then run the same query using a client using CLIENT_DEPRECATE_EOF:

$ ~/opt/mysql/5.6.44/bin/mysql -u sbtest -psbtest -h 127.0.0.1 -P6033 -e "SELECT 1"
Warning: Using a password on the command line interface can be insecure.
+---+
| 1 |
+---+
| 1 |
+---+
$ ~/opt/mysql/8.0.22/bin/mysql -u sbtest -psbtest -h 127.0.0.1 -P6033 -e "SELECT 1"
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query

The resultset captured by tcpdump when using 5.6.44 client :

19:35:02.092147 IP 127.0.0.1.6033 > 127.0.0.1.35718: Flags [P.], seq 171:227, ack 137, win 512, options [nop,nop,TS val 2128847354 ecr 2128847353], length 56
	0x0000:  4500 006c 4c2b 4000 4006 f05e 7f00 0001  E..lL+@.@..^....
	0x0010:  7f00 0001 1791 8b86 a676 96da fb2e f929  .........v.....)
	0x0020:  8018 0200 fe60 0000 0101 080a 7ee3 a1fa  .....`......~...
	0x0030:  7ee3 a1f9 0100 0001 0117 0000 0203 6465  ~.............de
	0x0040:  6600 0000 0131 000c 3f00 0100 0000 0881  f....1..?.......
	0x0050:  8000 0000 0500 0003 fe00 0002 4002 0000  ............@...
	0x0060:  0401 3105 0000 05fe 0000 0200            ..1.........

The resultset captured by tcpdump when using 8.0.22 client :

19:36:28.054311 IP 127.0.0.1.6033 > 127.0.0.1.35598: Flags [P.], seq 212:261, ack 141, win 512, options [nop,nop,TS val 2128753316 ecr 2128753315], length 49
	0x0000:  4500 0065 e908 4000 4006 5388 7f00 0001  E..e..@[email protected].....
	0x0010:  7f00 0001 1791 8b0e bc6b 8ab9 83bf bdc4  .........k......
	0x0020:  8018 0200 fe59 0000 0101 080a 7ee2 32a4  .....Y......~.2.
	0x0030:  7ee2 32a3 0100 0001 0117 0000 0203 6465  ~.2...........de
	0x0040:  6600 0000 0131 000c 3f00 0100 0000 0881  f....1..?.......
	0x0050:  8000 0000 0200 0004 0131 0700 0005 fe00  .........1......
	0x0060:  0000 0002 00                             .....

For reference to MySQL Protocol:
https://dev.mysql.com/doc/internals/en/mysql-packet.html
https://dev.mysql.com/doc/internals/en/com-query-response.html#packet-ProtocolText::Resultset
The resultset sent to 8.0.22 is missing the first EOF packet: this is expected!
But the following packet should use sequence ID number 3 , instead it uses sequence ID number 4.

• The full ProxySQL error log (default location: /var/lib/proxysql/proxysql.log)

The full error log it is not relevant. What is relevant is that the client disconnects:

2021-11-19 19:30:51 MySQL_Thread.cpp:4879:process_all_sessions(): [WARNING] Closing unhealthy client connection 127.0.0.1:35590

root cause

The problem originates in eof_to_ok_packet that simply skip the first EOF packet and copies the rest.

solution

In function eof_to_ok_packet() should, after copying the resultset, we must change and reduce by one each packet following the skipped EOF packet.
We should scan all the packets reading the header, computing the length, jumping to the next packet header, and repeat until we reach the end.
It is important to remember that sequence ID ranges from 0 to 255 and then it rolls over.