[Bug]: Filecache for Groupfolder "OpenProject" with activated option "Automatically managed folders" is buggy

DocSnyderLiMA commented 1 month ago

⚠️ This issue respects the following points: ⚠️

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

Bug description

I have experienced severe trouble with the file cache (db table oc_filecache) when using the groupfolder "OpenProject" (set up as suggested here) including the option "Automatically managed folders".

My observation is that the db table oc_filecache is flooded with "pseudo-redundant" entries from this groupfolder (only the table entry "parent" seems to vary) which has the nasty effect, that users within the NC WebGUI get very buggy views on the content of their groupfolders resulting in:

randomly showing subfolders or files (sometimes some of them are missing when entering project folder, after re-entering all folder/files are shown)
size of the folders is jumping between "xx kB/MB/GB)" or "Outstanding")

To me this looks like a background job is constantly creating "pseudo-redundant" entries in the oc_filecache db table for certain group-subfolders (maybe with different time stamps/versions of this subfolder) without cleaning up older entries and therefor - out of this multliple "pseudo redundant" entries - randomly one entry is taken for the NC WebGUI view which might be outdated or not.

Any ideas on how this pseudo redundant entries evolve and especially on how to fix this issue are welcome.

I tried to delete this entries on database level manually by:

DELETE FROM oc_filecache WHERE fileid IN(SELECT DISTINCT f.fileid FROM oc_filecache AS f INNER JOIN oc_mounts AS m ON m.storage_id = f.storage WHERE size < 0 AND parent > -1);

which initially blanks out the filecache for this project groupfolders but after some time when filecache gets refilled the situation becomes buggy again.

For other groufolders already in use within our NC instance this behaviour can not be observed - hence this issue seems to be linked to the open project integration setup (any faulty synchronization job in the background?).

Steps to reproduce

Setup OpenProject integration as given here
Fill your project folders with content and implement several user permissions via project members
Check visibility of project folders content via NC WebGUI

We are using OpenProject 14.3.0.

Expected behavior

Get always a reliable view on the projects folder content within the NC WebGUI (our users get frustrated as it seems that their data is missing or sometimes there/sometimes gone).

Installation method

Community Web installer on a VPS or web space

Nextcloud Server version

29

Operating system

Debian/Ubuntu

PHP engine version

PHP 8.1

Web server

Apache (supported)

Database engine version

PostgreSQL

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

Upgraded to a MAJOR version (ex. 28 to 29)

Are you using the Nextcloud Server Encryption module?

Encryption is Disabled

What user-backends are you using?

[X] Default user-backend (database)
[ ] LDAP/ Active Directory
[ ] SSO - SAML
[ ] Other

Configuration report

{
    "system": {
        "instanceid": "***REMOVED SENSITIVE VALUE***",
        "passwordsalt": "***REMOVED SENSITIVE VALUE***",
        "secret": "***REMOVED SENSITIVE VALUE***",
        "trusted_domains": [
            "cloud.***.de",
            "192.**.*"
        ],
        "debug": false,
        "defaultapp": "files",
        "default_language": "de",
        "default_locale": "de_DE",
        "default_phone_region": "de",
        "datadirectory": "***REMOVED SENSITIVE VALUE***",
        "dbtype": "pgsql",
        "version": "29.0.4.1",
        "overwrite.cli.url": "https:\/\/cloud.****.de",
        "overwritehost": "cloud.****.de",
        "overwriteprotocol": "https",
        "dbname": "***REMOVED SENSITIVE VALUE***",
        "dbhost": "***REMOVED SENSITIVE VALUE***",
        "dbport": "",
        "dbtableprefix": "oc_",
        "mysql.utf8mb4": true,
        "dbpersistent": true,
        "dbuser": "***REMOVED SENSITIVE VALUE***",
        "dbpassword": "***REMOVED SENSITIVE VALUE***",
        "installed": true,
        "maintenance_window_start": 1,
        "maintenance": false,
        "mail_from_address": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpmode": "smtp",
        "mail_sendmailmode": "smtp",
        "mail_domain": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpauthtype": "LOGIN",
        "mail_smtpauth": 1,
        "mail_smtphost": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpname": "***REMOVED SENSITIVE VALUE***",
        "mail_smtppassword": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpsecure": "ssl",
        "htaccess.RewriteBase": "\/",
        "mail_smtpport": "465",
        "filelocking.enabled": "true",
        "memcache.local": "\\OC\\Memcache\\APCu",
        "memcache.distributed": "\\OC\\Memcache\\Redis",
        "memcache.locking": "\\OC\\Memcache\\Redis",
        "redis": {
            "host": "***REMOVED SENSITIVE VALUE***",
            "port": 6379,
            "password": "***REMOVED SENSITIVE VALUE***",
            "timeout": 1.5
        },
        "share_folder": "\/Freigaben",
        "theme": "",
        "loglevel": 2,
        "data-fingerprint": "259c283b5f65c9bec18c6eb1b011e604",
        "allow_local_remote_servers": "1",
        "app_install_overwrite": [
            "files_videoplayer",
            "accessibility",
            "pdfdraw",
            "epubreader",
            "files_downloadactivity"
        ],
        "enable_previews": true,
        "enabledPreviewProviders": [
            "OC\\Preview\\Imaginary",
            "OC\\Preview\\PNG",
            "OC\\Preview\\JPEG",
            "OC\\Preview\\TIF",
            "OC\\Preview\\TIFF",
            "OC\\Preview\\GIF",
            "OC\\Preview\\HEIC",
            "OC\\Preview\\BMP",
            "OC\\Preview\\XBitmap",
            "OC\\Preview\\MP3",
            "OC\\Preview\\TXT",
            "OC\\Preview\\MarkDown",
            "OC\\Preview\\MP4",
            "OC\\Preview\\PDF"
        ],
        "preview_imaginary_url": "***REMOVED SENSITIVE VALUE***",
        "jpeg_quality": "70",
        "preview_max_x": "1024",
        "preview_max_y": "1024",
        "preview_max_memory": -1,
        "preview_max_filesize_image": -1,
        "preview_concurrency_all": "4",
        "preview_concurrency_new": "2",
        "ldapProviderFactory": "OCA\\User_LDAP\\LDAPProviderFactory"
    }
}

