Duplicate Key constraint error when activating

ghost commented 6 years ago

Steps to reproduce

Activate Antivirus App for file results in this error: An exception occurred while executing 'ALTER TABLE oc_files_antivirus ADD PRIMARY KEY ("fileid")': SQLSTATE[23505]: Unique violation: 7 ERROR: could not create unique index "oc_files_antivirus_pkey" DETAIL: Key (fileid)=(474) is duplicated.
under deactivated apps, remove the app again to have the database cleaned
activate antivirus app again, but same error: An exception occurred while executing 'ALTER TABLE oc_files_antivirus ADD PRIMARY KEY ("fileid")': SQLSTATE[23505]: Unique violation: 7 ERROR: could not create unique index "oc_files_antivirus_pkey" DETAIL: Key (fileid)=(474) is duplicated.

Expected behaviour

Well, of course the app should install flawlessly ;-) Second, after remove the app, I would expect that the database will be in an consistent state that will enable the installation on a second try.

Actual behaviour

An exception occurred while executing 'ALTER TABLE oc_files_antivirus ADD PRIMARY KEY ("fileid")': SQLSTATE[23505]: Unique violation: 7 ERROR: could not create unique index "oc_files_antivirus_pkey" DETAIL: Key (fileid)=(474) is duplicated.

Server configuration

Debian Stable

List of activated apps: Enabled:

activity: 2.6.1
admin_audit: 1.3.0
admin_notifications: 1.0.1
announcementcenter: 3.2.1
apporder: 0.4.1
audioplayer: 2.2.5
bruteforcesettings: 1.0.3
calendar: 1.6.1
checksum: 0.3.5
circles: 0.13.6
cms_pico: 0.9.7
comments: 1.3.0
contacts: 2.1.2
dav: 1.4.6
deck: 0.3.1
dicomviewer: 0.0.2
drop_account: 0.0.7
dropit: 0.1.0
end_to_end_encryption: 1.0.4
federatedfilesharing: 1.3.1
federation: 1.3.0
files: 1.8.0
files_accesscontrol: 1.3.0
files_automatedtagging: 1.3.0
files_downloadactivity: 1.2.0
files_external: 1.4.1
files_markdown: 2.0.4
files_pdfviewer: 1.2.0
files_reader: 1.2.3
files_retention: 1.2.0
files_sharing: 1.5.0
files_texteditor: 2.5.1
files_trashbin: 1.3.0
files_versions: 1.6.0
files_videoplayer: 1.2.0
firstrunwizard: 2.2.1
flowupload: 0.0.7
gallery: 18.0.0
groupfolders: 1.2.0
impersonate: 1.0.2
logreader: 2.0.0
lookup_server_connector: 1.1.0
mail: 0.7.10
nextcloud_announcements: 1.2.0
notes: 2.3.2
notifications: 2.1.2
oauth2: 1.1.0
passman: 2.1.4
password_policy: 1.3.0
polls: 0.8.1
previewgenerator: 1.0.9
provisioning_api: 1.3.0
quota_warning: 1.2.0
ransomware_protection: 1.1.0
richdocuments: 2.0.3
serverinfo: 1.3.0
sharebymail: 1.3.0
spreed: 3.1.0
survey_client: 1.1.0
systemtags: 1.3.0
tasks: 0.9.6
theming: 1.4.1
twofactor_backupcodes: 1.2.3
weather: 1.5.1
workflowengine: 1.3.0 Disabled:
encryption
files_antivirus
updatenotification
user_external
user_ldap

Nextcloud configuration: { "system": { "instanceid": "REMOVED SENSITIVE VALUE", "passwordsalt": "REMOVED SENSITIVE VALUE", "datadirectory": "REMOVED SENSITIVE VALUE", "dbtype": "pgsql", "version": "13.0.0.14", "appstoreenabled": true, "appspaths": [ { "path": "\/var\/www\/nextcloud\/apps", "url": "\/apps", "writable": true } ], "dbname": "REMOVED SENSITIVE VALUE", "dbhost": "REMOVED SENSITIVE VALUE", "dbtableprefix": "oc", "dbuser": "REMOVED SENSITIVE VALUE", "dbpassword": "REMOVED SENSITIVE VALUE", "installed": true, "forcessl": true, "theme": "", "maintenance": false, "loglevel": 9, "mail_domain": "REMOVED SENSITIVE VALUE", "mail_smtpdebug": true, "mail_smtpmode": "smtp", "mail_smtphost": "REMOVED SENSITIVE VALUE", "mail_smtpport": "25", "mail_smtptimeout": 10, "mail_smtpname": "REMOVED SENSITIVE VALUE", "mail_smtppassword": "REMOVED SENSITIVE VALUE", "trusted_domains": [ "silverhaze.org", "cloud.mycr.de", "cloud.amkabutzenhof.de" ], "mail_from_address": "REMOVED SENSITIVE VALUE", "secret": "REMOVED SENSITIVE VALUE", "trashbin_retention_obligation": "auto", "appstore.experimental.enabled": false, "memcached_servers": [ [ "127.0.0.1", 11211 ], [ "192.168.254.254", 11211 ] ], "memcache.distributed": "\OC\Memcache\Redis", "memcache.locking": "\OC\Memcache\Redis", "memcache.local": "\OC\Memcache\APCu", "redis": { "host": "REMOVED SENSITIVE VALUE", "port": 6379 }, "updater.release.channel": "stable", "overwrite.cli.url": "https:\/\/silverhaze.org", "htaccess.RewriteBase": "\/", "updater.secret": "REMOVED SENSITIVE VALUE" } }

