Useless entries in database table oc_filecache turns snap instance with external storage into ticking time bomb

šŸ·ļø General
, , ,
1

Nextcloud version (eg, 29.0.5): 28.0.6.1
Operating system and version (eg, Ubuntu 29.04): Ubuntu 22.04.4 LTS x86_64

My Nextcloud instance broke for some reason so I decided to recover from a recent backup. I am unable to recover the backup. Iā€™m confronted with database error messages since my nextcloud instance broke.

Iā€™ve been running the snap version of Nextcloud. I hope someone can give me a clue, Iā€™ve been trying to recover my Nextcloud instance for the last three days an Iā€™ve run out of ideas.

Steps to replicate it:

  1. Uninstall and purge Nextcloud from snap
  2. Reinstall Nextcloud
  3. Run the nextcloud.import command

The output of your Nextcloud log in Admin > Logging:

cat: /var/snap/nextcloud/common/nextcloud/data/nextcloud.log: No such file or directory

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

<?php
$CONFIG = array (
  'apps_paths' => 
  array (
    0 => 
    array (
      'path' => '/snap/nextcloud/current/htdocs/apps',
      'url' => '/apps',
      'writable' => false,
    ),
    1 => 
    array (
      'path' => '/var/snap/nextcloud/current/nextcloud/extra-apps',
      'url' => '/extra-apps',
      'writable' => true,
    ),
  ),
  'supportedDatabases' => 
  array (
    0 => 'mysql',
  ),
  'memcache.locking' => '\\OC\\Memcache\\Redis',
  'memcache.local' => '\\OC\\Memcache\\Redis',
  'redis' => 
  array (
    'host' => '/tmp/sockets/redis.sock',
    'port' => 0,
  ),
  'log_type' => 'file',
  'logfile' => '/var/snap/nextcloud/current/logs/nextcloud.log',
  'logfilemode' => 416,
  'maintenance_window_start' => 1,
  'instanceid' => 'oc214y690tfs',
  'passwordsalt' => 'uLgeMtsMQciopXnIsspT2Bqo4ywf2x',
  'secret' => 'Ge2xK7U2MUVCvYB6z+a20ZWur+EQFlXi9DfNQCsuHvI5GdDi',
  'trusted_domains' => 
  array (
    0 => '192.168.178.49',
  ),
  'datadirectory' => '/var/snap/nextcloud/common/nextcloud/data',
  'dbtype' => 'mysql',
  'version' => '28.0.6.1',
  'overwrite.cli.url' => 'http://192.168.178.49',
  'dbname' => 'nextcloud',
  'dbhost' => 'localhost:/tmp/sockets/mysql.sock',
  'dbport' => '',
  'dbtableprefix' => 'oc_',
  'mysql.utf8mb4' => true,
  'dbuser' => 'nextcloud',
  'dbpassword' => 'tRFqTCXcohveKNu1zBR5NR7OYJHgWynBl64xIqDYMPcWiskk6k0UqyJzEWOGqaIO',
  'installed' => true,
  'maintenance' => true,
);

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

ls: cannot access '/var/snap/nextcloud/current/apache/logs': No such file or directory

Output errors in nextcloud.log in /var/www/ or as admin user in top right menu, filtering for errors. Use a pastebin service if necessary.

2

Iā€™ve received no reply but found the issue. The database bloated itself up to a size of 50gb even though it only managed 4 users and a couple hundred Gb of files. Iā€™ve looked up the database Import and INSERT INTO oc_filecache kept showing up for days. With over 30 Million entries. This apears to be a known bug that is present in Nextcloud since at least 2017. Hereā€™s the related github issue:

Iā€™ve had my backupserver mounted to my Nextcloud instance. This was a mistake. If anyone stumbles over this, the Feature to mount external Filesystems is problematic. Iā€™ll attempt to apply the fixes discussed in the gitHub post and then update this entry. I may also open a new issue on gitHub that will hopefully get the attention of the right people.

3

Took me three weeks, but my nextcloud instance is up and running again! Hooray. If your oc_filecache table has bloated up so much that your instance is no longer running follow these simple and easy steps:

  1. copy the backup to a fast pc with fast storage

The Database is bloated and importing it is difficult with limited resrouces, Iā€™ve copied the database to my Desktop pc which has the fastest storage and the most RAM out of all of my setups. Iā€™d recommend you do the same otherwise the import will take weeks.

  1. create new empty database

The database dump database.sql needs to be imported to perform the necessary operation.
I installed MariaDB on my Arch desktop and created a new database called ā€œrecoverncdbā€. If your OS supports mysql without much hassle use that. Thereā€™s an issue with mariadb and mysql dumps that is well known. Hereā€™s the relevant section from the MariaDB manual:

Note: From MariaDB 10.5.25, MariaDB 10.6.18, MariaDB 10.11.8, MariaDB 11.0.6, MariaDB 11.1.5, MariaDB 11.2.4 and MariaDB 11.4.2 mariadb-dump generates a command at the beginning of the dump to enable sandbox mode. This command cannot be interpreted by earlier versions of the MariaDB command line client or by MySQLā€™s command line client, and the client will generate an error if used against the versions that do not support it. This does not affect other methods of importing the data.

  1. Import Database

The database backup needs to be imported into the newly created empty database, I used the command

sudo mariadb recoverncdb < database.sql

as described in this post:

database - How to import an SQL file using the command line in MySQL? - Stack Overflow

be mindfull to optimize the import, I made the mistake to not optimize it resulting in a 5 hour import time. The most relevant variable is the ā€œinnodb_buffer_pool_sizeā€.

  1. Repair database

File cache table excessively large (and does not shrink after data removal) / Nextcloud should defragment file cache table Ā· Issue #7312 Ā· nextcloud/server Ā· GitHub

Didnā€™t work, always threw an error message about so Iā€™ve dropped the table oc_filecache, so a new empty table of the right structure needs to be imported.

  1. Export database backup

sudo mariadbdump recoverncdb > database.sql

  1. Copy changed database back to server

Iā€™m using a rather weird system of vms and vpns that I connect to via ssh and wire shark, youā€™ll have to find out yourself how to do it in your case

  1. Import database

As previously mentioned thereā€™s an issue with importing mysql dumps into mariadb. If you arenā€™t mindful of it itā€™ll result in this error:

ERROR at line 1: Unknown command ā€˜-ā€™.

Luckily I made a snapshot in my VM manager before importing, nextcloud refused the import and would complain about the database not being present, so you a) use mysql to avoid this bug or b) follow this articleā€™s advice:

Issue - MariaDB failed imports - ERROR at line 1: Unknown command '\-'. | Plesk Forum

Now before leaving maintenance mode the missing table needs to be added back into the database. This requires a fresh install of nextcloud (the same version of NC and that is very important). Simply run the following command in the sql client of a fresh NC install:

show create table nextcloud.oc_filecache;

It will provide you with the command to recreate the Table. Run it in the nextcloud instance you just imported.

Now you should be able into start your nextcloud instance again.