database log filled with errors: `duplicate key value violates unique constraint “oc_systemtag_object_mapping_pkey”`

[warlordattack]

Which version of recognize are you using?

latest

Enabled Modes

Object recognition, Face recognition, Video recognition, Music recognition

TensorFlow mode

WASM mode

Downstream App

Memories App

Which Nextcloud version do you have installed?

latest

Which Operating system do you have installed?

ubuntu

Which database are you running Nextcloud on?

postgres

Which Docker container are you using to run Nextcloud? (if applicable)

recommended AIO install

How much RAM does your server have?

16GiB

What processor Architecture does your CPU have?

x86_64

Describe the Bug

ERROR: duplicate key value violates unique constraint “oc_systemtag_object_mapping_pkey”
DETAIL: Key (objecttype, objectid, systemtagid)=(files, 112177, 34) already exists.
CEST [1789] STATEMENT: INSERT INTO “oc_systemtag_object_mapping” (“objectid”, “objecttype”, “systemtagid”) VALUES($1, $2, $3)

Expected Behavior

shold not fill the database log with errors every second

To Reproduce

installed fresh nextcloud via recommended AIO methos on a new Ubuntu proxmox VM,
installed Memories and Recognize
activated auto tagging
dropped few thousands wallpapers there
opened the logs found database log filled with errors and 20Mo size

Debug log

nextcloud-aio-database-2024-04-18T15-05-49.log

[warlordattack]

this bug makes nextcloud database log unusable :
-huge size : 20MO after 1 day
-errors added every second so it scrolls indefinitly
-it also makes the whole nextcloud slow

[warlordattack]

I tryed to delete the content inside oc_systemtag_object_mapping table, then restarted autotagging using :
sudo docker exec --user www-data -it nextcloud-aio-nextcloud php occ recognize:recrawl
The errors continue after deleting all lines inside oc_systemtag_object_mapping table.

And i would like to delete the content of the nextcloud database log, but did not find where the file is.

Also tryed this : sudo docker exec --user www-data -it nextcloud-aio-nextcloud php occ maintenance:repair
But it did not solve the problem, because there is no problem with the database itself probably,
but with the code that try to insert things inside oc_systemtag_object_mapping without verifying if the line already exists first ?

[github-actions]

Hello 👋

Thank you for taking the time to open this issue with recognize. I know it's frustrating when software
causes problems. You have made the right choice to come here and open an issue to make sure your problem gets looked at
and if possible solved.
I try to answer all issues and if possible fix all bugs here, but it sometimes takes a while until I get to it.
Until then, please be patient.
Note also that GitHub is a place where people meet to make software better together. Nobody here is under any obligation
to help you, solve your problems or deliver on any expectations or demands you may have, but if enough people come together we can
collaborate to make this software better. For everyone.
Thus, if you can, you could also look at other issues to see whether you can help other people with your knowledge
and experience. If you have coding experience it would also be awesome if you could step up to dive into the code and
try to fix the odd bug yourself. Everyone will be thankful for extra helping hands!
One last word: If you feel, at any point, like you need to vent, this is not the place for it; you can go to the forum,
to twitter or somewhere else. But this is a technical issue tracker, so please make sure to
focus on the tech and keep your opinions to yourself. (Also see our Code of Conduct. Really.)

I look forward to working with you on this issue
Cheers 💙

[marcelklehr]

Hello @warlordattack

I'm sorr for your troubles. This appears to be a bug in nextcloud server (core) rather than the recognize app. I'm thus transferring this issue to the server repository.

[warlordattack]

If more information is needed i can provide.
Best regards :)

[joshtrichards]

I know you mentioned new installation in the reproduction steps, but any chance your existing database was restored from backup at some point or migrated from another installation?

[warlordattack]

It was a clean new install, i am testing, so i delete all containers and docker disks and dosker directories linked to containers before new install, database was normally deleted when i deleted docker disks before install.

[marcelklehr]

Looks like this is the code that causes this. The errors don't seem to be fatal, but sure are annoying I assume: https://github.com/nextcloud/server/blame/master/lib/private/SystemTag/SystemTagManager.php#L180