File cache table excessively large (and does not shrink after data removal) / Nextcloud should defragment file cache table

Problem

My nextcloud instance (small-scale, single-server setup, 6 users) has an excessively large database size (20 GB as of today). This size doesn't sensibly relate to the amount of data managed (approx. 1 TB, now reduced to 4.3 GB).

I have found that almost all of the 20GB is located in a single table, the file cache:

root@helge:~# ls -lh /var/snap/nextcloud/current/mysql/nextcloud/oc_filecache.*
-rw-r----- 1 root root 21K Jan 8 2017 /var/snap/nextcloud/current/mysql/nextcloud/oc_filecache.frm
-rw-r----- 1 root root 19G Nov 27 15:17 /var/snap/nextcloud/current/mysql/nextcloud/oc_filecache.ibd

This file seems to grow and grow, but never shrinks.

Further details

• My nextcloud used to manage roughly 1TB of data, most of them located on external storage (external disks attached to my server).
• External storage was included through nextcloud’s “external storage” app as “Local”.
• I have since removed all external storage from my nextcloud instance:

root@helge:~# nextcloud.occ files_external:list --all
No mounts configured

• The data managed by my nextcloud instance is now only 4.3 GB (compared to 1 TB before). Surely this should reduce the file cache?
• I have called “files:scan” and “files:cleanup”, but to no effect:

root@helge:~# nextcloud.occ files:cleanup
0 orphaned file cache entries deleted

Here’s something I don’t understand: there should be thousands of orphaned entries now that most of the data is gone!

=> I suspect that my file cache table is somehow broken, but I don’t know how to fix it. I believe I should clear the table and reproduce it, but I do not know how to do that.

Steps to reproduce

Unsure, sorry

Expected behaviour

Flie cache table should shrink after external storage was removed.

Actual behaviour

File cache table stays the same (and is excessively large)

General server configuration

Operating system: Linux helge 4.13.0-17-generic #20-Ubuntu SMP Mon Nov 6 10:04:08 UTC 2017 x86_64

Web server: Apache/2.4.28 (Unix) OpenSSL/1.0.2g (fpm-fcgi)

Database: mysql 5.7.18

PHP version: 7.0.23

 - Core
 - date
 - libxml
 - openssl
 - pcre
 - sqlite3
 - zlib
 - bz2
 - ctype
 - curl
 - dom
 - hash
 - fileinfo
 - filter
 - gd
 - SPL
 - iconv
 - intl
 - json
 - mbstring
 - mcrypt
 - PDO
 - session
 - pdo_sqlite
 - posix
 - Reflection
 - standard
 - SimpleXML
 - mysqlnd
 - exif
 - tokenizer
 - xml
 - xmlreader
 - xmlwriter
 - zip
 - pdo_mysql
 - cgi-fcgi
 - redis

Nextcloud configuration

Nextcloud version: 11.0.5 (stable) - 11.0.5.1 (snap version 3680)

Updated from an older Nextcloud/ownCloud or fresh install: Nextcloud snap, updated from previous snap versions (3317, 2707)

Where did you install Nextcloud from: snap

Are you using external storage, if yes which one: \OC\Files\Storage\Local, see description for details!

Are you using encryption: no

Are you using an external user-backend, if yes which one: Nextcloud sync client from the ubuntu store

 - activity: 2.4.1
 - admin_audit: 1.1.0
 - audioplayer: 2.2.1
 - comments: 1.1.0
 - dav: 1.1.1
 - federatedfilesharing: 1.1.1
 - federation: 1.1.1
 - files: 1.6.1
 - files_external: 1.1.2
 - files_pdfviewer: 1.0.1
 - files_sharing: 1.1.1
 - files_texteditor: 2.2
 - files_trashbin: 1.1.0
 - files_versions: 1.4.0
 - files_videoplayer: 1.0.0
 - firstrunwizard: 2.0
 - gallery: 16.0.0
 - issuetemplate: 0.2.2
 - logreader: 2.0.0
 - lookup_server_connector: 1.0.0
 - nextcloud_announcements: 1.0
 - notifications: 1.0.1
 - password_policy: 1.1.0
 - provisioning_api: 1.1.0
 - serverinfo: 1.1.1
 - sharebymail: 1.0.1
 - survey_client: 0.1.5
 - systemtags: 1.1.3
 - theming: 1.1.1
 - twofactor_backupcodes: 1.0.0
 - workflowengine: 1.1.1

 - activitylog
 - calendar
 - encryption
 - external
 - files_accesscontrol
 - files_automatedtagging
 - files_retention
 - ownbackup
 - templateeditor
 - user_external
 - user_ldap
 - user_saml

{
    "apps_paths": [
        {
            "path": "\/snap\/nextcloud\/current\/htdocs\/apps",
            "url": "\/apps",
            "writable": false
        },
        {
            "path": "\/var\/snap\/nextcloud\/current\/nextcloud\/extra-apps",
            "url": "\/extra-apps",
            "writable": true
        }
    ],
    "supportedDatabases": [
        "mysql"
    ],
    "memcache.locking": "\\OC\\Memcache\\Redis",
    "memcache.local": "\\OC\\Memcache\\Redis",
    "redis": {
        "host": "\/tmp\/sockets\/redis.sock",
        "port": 0
    },
    "instanceid": "ocwzswpevaos",
    "passwordsalt": "***REMOVED SENSITIVE VALUE***",
    "secret": "***REMOVED SENSITIVE VALUE***",
    "trusted_domains": [
        "rkupper.no-ip.org",
        "helge"
    ],
    "datadirectory": "\/media\/Data\/nextcloud\/data",
    "overwrite.cli.url": "http:\/\/rkupper.no-ip.org",
    "dbtype": "mysql",
    "version": "11.0.5.1",
    "dbname": "nextcloud",
    "dbhost": "localhost:\/tmp\/sockets\/mysql.sock",
    "dbport": "",
    "dbtableprefix": "oc_",
    "dbuser": "***REMOVED SENSITIVE VALUE***",
    "dbpassword": "***REMOVED SENSITIVE VALUE***",
    "logtimezone": "UTC",
    "installed": true,
    "mail_smtpmode": "php",
    "mail_smtpsecure": "tls",
    "mail_from_address": "ruediger",
    "mail_domain": "rkupper.no-ip.org",
    "mail_smtpauthtype": "LOGIN",
    "mail_smtpauth": 1,
    "mail_smtpname": "***REMOVED SENSITIVE VALUE***",
    "mail_smtppassword": "***REMOVED SENSITIVE VALUE***",
    "loglevel": 3,
    "maintenance": false,
    "singleuser": false,
    "log_rotate_size": 1073741824
}

Client configuration

Browser: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/605.1 (KHTML, like Gecko) Version/11.0 Safari/605.1 Ubuntu/17.10 (3.26.1-1ubuntu1) Epiphany/3.26.1 (Web App)

Operating system: GNU/Linux (Ubuntu 17.10)

The report identifies the MySQL oc_filecache table and the nextcloud.occ files:scan and files:cleanup commands, but no source file or test. Start by reproducing the reported behavior around removed local external storage and inspect why cleanup reports no orphaned entries; done means establishing the cause and a verified way for the file-cache data or table size to decrease.