Stale oc_properties values in the wild

[tomneedham]

ownCloud 10.x - Server

Sometimes apps / plugins etc add properties into oc_properties - which relate to a fileid on the oc_filecache table. There is no foreign key with cascading delete - so deleted in the filecache can cause stale entries in the oc_properties table. Individual plugins are then resposible for implementing a OCP\Files\NotFoundExcepetion check which doesn't always happen / insn't in place yet.

You can spot stale entries with thie query:

SELECT * FROM oc_properties p WHERE NOT EXISTS (SELECT null FROM oc_filecache f WHERE f.fileid = p.fileid)

Maybe we should run this on a few instnaces to see what propertyname values we have?

I've seen at least upload-time which was left of over holding_period. And {http://owncloud.org/ns}role with a null fileid ?!

We could cleanup this in a maintenance step, or get individual use cases to cleanup their entries in repair steps?

[tomneedham]

Related to #13143

[PVince81]

isn't this solved with #33413 that was released with 10.1.0 ?

[PVince81]

foreign keys would be nice though... we started adding some for the oc_persistent_locks properties

[tomneedham]

isn't this solved with #33413 that was released with 10.1.0 ?

Yes - but existing stale values. Maintennace step to cleanup would be nice.

Where does {http://owncloud.org/ns}role come from, why does it have a NULL fileid, and present multiple times?

[DeepDiver1975]

as designed - bad designed - but as designed

solution: build a occ command for clean up

[PVince81]

or have an occ command to run the bkg job right away..

sometimes it feels like we'd need to factorize "background job", "repair step" and "occ command" into a single concept. then each can be run either periodically, on CLI or on upgrade... this way we don't reimplement as occ something that already exists as background job.

also note this PR to run bkg job directly: #31617