Reconnect to database

[DestinyMKas]

Hello,

I use phalcon 5.9.3 + php 8.4 + cockroachdb over postregsql extension.

I have script which is long running and sometimes stale for 10 minutes. In such moment database connection is lost.
Exception: SQLSTATE[HY000]: General error: 7 no connection to the server

I try to do:

$this->db->close();
$this->db->connect();
$this->db->query($myQuery);

but that doesn't help.

Maybe you have easy solution how to reconnect to database?

[ismaildasci]

for long running scripts with phalcon, the db connection times out. close() and connect() should work but you need to get fresh instance.

try this approach:

// get the di
$di = Phalcon\Di\Di::getDefault();

// remove old connection
$di->remove("db");

// set new connection
$di->set("db", function() {
    return new Phalcon\Db\Adapter\Pdo\Postgresql([
        "host" => "your-host",
        "username" => "user",
        "password" => "pass",
        "dbname" => "dbname",
    ]);
}, true);

// now get fresh connection
$db = $di->get("db");

or wrap queries with reconnect logic:

function executeWithReconnect($query) {
    try {
        return $this->db->query($query);
    } catch (Exception $e) {
        if (strpos($e->getMessage(), "no connection") !== false) {
            // reconnect
            $this->db = $this->getDI()->get("db");
            return $this->db->query($query);
        }
        throw $e;
    }
}

also check cockroachdb idle connection timeout settings