MySQL Bugs: #106266: MySQL Router does not work with PHP PDO network communication compression

Bug #106266	MySQL Router does not work with PHP PDO network communication compression
Submitted:	24 Jan 2022 17:56	Modified:	6 Feb 2023 16:26
Reporter:	Victor Michnowicz	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Router	Severity:	S2 (Serious)
Version:	8.0.23-8.0.28	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	MySQL Router, PDO, PDO_MySQL, php

Description:
We are experiencing an issue with MySQL Router version 8.0.23 through 8.0.28 and PHP PDO network communication compression (PDO::MYSQL_ATTR_COMPRESS).

When we a execute a query from PHP to MySQL Router, with compression enabled, MySQL Server shows a "Connect" entry in the MySQL Server general query log, but there is never a "Query" entry and the query is never executed. However, when we execute a query from PHP directly to MySQL Server, with compression enabled, the query executes as expected. Additionally, if we execute a query from PHP to MySQL Router, with compression disabled, the query also executes as expected.

We can replicate this issue on the following software versions:

MySQL Server version: 8.0.28
MySQL Router versions: 8.0.23, 8.0.28
PHP versions: 7.4.13, 8.1.2

This issue is not present in MySQL Router versions 8.0.22 through 8.0.16.

Here is the PHP code we are using to connect to MySQL Router with network communication compression:

$pdo = new PDO('mysql:host=172.16.2.15;port=[port]', '[user]', '[pass]', [
  PDO::MYSQL_ATTR_COMPRESS => true,
]);

$fetch = $pdo->query("select * from `performance_schema`.`users`");

while ($row = $fetch->fetch(PDO::FETCH_ASSOC))
{
  var_dump($row);
} 

How to repeat:
Connect to MySQL Router 8.0.23 from PHP with PDO network communication compression enabled and attempt to execute a query.

I updated the "Version" from "8.0.23" to "8.0.23-8.0.28".

Hi,

One question while trying to reproduce this. If you try to connect from PHP directly to mysql server 8.0.28 (ignoring router) does it work or you have similar problem

thanks

If connect from PHP directly to MySQL Server 8.0.28 (ignoring the router) I am unable to reproduce the error.

Only connecting to MySQL Router 8.0.23 (and above versions) reproduces the error.

Posted by developer:
 
I was using mysqlserver/router v28 

when connecting to the MySQLserver it looks ok:
root@php:/bugy.php# php bugy.php 
Current PHP version: 7.4.28array(3) {
  ["USER"]=>
  NULL
  ["CURRENT_CONNECTIONS"]=>
  string(2) "36"
  ["TOTAL_CONNECTIONS"]=>
  string(2) "55"
}
array(3) {
  ["USER"]=>
  string(15) "event_scheduler"
  ["CURRENT_CONNECTIONS"]=>
  string(1) "1"
  ["TOTAL_CONNECTIONS"]=>
  string(1) "1"
}
array(3) {
  ["USER"]=>
  string(4) "root"
  ["CURRENT_CONNECTIONS"]=>
  string(1) "1"
  ["TOTAL_CONNECTIONS"]=>
  string(2) "28"
}
array(3) {
  ["USER"]=>
  string(13) "healthchecker"
  ["CURRENT_CONNECTIONS"]=>
  string(1) "0"
  ["TOTAL_CONNECTIONS"]=>
  string(1) "9"
}

while changing the port to the router one and connecting I get this error:
root@php:/bugy.php# php bugy.php 
Current PHP version: 7.4.28
Fatal error: Uncaught PDOException: SQLSTATE[HY000] [2002] Connection refused in /bugy.php/bugy.php:4
Stack trace:
#0 /bugy.php/bugy.php(4): PDO->__construct('mysql:host=rout...', 'root', 'root', Array)
#1 {main}
  thrown in /bugy.php/bugy.php on line 4
root@php:/bugy.php# 

I attach tcpdumps of both tries

This can be reproduced with mysql command line tool (ERROR 2066 (HY000): Connection failed due to wrongly configured compression algorithm)
Router version 8.0.21 works and i believe it's the last version that works.

Tested with: 8.0.28 client, 8.0.26 server, 8.0.21 and 8.0.28 router.
Client:
[root@server ~]# mysql --version
mysql  Ver 8.0.28 for Linux on x86_64 (MySQL Community Server - GPL)

Test 1: 8.0.21 router - works:

[root@server ~]# mysql --host server1 --port 6446 --compress --compression-algorithms=zlib,zstd
[...]
Server version: 8.0.26 MySQL Community Server - GPL
[...]
mysql> status
--------------
mysql  Ver 8.0.28 for Linux on x86_64 (MySQL Community Server - GPL)
[...]
Protocol:               Compressed
[...]

Test 2: with 8.0.28 router, same client, same server 

[root@server ~]# mysql --host server2 --port 6446 --compress --compression-algorithms=zlib,zstd
ERROR 2066 (HY000): Connection failed due to wrongly configured compression algorithm

Test 3: directly to server

[root@s13 shoty]# mysql --host server1 --port 3306 --compress --compression-algorithms=zlib,zstd
[...]
Server version: 8.0.26 MySQL Community Server - GPL
[...]
mysql> status
[...]
Protocol:               Compressed
[...]

Hi guys,

I have the similar problem with mysql router 8.0.32 and mysql database 8.0.32.
No connection with php (7.4 and 8.1) to the router, but it's going well to the database.

Do you have any clue ?

Regards,

Martin

As of 2023-02-06 this is still an issue. The MySQL Router 8.0.31 release notes state: 

"MySQL Router rejected compressed connections with the error error:5000, bad message.

MySQL Router does not currently support compressed connections. As of this release, if a client requests a compressed connection, MySQL Router replies that it does not support them. The client can then use an uncompressed connection. (Bug #34445287)"