search

chesslablab / chess-server

Asynchronous PHP chess server.
https://chesslablab.github.io/chess-server/
MIT License
47 stars 24 forks source link

Automatic database reconnection after lost connection #354

Closed programarivm closed 1 month ago

programarivm commented 1 month ago

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:

joanhey commented 1 month ago

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:

  1. try catch the query and check the error, to reconnect
  2. 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.

programarivm commented 1 month ago

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}"} []
programarivm commented 1 month ago
joanhey commented 1 month ago

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

joanhey commented 1 month ago

But I give you a fast tip.

Timer::add( 30, function() {
          try {
              Db::sql('SELECT 1');
          catch ( PDOException $Exception ) {
              Db::reconnect();
          }
});

https://manual.workerman.net/doc/en/timer/add.html