Laravel 4 and "Error while sending STMT_PREPARE packet"

Sometimes programmers have to maintain and support legacy projects written in older versions of frameworks.

So do I. One of my projects still uses Laravel 4 and, unfortunately, there’s a bug in its Database package which sometimes causes an error when working with long running jobs.

The reason

Laravel 4 tries to automatically reconnect to MySQL when connection is lost (e.g. after being idle for more than MySQL’s wait_timeout) and here’s how:

/**
 * Determine if the given exception was caused by a lost connection.
 *
 * @param  \Illuminate\Database\QueryException
 * @return bool
 */
protected function causedByLostConnection(QueryException $e)
{
    return str_contains($e->getPrevious()->getMessage(), 'server has gone away');
}

Unfortunately, ‘server has gone away’ is not the only error message signaling that the database connection is lost and that’s why sometimes Laravel fails to reconnect when it’s necessary.

This bug has already been fixed in Laravel 5.x, so let’s see how it handles the same task:

/**
 * Determine if the given exception was caused by a lost connection.
 *
 * @param  \Exception  $e
 * @return bool
 */
protected function causedByLostConnection(Exception $e)
{
    $message = $e->getMessage();

    return Str::contains($message, [
        'server has gone away',
        'no connection to the server',
        'Lost connection',
        'is dead or not enabled',
        'Error while sending',
        'decryption failed or bad record mac',
        'SSL connection has been closed unexpectedly',
    ]);
}

Okay, now let’s fix this in Laravel 4 without changing the code of the framework (it’s surprisingly hard).

The solution

To fix the problem, we have to perform the following steps:

1) Override the \Illuminate\Database\MySqlConnection class and fix the causedByLostConnection method

2) Override the \Illuminate\Database\Connectors\ConnectionFactory class, so it uses the fixed version of MySqlConnection class

3) Override the DatabaseServiceProvider, so Laravel uses the overridden ConnectionFactory class

Ok, let’s do it.

First of all, create the following class:

<?php

namespace YourApp\Services\Illuminate\Database;

use Illuminate\Database\QueryException;
use Illuminate\Support\Str;

class MySqlConnection extends \Illuminate\Database\MySqlConnection
{
    /**
     * Determine if the given exception was caused by a lost connection.
     *
     * @param  \Illuminate\Database\QueryException
     * @return bool
     */
    protected function causedByLostConnection(QueryException $e)
    {
        $message = $e->getMessage();

        return Str::contains(
            $message,
            [
                'server has gone away',
                'no connection to the server',
                'Lost connection',
                'is dead or not enabled',
                'Error while sending',
                'decryption failed or bad record mac',
                'SSL connection has been closed unexpectedly',
            ]
        );
    }
}

As you see, in this class we just replace causedByLostConnection method of Laravel 4 with the one from Laravel 5.x.

Now we have to make the framework use this new class instead of a stock one.

For this purpose we have to override the ConnectionFactory class as follows:

<?php

namespace YourApp\Services\Illuminate\Database\Connectors;

use YourApp\Services\Illuminate\Database\MySqlConnection;
use Illuminate\Database\PostgresConnection;
use Illuminate\Database\SQLiteConnection;
use Illuminate\Database\SqlServerConnection;
use PDO;

class ConnectionFactory extends \Illuminate\Database\Connectors\ConnectionFactory
{
    /**
     * Create a new connection instance.
     *
     * @param  string $driver
     * @param  \PDO   $connection
     * @param  string $database
     * @param  string $prefix
     * @param  array  $config
     * @return \Illuminate\Database\Connection
     *
     * @throws \InvalidArgumentException
     */
    protected function createConnection($driver, PDO $connection, $database, $prefix = '', array $config = [])
    {
        if ($this->container->bound($key = "db.connection.{$driver}")) {
            return $this->container->make($key, [$connection, $database, $prefix, $config]);
        }

        switch ($driver) {
            case 'mysql':
                return new MySqlConnection($connection, $database, $prefix, $config);

            case 'pgsql':
                return new PostgresConnection($connection, $database, $prefix, $config);

            case 'sqlite':
                return new SQLiteConnection($connection, $database, $prefix, $config);

            case 'sqlsrv':
                return new SqlServerConnection($connection, $database, $prefix, $config);
        }

        throw new \InvalidArgumentException("Unsupported driver [$driver]");
    }
}

And now we have to make sure that Laravel uses our overridden ConnectionFactory, so we have to extend its DatabaseServiceProvider as follows:

<?php

namespace YourApp\ServiceProviders;

use YourApp\Services\Illuminate\Database\Connectors\ConnectionFactory;
use Illuminate\Database\DatabaseManager;

class DatabaseServiceProvider extends \Illuminate\Database\DatabaseServiceProvider
{
    /**
     * Register the service provider.
     *
     * @return void
     */
    public function register()
    {
        $this->app->bindShared('db.factory', function($app)
        {
            return new ConnectionFactory($app);
        });

        $this->app->bindShared('db', function($app)
        {
            return new DatabaseManager($app, $app['db.factory']);
        });
    }
}

Finally, replace the stock DatabaseServiceProvider with your version in the app/config/app.php file:

'providers' => [
    ...
    //'Illuminate\Database\DatabaseServiceProvider',
    'YourApp\ServiceProviders\DatabaseServiceProvider',
    ...
];

That’s it!

Now Laravel 4 will be able to identify all possible situations when a database connection is lost and it will automatically reconnect instead of throwing a strange exception.

← Back