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.

Answer 1

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.