Automatic database reconnection after lost connection
programarivm opened this issue · 5 comments
At the moment when the Chess Data server restarts for some reason, the data service's database connection implemented in src/Command/Data/Db.php will be lost. So when the chess data server is back up and running, the data service won't be able to communicate with it anymore because the connection has been lost.
This is happening because the ChessServer\Command\Data\Db class was originally copied and pasted from the PHP Chess API where it is intended to run on-demand over an HTTP connection.
With HTTP, the database connection is opened and closed for each HTTP request. ChessServer\Command\Data\Db is instantiated on each HTTP request. However, the WebSocket protocol provides a full-duplex, real-time communication channel over a single, long-lived connection. With this scheme there is no automatic reconnection if the connection is lost.
ChessServer\Command\Data\Db needs to reconnect to the chess data server if the connection with it is lost.
See:
This is normal with any persistent application in any language.
The databases have a time of inactivity for connections, and then they close it.
Also fail for a db server stop or restart, a network fail, ....
Exist 2 solutions:
- try catch the query and check the error, to reconnect
- Use a timer to call the database with "SELECT 1" if fail, reconnect
Both are easy to implement, but I prefer option 2.
Your Db class, need more work, even for a normal PHP with php-fpm.
I'll try to help you, with this class.
SQLSTATE[HY000]: General error: 2006 MySQL server has gone away.
[2024-08-16T08:45:43.183278+00:00] data.ERROR: Occurred an error {"message":"SQLSTATE[HY000]: General error: 2006 MySQL server has gone away","file":"/usr/share/chess-server/src/Command/Data/Db.php","line":43,"trace":"#0 /usr/share/chess-server/src/Command/Data/Db.php(43): PDOStatement->execute()\n#1 /usr/share/chess-server/src/Command/Data/StatsOpeningCommand.php(32): ChessServer\\Command\\Data\\Db->query()\n#2 /usr/share/chess-server/src/Socket/WorkermanWebSocket.php(57): ChessServer\\Command\\Data\\StatsOpeningCommand->run()\n#3 /usr/share/chess-server/vendor/workerman/workerman/Connection/TcpConnection.php(646): ChessServer\\Socket\\WorkermanWebSocket->ChessServer\\Socket\\{closure}()\n#4 /usr/share/chess-server/vendor/workerman/workerman/Events/Select.php(311): Workerman\\Connection\\TcpConnection->baseRead()\n#5 /usr/share/chess-server/vendor/workerman/workerman/Worker.php(1638): Workerman\\Events\\Select->loop()\n#6 /usr/share/chess-server/vendor/workerman/workerman/Worker.php(1429): Workerman\\Worker::forkOneWorkerForLinux()\n#7 /usr/share/chess-server/vendor/workerman/workerman/Worker.php(1403): Workerman\\Worker::forkWorkersForLinux()\n#8 /usr/share/chess-server/vendor/workerman/workerman/Worker.php(560): Workerman\\Worker::forkWorkers()\n#9 /usr/share/chess-server/src/Socket/WorkermanWebSocket.php(110): Workerman\\Worker::runAll()\n#10 /usr/share/chess-server/cli/workerman/data.php(37): ChessServer\\Socket\\WorkermanWebSocket->run()\n#11 {main}"} []It is NOT the right direction.
Sorry, but I live in Mallorca with the Dana and the local town parties !!
Later I'll help you !!
But I give you a fast tip.
Timer::add( 30, function() {
try {
Db::sql('SELECT 1');
catch ( PDOException $Exception ) {
Db::reconnect();
}
});