|
|
php.net | support | documentation | report a bug | advanced search | search howto | statistics | random bug | login |
[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. PatchesPull RequestsHistoryAllCommentsChangesGit/SVN commits
|
|||||||||||||||||||||||||||||||||||||
Copyright © 2001-2024 The PHP GroupAll rights reserved. |
Last updated: Tue Dec 03 17:01:29 2024 UTC |
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?