Symfony PDOSessionHandler重新连接到棘轮websocket服务器的MySQL数据库

Question

So, this is quiet a specific situation, but i'll try anyway. I have a Symfony website and a Ratchet websocket app running on the same server. I would like to share symphony session data as described here: http://socketo.me/docs/sessions. Except that i would like to use the PDOSessionHandler. My code to start the websocket server looks as follows:

//prepare websocket app
$pusher = $this->getContainer()->get('webSocketApp');
$loop   = \React\EventLoop\Factory::create();

// Listen for messages from the http server
$context = new \React\ZMQ\Context($loop);
$pull = $context->getSocket(\ZMQ::SOCKET_PULL);
$pull->bind('tcp://127.0.0.1:5050'); // Binding to 127.0.0.1 means the only client that can connect is itself
$pull->on('message', array($pusher, 'onServerMessage'));

//prepare pdosessionhandler for session data
$entityManager = $this->getContainer()->get('doctrine')->getEntityManager();
$pdo = $entityManager->getConnection()->getWrappedConnection();
$dbOptions = array(
            'db_table' => 'sessions',
            'db_id_col' => 'sess_id',
            'db_data_col' => 'sess_data',
            'db_time_col' => 'sess_time',
            'db_lifetime_col' => 'sess_lifetime',
            'lock_mode'       => 0
);
$session = new PdoSessionHandler($pdo, $dbOptions);

// Set up our WebSocket server for clients wanting real-time updates
$webSock = new \React\Socket\Server($loop);
$webSock->listen(8080, '0.0.0.0'); // Binding to 0.0.0.0 means remotes can connect
$webServer = new IoServer(
    new HttpServer(
            new WsServer(
                    new SessionProvider($pusher, $session)
            )
    ),
    $webSock
);

$loop->run();

Everything works fine, except that after 8 hours without traffic on the websocket server, the pdo connection provided to the PDOSessionHandler hits the mysql wait_timeout and interactive_timeout and any websocket connections that open afterwards and want to access session data cause a General error: 2006 MySQL server has gone away.

For any other database query within the websocket server i can simply run this code to reconnect to the database in case there is an error:

//in case the db connection is timed out (I hope this helps...)
if ($this->em->getConnection()->ping() === false) {
    $this->em->getConnection()->close();
    $this->em->getConnection()->connect();
}

However, since I have to provide the pdo connection for the sessionHandler when starting the websocket server, this does not help when accessing any session data. So the question is if there is any way to get the Symfony PDOSessionHandler to reconnect to the database when the connection has timed out? To me this seems like a feature that should be pretty standart in a PDO session handler...

Alternatively, Is there a possibility to access session data directly from my websocket app (a php script that is called from command line)?

Answer 1

We had a similar Problem with the PDOSessionHandler and the GOSWebsocketBundle: https://github.com/GeniusesOfSymfony/WebSocketBundle

We used the PDOSessionHanlder to authenticate Users through sessions in our application and after 8 hours of "waiting" we got the same MySQL Error when trying to log in in our application.

We created a "Periodic Service" (function to be run every x seconds with the IO loop.) https://github.com/GeniusesOfSymfony/WebSocketBundle/blob/master/Resources/docs/PeriodicSetup.md

We injected the PDO Service in the Periodic Service:

<service id="myapp.database_reconnect.periodic" class="My\App\AppBundle\Websocket\DatabaseReconnectPeriodic">
        <argument type="service" id="doctrine.orm.entity_manager" />
        <argument type="service" id="pdo" />
        <tag name="gos_web_socket.periodic" />
    </service>

And then in die Periodic Service on every "Tick" (every X Seconds) we run a query with the PDO Service:

try {
        /** @var \PDOStatement $res */
        $res = $this->pdo->query('SELECT 1'); //where $this->pdo is the injected service.
        echo 'Result: ' . $res->errorCode();
    } catch (PDOException $e) {
        echo 'PDO connection lost';
    }

Well you can inject a logger service and log this "ticks" and the results to the dev.log or prod.log. The 8 hours are from the wait_timeout from your my.ini or .cnf. Make sure to run the "tick" every X hours where X is smaller than the timeout value in your mysql configuration. After running the SELECT Statement the timout will reset to 8 hours.

Maybe i would help you? :)

And well yes, we had first the solution with the ping();. You can run this snippet in every "tick", too. Just inject the EntityManager Service ;-)