Inaccessible files and possible database corruption after recent MySQL-Update

Zealot · July 31, 2018, 12:52pm

Support intro

Sorry to hear you’re facing problems

help.nextcloud.com is for home/non-enterprise users. If you’re running a business, paid support can be accessed via portal.nextcloud.com where we can ensure your business keeps running smoothly.

In order to help you as quickly as possible, before clicking Create Topic please provide as much of the below as you can. Feel free to use a pastebin service for logs, otherwise either indent short log examples with four spaces:

example

Or for longer, use three backticks above and below the code snippet:

longer
example
here

Some or all of the below information will be requested if it isn’t supplied; for fastest response please provide as much as you can

Nextcloud version: 13.0.5
Operating system and version: Ubuntu 16.04.5 LTS (Virtualbox guest system on Windows 7 host)
Apache or nginx version: Apache 2.4.18
PHP version: 7.0.30

The issue you are facing:
I’m running an approx. 2 year-old installation based on a pre-configured VM-Image from techandme.se, it comes with Webmin pre-installed which I regularly use for updating software packages. Yesterday, five packages pertaining to MySQL were available, so I installed them:

main amd64 mysql-common all 5.7.23-0ubuntu0.16.04.1
main amd64 mysql-client-5.7 amd64 5.7.23-0ubuntu0.16.04.1
main amd64 mysql-client-core-5.7 amd64 5.7.23-0ubuntu0.16.04.1
main amd64 mysql-server-5.7 amd64 5.7.23-0ubuntu0.16.04.1
main amd64 mysql-server-core-5.7 amd64 5.7.23-0ubuntu0.16.04.1

Right after installing, my Nextcloud-Log filled with MySQL-errors like

General error: 1034 Incorrect key file for table ‘oc_properties’; try to repair it
General error: 2006 MySQL server has gone away

See below for log. I can still access my files via the Nextcloud-UI, but I am no longer able to use Mountain Duck to mount my storage as a local volume in Windows. Upon trying to connect, Mountain Duck shows a 500 internal server error. Log: https://pastebin.com/d4JA4NjZ
I have already tried repairing the table “oc_properties” by issuing the command “repair table oc_properties” but all I’m getting is “The storage engine for the table doesn’t support repair”. I don’t have any other ideas on how to fix this issue, any help is appreciated.

Is this the first time you’ve seen this error?: Y

Steps to replicate it:

Install recent MySQL-Updates
Try to access your files via Mountain Duck

The output of your Nextcloud log in Admin > Logging:
https://pastebin.com/fcj2ZxVF

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

<?php
$CONFIG = array (
  'passwordsalt' => '<removed>',
  'secret' => '<removed>',
  'trusted_domains' => 
  array (
    0 => 'localhost',
    1 => '10.0.0.20',
    2 => 'nextcloud',
    3 => '<removed>',
  ),
  'datadirectory' => '/var/ncdata',
  'overwrite.cli.url' => 'https://10.0.0.20/',
  'dbtype' => 'mysql',
  'version' => '13.0.5.2',
  'dbname' => 'nextcloud_db',
  'dbhost' => 'localhost',
  'dbport' => '',
  'dbtableprefix' => 'oc_',
  'dbuser' => 'oc_ncadmin',
  'dbpassword' => '<removed>',
  'logtimezone' => 'UTC',
  'installed' => true,
  'instanceid' => '<removed>',
  'appstore.experimental.enabled' => 'true',
  'mail_smtpmode' => 'smtp',
  'mail_smtpauth' => 1,
  'mail_smtpauthtype' => 'LOGIN',
  'mail_smtpsecure' => 'ssl',
  'mail_smtpname' => '',
  'mail_smtppassword' => '',
  'preview_libreoffice_path' => '/usr/bin/libreoffice',
  'memcache.local' => '\\OC\\Memcache\\APCu',
  'filelocking.enabled' => true,
  'memcache.distributed' => '\\OC\\Memcache\\Redis',
  'memcache.locking' => '\\OC\\Memcache\\Redis',
  'redis' => 
  array (
    'host' => '/var/run/redis/redis.sock',
    'port' => 0,
    'timeout' => 0,
    'dbindex' => 0,
  ),
  'htaccess.RewriteBase' => '/',
  'maintenance' => false,
  'updater.release.channel' => 'stable',
  'theme' => '',
  'loglevel' => 2,
  'trashbin_retention_obligation' => 'disabled',
);

