can't delete subfolder of group folders

[outnsp]

Which version of Nextcloud are you using?

31.0.7

Which version of PHP context_chat are you using?

4.4.1

Which version of backend context_chat are you using?

4.4.1

Which browser are you using? In case you are using the phone App, specify the Android or iOS version and device please.

EDGE

Nextcloud deployment method?

nginx

Describe the Bug

When context_chat is active, subfolders of group folders cannot be deleted.
The database accumulates many queries trying to determine the subfolders of the deleted subfolders.
These queries run for a very long time and pile up to such an extent that the entire Nextcloud becomes non-functional.

To Reproduce

1. Enable context_chat.
2. Attempt to delete a subfolder within a group folder.
3. Observe that the subfolder is not properly deleted.
4. Monitor database activity — numerous long-running queries accumulate.
5. Over time, system performance degrades until the Nextcloud instance becomes non-functional.

PHP logs (Warning these might contain sensitive information)

No response

Ex-App logs (Warning these might contain sensitive information)

No response

Server logs (if applicable)

No response

[outnsp]

starts with version 4.4.0

[marcelklehr]

Hello @outnsp
Thank you for taking the time to give feedback. I'm sorry for the trouble this has caused you.

numerous long-running queries accumulate

Could you share the queries that are accumulating?

Observe that the subfolder is not properly deleted.

That is unfortunate :/ I will investigate this

[marcelklehr]

@outnsp Would you mind trying to apply the patch from the following PR: #159 ?

[outnsp]

example query:
SELECT * FROM 'oc_filecache' 'filecache' WHERE ((SELECT 'p'.'encrypted' FROM 'oc_filecache' 'p' WHERE 'p'.'fileid' = 'filecache'.'parent') = 0) AND ('filecache'.'path' LIKE '__groupfolders/553/Arbeitsdateien/%') AND ('filecache'.'storage' = '3376') AND ('filecache'.'fileid' > '0') AND ('filecache'.'mimetype' IN (26, 6, 4, 7, 28, 36, 8, 49, 23, 24, 14, 32, 17, 35, 176, 39, 172, 173, 147)) ORDER BY 'filecache'.'fileid' ASC

-> Sort: filecache.fileid (cost=146454 rows=2.77e+6) (actual time=32436..32436 rows=0 loops=1)
-> Filter: (((select #2) = 0) and (filecache.'path' like '__groupfolders/553/Arbeitsdateien/%') and (filecache.mimetype in (26,6,4,7,28,36,8,49,23,24,14,32,17,35,176,39,172,173,147))) (cost=146454 rows=2.77e+6) (actual time=32436..32436 rows=0 loops=1)
-> Index lookup on filecache using fs_storage_path_hash (storage=3376), with index condition: ((filecache.'storage' = 3376) and (filecache.fileid > 0)) (cost=146454 rows=2.77e+6) (actual time=0.281..18343 rows=5.18e+6 loops=1)
-> Select #2 (subquery in condition; dependent)
-> Single-row index lookup on p using PRIMARY (fileid=filecache.parent) (cost=0.35 rows=1) (actual time=0.00225..0.00227 rows=1 loops=5.18e+6)

With just a single command, the server handles it in one minute. But when multiple folders are deleted or moved at the same time, it quickly stacks up, and the queries get stuck with a runtime of over 30 minutes.

The positive side of the error is that, by cleaning up the previews, I was able to reduce the filecache table from 12 million to 5 million rows.

If I have some time tomorrow, I'll be able to test the patch.

[marcelklehr]

If I have some time tomorrow, I'll be able to test the patch.

The patch will likely not help, then. It doesn't touch that query.