[Bug]: database table column lengths mismatch in `oc_files_trash` vs `oc_filecache` causes deletion errors

⚠️ 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 (I've searched it).
• Nextcloud Server is up to date. See Maintenance and Release Schedule for supported versions.
• Nextcloud Server is running on 64bit capable CPU, PHP and OS.
• I agree to follow Nextcloud's Code of Conduct.

Bug description

I have a use case where I sync a file tree with rather long paths (both directory names and filenames are long). I was getting errors as I was deleting these synced files. Looking closer at the problem, I noticed that the db table oc_files_trash filename and path columns where way to small in size for me

MariaDB [nextcloud]> describe oc_files_trash;
+-----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+----------------+
| auto_id | bigint(20) | NO | PRI | NULL | auto_increment |
| id | varchar(250) | NO | MUL | | |
| user | varchar(64) | NO | MUL | | |
| timestamp | varchar(12) | NO | MUL | | |
| location | varchar(512) | NO | | | |
| type | varchar(4) | YES | | NULL | |
| mime | varchar(255) | YES | | NULL | |
+-----------+--------------+------+-----+---------+----------------+

It seems the id column holds the filename (250 bytes) and the location column holds the path (512 bytes). My instance kept failing the delete sync action since filenames and/or paths where too long to be stored in this table. As I turned off the app “Deleted Files” in the Apps menu the deleted sync problems stopped, presumably because files no longer ended up in the oc_files_trash as part of the deletion process.

The oc_filecache table (which I assume holds the index of all syned files) has a path column in its table which hold 4000 characters, which is big enough to allow my long paths, hence file syncing succeeded when information was written to this table.

I think that the id and location limits in the oc_files_trash table needs to be increased in order to accommodate longer paths/filenames. The exact column values might be a bit tricky to get right though since both tables stores path/filenames in a relative location to the install dir + the user home dir, so it might not be a simple matter of extending the column sizes to 4096 (Linux’s defined PATH_MAX) as the underlying file access path in some situations could exceed the allowed path length. However I’m not sure since I haven’t studied the code. Depending on how this is written, the column length might need to be offset against the install base path length and also take into account the username length somehow.

It might also be beneficial to investigate other Unix flavors’ PATH_MAX and modify the db installer with allowed max values for other OS’es than Linux.

Finally, I think it would be beneficial to synergize the max paths in the tables discussed here, oc_files_trash and oc_filecache. I would suggest refactoring oc_files_trash to only contain a path column (if possible).

Steps to reproduce

Assuming the directory sync/ in the example below is synced by a nextcloud instance with the “Deleted Files” app running; create a file exceeding 250 characters long filename and then delete it.

Since the file is indexed in oc_filecache which has a path of 4000, the file can be stored. But when it's deleted, and nextcloud tries to store the filename in oc_files_trash's id column (250 chars), it will fail.

unset filename ; for i in $(seq 1 255); do filename=${filename}A; done ; echo A > sync/$filename
sleep 10
rm -f sync/$filename

Expected behavior

File deletion sync action should work just as well as file create/modify sync events. I.e. the path lengths between the oc_filecache and oc_files_trash should be harmonized (allow for the same path lengths).

Maybe a deeper investigation should be conducted just how long paths/filenames nextcloud should support

Installation method

Community Docker image

Operating system

Debian/Ubuntu

PHP engine version

PHP 8.1

Web server

Nginx

Database engine version

MariaDB

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

Fresh Nextcloud Server install

Are you using the Nextcloud Server Encryption module?

Encryption is Disabled

What user-backends are you using?

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

Configuration report

No response

List of activated Apps

Enabled:
  - activity: 2.17.0
  - bruteforcesettings: 2.5.0
  - cloud_federation_api: 1.8.0
  - dashboard: 7.5.0
  - dav: 1.24.0
  - federatedfilesharing: 1.15.0
  - files: 1.20.1
  - files_pdfviewer: 2.6.0
  - files_rightclick: 1.4.0
  - files_sharing: 1.17.0
  - files_versions: 1.18.0
  - firstrunwizard: 2.14.0
  - logreader: 2.10.0
  - lookup_server_connector: 1.13.0
  - nextcloud_announcements: 1.14.0
  - notifications: 2.13.1
  - oauth2: 1.13.0
  - password_policy: 1.15.0
  - photos: 2.0.0
  - privacy: 1.9.0
  - provisioning_api: 1.15.0
  - recommendations: 1.4.0
  - related_resources: 1.0.3
  - serverinfo: 1.15.0
  - settings: 1.7.0
  - text: 3.6.0
  - theming: 2.0.1
  - twofactor_backupcodes: 1.14.0
  - updatenotification: 1.15.0
  - user_status: 1.5.0
  - viewer: 1.9.0
  - workflowengine: 2.7.0
Disabled:
  - admin_audit
  - calendar: 3.5.0
  - circles: 24.0.1
  - comments: 1.14.0
  - contacts: 4.2.0
  - contactsinteraction: 1.5.0
  - encryption
  - federation: 1.14.0
  - files_external
  - files_trashbin: 1.14.0
  - mail: 1.13.8
  - richdocuments: 6.2.0
  - richdocumentscode: 22.5.502
  - sharebymail: 1.14.0
  - spreed: 14.0.4
  - support: 1.7.0
  - survey_client: 1.12.0
  - suspicious_login
  - systemtags: 1.14.0
  - twofactor_totp
  - user_ldap
  - weather_status: 1.4.0

Nextcloud Signing status

No errors have been found.

Nextcloud Logs

No response

Additional info

No response

Start by comparing the oc_files_trash and oc_filecache table definitions and trace the deleted-file handling that stores the filename and path. Reproduce the failure with the provided long-filename commands against MariaDB. Done means deletion sync succeeds for supported path lengths and the two tables' limits are consistently defined, with any required migration covered.