DB - index corrupted

Nextcloud version (eg, 12.0.2): 14.0.3
Operating system and version (eg, Ubuntu 17.04): Ubuntu Server 18.04.1
Apache or nginx version (eg, Apache 2.4.25): Apache/2.4.29
PHP version (eg, 7.1): 7.2.10
DB: MariaDB 10.1.34

The issue you are facing:

The short version is that an index of one (or more, not sure) of the database tables got corrupted. As to how that might have happened, a few days ago I found out that my entire filesystem was read-only. I used fsck to fix the issues and that’s probably how the index became corrupted. Here is the error from Apache2’s error.log:

PHP Fatal error:  Uncaught PDOException: SQLSTATE[HY000]: General error: 1712 Index oc_file_locks is corrupted in /var/www/html/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:105\nStack trace:\n#0 /var/www/html/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php(105): PDOStatement->execute(NULL)\n#1 /var/www/html/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(1006): Doctrine\\DBAL\\Driver\\PDOStatement->execute()\n#2 /var/www/html/lib/private/DB/Connection.php(216): Doctrine\\DBAL\\Connection->executeUpdate('UPDATE `oc_file...', Array, Array)\n#3 /var/www/html/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php(208): OC\\DB\\Connection->executeUpdate('UPDATE `oc_file...', Array, Array)\n#4 /var/www/html/lib/private/DB/QueryBuilder/QueryBuilder.php(214): Doctrine\\DBAL\\Query\\QueryBuilder->execute()\n#5 /var/www/html/lib/private/Lock/DBLockingProvider.php(310): OC\\DB\\QueryBuilder\\QueryBuilder->execute()\n#6 [internal function]: OC\\Lock\\DBLockingProvider->releaseAll()\n#7 {main}\n\nNext Doctrine\\D in /var/www/html/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php on line 115

The same error comes when I try to do mysqldump:

mysqldump: Error 1712: Index oc_file_locks is corrupted when dumping table `oc_file_locks` at row: 0

What options do I have? Is it possible to recreate the index? If so, how? Or I could restore a mysqldump from a few days ago, before it happened. Will I be able to update the database with the new files?
Another option I see is to replace the current virtual machine with a backup from before 3 weeks (the nextcloud is running inside a VM with a local mariadb). Data folder is on another physical hard disk drive, so it shouldn’t be corrupted. Is there a way to update the database with the new files that the users have uploaded/changed in those 3 weeks?
Any hints are appreciated.

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

Steps to replicate it: Probably not, the original cause of issue might be a failing HDD

The output of your Nextcloud log in Admin > Logging: I cannot log in, all requests end up on Nextcloud’s “Internal Server Error” page. I have added logging to config.php and the error message is the same as above: index of oc_file_locks is corrupted.

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

