PostgreSQL : duplicate key value violates unique constraint error message

[cyayon]

How to use GitHub

• Please use the 👍 reaction to show that you are affected by the same issue.
• Please don't comment if you have no relevant information to add. It's just extra noise for everyone subscribed to this issue.
• Subscribe to receive notifications on status change and new comments.

Steps to reproduce

just logon on nextcloud and check syslog/postgresql log messages

Server configuration

Nextcloud version : 12.0.2
Operating system and version : archlinux (current)
Apache or nginx version : nginx 1.12.1
PHP version : 7.1.7
PostgreSQL : 9.6.3

Hi all !

I encounter a postgresql error message in my syslog everytime i login on my nextcloud instance :

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

No problem for login/logout and using nextcloud, only this error message.

Thanks !

[MorrisJobke]

Could you try this one here as well: #366 (comment)

Thanks.

[cyayon]

Hi,

I just tried redis-cli FLUSHALL command (return OK), but the PostrgreSQL error message continue to appears after each logon ...

[MorrisJobke]

cc @icewind1991 @nickvergessen

@cyayon Are you using LDAP Auth?

[cyayon]

Hi,

No, i am using simple locale authentication.

[Gottox]

Same issue for me:

Nextcloud version : 12.0.3
Operating system and version : Voidlinux
Apache or nginx version : nginx 1.12.1
PHP version : 7.1.7
PostgreSQL : 9.6.5

Local auth, no redis cache.

[enoch85]

I'm also affected.

2017-10-25 20:26:07 CEST [2992-1] user@nextcloud_db ERROR:  duplicate key value violates unique constraint "oc_credentials_pkey"
2017-10-25 20:26:07 CEST [2992-2] user@nextcloud_db DETAIL:  Key ("user", identifier)=(beate, password::logincredentials/credentials) already exists.
2017-10-25 20:26:07 CEST [2992-3] user@nextcloud_db STATEMENT:  INSERT INTO "oc_credentials" ("user", "identifier", "credentials") VALUES($1, $2, $3)
2017-10-25 20:27:23 CEST [3035-1] user@nextcloud_db ERROR:  duplicate key value violates unique constraint "oc_credentials_pkey"
2017-10-25 20:27:23 CEST [3035-2] user@nextcloud_db DETAIL:  Key ("user", identifier)=(Dario, password::logincredentials/credentials) already exists.
2017-10-25 20:27:23 CEST [3035-3] user@nextcloud_db STATEMENT:  INSERT INTO "oc_credentials" ("user", "identifier", "credentials") VALUES($1, $2, $3)
2017-10-25 20:28:34 CEST [3080-1] user@nextcloud_db ERROR:  duplicate key value violates unique constraint "oc_credentials_pkey"
2017-10-25 20:28:34 CEST [3080-2] user@nextcloud_db DETAIL:  Key ("user", identifier)=(Dario, password::logincredentials/credentials) already exists.
2017-10-25 20:28:34 CEST [3080-3] user@nextcloud_db STATEMENT:  INSERT INTO "oc_credentials" ("user", "identifier", "credentials") VALUES($1, $2, $3)
2017-10-25 20:30:39 CEST [3213-1] user@nextcloud_db ERROR:  duplicate key value violates unique constraint "oc_credentials_pkey"
2017-10-25 20:30:39 CEST [3213-2] user@nextcloud_db DETAIL:  Key ("user", identifier)=(Dario, password::logincredentials/credentials) already exists.
2017-10-25 20:30:39 CEST [3213-3] user@nextcloud_db STATEMENT:  INSERT INTO "oc_credentials" ("user", "identifier", "credentials") VALUES($1, $2, $3)
2017-10-25 20:35:04 CEST [3364-1] user@nextcloud_db ERROR:  duplicate key value violates unique constraint "oc_credentials_pkey"
2017-10-25 20:35:04 CEST [3364-2] user@nextcloud_db DETAIL:  Key ("user", identifier)=(Dario, password::logincredentials/credentials) already exists.
2017-10-25 20:35:04 CEST [3364-3] user@nextcloud_db STATEMENT:  INSERT INTO "oc_credentials" ("user", "identifier", "credentials") VALUES($1, $2, $3)

PostgreSQL 9.6.5
PHP 7.0
APache 2.4
Nextcloud 12.0.3

This is even after I did FLUSALL on Redis. Think I forgot to try to login again the last time I tried.

[aventrax]

Yes, same here.

[nickvergessen]

Well the issue is actually:

server/lib/private/DB/Connection.php

Line 289 in 0eebff1

} catch (ConstraintViolationException $e) {

Maybe we should change that to "insert if not exists" instead. to avoid logging the error (which we catch php-wise afterwards).