duplicate key value violates unique constraint "oc_preferences_pkey" on every login

[spitzlei]

Steps to reproduce

1. Login to Owncloud Website
2. Open postgresql Serverlog

Expected behaviour

No Error in logs

Actual behaviour

Logfile is being spammed with errors:
2016-03-14 09:48:10 GMT STATEMENT: INSERT INTO "oc_preferences" ("userid", "appid", "configkey", "configvalue") VALUES($1, $2, $3, $4)
2016-03-14 09:48:34 GMT ERROR: duplicate key value violates unique constraint "oc_preferences_pkey"
2016-03-14 09:48:34 GMT DETAIL: Key (userid, appid, configkey)=(testuser, files, quota) already exists.

(I already cleaned up the table, entries being set after next login. After logout und relogin Errors appear again).

Server configuration

Operating system:
Debian 8.3

Web server:
Package: apache2
Version: 2.4.10-10+deb8u1

Database:
Package: postgresql
Version: 9.4+165

PHP version:
Package: php5
Version: 5.6.17+dfsg-0+deb8u1

ownCloud version: (see ownCloud admin page)
9.0.0 (stable)

Updated from an older ownCloud or fresh install:
Update: 7 > 8 > 9

Where did you install ownCloud from:
deb http://download.owncloud.org/download/repositories/stable/Debian_8.0/ /

Signing status (ownCloud 9.0 and above):
No errors have been found.

List of activated apps:
Enabled:

• activity: 2.2.1
• calendar: 1.0
• comments: 0.2
• contacts: 1.0.0.0
• dav: 0.1.5
• external: 1.2
• federatedfilesharing: 0.1.0
• federation: 0.0.4
• files: 1.4.4
• files_external: 0.5.2
• files_pdfviewer: 0.8
• files_sharing: 0.9.1
• files_texteditor: 2.1
• files_trashbin: 0.8.0
• files_versions: 1.2.0
• files_videoplayer: 0.9.8
• firstrunwizard: 1.1
• gallery: 14.5.0
• notifications: 0.2.3
• provisioning_api: 0.4.1
• systemtags: 0.2
• templateeditor: 0.1
• updatenotification: 0.1.0
  Disabled:
• encryption
• user_external
• user_ldap

The content of config/config.php:
{
"system": {
"instanceid": "_REMOVED",
"passwordsalt": "_REMOVED SENSITIVE VALUE",
"trusted_domains": [
"_REMOVED"
],
"datadirectory": "/var/www/owncloud/data",
"dbtype": "pgsql",
"version": "9.0.0.19",
"dbname": "owncloud",
"dbhost": "localhost",
"dbtableprefix": "oc",
"dbuser": "_REMOVED SENSITIVE VALUE",
"dbpassword": "_REMOVED SENSITIVE VALUE",
"installed": true,
"mail_from_address": "cloud",
"mail_smtpmode": "php",
"mail_domain": "_REMOVED",
"forcessl": true,
"theme": "",
"maintenance": false,
"secret": "_REMOVED SENSITIVE VALUE",
"loglevel": 2,
"memcache.local": "\OC\Memcache\APC",
"trashbin_retention_obligation": "auto",
"updatechecker": false
}
}

Are you using external storage, if yes which one:
No

Are you using encryption:
no

Are you using an external user-backend, if yes which one:
no

Client configuration

Browser:
Firefox 45

Operating system:
Debian 8

[LukasReschke]

Duplicate of #22563

[spitzlei]

Why is this a Duplicate?
I'm using Owncloud 9.0 and not 8.0 and here the oc_preferences tables is affected and not the oc_filecache.

[nebulade]

We are seeing a similar issue, maybe related but in LDAP code:

ERROR:  duplicate key value violates unique constraint "preferences_pkey"
Mar 14 19:38:07 smartserver.io docker[750]: 2016-03-14 19:38:07 UTC DETAIL:  Key (userid, appid, configkey)=(username, user_ldap, displayName) already exists.
Mar 14 19:38:07 smartserver.io docker[750]: 2016-03-14 19:38:07 UTC STATEMENT:  INSERT INTO "preferences" ("userid", "appid", "configkey", "configvalue") VALUES($1, $2, $3, $4)

If I understand #22563 correctly, can this be ignored for now? I am also using oC 9.0 but with mysql

[mobstef]

There is a workaround for this issue, which seems to affect ldap user login in 9.0.
To stop flooding of the logs with the reported error message add a rule to ignore the insert if the constraint violation would occur. After http://dba.stackexchange.com/questions/30499/optimal-way-to-ignore-duplicate-inserts:
CREATE OR REPLACE RULE oc_preferences_ignore_duplicate_inserts AS ON INSERT TO oc_preferences WHERE (EXISTS ( SELECT 1 FROM oc_preferences WHERE oc_preferences.userid = NEW.userid and oc_preferences.appid = NEW.appid and oc_preferences.configkey = NEW.configkey)) DO INSTEAD NOTHING;

[jknockaert]

Seeing a similar issue, but without LDAP:

[2016-03-22 16:23:33.169 CET] REMOVED ERROR:  duplicate key value violates unique constraint "oc_preferences_pkey"
[2016-03-22 16:23:33.169 CET] REMOVED DETAIL:  Key (userid, appid, configkey)=(REMOVED, login, lastLogin) already exists.
[2016-03-22 16:23:33.169 CET] REMOVED STATEMENT:  INSERT INTO "oc_preferences" ("userid", "appid", "configkey", "configvalue") VALUES($1, $2, $3, $4)

The postgres log is full of these lines. Especially with the os x client app this error happens multiple times per user per minute.

@LukasReschke Pls reopen. This happens on OC 9 (with postgres) and is not a duplicate of #22563

[jknockaert]

Do we already know which regression caused this behaviour?

[voidzero]

@mobstef Any idea how to reverse this query, for when the bug gets fixed?

[mobstef]

@voidzero Rather straightforward:
drop rule oc_preferences_ignore_duplicate_inserts on oc_preferences;

[voidzero]

Perfect, @mobstef. Much thanks.

[nickvergessen]

Just had a look at the code, it seems to be expected. We run the insert, then catch the php side exception that is thrown by doctrine when the key is violated and update instead.

Basically we could try and change the preference stuff to use insertIfNotExists(), but we can't do that in the current method Connection::setValues() because we don't know for all of the use-cases of the method, what keys they would need to check.