Error - MySQL Server has gone away

ℹ️ Support
1

Nextcloud version (eg, 20.0.5): 26.0.1
Operating system and version (eg, Ubuntu 20.04): NextCloudPi
Apache or nginx version (eg, Apache 2.4.25): 2.4.56
PHP version (eg, 7.4): 8.1.10

The issue you are facing:
We recently started using Nextcloud Talk on our installation. Since then I see the server regularly running into an error. The server however keeps returning after some 30-60 seconds. When checking the logging on the installation, there are errors relating to “mySQL server has gone away” and I am struggling, where to start resolving.

Is this the first time you’ve seen this error? (Y/N): Y

The output of your Nextcloud log in Admin > Logging:

image
image2352×1272 380 KB

The output of your config.php file in /path/to/nextcloud (make sure you remove any identifiable information!):

<?php
$CONFIG = array (
  'passwordsalt' => 'XXX',
  'secret' => 'XXX',
  'trusted_domains' => 
  array (
    0 => 'localhost',
    7 => 'nextcloudpi',
    5 => 'nextcloudpi.local',
    8 => 'nextcloudpi.lan',
    3 => 'nextcloudpi',
    11 => 'XXX',
    1 => '192.168.178.XXX',
    14 => 'nextcloudpi',
    20 => 'cloud.XXX.de',
    21 => 'XXX.ddns.net',
    22 => '192.168.178.XXX',
  ),
  'datadirectory' => '/media/myCloudDrive/ncdata/data',
  'dbtype' => 'mysql',
  'version' => '26.0.1.1',
  'overwrite.cli.url' => 'https://nextcloudpi/',
  'dbname' => 'nextcloud',
  'dbhost' => 'localhost',
  'dbport' => '',
  'dbtableprefix' => 'oc_',
  'mysql.utf8mb4' => true,
  'dbuser' => 'XXX',
  'dbpassword' => 'XXX',
  'installed' => true,
  'instanceid' => 'ocbl8r0xpy2t',
  'memcache.local' => '\\OC\\Memcache\\Redis',
  'memcache.locking' => '\\OC\\Memcache\\Redis',
  'redis' => 
  array (
    'host' => '/var/run/redis/redis.sock',
    'port' => 0,
    'timeout' => 0.0,
    'password' => 'XXX',
  ),
  'tempdirectory' => '/media/myCloudDrive/ncdata/data/tmp',
  'mail_smtpmode' => 'smtp',
  'mail_smtpauthtype' => 'LOGIN',
  'mail_from_address' => 'cloud',
  'mail_domain' => 'XXX.de',
  'preview_max_x' => '2048',
  'preview_max_y' => '2048',
  'jpeg_quality' => '60',
  'overwriteprotocol' => 'https',
  'trusted_proxies' => 
  array (
    11 => '127.0.0.1',
    12 => '::1',
    13 => 'nextcloudpi',
    14 => '192.168.178.XXX',
    15 => '192.168.178.XXX',
  ),
  'maintenance' => false,
  'logfile' => '/media/myCloudDrive/ncdata/data/nextcloud.log',
  'loglevel' => '2',
  'log_type' => 'file',
  'default_phone_region' => 'DE',
  'mail_sendmailmode' => 'smtp',
  'mail_smtpsecure' => 'ssl',
  'mail_smtpauth' => 1,
  'mail_smtphost' => 'smtp.XXX.de',
  'mail_smtpport' => '465',
  'mail_smtpname' => 'XXX',
  'mail_smtppassword' => 'XXX',
  'htaccess.RewriteBase' => '/',
  'data-fingerprint' => 'XXX',
  'theme' => '',
  'app_install_overwrite' => 
  array (
    0 => 'nextcloudpi',
    1 => 'previewgenerator',
  ),
);
2

I am not sure about this but once i encountered a similar error and i was missing pdo_mysql php module so try looking if you have it installed and enabled. Probably a longshot but i hope it points you in the right direction.

3

@Irdi
Unfortunately PDO and pdo_mysql is installed. I just checked it via php -m

Any other ideas anyone? It is a real pain, unfortunately. Still bringing up errors.

image
image2356×1430 482 KB

4

This is a general PHP error message that has been troubling users for years; I’ve especially been hit on Drupal installations.

I can be hard to troubleshoot and fix, but here’s some tips to get started: PHP emits this message when it has sent a query to the SQL server but hasn’t received a response within a timeout. This can happen if you submit a huge and slow query or if your SQL server is under heavy load for other reasons.

One way to mitigate this is to check the timeout values set on the PHP and the SQL server side: Ideally, the SQL server should timeout before PHP - passing on a meaningful error message. If PHP times out before SQL, you won’t receive an error message, and the SQL server might even emit another error when the result is ready - because PHP has closed the connection in the meantime.

Troubleshooting along these lines have helped me minimize the error message - but it still shows up every now and then when my setup is under heave load for some reason.

1 Like
5

@larsskj Awesome - thanks for your quick reply. Can you let me know, where to look for the mySQL timeout values? I know where to adjust the php settings (which is 30 seconds btw) but I do not know where in my NextCloudPi installation the mySQL ini is stored.

6

I amended the php.ini and am down to 3 remaining error messages. Any idea, what or how I get rid of those?

image
image2360×674 70.7 KB

7

Open your sql console and do these two queries:

SHOW VARIABLES LIKE 'wait_timeout';
SHOW VARIABLES LIKE 'interactive_timeout';

The default value for those are 28800 (seconds) which are 8 hours.

Some database setups have a shorter value set in the my.conf

8

I agree on the basic MySQL settings; however, unfortunately things aren’t that simple, and I believe that most PHP database drivers have further optimizations that can be set - but the way depends on the specific driver.

The same can be said for the database: I’m running a MariaDB Galera cluster with three nodes which increases complexity quite a bit - but otherwise is an amazing and very stable solution.

Try googling for “MySQL server has gone away” - you’ll get a ton of hits that’ll help you refine your settings even further. But don’t expect it to be an error that can be completely eliminated - you should more try to minimize it.

Recently I’ve had a similar problem with a Roundcube mailserver (a PHP app as well). Core problem was that it didn’t like an inbox with 13,500+ emails - one of the solution strategies could be to reduce the size of the inbox. :smile:

Though I didn’t do that: I managed to make it work by assigning more CPU power.