php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #81335 PHP Warning, Packets out of order after connection timeout
Submitted: 2021-08-06 10:37 UTC Modified: 2021-09-14 12:27 UTC
Votes:94
Avg. Score:4.3 ± 0.9
Reproduced:91 of 91 (100.0%)
Same Version:35 (38.5%)
Same OS:30 (33.0%)
From: bohuslav at simek dot si Assigned:
Status: Verified Package: PDO MySQL
PHP Version: 7.4.22 OS: Alpine Linux 3.14
Private report: No CVE-ID: None
Have you experienced this issue?
Rate the importance of this bug to you:

 [2021-08-06 10:37 UTC] bohuslav at simek dot si
Description:
------------
After migration from MySQL 8.0.23 to 8.0.25 a warning "Packets out of order. Expected 1 received 0. Packet size=145" will be shown after MySQL connection timeout. mysqlnd is used a as connection library.

pdo_mysql

PDO Driver for MySQL => enabled
Client API version => mysqlnd 8.0.8

I don't want to speculate, but this can be caused by changes introduce in Connection Management by MySQL 8.0.24 (https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-24.html header Connection Management Notes) as MySQL now provides reason why client has been disconnected.



Test script:
---------------
<?php declare(strict_types=1);

$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_PERSISTENT         => false,
    PDO::MYSQL_ATTR_INIT_COMMAND => 'SET time_zone = "UTC"',
    PDO::ATTR_EMULATE_PREPARES   => true,
];

$pdo = new PDO(
    'mysql:dbname=unit_tests;host=mysql-test;port=3306;charset=utf8mb4',
    'root',
    '',
    $options
);

$pdo->exec('SET SESSION wait_timeout=1');
sleep(3);
$pdo->exec('SELECT 123'); // PHP Warning:  Packets out of order. Expected 1 received 0. Packet size=145


Expected result:
----------------
warning "Packets out of order. Expected 1 received 0. Packet size=145" should not happen.


Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2021-08-06 12:43 UTC] dharman@php.net
-Status: Open +Status: Verified
 [2021-08-06 12:43 UTC] dharman@php.net
Thanks for reporting. This indeed should be fixed in mysqlnd. Tested on git.master and the mysqlnd client doesn't notice the connection has dropped when sending a query.
 [2021-08-06 18:16 UTC] cmb@php.net
With MySQL 5.6.44 and 8.0.19 I get:
SQLSTATE[HY000]: General error: 2006 MySQL server has gone away

I'm waiting for the update to 8.0.26 to finish for about eight
hours now.  I'll test with that version when it's finished (if it
ever finishes).
 [2021-09-14 12:27 UTC] cmb@php.net
Oops, forgot about this.  Anyway, result with git-master and MySQL
8.0.26:

SQLSTATE[HY000]: General error: 2006 MySQL server has gone away

Is this Linux specific?
 [2021-09-30 20:23 UTC] vovikems at gmail dot com
Having same error using official docker images: php:8.0.9-cli and mysql:8.0.26. Typically this error seen on first DB query since long time (about 10h-15h) keeping DB connection without any query sent. Next query is always ok.
FYI i'm using long-running php application (RoadRunner, Spiral Framework, CycleORM).
 [2022-03-07 16:20 UTC] maurice dot volaski at einsteinmed dot edu
I am seeing this on my Mac with PHP 8.1.2 (Apache module) and MySQL 8.0.28 Homebrew.
 [2022-09-06 14:06 UTC] peter dot maatman at rentman dot nl
We are seeing this issue appear in production after we started migrating our production database servers from mysql 8.0.19 to 8.0.28.

Our php version currently is 7.4.16 with pdo_mysql enabled, we run our php in a docker container based on the official php:7.4-fpm image.

I can confirm that the test script reproduces the bug.
 [2022-11-03 15:22 UTC] 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
 [2022-11-08 22:42 UTC] 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...
 [2023-02-08 12:01 UTC] 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
 [2023-04-06 20:32 UTC] 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-05-26 14:48 UTC] pgolovin at gmail dot com
Have the same problem:
- PHP 8.1.15
- MySQL 8.0.32
- CentOS Linux release 7.7.1908
 [2023-07-17 00:05 UTC] 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-09-06 09:33 UTC] lurajcevi at gmail dot com
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.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Fri Mar 29 00:01:28 2024 UTC