Bug #81335 [Com]: PHP Warning, Packets out of order after connection timeout

Edit report at https://bugs.php.net/bug.php?id=81335&edit=1 ID: 81335 Comment by: lurajcevi at gmail dot com Reported by: bohuslav at simek dot si Summary: PHP Warning, Packets out of order after connection timeout Status: Verified Type: Bug Package: PDO MySQL Operating System: Alpine Linux 3.14 PHP Version: 7.4.22 Block user comment: N Private report: N New Comment: I've been investigating this for some time now, as we've had this warning logged in Magento (php (fpm) 8.1 with MySQL 8.0.33, PDO connection), and the issue with "Headers already sent" due to this warning. In order to reproduce the issue, I've set wait_timeout in MySQL to a lower value (say 20 seconds), connect to a DB with PDO (using persistent connection), let the script finish. Wait for > 20 seconds, and re-connect again. This time, warning shows up, because MySQL dropped the connection, but PHP-FPM still has it cached. Turning off persistent database connection seems to resolve the issue when accessing the store via browser. Setting PDO::ATTR_PERSISTENT to "false", forces new connection to be established, and not reuse the old one. My understanding is that MySQL connections, when persistent, are kept alive as a part of PHP-FPM process, and as long as that process is alive, connection will be reused. Once the process dies, connection dies with it. I was able to make the issue "go away" by killing the FPM processes manually, forcing new ones to be created - this in turn forced new MySQL connection to be established, without this warning. When it comes to php-cli, every call to php executable gets its own process, so connection reuse is not available. Previous Comments: ------------------------------------------------------------------------ [2023-07-17 00:05:06] phpacolyte at ftml dot net In case it helps someone, here is my report. Apologies if it's too much information or I've strayed unduly far afield. ** Part I ** I've encountered similar errors running bohuslav's test script in my "new" and "old" environments. My new environment is: ubuntu 22.04: "Linux ubuntu-jammy 5.15.0-76-generic #83-Ubuntu SMP Thu Jun 15 19:16:32 UTC 2023 x86_64" mysqld: "Ver 8.0.33-0ubuntu0.22.04.2 for Linux on x86_64 ((Ubuntu))" php: "PHP 8.2.7" php pdo driver: "mysqlnd 8.2.7" nginx: v.1.24.0 (connects to php-fpm with unix socket) My old environment is: ubuntu 18.04: "Linux jupiter 4.15.0-213-generic #224-Ubuntu SMP Mon Jun 19 13:30:12 UTC 2023 x86_64" mysqld: "Ver 8.0.33 for Linux on x86_64 (MySQL Community Server - GPL)" php: php7.3: "Version 7.3.33-10+ubuntu18.04.1+deb.sury.org+1" php pdo driver: "mysqlnd 5.0.12-dev - 20150407 - $Id: 7cc7cc96e675f6d72e5cf0f267f48e167c2abb23" nginx: v.1.14.1 (connects to php-fpm with unix socket) ------- - In my new environment, Bohuslav's test script causes the following errors: Warning: Packets out of order. Expected 1 received 0. Packet size=145 in <path/to/testscript.php> on line <line number of $pdo->exec('SELECT 123')> Fatal Error: Uncaught PDOException: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away in <same file, same line> - In my old environment, Bohuslav's test script causes the following errors: Warning: Error while sending QUERY packet. PID=<#> in <path/to/testscript.php> on line <line number of $pdo->exec('SELECT 123')> Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away in <same file, same line> - In both environments, both errors occur on the line where the script invokes PDO::exec. ** Part II ** I've also varied the test script somewhat, to try to match more closely what I've been experiencing with my production code. Namely, my production code receives no error messages in my old environment, but under some conditions it receives the "Packets out of order" warning error message in my new environment. Here is what I've done to vary the test script: I moved the "wait-timeout" setting out of the test script itself and into the mysql server configuration files, in my case at "/etc/mysql/conf.d/<mytestconf.cnf>": [mysqld] wait-timeout = 1 Then, I ran a slight variation of the test script, to try it with PDO::ATTR_PERSISTENT alternately set to false and true, and alternately to call PDO::exec and PDO::query to issue the SELECT 123 statement. I've copy/pasted my variation of the test script toward the end of this comment. In sum, it instantiates a PDO connection; uses the PDO::exec (or PDO::query) method to issue a "SELECT 123" SQL statement; destroys the PDO instance; sleeps 3 seconds; and then repeats those steps a second time. ------- In my old environment, that variation of the test script generates no errors, whether I run it with php-cli or php-fpm. In my new environment, it generates errors when PDO::ATTR_PERSISTENT is true for both of the consecutive PDO instantiations. The only error it causes is: "Warning: Packets out of order. Expected 1 received 0. Packet size=145 in <path/to/testscript.php> on line <line number where $pdo = new PDO(...); is called>" With php-cli, the error occurs upon creating the second PDO object (due to, I imagine, establishing a second connection after expiration of the first). Also, oddly (?), the error only occurs if the first PDO object's PDO::query method was called, not if its PDO::exec method was called. With php-fpm, the error's appearance has been somewhat more flaky, and I haven't been able to ascertain a wholly consistent pattern. See the end of this comment for details. ** Part III ** For my production code, in my new environment, I'm experimenting with two ways to address the error. One way is to change PDO::ATTR_PERSISTENT from true to false. That is, I seem to be able to eliminate the error by ceasing to use persistent connections. A second alternative I've also used is, in an error handler, which I set with php's \set_error_handler function, I ignore any error with a message containing the substring "Packets out of order. Expected 1 received 0.". ** Part IV (test script variation and procedure I used) ** Here is the variation of Bohuslav's test that I've performed. First, I configured mysql server by placing in /etc/mysql/conf.d/<mytestconf.cnf> the following: [mysqld] wait-timeout = 1: Second, I ran the following php code: <?php declare(strict_types=1); $iterations = [ [false, "query"], // and other [true|false, "query"|"exec"] permutations [false, "query"] // and other [true|false, "query"|"exec"] permutations ]; foreach ($iterations as $iteration) { sleep(3); list($pdo_attr_persistent, $pdo_method) = $iteration; connect_and_query($pdo_attr_persistent, $pdo_method); } function connect_and_query(bool $pdo_attr_persistent, string $pdo_method) { $dsn = 'mysql:dbname=<mydbname>;host=localhost;charset=utf8mb4'; $user = '<myuser>'; $password = '<mypassword>'; $options = [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_PERSISTENT => $pdo_attr_persistent, PDO::ATTR_EMULATE_PREPARES => true, ]; echo "connecting with PDO::__construct...", PHP_EOL; $pdo = new PDO($dsn, $user, $password, $options); echo 'PDO::ATTR_PERSISTENT is: ', var_export($pdo->getAttribute(PDO::ATTR_PERSISTENT), true), PHP_EOL; echo "calling PDO::$pdo_method to 'SELECT 123'...", PHP_EOL, PHP_EOL; $pdo->$pdo_method('SELECT 123'); $pdo = null; } Third, I tried to run the preceding code with each of the following subset of permutations for persistent connections and PDO methods for the two consecutive PDO instantiations. 1st PDO (persist-false, exec) 2nd PDO (persist-false, exec) -------- 1st PDO (persist-false, query) 2nd PDO (persist-false, query) -------- 1st PDO (persist-false, exec) 2nd PDO (persist-false, query) -------- 1st PDO (persist-false, query) 2nd PDO (persist-false, exec) -------- 1st PDO (persist-true, exec) 2nd PDO (persist-true, exec) -------- 1st PDO (persist-true, query) 2nd PDO (persist-true, query) -------- 1st PDO (persist-true, exec) 2nd PDO (persist-true, query) -------- 1st PDO (persist-true, query) 2nd PDO (persist-true, exec) Fourth, I've tried to run the above code with php-cli; with php's built-in cli web server (invoking it with curl); and with php-fpm (invoking it through curl and nginx). In my old OS environment (see top of comment for details), no errors are occurring. In my new OS environment (see top of comment for details): - When running php-cli, the error is generated upon instantiating the second PDO object if both PDO objects are "persistent" and the method we invoke on the first PDO object is PDO::query. - When running php-fpm via curl and nginx, the error is emitted under the same conditions as with php-cli. Further, in addition, the error sometimes is emitted upon the instantiation of the second PDO object even if we called PDO::exec on the first PDO object instead of PDO::query. Also, the error sometimes is emitted upon the instantiation of the first PDO object too?! I'm at a loss to speculate what is happening here to differentiate the php-fpm behavior from php-cli. Perhaps cache(s), or fpm worker lifecycles are intervening and obscuring? - Finally, when running the script with php's built-in cli web server, via curl, no errors are generated. Again, I'm at a loss why. To speculate: Perhaps php's built-in web server is using a different mysql pdo driver than both php-cli and php-fpm? Or, perhaps the built-in web server doesn't enable persistent PDO connections at all? ------------------------------------------------------------------------ [2023-05-26 14:48:59] pgolovin at gmail dot com Have the same problem: - PHP 8.1.15 - MySQL 8.0.32 - CentOS Linux release 7.7.1908 ------------------------------------------------------------------------ [2023-04-06 20:32:20] adrien at poupa dot net We are seeing the same problem after migrating to MySQL 8.0.28. However in our case it makes sense: we are using persistent connections, and this is happening after the server closed the connection. My fix would have been to retry to connect when this happens; however even with mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); I am not able to catch this as the warning is always reported and the exception is 2006 - MySQL has gone away. It should trigger a catchable exception in my opinion. ------------------------------------------------------------------------ [2023-02-08 12:01:53] jeka dot corvus at gmail dot com I have the same error docker image: php:8.2-fpm-alpine - php 8.2.2 docker image: percona-server:8.0 - mysql:8.0.30 Linux Kernel: 5.15.49 PHP Warning: Packets out of order. Expected 1 received 0. Packet size=145 ------------------------------------------------------------------------ [2022-11-08 22:42:52] johnnywalker at gmail dot com I can confirm script failing in 2 different setups with Php 7.4.3 ## Setup 1 ## Mysql Server version: 8.0.31 Distribution: Ubuntu 20.04.5 Linux Kernel: 5.15.0 Php Version: 7.4.3 PHP Warning: Packets out of order. Expected 1 received 0. Packet size=145 in... ## Setup 2 ## Mysql Server version: 8.0.30 Distribution: Ubuntu 20.04.5 LTS Linux Kernel: 5.4.0 Php Version: 7.4.3 PHP Warning: Packets out of order. Expected 1 received 0. Packet size=145 in... ------------------------------------------------------------------------ The remainder of the comments for this report are too long. To view the rest of the comments, please view the bug report online at https://bugs.php.net/bug.php?id=81335 -- Edit this bug report at https://bugs.php.net/bug.php?id=81335&edit=1