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: phpacolyte at ftml dot net 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: 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? Previous Comments: ------------------------------------------------------------------------ [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... ------------------------------------------------------------------------ [2022-11-03 15:22:22] addi dot hurz at gmail dot com macOS 12.5.1 with arm CPU _ PHP 8.1.12 _ MySQL 8.0.31 all installed and freshly updated with homebrew script still produces: Warning: Packets out of order. Expected 1 received 0. Packet size=145 Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away ------------------------------------------------------------------------ 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