[Bug]: MySQL "Integrity constraint violation" with an account whose password has changed since last log in

[devsamt]

⚠️ This issue respects the following points: ⚠️

• This is a bug, not a question or a configuration/webserver/proxy issue.
• This issue is not already reported on Github (I've searched it).
• Nextcloud Server is up to date. See Maintenance and Release Schedule for supported versions.
• Nextcloud Server is running on 64bit capable CPU, PHP and OS.
• I agree to follow Nextcloud's Code of Conduct.

Bug description

Since the major update of NextCloud in version 26, the user has an error message if he connects with his LDAP account and if his password has changed since his last connection to the application:

Integrity constraint violation: 1062 Duplicate entry

MySql logs:

SELECT `credentials` FROM `oc_storages_credentials`
  WHERE (`identifier` = 'password::logincredentials/credentials') AND (`user` = 'bob')

INSERT INTO `oc_storages_credentials` (`user`, `identifier`, `credentials`)
  VALUES('bob', 'password::logincredentials/credentials', '***hidden***')

The first query returns one result, so the second query should be :

UPDATE `oc_storages_credentials` SET `credentials` = '***hidden***'
  WHERE (`identifier` = 'password::logincredentials/credentials') AND (`user` = 'bob')

Source here:
https://help.nextcloud.com/t/integrity-constraint-violation-1062-duplicate-entry/157651

Steps to reproduce

1. Try logging into NextCloud with an LDAP account whose password has changed since you last logged into the app

Expected behavior

No error

Installation method

None

Nextcloud Server version

26

Operating system

Debian/Ubuntu

PHP engine version

PHP 8.1

Web server

Apache (supported)

Database engine version

MySQL

Is this bug present after an update or on a fresh install?

Updated to a major version (ex. 22.2.3 to 23.0.1)

Are you using the Nextcloud Server Encryption module?

None

What user-backends are you using?

• Default user-backend (database)
• LDAP/ Active Directory
• SSO - SAML
• Other

Configuration report

No response

List of activated Apps

- activity: 2.18.0
  - admin_audit: 1.16.0
  - bruteforcesettings: 2.6.0
  - circles: 26.0.0
  - cloud_federation_api: 1.9.0
  - comments: 1.16.0
  - contactsinteraction: 1.7.0
  - dashboard: 7.6.0
  - dav: 1.25.0
  - federatedfilesharing: 1.16.0
  - federation: 1.16.0
  - files: 1.21.1
  - files_external: 1.18.0
  - files_pdfviewer: 2.7.0
  - files_rightclick: 1.5.0
  - files_sharing: 1.18.0
  - files_trashbin: 1.16.0
  - files_versions: 1.19.1
  - firstrunwizard: 2.15.0
  - logreader: 2.11.0
  - lookup_server_connector: 1.14.0
  - nextcloud_announcements: 1.15.0
  - notifications: 2.14.0
  - oauth2: 1.14.0
  - password_policy: 1.16.0
  - photos: 2.2.0
  - privacy: 1.10.0
  - provisioning_api: 1.16.0
  - recommendations: 1.5.0
  - related_resources: 1.1.0-alpha1
  - serverinfo: 1.16.0
  - settings: 1.8.0
  - sharebymail: 1.16.0
  - support: 1.9.0
  - survey_client: 1.14.0
  - systemtags: 1.16.0
  - text: 3.7.2
  - theming: 2.1.1
  - twofactor_backupcodes: 1.15.0
  - updatenotification: 1.16.0
  - user_ldap: 1.16.0
  - user_status: 1.6.0
  - viewer: 1.10.0
  - weather_status: 1.6.0
  - workflowengine: 2.8.0
Disabled:
  - encryption: 2.14.0 (installed 2.4.0)
  - suspicious_login: 4.4.0
  - twofactor_totp: 8.0.0-alpha.0

Nextcloud Signing status

No response

Nextcloud Logs

No response

Additional info

No response

[joba-1]

some more data:

I have the same error on a new installation but don't use ldap.

"An exception occurred while executing
 a query: SQLSTATE[23000]: Integrity constraint vio
lation: 1062 Duplicate entry 'joachim-password::logincredentials/credentials' for key 'stocred_ui'"

It worked nicely after installing the docker container basically as described here: https://github.com/ladral/qnap-nextcloud

I did change passwords of users (play around phase…)

edit: only happens when using the correct password

edit2: changing the password of the user as admin does not help

edit3: no risk, no fun! Deleting a single row in the oc_storages_credentials table fixed it for me. All users can login again:

For me (using mariadb in a docker container of the QNAP Container Station) this meant:

• Open QNAP admin page
• Login (user with sufficient authorization for Container Station)
• Open Container Station app
• Select Containers
• Select name of your database container (mine was nextcloud_db)
• Select [Execute], then /bin/bash to start a shell console inside the container
• In the console start mysql cli with your nextclouduser: enter mysql -u nextclouduser -p
• In the mariadb prompt switch to your database (mine is named nextcloud): enter use nextcloud
• Sanity check: you should see one row with the not working user when executing this line:
  SELECT * FROM `oc_storages_credentials` WHERE (`identifier` = 'password::logincredentials/credentials');
• Finally remove the row with
  DELETE FROM `oc_storages_credentials` WHERE (`identifier` = 'password::logincredentials/credentials');

All guarantees are void... :)

[devsamt]

Perhaps another important information:
An external storage (SMB) is mounted for all domain users with their credentials stored in database.

[joba-1]

FWIW; I also use external storage (SFTP)

[kuba-n]

Adding some details about the same issue in my setup:

OC\DB\Exceptions\DbalException: An exception occurred while executing a query: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'novyj-password::logincredentials/credentials' for key 'stocred_ui'

• I know about only one user who has this issue after upgrade, all others can log in without any problems,
• I dont see any apparent reason why just this one account is affected,
• When logging in with the right username and password, Internal Server Error message is shown to the user,
• I dont have LDAP and I dont have any external storage connected,
• Table oc_storages_credentials seems ok - each user (no matter if working or non-working one) has only one row with unique values there

[ThaDaVos]

I am having the same issue - trying to update my global password - I am using a Nextcloud as external storage (TransIP's stack) - the owner turned on 2FA - so I had to create a token to connect - but noticed I couldn't update it, what ever I tried - the logs keep being spammed with Integrity Contraint Violation as I try to update the password - for now, I filled the password into the per connection config instead of the global one (I have multiple maps from the same external storage mapped).

Also, due this issue, my Overview page showed there were errors with my WebDav (even though there weren't any at all...)

I also want to include a feature request for this:
Allow multiple globally storage credentials per user

[DanKapone]

Same issue here

• I know about only one user who has this issue after upgrade, all others can log in without any problems,
• I dont see any apparent reason why just this one account is affected,
• When logging in with the right username and password, Internal Server Error message is shown to the user,
• We have LDAP and are using external storage connected (SMB Windows File Server)

[NiiTTrox1337]

When i delete the Entry of the effected User and Remove all SMB Shares i am able to log back in.
No new entry in the Database. After enabeling all sahres again I am able to Log in once. After that same error.
But another User (Same SMB Shares) can still Login.

[brotkastn]

This sounds a lot like #38201 - my guess is that this is the same Problem

[blizzz]

It's likely this is fixed with #37549, to be included in 26.0.2. Testing would be appreciated to confirm it fixes the issue you experience.