DB Connection in PHP Socket Server

Question

I'm running a Yii2 console application which starts a websocket chat service. That all is working fine and as it's supposed to, but after some time of inactivity I get SQLSTATE[HY000]: General error: 2006 MySQL server has gone away. I tried to increase timeouts, set the user abort to false and to set PDO::ATTR_PERSISTENT => true in the PDO's constructor, but this is still happening.

Is there a way to check if a database connection is still active or if not how to reconnect to the db. Either in pure php or better with the Yii2 framework.

did you tried to set the MYSQL `host` in config to `127.0.0.1` instead of `localhost` — Muhammad Omer Aslam, Apr 29 '18 at 16:41

score 1 · Accepted Answer · answered Apr 29 '18 at 18:41

I had a similar problem and solved it by creating my own class for DB connection, which ensures that connection is active before actual query.

class DbConnection extends \yii\db\Connection {

    private $stamp;

    /**
     * {@inheritdoc}
     */
    public function createCommand($sql = null, $params = []) {
        try {
            // send ping on every 10 seconds
            if ($this->stamp < time()) {
                $this->stamp = time() + 10;
                parent::createCommand('SELECT 1')->execute();
            }
        } catch (\yii\db\Exception $e) {
            // if ping fail, reconnect
            $this->close();
            $this->open();
        }
        return parent::createCommand($query);
    }
}

Once every 10 seconds it sends "ping" query before creating a command. If ping fails (connection was interrupted), it tries to reconnect.

This will not prevent from disconnecting, but it will automatically reconnect in case if connection was interrupted. This may be tricky if you're using transactions - if connection is interrupted in the middle of transaction, transaction will be implicitly rollback by DB, and above code will implicitly reconnect, so you don't even notice that your transaction was rollback at some point.

Also I didn't test it in master-slave configuration. But it worked perfectly fine in my case (read only connection to single server), so you may use it as a base and adjust for your needs with additional checks for transactions or master/slave connections.

I was thinking about something similar, thanks for sharing. I'll give it a try. — Fussel, Apr 29 '18 at 18:44

score 0 · Answer 2 · answered Apr 29 '18 at 17:26

0

Not sure about where exactly you should go for this code exactly, but the concerned code to check if the connection is active following methods in yii\db\Connection class can be used

getIsActive() : Returns a value indicating whether the DB connection is established.

and to reconnect you can use

open() : Establishes a DB connection. It does nothing if a DB connection has already been established.

if(Yii::$app->db->isActive === FALSE){
     Yii::$app->db->open();
}

answered Apr 29 '18 at 17:26

Muhammad Omer Aslam

22,976
9
42
68

I tried to call just `Yii::$app->db->open();` as it has internal checks if the connection exists, but the problem seems to be that the PHP process assumes that the connection is still open, while it's not (or was closed by the database server) and I can't find any way to check exactly that. – Fussel Apr 29 '18 at 17:34
did you tried to add `mysql.connect_timeout = -1` in your `/cli/php.ini` temporarily to troubleshoot. – Muhammad Omer Aslam Apr 29 '18 at 17:51
Not yet, I will try it – Fussel Apr 29 '18 at 18:13

DB Connection in PHP Socket Server

2 Answers2