MySQL with ND connection failover with PhalconPHP/PDO

Question

In this part of Mysql-nd manual is described how to implement recommended way of failing over when loosing connection to slave MySQL server.

I'am willing to implement it in PhalconPHP. As I have couple of important projects using Phalcon and Mysql-nd, it is really important for me to do it in right place.

Trying to find some documentation, but can't find any example to even start with. Was trying to find EventManager approach, looking into Phalcon Documentation here and here but can't find way to do it transparently.

Most attractive way would be to use event manager to capture error event and query the same again if connection error.

1 UPDATE

After reading some Phalcon sources I found, that there may be no way to run same query second time in standard way - I mean here via some kind of PDO parameter or using Phalcons' EventManager attached to db service. One possible attempt I found is to actually run any query after db:afterConnection event, but it is not a solution.

2 UPDATE

db:afterConnection is hardly reachable, instead it is possible to gather everything during db:beforeQuery. Problem is, that PDO is run by Phalcon with PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, so when connection to slave dies it can't reach db:afterConnection event. It is possible to obtain PDO instance during db:beforeQuery and change this attribute via EventManager, but it gives nothing, because even if I am able to send same query second time, I can't find a way to return it in proper place (cannot override query result during db:afterQuery) because obtained statement is not a part of event send, and Eventmanager result is not being used at all:

    if typeof statement == "object" {
        if typeof eventsManager == "object" {
            eventsManager->fire("db:afterQuery", this, bindParams);
        }
        return new ResultPdo(this, statement, sqlStatement, bindParams, bindTypes);
    }

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

For now it seems fixed with configuration:

{
    "db-cluster": {
        "master": {
            "master": {
                "host": "master.local",
                "port": 3306
            }
        },
        "slave": {
            "slave-1": {
                "host": "slave-1.local",
                "port": 3306
            },
            "slave-2": {
                "host": "slave-2.local",
                "port": 3306
            },
            "slave-3": {
                "host": "slave-3.local",
                "port": 3306
            }
        },
        "filters": {
            "roundrobin": []
        },
        "failover": {
            "strategy": "loop_before_master",
            "remember_failed": true,
            "max_retries": 1
        },
        "server_charset": "utf8"
    }
}

If server is unreachable it fallbacks to other, problem was that it was trying to connect to unreachable server for at least 3 seconds. Walkaround for that would be:

$eventsManager = new EventsManager();
$connection->setEventsManager($eventsManager);

$eventsManager->attach('db:beforeQuery', function($event, $connection) {

    // fix: if slave does not respond, without this it goes over 3 seconds before trying next one
    !defined('DST') && define('DST', ini_get('default_socket_timeout'));
    ini_set("default_socket_timeout", 1);

});

$eventsManager->attach('db:afterQuery', function($event, $connection) {
    ini_set('default_socket_timeout', defined('DST') ? DST : 60);
});

Even if it works now (when slave is unreachable it hangs on it for at most 1 second - and this is still a lot of time), it still does not allow me to put my hands on connection from PHP source to write a recommended solution.

UPDATE

It is possible to override executePrepared method from Phalcon\Db\Adapter\Pdo class via extending Phalcon\Db\Adapter\Pdo\Mysql class:

namespace Application;

use \PDOException;

class Mysql extends \Phalcon\Db\Adapter\Pdo\Mysql {
    
    public function executePrepared(statement, placeholders, dataTypes) {

        try {
            !defined('DST') && define('DST', ini_set('default_socket_timeout', 1));
            $stmt = parent::executePrepared(statement, placeholders, dataTypes);
            ini_set('default_socket_timeout', DST ?: 60);
            return $stmt;
        } catch(PDOException $e) {

            if(/* logic to find [2002, 2003, 2005] sql errors */) {
                return $this->executePrepared(statement, placeholders, dataTypes);
            }

            throw $e;
        }
    }
}

and build db service with it.

MySQL with ND connection failover with PhalconPHP/PDO

1 UPDATE

2 UPDATE

1 Answers1

UPDATE