How can I check a PDO MySQL connection for errors BEFORE I run a query?

Question

My scripts are getting quite riddled with forked processes in a lot of different functions. Whenever pcntl_fork() is called, all MySQL connections are lost. If I run a query on a PDO MySQL connection I get the error "MySQL server has gone away".

The problem is that this error only shows up in PDO::errorinfo() after a failed query execution. I would like to be able to detect if the MySQL server "has gone away" before I try to run a query. That way I could create a PDO wrapper that makes a new connection for me in such situations.

Any ideas?

What about fixing an issue caused that error? You need to cure the disease, not just hide the symptoms. — zerkms, Jul 26 '11 at 15:06
Isn't the cause of the error the forking? Doesn't that mean I **have** to create a new connection every time I fork? — Hubro, Jul 26 '11 at 15:26
The forking is very likely to be the cause of the problem. Ideally, you should fork first and then open a connection. If that is not possible, this comment in the docs may help: http://php.net/manual/en/function.pcntl-fork.php#70721 See also [MySQL server has gone away](http://dev.mysql.com/doc/refman/5.5/en/gone-away.html) - specifically the line about forking. — Mike, Jul 26 '11 at 15:54
Thanks for the links Mike. They confirm everything I guessed. But it doesn't really help me with my problem. Many of my functions fork child processes, and many of these functions are called from a multitude of circumstances. It's simply too much work to keep track of every time a function that might spawn a child process is called and manually create new connections. All I need is some way to detect if a connection has been lost before I attempt to run a query. — Hubro, Jul 26 '11 at 18:04
@Codemonkey: just wrap PDO's query with your own function and check if it has been performed successfully. If not - just re-do it. — zerkms, Jul 27 '11 at 01:18

sivann · Answer 1 · 2012-07-16T20:27:13.943

2

FYI: this has been reported as a bug several times 1,2,3 with no fix so far (5.3.14). The only solution is to do a new connection each time after forking a child, and also set PDO::ATTR_PERSISTENT => false

edited Jul 16 '12 at 20:27

answered Jul 16 '12 at 20:15

sivann

2,083
4
29
44

score 2 · Accepted Answer · answered Jul 31 '11 at 09:30

I give you 2 methods by example (similar in some ways) :
Example 1 :

$sql = 'SELECT count(*) FROM `TABLE`;';
for ($i = 1; $i <= 2; $i++) {
    try {
        $nb = $pdo->query($sql)->fetchColumn();
        if (is_int($nb)) {
            // OK
            break;
        }
    } catch (PDOException $e) {
    //Oups
        if ($i == 1) {
            // First time ? retry
            $pdo = new PDO($dsn, $user, $password);
        } else {
            // Second time, KO
            $nb = "(unknown)";
            echo 'PDO Connection failed: ' . $e->getMessage().'. ';
        }
    }
}

Example 2 :

// check
try {
    $pdo->query('select 1;')
    //OK
} catch (PDOException $e) {
    //Oups => reconnect
    $pdo = new PDO($dsn, $user, $password);
}
// Should be good
$sql = 'SELECT count(*) FROM `TABLE`;';
$nb = $pdo->query($sql)->fetchColumn();

How can I check a PDO MySQL connection for errors BEFORE I run a query?

2 Answers2