Automatically delete duplicate keys in filecache table

[karlitschek]

It seems that there are still a lot of ownCloud installations out there with broken filecache entries. The often break during upgrade because of duplicate keys in the filecache table.
I think we should cleanup duplicate keys in the repair function.

What do you thing @icewind1991

[karlitschek]

And here is a real life example. #6994

[icewind1991]

#6994 is not a duplicate entry in the filecache table but a duplicate entry in the appconfig table

[karlitschek]

Ups. True :-)
But we should try to do the same here.

[PVince81]

#6994 Rather seems to be for the oc_preferences table. But anyway, we should probably check most of the config/preference tables for duplicates anyway.

[PVince81]

@icewind1991 I've raised a separate issue for the oc_appconfig instead: #8469

[PVince81]

@icewind1991 do we have a primary key / index already on the filecache table in OC 6?

[icewind1991]

Yes

[PVince81]

Just checked on OC 6.0.3 and I'm not able to add duplicate keys into the oc_preferences table.

owncloud=> insert into oc_preferences values ('root', 'test', 'x', 'y');
ERROR:  duplicate key value violates unique constraint "oc_preferences_pkey"
DETAIL:  Key (userid, appid, configkey)=(root, test, x) already exists.
owncloud=> insert into oc_preferences values ('root', 'test', 'x', 'y');
ERROR:  duplicate key value violates unique constraint "oc_preferences_pkey"
DETAIL:  Key (userid, appid, configkey)=(root, test, x) already exists.
owncloud=> insert into oc_preferences values ('root', 'test', 'x', 'z');
ERROR:  duplicate key value violates unique constraint "oc_preferences_pkey"
DETAIL:  Key (userid, appid, configkey)=(root, test, x) already exists.

So this is again a case of moving from OC 5 to OC 6, not OC 6 to OC 7.

[karlitschek]

So I understand that in current versions this can´t happen because we have proper indexes/keys. Closing.