We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

Check the connection before querying into database

It is necessary to check the connection to the database before the querying, if there is no connection - need to make a reconnect, and then immediately repeat this query.

I wrote EventsManager but I don't know how to execute the current query. I do not know where to get and in what format.

    $di->set('db', function () use ($config) {
        $connection = new \Phalcon\Db\Adapter\Pdo\Mysql(array(
            'host'                  => $this->get('config')->database->host,
            'username'              => $this->get('config')->database->username,
            'password'              => $this->get('config')->database->password,
            'dbname'                => $this->get('config')->database->dbname,
            'charset'               => $this->get('config')->database->charset,
            'notNullValidations'    => false,
        ));

        $eventsManager = new \Phalcon\Events\Manager();

        //Listen all the database events
        $eventsManager->attach('db', function($event, $connection) {
            if ($event->getType() == 'beforeQuery') {
                $sql =     // ???? HERE NEED TO GET THE CURRENT QUERY
                try {
                    $connection->executeQuery($sql);
                } catch (\PDOException $e) {
                    if (strpos($e->getMessage(), 'MySQL server has gone away') !== false) {
                        $connection->connect();
                        $connection->executeQuery($sql);
                    }
                }

            }
        });

        //Assign the eventsManager to the db adapter instance
        $connection->setEventsManager($eventsManager);

        return $connection;
    });
edited Jun '18

We have some long running tasks in CLI, and we ping the DB connection periodically with eventloop.

You could place that ping in the event callback like so:

        $eventsManager->attach('db', function($event, $connection) {
            if ($event->getType() == 'beforeQuery') {
                try {
                    // the query doesnt matter, this will also fail if there's no connection
                    $connection->executeQuery("SELECT 1");
                }
                catch(\Exception $e) {
                    // nor we care about the specific query here, we just need to reconnect on errors
                    $connection->connect();
                }
            }
        });

This is not the best performance-wise, although SELECT 1 is a relatively cheap operation.



14.3k

I saw this decision on the forum. this variant is not suitable. I don't want to ping manually every time. if I can do it automatically, if the primary query return the error ("PHP Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away")

edited Jun '18

Instead of hooking in with events, override the relevant methods of the db adapter's class

https://github.com/phalcon/cphalcon/blob/master/phalcon/db/adapter/pdo.zep#L200



14.3k

Is it really impossible to get the primary query, and then run it by means of phalcon in PHP?

            if ($event->getType() == 'beforeQuery') {
                $sql =     // ???? HERE NEED TO GET THE PRIMARY (SOURCE) QUERY
                try {
                    $connection->executeQuery($sql); // EXECUTE THIS QUERY
                } catch (\PDOException $e) {
                    if (strpos($e->getMessage(), 'MySQL server has gone away') !== false) { // IF WE GET ERROR
                        $connection->connect(); // RECONNECT
                        $connection->executeQuery($sql); // REPEAT THiS PRIMARY (SOURCE) QUERY
                    }
                }

            }


14.3k
edited Jun '18

I also try this method:

                try {
                    // the query doesnt matter, this will also fail if there's no connection
                    $connection->query('SELECT 1');
                }
                catch(\Exception $e) {
                    // nor we care about the specific query here, we just need to reconnect on errors
                    $connection->connect();
                }

But i get error: "Segmentation fault". Does anyone know what this means? ($connection->execute('SELECT 1'); returned the same error)

  • PHP 7.1.16 (cli) (built: Mar 28 2018 13:19:29) ( NTS )
  • CentOS 7 x64
  • installed Phalcon 3.3.2

$connection->getRealSQLStatement() for getting sql statement and $connection->getSqlVariables() for getting bind params.

Programs must be written for people to read, and only incidentally for machines to execute.