Client configuration

Browser:

Operating system:

Logs

Nextcloud log (data/owncloud.log)

{"reqId":"7QpcwO8ROhS5Y1HEeTuC","level":4,"time":"2018-03-13T11:23:07+00:00","remoteAddr":"2003:d2:abcf:ee00:b48c:8cc9:5df3:4e5c","user":"ij","app":"core","method":"POST","url":"\/index.php\/settings\/ajax\/enableapp.php","message":"Failed to update database structure (PDOException: SQLSTATE[23505]: Unique violation: 7 ERROR: could not create unique index \"oc_files_antiviruspkey\"\nDETAIL: Key (fileid)=(474) is duplicated. in \/var\/www\/nextcloud\/3rdparty\/doctrine\/dbal\/lib\/Doctrine\/DBAL\/Driver\/PDOConnection.php:104\nStack trace:\n#0 \/var\/www\/nextcloud\/3rdparty\/doctrine\/dbal\/lib\/Doctrine\/DBAL\/Driver\/PDOConnection.php(104): PDO->query('ALTER TABLE oc...')\n#1 \/var\/www\/nextcloud\/3rdparty\/doctrine\/dbal\/lib\/Doctrine\/DBAL\/Connection.php(954): Doctrine\DBAL\Driver\PDOConnection->query('ALTER TABLE oc...')\n#2 \/var\/www\/nextcloud\/lib\/private\/DB\/Migrator.php(260): Doctrine\DBAL\Connection->query('ALTER TABLE oc...')\n#3 \/var\/www\/nextcloud\/lib\/private\/DB\/Migrator.php(83): OC\DB\Migrator->applySchema(Object(Doctrine\DBAL\Schema\Schema))\n#4 \/var\/www\/nextcloud\/lib\/private\/DB\/MDB2SchemaManager.php(123): OC\DB\Migrator->migrate(Object(Doctrine\DBAL\Schema\Schema))\n#5 \/var\/www\/nextcloud\/lib\/private\/legacy\/db.php(187): OC\DB\MDB2SchemaManager->updateDbFromStructure('\/var\/www\/nextcl...')\n#6 \/var\/www\/nextcloud\/lib\/private\/Installer.php(139): OC_DB::updateDbFromStructure('\/var\/www\/nextcl...')\n#7 \/var\/www\/nextcloud\/lib\/private\/legacy\/app.php(385): OC\Installer->installApp('files_antivirus')\n#8 \/var\/www\/nextcloud\/settings\/ajax\/enableapp.php(45): OC_App->enable('files_antivirus', NULL)\n#9 \/var\/www\/nextcloud\/lib\/private\/Route\/Route.php(155): require_once('\/var\/www\/nextcl...')\n#10 [internal function]: OC\Route\Route->OC\Route\{closure}(NULL)\n#11 \/var\/www\/nextcloud\/lib\/private\/Route\/Router.php(297): call_user_func(Object(Closure), Array)\n#12 \/var\/www\/nextcloud\/lib\/base.php(998): OC\Route\Router->match('\/settings\/ajax\/...')\n#13 \/var\/www\/nextcloud\/index.php(37): OC::handleRequest()\n#14 {main}\n\nNext Doctrine\DBAL\Driver\PDOException: SQLSTATE[23505]: Unique violation: 7 ERROR: could not create unique index \"oc_files_antiviruspkey\"\nDETAIL: Key (fileid)=(474) is duplicated. in \/var\/www\/nextcloud\/3rdparty\/doctrine\/dbal\/lib\/Doctrine\/DBAL\/Driver\/PDOConnection.php:106\nStack trace:\n#0 \/var\/www\/nextcloud\/3rdparty\/doctrine\/dbal\/lib\/Doctrine\/DBAL\/Connection.php(954): Doctrine\DBAL\Driver\PDOConnection->query('ALTER TABLE oc...')\n#1 \/var\/www\/nextcloud\/lib\/private\/DB\/Migrator.php(260): Doctrine\DBAL\Connection->query('ALTER TABLE oc_...')\n#2 \/var\/www\/nextcloud\/lib\/private\/DB\/Migrator.php(83): OC\DB\Migrator->applySchema(Object(Doctrine\DBAL\Schema\Schema))\n#3 \/var\/www\/nextcloud\/lib\/private\/DB\/MDB2SchemaManager.php(123): OC\DB\Migrator->migrate(Object(Doctrine\DBAL\Schema\Schema))\n#4 \/var\/www\/nextcloud\/lib\/private\/legacy\/db.php(187): OC\DB\MDB2SchemaManager->updateDbFromStructure('\/var\/www\/nextcl...')\n#5 \/var\/www\/nextcloud\/lib\/private\/Installer.php(139): OC_DB::updateDbFromStructure('\/var\/www\/nextcl...')\n#6 \/var\/www\/nextcloud\/lib\/private\/legacy\/app.php(385): OC\Installer->installApp('files_antivirus')\n#7 \/var\/www\/nextcloud\/settings\/ajax\/enableapp.php(45): OC_App->enable('files_antivirus', NULL)\n#8 \/var\/www\/nextcloud\/lib\/private\/Route\/Route.php(155): require_once('\/var\/www\/nextcl...')\n#9 [internal function]: OC\Route\Route->OC\Route\{closure}(NULL)\n#10 \/var\/www\/nextcloud\/lib\/private\/Route\/Router.php(297): call_user_func(Object(Closure), Array)\n#11 \/var\/www\/nextcloud\/lib\/base.php(998): OC\Route\Router->match('\/settings\/ajax\/...')\n#12 \/var\/www\/nextcloud\/index.php(37): OC::handleRequest()\n#13 {main}\n\nNext Doctrine\DBAL\Exception\UniqueConstraintViolationException: An exception occurred while executing 'ALTER TABLE oc_files_antivirus ADD PRIMARY KEY (\"fileid\")':\n\nSQLSTATE[23505]: Unique violation: 7 ERROR: could not create unique index \"oc_files_antiviruspkey\"\nDETAIL: Key (fileid)=(474) is duplicated. in \/var\/www\/nextcloud\/3rdparty\/doctrine\/dbal\/lib\/Doctrine\/DBAL\/Driver\/AbstractPostgreSQLDriver.php:64\nStack trace:\n#0 \/var\/www\/nextcloud\/3rdparty\/doctrine\/dbal\/lib\/Doctrine\/DBAL\/DBALException.php(128): Doctrine\DBAL\Driver\AbstractPostgreSQLDriver->convertException('An exception oc...', Object(Doctrine\DBAL\Driver\PDOException))\n#1 \/var\/www\/nextcloud\/3rdparty\/doctrine\/dbal\/lib\/Doctrine\/DBAL\/Connection.php(964): Doctrine\DBAL\DBALException::driverExceptionDuringQuery(Object(Doctrine\DBAL\Driver\PDOPgSql\Driver), Object(Doctrine\DBAL\Driver\PDOException), 'ALTER TABLE oc...')\n#2 \/var\/www\/nextcloud\/lib\/private\/DB\/Migrator.php(260): Doctrine\DBAL\Connection->query('ALTER TABLE oc_...')\n#3 \/var\/www\/nextcloud\/lib\/private\/DB\/Migrator.php(83): OC\DB\Migrator->applySchema(Object(Doctrine\DBAL\Schema\Schema))\n#4 \/var\/www\/nextcloud\/lib\/private\/DB\/MDB2SchemaManager.php(123): OC\DB\Migrator->migrate(Object(Doctrine\DBAL\Schema\Schema))\n#5 \/var\/www\/nextcloud\/lib\/private\/legacy\/db.php(187): OC\DB\MDB2SchemaManager->updateDbFromStructure('\/var\/www\/nextcl...')\n#6 \/var\/www\/nextcloud\/lib\/private\/Installer.php(139): OC_DB::updateDbFromStructure('\/var\/www\/nextcl...')\n#7 \/var\/www\/nextcloud\/lib\/private\/legacy\/app.php(385): OC\Installer->installApp('files_antivirus')\n#8 \/var\/www\/nextcloud\/settings\/ajax\/enableapp.php(45): OC_App->enable('files_antivirus', NULL)\n#9 \/var\/www\/nextcloud\/lib\/private\/Route\/Route.php(155): require_once('\/var\/www\/nextcl...')\n#10 [internal function]: OC\Route\Route->OC\Route\{closure}(NULL)\n#11 \/var\/www\/nextcloud\/lib\/private\/Route\/Router.php(297): call_user_func(Object(Closure), Array)\n#12 \/var\/www\/nextcloud\/lib\/base.php(998): OC\Route\Router->match('\/settings\/ajax\/...')\n#13 \/var\/www\/nextcloud\/index.php(37): OC::handleRequest()\n#14 {main})","userAgent":"Mozilla\/5.0 (Macintosh; Intel Mac OS X 10.13; rv:58.0) Gecko\/20100101 Firefox\/58.0","version":"13.0.0.14"}

Browser log

Insert your browser log here, this could for example include:

a) The javascript console log
b) The network log
c) ...

rullzer commented 6 years ago

Yes I agree. But I can't yet switch to the migration (they were only introduced in NC13).

Please drop the tables manually and try again.

ghost commented 6 years ago

Ok, did this on psql:

owncloud=# drop table oc_files_antivirus cascade; DROP TABLE owncloud=#

After that installation/activation worked like expected.

I'm unsure whether or not the installation should check the result of the SQL command and in case of a duplicate key error drop that table itself or leave it to the admin to solve this issue. Prompting the admin with a choice "a) drop it or b) investigate further" would be the best choice, but is more something that needs decided of the core developers how they want to deal with such errors.

nextcloud / files_antivirus