Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

How to see if persistent database connection is still active?

When I go through this documentation: https://docs.phalconphp.com/en/latest/api/Phalcon%5Db%5Adapter%5Pdo%5Mysql.html

I find only getConnectionId() which seems to return 0 always for me. I'd like to find out if my connection is open or not.

I have a CLI task running in an eternal loop. Sometime its running lots of queries, but during the night it usually has nothing to run and may timeout. What I'd like to do is check if my connection is still open, before throwing any queries at it. I wanna avoid getting mysql has gone away error.

Is there anyway I could test if this is still active? $db = \Phalcon\Di::getDefault()->getShared('db');

I could do $db->connect() everytime before running queries, but it's not a persistent connection anymore then. It opens a new connection every time.



7.0k
Accepted
answer

The timeout will appear on MySQL site, even if method getConnectionId() returns an ID, Phalcon will not know that connection is timeouted. Your best way is to wrap your query to try-catch and on exception reconnect and retry.

edited Mar '16

I had exactly same issue with persistent CLI task. There are couple of ways to solve it. First, you want to make sure that you're actually using PDO persistent connections.

In your DB service, make sure you have an array with key 'options' as in my example:

    $dbConfig['options'] = [
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_PERSISTENT => 1,
        //Many web applications will benefit from making persistent connections to database servers. Persistent connections are not closed at the end of the script, but are cached and re-used when another script requests a connection using the same credentials.
        // The persistent connection cache allows you to avoid the overhead of establishing a new connection every time a script needs to talk to a database, resulting in a faster web application.
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
    ];

But, it might be safer and easier to just recconect each time from your CLI APP.

$this->db->connect(); //This will reconnect even active connection pipe