MySQL server has gone away - how to deal with it

[Draghmar]

1. What did you do? If possible, provide a simple script for reproducing the error. I'm trying to use Swoole as a API server. At the moment it works great but has one issue that I can't find workaround for. Basically I have DB connection through PDO to the MariaDB and some stuff that works with data. This is simplified code that I'm playing with:

   $dbpool = new ConnectionPoolExt(function() use ($config) {
       return new DB($config); // wrapper that returns PDO
   }, 500);
   $srv = new Server('127.0.0.1', 9503);
   $srv->set([
       'log_level' => 0,
       'log_file' => 'swoole.log',
       'reload_async' => true,
       'http_compression_level' => 5,
       'http_gzip_level' => 5,
   ]);
   $srv->on('request', function($request, $response) use ($srv, $config, $dbpool) {
       try {
           $db = $dbpool->get();
           // Do some stuff with DB
       } catch(Exception | Error $e) {
           var_dump($e);
           exit;
       }
       // Do some other stuff
   });

2. What did you expect to see? Properly working code, with working DB connection. ;) I think ConnectionPool should have some way to auto reconnect if connection is dead. I tried to do something about that but I couldn't get it to work as I intended:

   class ConnectionPoolExt extends ConnectionPool {
   public function __construct(callable $constructor, int $size = self::DEFAULT_SIZE, ?string $proxy = null) {
       parent::__construct($constructor, $size, $proxy);
   }
   
   public function get() {
       if($this->pool === null) {
           throw new RuntimeException('Pool has been closed');
       }
       if($this->pool->isEmpty() && $this->num < $this->size) {
           $this->make();
       }
       $out = $this->pool->pop();
       if(!$out || $out == null) {
           $this->make();
           $out = $this->pool->pop();
       }
       try {
           $r = $out->q('SELECT 1');
           if(!$r) throw new Exception('SELECT 1 failed - No connection');
       } catch(Exception $e) {
           $this->make();
           $out = $this->pool->pop();
       }
       return $out;
   }
   }

   Unfortunetly PDO doesn't have built-in way to detect connection status. No ping or anything like that. At least nothing I'm aware of. :( I'm still testing the code above to find a way to reconnect on failed connection.

3. What did you see instead? If I leave Swoole running along with MariaDB and there won't be enough connections during that time, MariaDB will timeout connections from connection pool with no mean to reconnect, with one of the errors:

   [Warning] Aborted connection 15554 to db: '-' user: '-' host: 'localhost' (Got timeout reading communication packets)
   [Warning] Aborted connection 15568 to db: '-' user: '-' host: 'localhost' (Got an error reading communication packets)

   and Swoole outputs:

   SQLSTATE[HY000]: General error: 2006 MySQL server has gone away

4. What version of Swoole are you using (show your php --ri swoole)?

   
   Swoole => enabled
   Author => Swoole Team <team@swoole.com>
   Version => 4.6.4
   Built => Mar 24 2021 12:21:58
   coroutine => enabled with boost asm context
   epoll => enabled
   eventfd => enabled
   signalfd => enabled
   cpu_affinity => enabled
   spinlock => enabled
   rwlock => enabled
   openssl => OpenSSL 1.1.1j  16 Feb 2021
   dtls => enabled
   http2 => enabled
   pcre => enabled
   zlib => 1.2.11
   mutex_timedlock => enabled
   pthread_barrier => enabled
   futex => enabled
   async_redis => enabled

Directive => Local Value => Master Value swoole.enable_coroutine => On => On swoole.enable_library => On => On swoole.enable_preemptive_scheduler => Off => Off swoole.display_errors => On => On swoole.use_shortname => On => On swoole.unixsock_buffer_size => 8388608 => 8388608

5. What is your machine environment used (show your `uname -a` & `php -v` & `gcc -v`) ?

Linux 5.10.12-arch1-1 #1 SMP PREEMPT Sun, 31 Jan 2021 00:41:06 +0000 x86_64 GNU/Linux

PHP 8.0.3 (cli) (built: Mar 4 2021 05:33:14) ( NTS ) Copyright (c) The PHP Group

Using built-in specs. COLLECT_GCC=gcc COLLECT_LTO_WRAPPER=/usr/lib/gcc/x86_64-pc-linux-gnu/10.2.0/lto-wrapper Target: x86_64-pc-linux-gnu Configured with: /build/gcc/src/gcc/configure --prefix=/usr --libdir=/usr/lib --libexecdir=/usr/lib --mandir=/usr/share/man --infodir=/usr/share/info --with-bugurl=https://bugs.archlinux.org/ --enable-languages=c,c++,ada,fortran,go,lto,objc,obj-c++,d --with-isl --with-linker-hash-style=gnu --with-system-zlib --enable-__cxa_atexit --enable-cet=auto --enable-checking=release --enable-clocale=gnu --enable-default-pie --enable-default-ssp --enable-gnu-indirect-function --enable-gnu-unique-object --enable-install-libiberty --enable-linker-build-id --enable-lto --enable-multilib --enable-plugin --enable-shared --enable-threads=posix --disable-libssp --disable-libstdcxx-pch --disable-libunwind-exceptions --disable-werror gdc_include_dir=/usr/include/dlang/gdc Thread model: posix Supported LTO compression algorithms: zlib zstd gcc version 10.2.0 (GCC)

[NathanFreeman]

try to set max_execution_time = 0 in php.ini

[Draghmar]

max_execution_time is set to 0 for CLI, so there's no need for that. Even if it would be set to something else, it would stop the server altogether, not the DB connection alone. MariaDB is closing connection because there was no transfer from Swoole server - connections in pool aren't used on my test server so often. I could bump equivalent in MariaDB but that's not the solution I'm looking for. ;)

[matyhtf]

Try to reconnect to the database.

for ($i = 0; $i < 2; $i++) {
  $result = $db->query($sql);
  if ($result === false) {
      if ($db->errno == 2013 or $db->errno == 2006 or ($db->errno == 0 and !$db->ping())) {
          $db = $dbpool->get();
          if ($r === true) {
              continue;
          }
      } else {
          return false;
      }
  }
  break;
}

[TorstenDittmann]

The best way to deal with this is having PDOProxy from swoole take care of the exceptions and reconnect itself.

Just add following to the PDOConfig:

->withOptions([
    PDO::ATTR_ERRMODE => PDO::ERRMODE_WARNING
])

This will still throw exceptions, but after the proxy classes have done its work 👍🏻

@matyhtf This should probably be part of the documentation or set by default, otherwise if the exception is thrown here the remaining code is never reached.