Check the connection before querying into database

Question

Check the connection before querying into database

hostbox2 Jun '18

Created Jun '18	Last Reply Aug '18	Replies 4	Views 1214	Votes 1

hostbox2 14.3k

Jun '18

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;
    });

1

Lajos Bencz · Answer 1 · 2018-06-07T08:25:02-07:00

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.

hostbox2 · Answer 2 · 2018-06-07T09:00:42-07:00

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")

Lajos Bencz · Answer 3 · 2018-06-07T14:25:32-07:00

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

hostbox2 · Answer 4 · 2018-06-07T23:59:13-07:00

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
                    }
                }

            }

hostbox2 · Answer 5 · 2018-06-08T01:08:27-07:00

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

Wojciech Ślawski · Answer 6 · 2018-06-08T05:03:07-07:00

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

javaschoo · Answer 7 · 2018-08-20T21:50:11-07:00

javaschoo
102

Aug '18

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