[Bug]: `occ trashbin:restore` is non-atomic (leaves inconsistent database state if errors encountered)

⚠️ This issue respects the following points: ⚠️

• This is a bug, not a question or a configuration/webserver/proxy issue.
• This issue is not already reported on Github OR Nextcloud Community Forum (I've searched it).
• Nextcloud Server is up to date. See Maintenance and Release Schedule for supported versions.
• I agree to follow Nextcloud's Code of Conduct.

Bug description

I encountered a (as yet unknown) mass file-deletion event, potentially related to the Android client.

Working through restoring the affected files with occ trashbin:restore (fortunately the few thousand files deleted were within a fairly narrow time-window).

During the restore (which proceed at about 1 file per second), my instance span up a cron task which occupied the database. (Potentially duplicate_finder, which I've now turned off!). During the cron task, file restoration started to fail with a MySQL error relating to a timeout acquiring a lock.

Unfortunately I don't have a log of the output from occ trashbin:restore to quote the exact error message encountered during the restoration

Ultimately, I found the files affected by the error had been moved from {user}/files_trashbin/files/{name}.r{timestamp} back to their proper restored locations; however, the audit log for this was not completed, and the database + filesystem was left in an inconsistent state.

• oc_filecache was correctly updated to reflect the restoration
• oc_files_trash was NOT updated to reflect the restoration
• Versioned copies of the files were NOT restored (and remained in {user}/files_trashbin/versions/...
• oc_filecache remained consistent with the {user}/files_trashbin/versions/... copies.
• audit log possibly not updated?
• activities not updated.

As occ trashbin:restore appears to operate by enumerating files in {user}/files_trashbin/files/ rather than considering a query on oc_files_trash, once in this state occ trashbin:restore did not "see" files within the time-window to restore. (And could not complete the process / audit / activity log etc..)

I was able to fix my situation by manually using occ:files:move to manually move the "half" restored files back into {user}/files_trashbin/files/.... (with appropriate timestamp suffixes corresponding to DB entries in oc_files_trash

With the affected files moved back, occ_trashbin:restore would then correctly operate on them (and restore the versioned copies from the {user}/files_trashbin/versions/ as well.

Steps to reproduce

1. Delete files
2. Attempt to restore files with occ:trashbin:restore
3. Cause restoration operation to fail somehow (in my case, a cron task potentially including dupicate_finder caused the DB to time-out waiting for a lock
4. IF occ:trashbin:restore is interrupted, observe inconsistent DB state.

Expected behavior

Ideally the restore should be atomic, or failing that, arranged in such a way that a subsequent attempt to restore can complete the operation. (Possibly the restore process needs to check oc_files_trash for entries that are inconsistent with the storage backend state - and hopefully offer a method to recover.

Nextcloud Server version

31

Operating system

Other

PHP engine version

PHP 8.3

Web server

Nginx

Database engine version

MySQL

Is this bug present after an update or on a fresh install?

None

Are you using the Nextcloud Server Encryption module?

None

What user-backends are you using?

• Default user-backend (database)
• LDAP/ Active Directory
• SSO - SAML
• Other

Configuration report

{
    "system": {
        "apps_paths": [
            {
                "path": "\/usr\/local\/www\/nextcloud\/apps",
                "url": "\/apps",
                "writable": true
            },
            {
                "path": "\/usr\/local\/www\/nextcloud\/apps-pkg",
                "url": "\/apps-pkg",
                "writable": true
            }
        ],
        "logfile": "\/var\/log\/nextcloud\/nextcloud.log",
        "memcache.local": "\\OC\\Memcache\\Redis",
        "filelocking.enabled": true,
        "memcache.locking": "\\OC\\Memcache\\Redis",
        "redis": {
            "host": "***REMOVED SENSITIVE VALUE***",
            "port": 0,
            "timeout": 0,
            "password": "***REMOVED SENSITIVE VALUE***"
        },
        "instanceid": "***REMOVED SENSITIVE VALUE***",
        "passwordsalt": "***REMOVED SENSITIVE VALUE***",
        "secret": "***REMOVED SENSITIVE VALUE***",
        "__new_instanceid": "***REMOVED SENSITIVE VALUE***",
        "__new_passwordsalt": "***REMOVED SENSITIVE VALUE***",
        "__new_secret": "***REMOVED SENSITIVE VALUE***",
        "trusted_domains": [
            "***REMOVED SENSITIVE VALUE***",
            "nextcloud",
            "nextcloud.",
            "***REMOVED SENSITIVE VALUE***",
            "***REMOVED SENSITIVE VALUE***"
        ],
        "trusted_proxies": "***REMOVED SENSITIVE VALUE***",
        "datadirectory": "***REMOVED SENSITIVE VALUE***",
        "dbtype": "mysql",
        "version": "31.0.7.1",
        "overwrite.cli.url": "***REMOVED SENSITIVE VALUE***",
        "dbname": "***REMOVED SENSITIVE VALUE***",
        "dbhost": "***REMOVED SENSITIVE VALUE***",
        "dbport": "",
        "dbtableprefix": "oc_",
        "__old_dbuser": "oc_admin",
        "dbuser": "***REMOVED SENSITIVE VALUE***",
        "dbpassword": "***REMOVED SENSITIVE VALUE***",
        "installed": true,
        "mail_domain": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpmode": "smtp",
        "mail_from_address": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpsecure": "ssl",
        "mail_smtphost": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpport": "465",
        "auth.bruteforce.protection.enabled": false,
        "maintenance": false,
        "mysql.utf8mb4": true,
        "updater.release.channel": "stable",
        "theme": "",
        "debug": false,
        "loglevel": 1,
        "data-fingerprint": "***REMOVED SENSITIVE VALUE***",
        "twofactor_enforced": "true",
        "twofactor_enforced_groups": [],
        "twofactor_enforced_excluded_groups": [
            "NO_2FA_YET"
        ],
        "mail_sendmailmode": "smtp",
        "app_install_overwrite": [
            "calendar",
            "spgverein",
            "twofactor_admin",
            "admin_notifications",
            "impersonate",
            "twofactor_yubikey",
            "socialsharing_email",
            "whiteboard",
            "sensorlogger",
            "files_3d",
            "files_downloadactivity",
            "appointments",
            "keeweb",
            "tasks",
            "timemanager",
            "files_texteditor",
            "previewgenerator",
            "gallery",
            "drawio",
            "twofactor_u2f",
            "accessibility",
            "notify_push",
            "deck",
            "duplicatefinder",
            "event_update_notification",
            "groupfolders",
            "ransomware_protection",
            "files_snapshots",
            "hibp_login_check",
            "twofactor_reminder"
        ],
        "mail_smtpauth": 1,
        "mail_smtpname": "***REMOVED SENSITIVE VALUE***",
        "mail_smtppassword": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpauthtype": "PLAIN",
        "default_phone_region": "GB",
        "deferred_script_loading": true,
        "bulkupload.enabled": false,
        "maintenance_window_start": 3
    }
}

List of activated Apps

NB: duplicatefinder was ENABLED during bug sequence, later disabled during recovery.

root@nextcloud-3:~ # occ app:list
Enabled:
  - activity: 4.0.0
  - admin_audit: 1.21.0
  - announcementcenter: 7.1.4
  - app_api: 5.0.2
  - appointments: 2.4.5
  - bruteforcesettings: 4.0.0
  - calendar: 5.3.8
  - circles: 31.0.0
  - cloud_federation_api: 1.14.0
  - comments: 1.21.0
  - contacts: 7.2.3
  - contactsinteraction: 1.12.0
  - dashboard: 7.11.0
  - dav: 1.33.0
  - deck: 1.15.2
  - event_update_notification: 2.6.1
  - federatedfilesharing: 1.21.0
  - federation: 1.21.0
  - files: 2.3.1
  - files_3dmodelviewer: 0.0.15
  - files_downloadlimit: 4.0.0
  - files_external: 1.23.0
  - files_lock: 31.0.2
  - files_pdfviewer: 4.0.0
  - files_reminders: 1.4.0
  - files_sharing: 1.23.1
  - files_trashbin: 1.21.0
  - files_versions: 1.24.0
  - firstrunwizard: 4.0.0
  - groupfolders: 19.1.3
  - hibp_login_check: 0.0.2
  - impersonate: 2.0.0
  - logreader: 4.0.0
  - lookup_server_connector: 1.19.0
  - notes: 4.12.2
  - notifications: 4.0.0
  - notify_push: 1.1.0
  - oauth2: 1.19.1
  - password_policy: 3.0.0
  - photos: 4.0.0
  - previewgenerator: 5.9.0
  - privacy: 3.0.0
  - profile: 1.0.0
  - provisioning_api: 1.21.0
  - recommendations: 4.0.0
  - related_resources: 2.0.0
  - serverinfo: 3.0.0
  - settings: 1.14.0
  - sharebymail: 1.21.0
  - socialsharing_email: 3.3.0
  - survey_client: 3.0.0
  - suspicious_login: 9.0.1
  - systemtags: 1.21.1
  - tasks: 0.16.1
  - text: 5.0.0
  - theming: 2.6.1
  - timemanager: 0.3.18
  - twofactor_admin: 4.8.0
  - twofactor_backupcodes: 1.20.0
  - twofactor_totp: 13.0.0-dev.0
  - twofactor_webauthn: 2.2.0
  - updatenotification: 1.21.0
  - user_status: 1.11.0
  - viewer: 4.0.0
  - weather_status: 1.11.0
  - webhook_listeners: 1.2.0
  - whiteboard: 1.1.2
  - workflowengine: 2.13.0
Disabled:
  - duplicatefinder: 1.7.3 (installed 1.7.3)
  - eidlogin: 1.0.18 (installed 1.0.18)
  - encryption: 2.19.0
  - files_downloadactivity: 1.18.1 (installed 1.18.1)
  - nextcloud_announcements: 3.0.0 (installed 1.3.0)
  - spreed: 21.1.2 (installed 20.1.9)
  - support: 3.0.0 (installed 1.0.0)
  - twofactor_nextcloud_notification: 5.0.0
  - twofactor_reminder: 1.1.0 (installed 1.1.0)
  - user_ldap: 1.22.0

Nextcloud Signing status

No errors have been found.

Nextcloud Logs

Can't find anything relevant to the problems encountered in nextcloud.log

Additional info

No response

Start with the occ trashbin:restore entry point and trace how it handles the filesystem paths, oc_files_trash, oc_filecache, versions, audit log, and activities. Reproduce an interrupted restore around a database lock timeout, then verify that a failed operation leaves recoverable state and that a subsequent restore completes all related database, filesystem, version, audit, and activity updates.