The output of your Apache/nginx/system log in /var/log/____:

[Tue Jul 31 06:25:07.297537 2018] [:notice] [pid 1250] mod_qos(009): loaded MPM is 'prefork' but mod_qos should be used with MPM 'Worker' only.
[Tue Jul 31 06:25:07.297567 2018] [:warn] [pid 1250] mod_qos(009): mod_unique_id not available (mod_qos generates simple request id if required)
[Tue Jul 31 06:25:07.297611 2018] [mpm_prefork:notice] [pid 1250] AH00163: Apache/2.4.18 (Ubuntu) OpenSSL/1.0.2g mod_qos/11.21 configured -- resuming normal operations
[Tue Jul 31 06:25:07.297614 2018] [core:notice] [pid 1250] AH00094: Command line: '/usr/sbin/apache2'
[Tue Jul 31 11:44:50.115356 2018] [mpm_prefork:notice] [pid 1250] AH00169: caught SIGTERM, shutting down
[Tue Jul 31 11:48:04.778684 2018] [:notice] [pid 1239] mod_qos(009): loaded MPM is 'prefork' but mod_qos should be used with MPM 'Worker' only.
[Tue Jul 31 11:48:04.789516 2018] [:warn] [pid 1239] mod_qos(009): mod_unique_id not available (mod_qos generates simple request id if required)
[Tue Jul 31 11:48:16.246833 2018] [:notice] [pid 1240] mod_qos(009): loaded MPM is 'prefork' but mod_qos should be used with MPM 'Worker' only.
[Tue Jul 31 11:48:16.246864 2018] [:warn] [pid 1240] mod_qos(009): mod_unique_id not available (mod_qos generates simple request id if required)
[Tue Jul 31 11:48:16.248904 2018] [mpm_prefork:notice] [pid 1240] AH00163: Apache/2.4.18 (Ubuntu) OpenSSL/1.0.2g mod_qos/11.21 configured -- resuming normal operations
[Tue Jul 31 11:48:16.248919 2018] [core:notice] [pid 1240] AH00094: Command line: '/usr/sbin/apache2'
[Tue Jul 31 13:46:47.316626 2018] [:error] [pid 1320] [client <removed>:49511] PHP Fatal error:  Uncaught Doctrine\\DBAL\\DBALException: Failed to connect to the database: An exception occured in driver: SQLSTATE[HY000] [2002] No such file or directory in /var/www/nextcloud/lib/private/DB/Connection.php:64\nStack trace:\n#0 /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(429): OC\\DB\\Connection->connect()\n#1 /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(389): Doctrine\\DBAL\\Connection->getDatabasePlatformVersion()\n#2 /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(328): Doctrine\\DBAL\\Connection->detectDatabasePlatform()\n#3 /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(623): Doctrine\\DBAL\\Connection->getDatabasePlatform()\n#4 /var/www/nextcloud/lib/private/DB/Connection.php(151): Doctrine\\DBAL\\Connection->setTransactionIsolation(2)\n#5 /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DriverManager.php(172): OC\\DB\\Connection->__construct(Array, Object(Doctrine\\DBAL\\Driver\\PDOMySql\\Driver), Obj in /var/www/nextcloud/lib/private/DB/Connection.php on line 64
[Tue Jul 31 13:46:47.316858 2018] [:error] [pid 1320] [client <removed>:49511] PHP Fatal error:  Uncaught Doctrine\\DBAL\\DBALException: Failed to connect to the database: An exception occured in driver: SQLSTATE[HY000] [2002] No such file or directory in /var/www/nextcloud/lib/private/DB/Connection.php:64\nStack trace:\n#0 /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(429): OC\\DB\\Connection->connect()\n#1 /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(389): Doctrine\\DBAL\\Connection->getDatabasePlatformVersion()\n#2 /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(328): Doctrine\\DBAL\\Connection->detectDatabasePlatform()\n#3 /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(623): Doctrine\\DBAL\\Connection->getDatabasePlatform()\n#4 /var/www/nextcloud/lib/private/DB/Connection.php(151): Doctrine\\DBAL\\Connection->setTransactionIsolation(2)\n#5 /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DriverManager.php(172): OC\\DB\\Connection->__construct(Array, Object(Doctrine\\DBAL\\Driver\\PDOMySql\\Driver), Obj in /var/www/nextcloud/lib/private/DB/Connection.php on line 64
[Tue Jul 31 13:47:31.123542 2018] [:error] [pid 15128] [client <removed>:49514] PHP Fatal error:  Uncaught Doctrine\\DBAL\\DBALException: Failed to connect to the database: An exception occured in driver: SQLSTATE[HY000] [2002] Connection refused in /var/www/nextcloud/lib/private/DB/Connection.php:64\nStack trace:\n#0 /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(429): OC\\DB\\Connection->connect()\n#1 /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(389): Doctrine\\DBAL\\Connection->getDatabasePlatformVersion()\n#2 /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(328): Doctrine\\DBAL\\Connection->detectDatabasePlatform()\n#3 /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(623): Doctrine\\DBAL\\Connection->getDatabasePlatform()\n#4 /var/www/nextcloud/lib/private/DB/Connection.php(151): Doctrine\\DBAL\\Connection->setTransactionIsolation(2)\n#5 /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DriverManager.php(172): OC\\DB\\Connection->__construct(Array, Object(Doctrine\\DBAL\\Driver\\PDOMySql\\Driver), Object(Doc in /var/www/nextcloud/lib/private/DB/Connection.php on line 64
[Tue Jul 31 13:47:31.123764 2018] [:error] [pid 15128] [client <removed>:49514] PHP Fatal error:  Uncaught Doctrine\\DBAL\\DBALException: Failed to connect to the database: An exception occured in driver: SQLSTATE[HY000] [2002] Connection refused in /var/www/nextcloud/lib/private/DB/Connection.php:64\nStack trace:\n#0 /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(429): OC\\DB\\Connection->connect()\n#1 /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(389): Doctrine\\DBAL\\Connection->getDatabasePlatformVersion()\n#2 /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(328): Doctrine\\DBAL\\Connection->detectDatabasePlatform()\n#3 /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(623): Doctrine\\DBAL\\Connection->getDatabasePlatform()\n#4 /var/www/nextcloud/lib/private/DB/Connection.php(151): Doctrine\\DBAL\\Connection->setTransactionIsolation(2)\n#5 /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DriverManager.php(172): OC\\DB\\Connection->__construct(Array, Object(Doctrine\\DBAL\\Driver\\PDOMySql\\Driver), Object(Doc in /var/www/nextcloud/lib/private/DB/Connection.php on line 64
[Tue Jul 31 14:03:32.771920 2018] [:error] [pid 14549] [client <removed>:49561] PHP Fatal error:  Uncaught Doctrine\\DBAL\\DBALException: Failed to connect to the database: An exception occured in driver: SQLSTATE[HY000] [2002] No such file or directory in /var/www/nextcloud/lib/private/DB/Connection.php:64\nStack trace:\n#0 /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(429): OC\\DB\\Connection->connect()\n#1 /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(389): Doctrine\\DBAL\\Connection->getDatabasePlatformVersion()\n#2 /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(328): Doctrine\\DBAL\\Connection->detectDatabasePlatform()\n#3 /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(623): Doctrine\\DBAL\\Connection->getDatabasePlatform()\n#4 /var/www/nextcloud/lib/private/DB/Connection.php(151): Doctrine\\DBAL\\Connection->setTransactionIsolation(2)\n#5 /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DriverManager.php(172): OC\\DB\\Connection->__construct(Array, Object(Doctrine\\DBAL\\Driver\\PDOMySql\\Driver), Obj in /var/www/nextcloud/lib/private/DB/Connection.php on line 64
[Tue Jul 31 14:03:32.772123 2018] [:error] [pid 14549] [client <removed>:49561] PHP Fatal error:  Uncaught Doctrine\\DBAL\\DBALException: Failed to connect to the database: An exception occured in driver: SQLSTATE[HY000] [2002] No such file or directory in /var/www/nextcloud/lib/private/DB/Connection.php:64\nStack trace:\n#0 /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(429): OC\\DB\\Connection->connect()\n#1 /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(389): Doctrine\\DBAL\\Connection->getDatabasePlatformVersion()\n#2 /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(328): Doctrine\\DBAL\\Connection->detectDatabasePlatform()\n#3 /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(623): Doctrine\\DBAL\\Connection->getDatabasePlatform()\n#4 /var/www/nextcloud/lib/private/DB/Connection.php(151): Doctrine\\DBAL\\Connection->setTransactionIsolation(2)\n#5 /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DriverManager.php(172): OC\\DB\\Connection->__construct(Array, Object(Doctrine\\DBAL\\Driver\\PDOMySql\\Driver), Obj in /var/www/nextcloud/lib/private/DB/Connection.php on line 64