List of activated Apps

Enabled:
  - activity: 2.21.1
  - admin_audit: 1.19.0
  - bruteforcesettings: 2.9.0
  - calendar: 4.7.13
  - cfg_share_links: 5.1.0
  - circles: 29.0.0-dev
  - cloud_federation_api: 1.12.0
  - comments: 1.19.0
  - contacts: 6.0.0
  - contactsinteraction: 1.10.0
  - dashboard: 7.9.0
  - dav: 1.30.1
  - federatedfilesharing: 1.19.0
  - files: 2.1.0
  - files_3dmodelviewer: 0.0.14
  - files_downloadactivity: 1.17.0
  - files_downloadlimit: 2.0.0
  - files_fulltextsearch: 29.0.0
  - files_pdfviewer: 2.10.0
  - files_reminders: 1.2.0
  - files_sharing: 1.21.0
  - files_trashbin: 1.19.0
  - files_versions: 1.22.0
  - firstrunwizard: 2.18.0
  - forms: 4.2.4
  - fulltextsearch: 29.0.0
  - fulltextsearch_elasticsearch: 29.0.1
  - groupfolders: 17.0.1
  - integration_openproject: 2.6.3
  - integration_zammad: 2.1.0
  - logreader: 2.14.0
  - lookup_server_connector: 1.17.0
  - mail: 3.7.6
  - maps: 1.4.0
  - metadata: 0.20.0
  - nextcloud_announcements: 1.18.0
  - notifications: 2.17.0
  - oauth2: 1.17.0
  - onlyoffice: 9.3.0
  - password_policy: 1.19.0
  - photos: 2.5.0
  - polls: 7.1.4
  - previewgenerator: 5.6.0
  - privacy: 1.13.0
  - provisioning_api: 1.19.0
  - recommendations: 2.1.0
  - related_resources: 1.4.0
  - root_cache_cleaner: 0.1.7
  - serverinfo: 1.19.0
  - settings: 1.12.0
  - sharebymail: 1.19.0
  - spreed: 19.0.7
  - support: 1.12.0
  - survey_client: 1.17.0
  - systemtags: 1.19.0
  - text: 3.10.1
  - theming: 2.4.0
  - twofactor_backupcodes: 1.18.0
  - updatenotification: 1.19.1
  - user_ldap: 1.20.0
  - user_status: 1.9.0
  - viewer: 2.3.0
  - weather_status: 1.9.0
  - welcome: 1.2.0
  - workflowengine: 2.11.0
Disabled:
  - encryption: 2.17.0 (installed 2.17.0)
  - federation: 1.19.0 (installed 1.17.0)
  - files_external: 1.21.0
  - files_fulltextsearch_tesseract: 27.0.0 (installed 27.0.0)
  - files_rightclick: 0.15.1 (installed 1.6.0)
  - integration_ews: 1.0.37 (installed 1.0.37)
  - suspicious_login: 7.0.0
  - twofactor_totp: 11.0.0-dev

Nextcloud Signing status

No errors have been found.

Nextcloud Logs

{"reqId":"e40coHbM7G1K7fJuHXRV","level":2,"time":"2024-08-08T07:15:05+00:00","remoteAddr":"","user":"--","app":"files","method":"","url":"--","message":"User **** still has unscanned files after running background scan, background scan might be stopped prematurely","userAgent":"--","version":"29.0.4.1","data":{"app":"files"}}

Additional info

No response

DocSnyderLiMA commented 1 month ago

Update: to me it looks like that on the OpenProject side something is forcing the nextcloud filecache to permanently update the entries for every "main project folder" entry. This leads in the oc_filecache db table to duplicate entries by a factor of 10 to 50 times more compared to the subfolder entries.

See two different project folder examples attached:

grafik ,

grafik

With this duplicates (seems to be different folder-fileversions with varying timestamps) the GUI is always throwing the dice in which "subfolder version" you enter by clicking initially on the main project folder as "parent".

So my wild guess is that the filecache-job for automatically managed folders is causing this trouble as it seems to not remove old fileversion entries of main project folders in the filecache making the folder browsing trhough the NC GUI looking that buggy.

DocSnyderLiMA commented 1 month ago

@joshtrichards Any first results on my reported bug issue?

nextcloud / groupfolders