PDO: How to check if connection is active, for real?

Question

There have been two questions about this already, but no one has actually answered the question.

I know that PDO will throw an exception if the connection fails (assuming you enable PDO::ERRMODE_EXCEPTION), but I want to test if a connection is still active, potentially hours later.

I have a long running script and after awhile it times out. Theoretically I can increase this amount of time with PDO::ATTR_TIMEOUT but basically I want to write a function that gives me back an active connection -- either an existing connection if it's already been established and has not timed out or re-connect if it has.

Should I just do SELECT 1, catch the exception, and re-connect? Or is there a nicer way?

Answer 1

The MySQL protocol supports a special command COM_PING for this purpose, and the C API has a call mysql_ping() to send it. This tests if the connection is active.

If the connection was created with MYSQL_OPT_RECONNECT, automatically connects (https://dev.mysql.com/doc/refman/5.6/en/auto-reconnect.html).

Unfortunately, neither of these features are supported if you use the current version of PDO. You can only submit SQL query strings, not special commands. And PDO now uses the mysqlnd driver, which has its advantages but doesn't support the reconnecting option. So the issue is moot anyway.

I don't know of any more elegant solution than trying to issue a "dummy" query like SELECT 1, catch the exception, and if you get error code 2006 (server has gone away), then reconnect.

You can create a singleton class to hold your db connection, and test for a live connection every time the application code calls getConnection(). Here's an example I tested:

class DB
{
    protected static $pdo = null;

    public static function getConnection() {
        // initialize $pdo on first call
        if (self::$pdo == null) {
            self::init();
        }

        // now we should have a $pdo, whether it was initialized on this call or a previous one
        // but it could have experienced a disconnection
        try {
            echo "Testing connection...\n";
            $old_errlevel = error_reporting(0);
            self::$pdo->query("SELECT 1");
        } catch (PDOException $e) {
            echo "Connection failed, reinitializing...\n";
            self::init();
        }
        error_reporting($old_errlevel);

        return self::$pdo;
    }

    protected static function init() {
        try {
            echo "Opening new connection...\n";
            self::$pdo = new PDO('mysql:host=huey;dbname=test', 'root', 'root');
            self::$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        } catch (PDOException $e) {
            die($e->getMessage());
        }
    }
}

Use it like this:

echo "Query for 2:\n";
$pdo = DB::getConnection();
echo $pdo->query("SELECT 2")->fetchColumn() . "\n";

echo "\nSleeping 10 seconds...\n";
sleep(10); /* meanwhile I use another window to KILL the connection */
echo "\n";

echo "Query for 3:\n";
$pdo = DB::getConnection();
echo $pdo->query("SELECT 3")->fetchColumn() . "\n";

Output:

Query for 2:
Opening new connection...
Testing connection...
2

Sleeping 10 seconds...

Query for 3:
Testing connection...
Connection failed, reinitializing...
Opening new connection...
3

You can see that it detects that the connection failed, and reinitializes.