Resolve ERROR: duplicate key value violates unique constraint "oc_credentials_pkey"

[realies]

Getting a lot of these in the logs of a few instances. All of which are linuxserver/nextcloud and library/postgres based, running latest releases of their Docker images and latest stable Nextcloud. How should this be resolved?

[nextcloud-bot]

GitMate.io thinks possibly related issues are #6343 (PostgreSQL : duplicate key value violates unique constraint error message), #4728 (Postgres duplicate key violation causes Internal Server Error during sync), #11779 (SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry * for key 'lock_key_index'), #1779 (Webdav Calendar Integrity constraint violation: 1062 Duplicate entry), and #9305 (Race condition with locks in database - unique constraint violation on oc_file_locks).

[adi-dev]

Same here, Nextcloud 14.0.3, PHP 7.2.10, PostgreSQL 11.0, Redis, no LDAP Auth.
Tried redis-cli FLUSHALL from #6343 (PostgreSQL : duplicate key value violates unique constraint error message) but no luck.

[realies]

Is there any way to fix this? Running two instances of latest Nextcloud with a PGSQL back-end and only one of them exhibits this issue. It seems like some manual interaction can resolve the never-ending log spam.

[stale]

This issue has been automatically marked as stale because it has not had recent activity and it seems to be missing some essential informations. It will be closed if no further activity occurs. Thank you for your contributions.

[adi-dev]

It looks like this issue is being ignored? Only a bot got interested 🤔

[doronbehar]

Hey, just wanted to share a small experience I've had with this issue: Thanks to Netdata, one day, all of sudden I got notifications from it saying my VPS is in a high CPU usage state for more than 5 minutes. I logged into it, ran journalctl --follow and so this error a zillion times from postgres. I logged in to the web interface and haven't seen anything suspicious. I rebooted and the high CPU usage was gone but I still see this messages in the journal.

[pirate]

Still happening every 500ms for me on the latest Nextcloud 16.0.4, it's generated almost 2gb of these repeated log messages at this point:

database_1  | ERROR:  duplicate key value violates unique constraint "oc_credentials_pkey"
database_1  | DETAIL:  Key ("user", identifier)=(nick, password::logincredentials/credentials) already exists.
database_1  | STATEMENT:  INSERT INTO "oc_credentials" ("user", "identifier", "credentials") VALUES($1, $2, $3)

I added command: postgres -c log_min_messages=LOG to my docker-compose.yml database container to silence the messages temporarily, but it would be great to see a real fix someday...

[kesselb]

There is some progress on this kind of issues but it's a tricky one.

What is logged into your logs as "error" is actually intended. We try to insert a record, a exception is emitted because the unique constraint is violated, we catch this exception and update the record instead.

If you do a select (is there a record with this data) first there is always a (very small) chance that in the time between the select and the insert another process creates the record and we run into the same issue.

General support for upsert has been added with this pr: #13721
Some other issues with this are fixed here: #16572

What's left is to migrate places (like this one) to the new code. From the code path i guess the default case here is update. I'm not sure why nextcloud tries to insert the credentials so often but this should be investigated first.

A doctrine developer (doctrine is the database abstraction layer used by nextcloud) stated about upsert support in doctrine:

I don't believe it's going to happen any time soon because of the complexity of the issue and absence of real demand. In my experience, upserts are mostly used when a DB table is used as a key-value storage which is usually way slower than other alternatives.

I think we are in such a key-value case here with the credentials stuff.

[Perflyst]

My postgresql log gets filled with this error too. If you need any further debug logs please tell me.

ERROR:  duplicate key value violates unique constraint "oc_credentials_pkey"
DETAIL:  Key ("user", identifier)=(xxx, password::logincredentials/credentials) already exists.
STATEMENT:  INSERT INTO "oc_credentials" ("user", "identifier", "credentials") VALUES($1, $2, $3)

[e1fueg0]

I got the same error after migration to a new server (but the domain name remained).
Removing cookies related to the domain helped (so it probably relates to cookies session support).

[SuperSandro2000]

I don't think it is acceptable to let postgres throw errors in normal operation. If a realy error happens I probably don't see it except if I do log filtering.

[kesselb]

Closing this one as duplicate of #6343. There is also a possible patch over there for you to test.

I don't think it is acceptable to let postgres throw errors in normal operation.

Yes! I wonder why no one earlier reported that to the postgres people. Logging a error for a unique constraint violation seems strange. At least from a application developer view with cross database compatibility in mind. Probably it makes sense for a database engineer. They are reporting the violation correctly back to doctrine/dbal (so nextcloud is able to handle it) but also log an error.

[HKMglicks]

currently facing the same problem in nextcloud 17 and providing us the same probelm
Thanks

[waltercool]

As mentioned by @HKMglicks, please reopen, it keeps appearing at Nextcloud 18