$CONFIG = array (
  'instanceid' => 'censored',
  'passwordsalt' => 'censored',
  'secret' => 'censored',
  'trusted_domains' =>
  array (
    0 => 'cloud.domain1',
    1 => 'cloud.domain2'
  'trusted_proxies' =>
  array (
    0 =>
    array (
      0 => 'reverse proxy IP',
  'forwarded_for_headers' =>
  array (
    0 => 'HTTP_X_FORWARDED',
  'datadirectory' => '/mnt/cloud-data/',
  'overwrite.cli.url' => 'https://cloud.domain1',
  'dbtype' => 'mysql',
  'version' => '',
  'dbname' => 'nextcloud',
  'dbhost' => 'localhost',
  'dbport' => '',
  'dbtableprefix' => 'oc_',
  'dbuser' => 'censored',
  'dbpassword' => 'censored',
  'installed' => true,
  'memcache.local' => '\\OC\\Memcache\\APCu',
  'mail_domain' => 'cloud.domain1',
  'mail_from_address' => 'nextcloud',
  'mail_smtpmode' => 'sendmail',
  'mail_smtpauthtype' => 'LOGIN',
  'maintenance' => false,
  'theme' => '',
  'loglevel' => 2,
  'updater.secret' => 'censored',

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

No new error entries in /var/logs/apache2/errors.log 

You can try to repair the database with mysqlcheck.

E.G. mysqlcheck -u $USER -p --auto-repair --check nextcloud

Thank you for the suggestion. The check found single corrupted table – oc_file_locks, but was unable to repair it:

Repairing tables
Error    : Table 'nextcloud.oc_file_locks' doesn't exist in engine
status   : Operation failed

Is it possible to drop the table oc_file_locks and recreate it?

Yes you can recreate it.

But this sounds like a bigger underlying issue.
I Would check the Filesystem and the rights (and selinux context) of the database files.

Do you know how to recreate the table, please? I tried to search for how to do it but got nothing. Thanks!

As mentioned in the original post, I suspect it is my HDD failing. I have already checked the filesystem and this corruption in the database is most likely the result of the fixes. I need to get my nextcloud into working shape so that I can do one more final backup and then I will reinstall my entire system on a new SSD.

I pulled a sql dump from backup from before the corruption occurred and replace the entire database. Re-scanned all files and now things are looking stable.

@rtznprmpftl if you know how to recreate the table, post it please. Perhaps other users will find it in case they don’t have sql backups. Thanks!

1 Like

For the Nextcloud version 14.04 this would be:

mysql> DROP TABLE oc_file_locks;
mysql> CREATE TABLE `oc_file_locks` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `lock` int(11) NOT NULL DEFAULT '0',
  `key` varchar(64) COLLATE utf8mb4_bin NOT NULL,
  `ttl` int(11) NOT NULL DEFAULT '-1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `lock_key_index` (`key`),
  KEY `lock_ttl_index` (`ttl`)

Thanks! Can you perhaps also add where to find this information? I was even browsing through nextcloud server source codes and couldn’t find it :slight_smile:

The easiest way is to install NC.

The Information itself is stored in non RDBMS Specific Migration Files: https://github.com/nextcloud/server/tree/master/core/Migrations therefore quite unreadable

I know this thread is rather old, but it was the first in the search results, therefore I will share my observations on this.

I use Nextcloud 22 and tonight my MariaDB reported [ERROR] InnoDB: Table nextcloud.oc_filecache is corrupted. Please drop the table and recreate.
Also Nextcloud cries with PDOException: SQLSTATE[HY000]: General error: 1712 Index oc_filecache is corrupted in /var/www/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Statement.php:92
Trying REPAIR TABLE oc_filecache didn’t do anything. So I did the following:

  • Put Nextcloud into maintenance mode.
  • Stopped MariaDB with mysqladmin -p shutdown because systemd reported an error and was not able to stop it:
    # systemctl status mysql
      mysql.service - LSB: Start and stop the mysql database server daemon
       Loaded: loaded (/etc/init.d/mysql; generated)
       Active: failed (Result: exit-code) since Wed 2021-12-01 08:54:50 CET; 18s ago
         Docs: man:systemd-sysv-generator(8)
      Process: 20558 ExecStop=/etc/init.d/mysql stop (code=exited, status=1/FAILURE)
        Tasks: 35 (limit: 4915)
       Memory: 412.3M
       CGroup: /system.slice/mysql.service
    # mysqladmin -p shutdown
    Enter password: 
  • Restarted mariadb on commandline with the --innodb-force-recovery=1 switch (details in this MariaDB KB article)
  • Connected to the mariadb instance on the commandline and recreated the table by copying its content to a new table:
    # mysql -p -D nextcloud
    Enter password: 
    MariaDB [nextcloud]> create table oc_filecache_repaired like oc_filecache;
    Query OK, 0 rows affected (0.418 sec)
    MariaDB [nextcloud]> insert oc_filecache_repaired select * from oc_filecache;
    Query OK, 1548596 rows affected (23 min 46.640 sec)
    Records: 1548596  Duplicates: 0  Warnings: 0
    MariaDB [nextcloud]> drop table oc_filecache;
    Query OK, 0 rows affected (0.755 sec)
    MariaDB [nextcloud]> rename table oc_filecache_repaired to oc_filecache;
    Query OK, 0 rows affected (0.446 sec)
    MariaDB [nextcloud]> shutdown;
    Query OK, 0 rows affected (0.000 sec)
    MariaDB [nextcloud]> quit
  • Startup via systemd systemctl start mysql
  • Disable maintenance mode.
  • Check if everything works in Nextcloud.

Maybe this helps someone, who is in the same situation.