Integrity constraint violation: 1062 Duplicate entry '-' for key 'pw_password_revision_index'

[Marcm]

Hi

on nextcloud 17.0.2 ubuntu 18.04.3 LTS. (Upgraded to 18.0.1 does not help).

update Passwords to 2020.3.1 failed because of php7.4.1 being detected
occ upgrade (This version of Nextcloud is not compatible with > PHP 7.3.
You are currently running 7.4.1)
I had to disable the Passwords app.

I can’t re-enable the app.

Any advice ?

[mdw]

When does this "Duplicate entry " error happen?
What database type are you using?
Are there any errors in the Nextcloud log?

[Marcm]

updating to 2020.3.1 from 2020.1
I’m using a mysql db

In the nextcloud log:

[core] Fatal: Failed to update database structure (PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘marcm-5ad03531-f1e8-434c-a41c-0adb7e95c3c8’ for key ‘pw_password_revision_index’ in /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:88
Stack trace:
#0 /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php(88): PDO->query(‘CREATE UNIQUE I…’)
#1 /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(1010): Doctrine\DBAL\Driver\PDOConnection->query(‘CREATE UNIQUE I…’)
#2 /var/www/nextcloud/lib/private/DB/Migrator.php(260): Doctrine\DBAL\Connection->query(‘CREATE UNIQUE I…’)
#3 /var/www/nextcloud/lib/private/DB/Migrator.php(83): OC\DB\Migrator->applySchema(Object(Doctrine\DBAL\Schema\Schema))
#4 /var/www/nextcloud/lib/private/DB/MDB2SchemaManager.php(123): OC\DB\Migrator->migrate(Object(Doctrine\DBAL\Schema\Schema))
#5 /var/www/nextcloud/lib/private/legacy/db.php(188): OC\DB\MDB2SchemaManager->updateDbFromStructure(’/var/www/nextcl…’)
#6 /var/www/nextcloud/lib/private/Installer.php(142): OC_DB::updateDbFromStructure(’/var/www/nextcl…’)
#7 /var/www/nextcloud/settings/Controller/AppSettingsController.php(447): OC\Installer->installApp(‘passwords’)
#8 /var/www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php(170): OC\Settings\Controller\AppSettingsController->enableApps(Array, Array)
#9 /var/www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php(99): OC\AppFramework\Http\Dispatcher->executeController(Object(OC\Settings\Controller\AppSettingsController), ‘enableApps’)
#10 /var/www/nextcloud/lib/private/AppFramework/App.php(126): OC\AppFramework\Http\Dispatcher->dispatch(Object(OC\Settings\Controller\AppSettingsController), ‘enableApps’)
#11 /var/www/nextcloud/lib/private/AppFramework/Routing/RouteActionHandler.php(47): OC\AppFramework\App::main(‘OC\Settings\Con…’, ‘enableApps’, Object(OC\AppFramework\DependencyInjection\DIContainer), Array)
#12 [internal function]: OC\AppFramework\Routing\RouteActionHandler->__invoke(Array)
#13 /var/www/nextcloud/lib/private/Route/Router.php(297): call_user_func(Object(OC\AppFramework\Routing\RouteActionHandler), Array)
#14 /var/www/nextcloud/lib/base.php(997): OC\Route\Router->match(’/settings/apps/…’)
#15 /var/www/nextcloud/index.php(42): OC::handleRequest()
#16 {main}

Next Doctrine\DBAL\Driver\PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘marcm-5ad03531-f1e8-434c-a41c-0adb7e95c3c8’ for key ‘pw_password_revision_index’ in /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:90
Stack trace:
#0 /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(1010): Doctrine\DBAL\Driver\PDOConnection->query(‘CREATE UNIQUE I…’)
#1 /var/www/nextcloud/lib/private/DB/Migrator.php(260): Doctrine\DBAL\Connection->query(‘CREATE UNIQUE I…’)
#2 /var/www/nextcloud/lib/private/DB/Migrator.php(83): OC\DB\Migrator->applySchema(Object(Doctrine\DBAL\Schema\Schema))
#3 /var/www/nextcloud/lib/private/DB/MDB2SchemaManager.php(123): OC\DB\Migrator->migrate(Object(Doctrine\DBAL\Schema\Schema))
#4 /var/www/nextcloud/lib/private/legacy/db.php(188): OC\DB\MDB2SchemaManager->updateDbFromStructure(’/var/www/nextcl…’)
#5 /var/www/nextcloud/lib/private/Installer.php(142): OC_DB::updateDbFromStructure(’/var/www/nextcl…’)
#6 /var/www/nextcloud/settings/Controller/AppSettingsController.php(447): OC\Installer->installApp(‘passwords’)
#7 /var/www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php(170): OC\Settings\Controller\AppSettingsController->enableApps(Array, Array)
#8 /var/www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php(99): OC\AppFramework\Http\Dispatcher->executeController(Object(OC\Settings\Controller\AppSettingsController), ‘enableApps’)
#9 /var/www/nextcloud/lib/private/AppFramework/App.php(126): OC\AppFramework\Http\Dispatcher->dispatch(Object(OC\Settings\Controller\AppSettingsController), ‘enableApps’)
#10 /var/www/nextcloud/lib/private/AppFramework/Routing/RouteActionHandler.php(47): OC\AppFramework\App::main(‘OC\Settings\Con…’, ‘enableApps’, Object(OC\AppFramework\DependencyInjection\DIContainer), Array)
#11 [internal function]: OC\AppFramework\Routing\RouteActionHandler->__invoke(Array)
#12 /var/www/nextcloud/lib/private/Route/Router.php(297): call_user_func(Object(OC\AppFramework\Routing\RouteActionHandler), Array)
#13 /var/www/nextcloud/lib/base.php(997): OC\Route\Router->match(’/settings/apps/…’)
#14 /var/www/nextcloud/index.php(42): OC::handleRequest()
#15 {main}

Next Doctrine\DBAL\Exception\UniqueConstraintViolationException: An exception occurred while executing ‘CREATE UNIQUE INDEX pw_password_revision_index ON oc_passwords_entity_password_revision (user_id, uuid)’:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘marcm-5ad03531-f1e8-434c-a41c-0adb7e95c3c8’ for key ‘pw_password_revision_index’ in /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:55
Stack trace:
#0 /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php(169): Doctrine\DBAL\Driver\AbstractMySQLDriver->convertException(‘An exception oc…’, Object(Doctrine\DBAL\Driver\PDOException))
#1 /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php(145): Doctrine\DBAL\DBALException::wrapException(Object(Doctrine\DBAL\Driver\PDOMySql\Driver), Object(Doctrine\DBAL\Driver\PDOException), ‘An exception oc…’)
#2 /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(1012): Doctrine\DBAL\DBALException::driverExceptionDuringQuery(Object(Doctrine\DBAL\Driver\PDOMySql\Driver), Object(Doctrine\DBAL\Driver\PDOException), ‘CREATE UNIQUE I…’)
#3 /var/www/nextcloud/lib/private/DB/Migrator.php(260): Doctrine\DBAL\Connection->query(‘CREATE UNIQUE I…’)
#4 /var/www/nextcloud/lib/private/DB/Migrator.php(83): OC\DB\Migrator->applySchema(Object(Doctrine\DBAL\Schema\Schema))
#5 /var/www/nextcloud/lib/private/DB/MDB2SchemaManager.php(123): OC\DB\Migrator->migrate(Object(Doctrine\DBAL\Schema\Schema))
#6 /var/www/nextcloud/lib/private/legacy/db.php(188): OC\DB\MDB2SchemaManager->updateDbFromStructure(’/var/www/nextcl…’)
#7 /var/www/nextcloud/lib/private/Installer.php(142): OC_DB::updateDbFromStructure(’/var/www/nextcl…’)
#8 /var/www/nextcloud/settings/Controller/AppSettingsController.php(447): OC\Installer->installApp(‘passwords’)
#9 /var/www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php(170): OC\Settings\Controller\AppSettingsController->enableApps(Array, Array)
#10 /var/www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php(99): OC\AppFramework\Http\Dispatcher->executeController(Object(OC\Settings\Controller\AppSettingsController), ‘enableApps’)
#11 /var/www/nextcloud/lib/private/AppFramework/App.php(126): OC\AppFramework\Http\Dispatcher->dispatch(Object(OC\Settings\Controller\AppSettingsController), ‘enableApps’)
#12 /var/www/nextcloud/lib/private/AppFramework/Routing/RouteActionHandler.php(47): OC\AppFramework\App::main(‘OC\Settings\Con…’, ‘enableApps’, Object(OC\AppFramework\DependencyInjection\DIContainer), Array)
#13 [internal function]: OC\AppFramework\Routing\RouteActionHandler->__invoke(Array)
#14 /var/www/nextcloud/lib/private/Route/Router.php(297): call_user_func(Object(OC\AppFramework\Routing\RouteActionHandler), Array)
#15 /var/www/nextcloud/lib/base.php(997): OC\Route\Router->match(’/settings/apps/…’)
#16 /var/www/nextcloud/index.php(42): OC::handleRequest()
#17 {main})

POST /settings/apps/enable
from 10.0.9.2 by ncadmin at 2020-03-05T14:23:07+00:00

[mdw]

For some reason your database seems to have two items with the same uuid. This causes an issue when the update adds an index to the table which requires the uuid to be unique.

Run SELECT * FROM oc_passwords_entity_password_revision WHERE uuid LIKE '5ad03531-f1e8-434c-a41c-0adb7e95c3c8' on your database to see the two entries with the same uuid and either delete one or change its uuid.

Also run SELECT uuid, COUNT(uuid) as total FROM oc_passwords_entity_password_revision GROUP BY uuid HAVING total > 1 to check for any other entry with a duplicate uuid.

If this happens again with the tables oc_passwords_entity_folder_revision or oc_passwords_entity_tag_revision you can fix it the same way.

Then you can execute the upgrade again. I would also recommend running ./occ maintenance:repair afterwards.

[Marcm]

Thank you, mdw.
it worked.

[Seferi]

Hi, I’m having the same problem. How do I rund the command @mdw mentions? Are they OCC commands?

[mdw]

Those are SQL queries. You need to run them on your database directly.

[Seferi]

Thanks for the reply mate. I’m running NCP on docker. What would be the command I should give? Never run sql commands… It’s a bit out of my league… Here is my entry:

Integrity constraint violation: duplicate entry

Hi, I’m running NCP docker container on a Raspberry pi. I found the server in maintenance mode today, it says Music app needs an update but when I press update, it fails. I tried updating from NCP web panel and with occ command with docker exec. All fails. This is what I’m getting: Cannot load Zend OPcache - it was already loaded Nextcloud or one of the apps require upgrade - only a limited number of commands are available You may use your browser or the occ upgrade command to do the upgrade …

[mdw]

Your post is about the music app, not the passwords app. This is not the same error as here.

I recommend asking in the category of the music app (https://help.nextcloud.com/c/apps/music/111) or opening a new issue on their bug tracker (https://github.com/owncloud/music/issues).

[Seferi]

Ok, thanks mate