Understanding and Fixing PHP Warning: Packets out of order. Expected 1 received 0. Packet size=145

In one of my applications, I’ve been noticing this error occurring more frequently.

PHP Warning: Packets out of order. Expected 1 received 0. Packet size=145

When investigating, I ran this long running command in the foreground and watched for a pattern. Sure enough, I found that when the program waited a long time between jobs, that the first command when it resumed would result in this error.

My application had some retry logic built-in, so that it resumed and went on as normal, so it was just an annoyance, but I don’t like it when I don’t understand how things are working.

I was able to recreate this problem reliably with this short script:

<?php
require_once 'include.php';   // Connects to the database

// Set the session wait_timeout to a small value
$db->query("SET session wait_timeout=10;");

// Prove that the connection works
$one = $db->getOne("SELECT 1");
echo "Got one = {$one}\n";

// Sleep for longer than the wait_timeout
sleep(11)

// Retry the query
$one = $db->getOne("SELECT 1");
echo "Got one = {$one}\n";

When executed, it provided this output, concluding that the wait_timeout is the problem:

got one = 1
PHP Warning:  Packets out of order. Expected 1 received 0. Packet size=145 in /path/to/myapp/db.class.php on line 68
PHP Stack trace:
PHP   1. {main}() /path/to/myapp/dbtest.php:0
PHP   2. db->getOne($sql = 'SELECT 1', $args = *uninitialized*, $recurse = *uninitialized*) /path/to/myapp/dbtest.php:13
PHP   3. PDOStatement->execute($params = []) /path/to/myapp/db.class.php:68

To prevent this problem, I implemented a timer that counts the time between queries and reconnects to the server if wait_timeout seconds elapses between queries. This may not be exact, because it counts the time between the start of the query, but it largely prevented this problem.

In my database connection class (db.class.php), it calls the conn() method for each query, so I added the timer here which causes it to disconnect when there is more than $sqlTimeout seconds between SQL queries

class db
{
    protected $lastActivityTs = null;
    static protected $sqlTimeout = 3600;  // Make sure you copy this value from your MySQL Server

    public function conn()
    {
        if (isset($this->dbh) && (microtime(true) - $this->lastActivityTs) >= self::$sqlTimeout) {
echo "Disconnecting after expired SQL connection\n";
            // Our connection is probably timed out by the server anyway
            $this->disconnect();
        }
        if (!isset($this->dbh)) {
            $this->_connect();
        }
        $this->lastActivityTs = microtime(true);
        return $this->dbh;
    }

Note that our library here automatically retries once when a connection error occurs. This has also been important to catch temporary failures and disconnects from the MySQL server and have it retry the connection.

    // Continuing in the db class
    public function getOne($sql, $args = [], $recurse = true)
    {
        try {
            $sth = $this->conn()->prepare($sql);
            $sth->execute($args);
            $sth->setFetchMode(PDO::FETCH_NUM);
            $row =  $sth->fetch();
            return $row[0] ?? null;
        } catch (PDOException $e) {
            if ($recurse && 'HY000' == $e->getCode()) {
                // SQLSTATE[HY000]: General error: 2013 Lost connection to MySQL server during query
                unset($this->dbh);
                return $this->getOne($sql, $args, false);
            }
            throw $e;
        }
    }