[Nextcloud] duplicate key value violates unique constraint "fs_storage_path_hash"

Moep90 commented 2 years ago

Hey folks, I migrated my database from MariaDB to PSQL this weekend using this command:

Versions

Backup = 1.0.6 Nextcloud Server = Nextcloud Hub II 23.0.0

DB Migration

docker exec -u <id www user>:<gid www user> -ti <containerID> php occ db:convert-type --all-apps --password="<db-pw>" pgsql nextcloud db-postgres nextcloud

This did not report any errors etc so I assume nextcloud migrated with success.

Error Picture

However, after the migration was done I encountered strange behavior, since nextcloud often switches to maintenance back and forth (almost minutewise). I looked up and saw in Postgres stuff like that:

docker-nextcloud-db-postgres-1  | 2022-06-22 04:03:44.235 UTC [381225] DETAIL:  Key (objecttype, objectid, systemtagid)=(files, 390134, 19) already exists.
docker-nextcloud-db-postgres-1  | 2022-06-22 04:03:44.236 UTC [381225] DETAIL:  Key (objecttype, objectid, systemtagid)=(files, 390131, 153) already exists.
docker-nextcloud-db-postgres-1  | 2022-06-22 04:03:44.237 UTC [381225] DETAIL:  Key (objecttype, objectid, systemtagid)=(files, 390107, 153) already exists.
docker-nextcloud-db-postgres-1  | 2022-06-22 04:03:44.237 UTC [381225] DETAIL:  Key (objecttype, objectid, systemtagid)=(files, 390107, 37) already exists.
[...]

and this...

docker-nextcloud-db-postgres-1  | 2022-06-22 04:07:41.198 UTC [381226] ERROR:  duplicate key value violates unique constraint "fs_storage_path_hash"
docker-nextcloud-db-postgres-1  | 2022-06-22 04:07:41.198 UTC [381226] DETAIL:  Key (storage, path_hash)=(14, 2f5b76cbc822adadc9782549ce5258a7) already exists.
docker-nextcloud-db-postgres-1  | 2022-06-22 04:07:41.198 UTC [381226] STATEMENT:  INSERT INTO "oc_filecache" ("mimepart", "mimetype", "mtime", "size", "etag", "storage_mtime", "permissions", "name", "parent", "checksum", "path_hash", "path", "storage") VALUES($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)
docker-nextcloud-db-postgres-1  | 2022-06-22 04:08:58.225 UTC [381226] ERROR:  duplicate key value violates unique constraint "fs_storage_path_hash"
docker-nextcloud-db-postgres-1  | 2022-06-22 04:08:58.225 UTC [381226] DETAIL:  Key (storage, path_hash)=(14, 213cd3df74fcae566453aff6e878f7f2) already exists.
docker-nextcloud-db-postgres-1  | 2022-06-22 04:08:58.225 UTC [381226] STATEMENT:  INSERT INTO "oc_filecache" ("mimepart", "mimetype", "mtime", "size", "etag", "storage_mtime", "permissions", "name", "parent", "checksum", "path_hash", "path", "storage") VALUES($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)
docker-nextcloud-db-postgres-1  | 2022-06-22 04:09:19.657 UTC [381226] ERROR:  duplicate key value violates unique constraint "fs_storage_path_hash"
docker-nextcloud-db-postgres-1  | 2022-06-22 04:09:19.657 UTC [381226] DETAIL:  Key (storage, path_hash)=(14, 94ad1a9b370fcdc1b4bfdb427a89831f) already exists.
docker-nextcloud-db-postgres-1  | 2022-06-22 04:09:19.657 UTC [381226] STATEMENT:  INSERT INTO "oc_filecache" ("mimepart", "mimetype", "mtime", "size", "etag", "storage_mtime", "permissions", "name", "parent", "checksum", "path_hash", "path", "storage") VALUES($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)
[...]

DB investigation

Running the following in Postgres gave me the clue that somethis is wrong with the backup app or at least with its database entries:

select * from oc_filecache where storage=14;

Which looks like this:

Reset

After Runnin the following command, the entities are still persistent within the database:

docker exec -u <id www user>:<gid www user> -ti <containerID> php occ backup:reset

I'd like to use the Backup app in the future so any help would be appreciated.

Moep90 commented 2 years ago

Any update here?

ArtificialOwl commented 2 years ago

I do not see how the Backup app is linked to your issue

Moep90 commented 2 years ago

Because the db error relats to the fields in the database. These files are related to the files the "backup app" creates. And when deleting it, it does not cleanup db entries which cuases the issue I described.

zenlord commented 1 year ago

I'm seeing the same error in my journal (AMD64 // Debian Bullseye // PostgreSQL 13.9 (Debian 13.9-0+deb11u1) // bare metal install):

Mar 07 07:28:20 HOST postgres[97699]: [6-1] 2023-03-07 07:28:20.285 CET [97699] nextcloud@nextcloud ERROR:  duplicate key value violates unique constraint "fs_storage_path_hash"
Mar 07 07:28:20 HOST postgres[97699]: [6-1] 2023-03-07 07:28:20.285 CET [97699] nextcloud@nextcloud DETAIL:  Key (storage, path_hash)=(2, 6ed7df8e17dab7bc10be0108fb27e92f) already exists.
Mar 07 07:28:20 HOST postgres[97699]: [6-1] 2023-03-07 07:28:20.285 CET [97699] nextcloud@nextcloud STATEMENT:  INSERT INTO "oc_filecache" ("mimepart", "mimetype", "mtime", "size", "etag", "storage_mtime", "permissions", "name", "parent", "checksum", "path_hash", "path", "storage") VALUES($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)

I have installed nextcloud 25.0.4 by updating from 24.0.10, but botched my database along the way and had to completely start over with a new database. I ran the 'missing-indices' command after the update.

Noticed the error twice now:

Mar 06 16:09:32
Mar 07 07:28:20

greenship24 commented 1 year ago

Also seeing the same error after migrating from mariadb to postgresql on 24.x and seeing it now that I'm on 25.0.3. Perhaps it existed on 24.x as well but I don't remember as this is a test instance. This causes random timeouts in the UI trying to upload files, add comments to Deck, etc. etc.

36
2023-04-14 21:25:05.269 UTC [761] ERROR:  duplicate key value violates unique constraint "fs_storage_path_hash"
35
2023-04-14 21:25:05.269 UTC [761] DETAIL:  Key (storage, path_hash)=(42, 0c6e3521b073fa8ff080ac1693265419) already exists.
34
2023-04-14 21:25:05.269 UTC [761] STATEMENT:  INSERT INTO "oc_filecache" ("mimepart", "mimetype", "mtime", "size", "etag", "storage_mtime", "permissions", "name", "parent", "checksum", "path_hash", "path", "storage") VALUES($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)
33
2023-04-14 21:25:05.341 UTC [761] ERROR:  duplicate key value violates unique constraint "fs_storage_path_hash"
32
2023-04-14 21:25:05.341 UTC [761] DETAIL:  Key (storage, path_hash)=(42, 670c209cc1c3bf3d64f9d6bf1c8038bd) already exists.
31
2023-04-14 21:25:05.341 UTC [761] STATEMENT:  INSERT INTO "oc_filecache" ("mimepart", "mimetype", "mtime", "size", "etag", "storage_mtime", "permissions", "name", "parent", "checksum", "path_hash", "path", "storage") VALUES($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)

nextcloud / backup