Aug 20 12:57:35 Servername postgres[911982]: 2020-08-20 12:57:35.633 UTC [911982] STATEMENT:  INSERT INTO "oc_credentials" ("user", "identifier", "credentials") VALUES($1, $2, $3)
Aug 20 12:57:35 Servername postgres[911981]: 2020-08-20 12:57:35.660 UTC [911981] ERROR:  duplicate key value violates unique constraint "oc_credentials_pkey"
Aug 20 12:57:35 Servername postgres[911981]: 2020-08-20 12:57:35.660 UTC [911981] DETAIL:  Key ("user", identifier)=(xxx, password::logincredentials/credentials) already exists.
Aug 20 12:57:35 Servername postgres[911981]: 2020-08-20 12:57:35.660 UTC [911981] STATEMENT:  INSERT INTO "oc_credentials" ("user", "identifier", "credentials") VALUES($1, $2, $3)
Aug 20 12:57:38 Servername postgres[911987]: 2020-08-20 12:57:38.984 UTC [911987] ERROR:  duplicate key value violates unique constraint "oc_credentials_pkey"
Aug 20 12:57:38 Servername postgres[911987]: 2020-08-20 12:57:38.984 UTC [911987] DETAIL:  Key ("user", identifier)=(xxx, password::logincredentials/credentials) already exists.
Aug 20 12:57:38 Servername postgres[911987]: 2020-08-20 12:57:38.984 UTC [911987] STATEMENT:  INSERT INTO "oc_credentials" ("user", "identifier", "credentials") VALUES($1, $2, $3)
Aug 20 12:57:39 Servername postgres[911986]: 2020-08-20 12:57:39.006 UTC [911986] ERROR:  duplicate key value violates unique constraint "oc_credentials_pkey"
Aug 20 12:57:39 Servername postgres[911986]: 2020-08-20 12:57:39.006 UTC [911986] DETAIL:  Key ("user", identifier)=(xxx, password::logincredentials/credentials) already exists.
Aug 20 12:57:39 Servername postgres[911986]: 2020-08-20 12:57:39.006 UTC [911986] STATEMENT:  INSERT INTO "oc_credentials" ("user", "identifier", "credentials") VALUES($1, $2, $3)

[im-evko]

please reopen, it keeps appearing at Nextcloud 19
2020-08-23 23:11:16.756 UTC [1601831] ncadmin@nextcloud_db STATEMENT: INSERT INTO "oc_credentials" ("user", "identifier", "credentials") VALUES($1, $2, $3) 2020-08-23 23:11:17.993 UTC [1601834] ncadmin@nextcloud_db ERROR: duplicate key value violates unique constraint "oc_credentials_pkey" 2020-08-23 23:11:17.993 UTC [1601834] ncadmin@nextcloud_db DETAIL: Key ("user", identifier)=(aparinova, password::logincredentials/credentials) already exists. 2020-08-23 23:11:17.993 UTC [1601834] ncadmin@nextcloud_db STATEMENT: INSERT INTO "oc_credentials" ("user", "identifier", "credentials") VALUES($1, $2, $3) 2020-08-23 23:11:34.063 UTC [1601842] ncadmin@nextcloud_db ERROR: duplicate key value violates unique constraint "oc_credentials_pkey" 2020-08-23 23:11:34.063 UTC [1601842] ncadmin@nextcloud_db DETAIL: Key ("user", identifier)=(aparinova, password::logincredentials/credentials) already exists. 2020-08-23 23:11:34.063 UTC [1601842] ncadmin@nextcloud_db STATEMENT: INSERT INTO "oc_credentials" ("user", "identifier", "credentials") VALUES($1, $2, $3) 2020-08-23 23:11:35.310 UTC [1601845] ncadmin@nextcloud_db ERROR: duplicate key value violates unique constraint "oc_credentials_pkey" 2020-08-23 23:11:35.310 UTC [1601845] ncadmin@nextcloud_db DETAIL: Key ("user", identifier)=(aparinova, password::logincredentials/credentials) already exists. 2020-08-23 23:11:35.310 UTC [1601845] ncadmin@nextcloud_db STATEMENT: INSERT INTO "oc_credentials" ("user", "identifier", "credentials") VALUES($1, $2, $3) 2020-08-23 23:11:36.700 UTC [1601851] ncadmin@nextcloud_db ERROR: duplicate key value violates unique constraint "oc_credentials_pkey" 2020-08-23 23:11:36.700 UTC [1601851] ncadmin@nextcloud_db DETAIL: Key ("user", identifier)=(aparinova, password::logincredentials/credentials) already exists. 2020-08-23 23:11:36.700 UTC [1601851] ncadmin@nextcloud_db STATEMENT: INSERT INTO "oc_credentials" ("user", "identifier", "credentials") VALUES($1, $2, $3) 2020-08-23 23:11:37.952 UTC [1601854] ncadmin@nextcloud_db ERROR: duplicate key value violates unique constraint "oc_credentials_pkey" 2020-08-23 23:11:37.952 UTC [1601854] ncadmin@nextcloud_db DETAIL: Key ("user", identifier)=(aparinova, password::logincredentials/credentials) already exists. 2020-08-23 23:11:37.952 UTC [1601854] ncadmin@nextcloud_db STATEMENT: INSERT INTO "oc_credentials" ("user", "identifier", "credentials") VALUES($1, $2, $3) ...