MichaIng · August 1, 2018, 4:03pm

Can you access mysql console directly and/or do a mysqldump, just to have a backup in case?

Nextcloud uses InnoDB database engine, which does indeed not support repairing, AFAIK. But you can try to “optimize” the tables which will recreate them and repair corruptions by this, hopefully: mysqloptimize nextcloud_db

Unusually it should not be needed, but it is at least safer to put Nextcloud into maintenance mode, before doing MySQL updates or other maintenance, e.g. via occ command: sudo -u www-data php /path/to/nextcloud/occ maintenance:mode --on and --off afterwards.

Zealot · August 2, 2018, 7:37am

Hello MichaIng, thanks for your help.

First, I tried to backup the databases via the Webmin interface, using the “Backup Database”-Button, which seemed to work at first (I was able to download backup files to my desktop), but the file sizes of the backup files are far too small to be full backups. The biggest of them is approx. 60 MB, but I store gigabytes of data in my cloud, so I’m not sure how these files will help me in the case of data loss. But then again, I’m no database expert.

Then I tried this from the command line:

mysqldump -u root -p<password> -h localhost --verbose nextcloud_db > nextcloud_db.bak

which gave me the following error message:

mysqldump: Got error: 1146: Table ‘nextcloud_db.oc_properties’ doesn’t exist when using LOCK TABLES

Also, the optimization didn’t seem to work. I used the following command:

mysqloptimize -p<password> nextcloud_db

which gave me lots of lines like this one:

nextcloud_db.oc_accounts
note : Table does not support optimize, doing recreate + analyze instead
status : OK

but for the table “oc_properties” I got this message at first:

nextcloud_db.oc_properties
note : Table does not support optimize, doing recreate + analyze instead
error : Index PRIMARY is corrupted
status : Operation failed

After trying again, I got this:

nextcloud_db.oc_properties
Error : Table ‘nextcloud_db.oc_properties’ doesn’t exist
status : Operation failed

This behaviour is reproducible, meaning that right after restarting the mysql-server, the table “oc_properties” is addressable but corrupt, and after some minutes it doesn’t seem to exist anymore.

Is there any way to restore or rebuild this table?

Zealot · August 25, 2018, 10:47pm

For anyone interested, I wasn’t able to fix this issue, so I set up a new Nextcloud VM image and copied everything over from a backup (files, calendars, contacts).