Duplicate key value violates unique constraint: pw_index_password_revision

🍱 Features & apps passwords
1

Hello,

An upgrade to Nextcloud 21 put the instance into Maintenance mode, because of conflicts with the Passwords app (I believe I was on version 2020.12.4). This was because the table oc_passwords_entity_tag_revision did not exist. I disabled the app and then successfully upgraded to Nextcloud 21 (Docker version). I then enabled Passwords 2021.7.21. The app opens, and all my passwords are available. However, when I try to edit an existing entry or add a new one, I get the following error:

Doctrine\DBAL\Exception\UniqueConstraintViolationException: An exception occurred while executing a query: SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value violates unique constraint “pw_index_password_revision” DETAIL: Key (user_id, uuid)=(***) already exists. (Where *** is my user_id and uuid.)

Adapting from the topic here, I deleted the relevant entry in the table oc_passwords_password_rv, then ran occ maintenance:repair. However, the problem persists.

Running:
Nextcloud 21.0.3
Passwords 2021.7.21
PHP 7.4.21
PostgreSQL 13.3

2

I think you might be affected by Can no longer add passwords · Issue #397 · marius-wieschollek/passwords · GitHub . The migration copied the data with the primary key and in this case PostgreSQL does not seem to update the auto_increment value. Installing the nightly version may fix this.

In case you need that deleted revision back, you can use the built in backups.

3

Thank you. Installing nightly build 2021.7.22-build4207 and then attempting the upgrade now gives me this:

Preparing update
Set log level to debug
Turned on maintenance mode
Repair step: Repair MySQL collation
Repair info: Not a mysql database → nothing to do
Repair step: Repair SQLite autoincrement
Repair step: Copy data from accounts table when migrating from ownCloud
Repair step: Drop account terms table when migrating from ownCloud
Updating database schema
Updated database
Repair step: Check System Requirements
Exception: Database error when running migration latest for app passwords

The log shows this:
Caused by Doctrine\DBAL\Exception\UniqueConstraintViolationException: An exception occurred while executing a query: SQLSTATE[23505]: Unique violation: 7 ERROR: could not create unique index “oc_passwords_password_rv_pkey” DETAIL: Key (id)=(18) is duplicated.

4

That sounds like there (now) is a duplicate entry for the id 18.

Can you run the SQL statement SELECT * FROM oc_passwords_password_rv WHERE id = 18; to see how many entries have that key and maybe also check how many entries there are in total.

5

I deleted a duplicate entry. I then had to run occ maintenance:repair BEFORE proceeding with the upgrade. Success! I can now edit and save passwords again.

Thank you!