• 9
name

A PHP Error was encountered

Severity: Notice

Message: Undefined index: userid

Filename: views/question.php

Line Number: 187

Backtrace:

File: /home/prodcxja/public_html/questions/application/views/question.php
Line: 187
Function: _error_handler

File: /home/prodcxja/public_html/questions/application/controllers/Questions.php
Line: 433
Function: view

File: /home/prodcxja/public_html/questions/index.php
Line: 315
Function: require_once

name Punditsdkoslkdosdkoskdo

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

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?

      • 2
    • I posted an answer, and didn't realise you put my answer in your question asking if there is a nicer way, my bad. I would go with using the SELECT 1 method, as IIRC, there isn't an actual "ping" method within PDO.
    • I will be watching this question in a very hopeful manner because I have a very similar scenario that I would really like an elegant solution to.

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.

  • 14
Reply Report