Bookmarks: sql error during v3 upgrade
Describe the bug
A clear and concise description of what the bug is.
To Reproduce
Steps to reproduce the behavior:
can t
Expected behavior
upgrade bookmark from 3.0.4 to 3.0.5
Updating
Doctrine\DBAL\Exception\UniqueConstraintViolationException: An exception occurred while executing 'INSERT INTO "oc_bookmarks_tree" ("id", "type", "parent_folder", "index") VALUES(?, ?, ?, ?)' with params [69, "bookmark", 11, 1]:
SQLSTATE[23505]: Unique violation: 7 ERREUR: la valeur d'une clé dupliquée rompt la contrainte unique « oc_bookmarks_tree_pkey »
DETAIL: La clé « (id, type, parent_folder)=(69, bookmark, 11) » existe déjà .
Update failed
Screenshots
If applicable, add screenshots to help explain your problem.
Desktop (please complete the following information):
- OS: debian 10.3
- Browser fire fox
- Version latest (75)
Server (please complete the following information):
- OS: debian 10.3
- HTTP server: apache 2.4.38
- Database postgresql 10.10
- PHP version: 7.3.14
- Nextcloud version: 18.0.4
- Bookmarks app version: 3.0.4 to 3.0.5
- Activated Nextcloud Apps:
Enabled:
- accessibility: 1.4.0
- activity: 2.11.0
- admin_audit: 1.8.0
- announcementcenter: 3.7.0
- apporder: 0.9.0
- calendar: 2.0.3
- checksum: 0.4.4
- cloud_federation_api: 1.1.0
- comments: 1.8.0
- contacts: 3.3.0
- dav: 1.14.0
- drawio: 0.9.5
- encryption: 2.6.0
- event_update_notification: 1.0.1
- external: 3.5.0
- federatedfilesharing: 1.8.0
- federation: 1.8.0
- files: 1.13.1
- files_accesscontrol: 1.8.1
- files_automatedtagging: 1.8.2
- files_downloadactivity: 1.7.0
- files_external: 1.9.0
- files_markdown: 2.2.0
- files_pdfviewer: 1.7.0
- files_rightclick: 0.15.2
- files_sharing: 1.10.1
- files_trackdownloads: 1.7.0
- files_versions: 1.11.0
- files_videoplayer: 1.7.0
- firstrunwizard: 2.7.0
- flowupload: 0.1.8
- groupfolders: 6.0.6
- logreader: 2.3.0
- lookup_server_connector: 1.6.0
- metadata: 0.11.1
- nextcloud_announcements: 1.7.0
- notes: 3.2.0
- notifications: 2.6.0
- oauth2: 1.6.0
- password_policy: 1.8.0
- photos: 1.0.0
- privacy: 1.2.0
- provisioning_api: 1.8.0
- qownnotesapi: 20.1.0
- recommendations: 0.6.0
- richdocuments: 3.5.3
- serverinfo: 1.8.0
- settings: 1.0.0
- sharebymail: 1.8.0
- spreed: 8.0.8
- support: 1.1.0
- systemtags: 1.8.0
- tasks: 0.12.2
- text: 2.0.0
- theming: 1.9.0
- twofactor_backupcodes: 1.7.0
- updatenotification: 1.8.0
- viewer: 1.2.0
- workflowengine: 2.0.0
Disabled: - bookmarks
- files_trashbin
- survey_client
- user_ldap
- Nextcloud configuration: [the output of running
sudo -u www-data php occ config:list system]
{
"system": {
"instanceid": "REMOVED SENSITIVE VALUE",
"passwordsalt": "REMOVED SENSITIVE VALUE",
"secret": "REMOVED SENSITIVE VALUE",
"trusted_domains": [
"tools.pra.rip"
],
"datadirectory": "REMOVED SENSITIVE VALUE",
"overwrite.cli.url": "https:\/\/tools.pra.rip",
"dbtype": "pgsql",
"version": "18.0.4.2",
"dbname": "REMOVED SENSITIVE VALUE",
"dbhost": "REMOVED SENSITIVE VALUE",
"dbtableprefix": "oc_",
"dbuser": "REMOVED SENSITIVE VALUE",
"dbpassword": "REMOVED SENSITIVE VALUE",
"installed": true,
"loglevel": 0,
"mail_smtpmode": "smtp",
"mail_from_address": "REMOVED SENSITIVE VALUE",
"mail_domain": "REMOVED SENSITIVE VALUE",
"mail_smtphost": "REMOVED SENSITIVE VALUE",
"mail_smtpport": "25",
"memcache.local": "\OC\Memcache\APCu",
"blacklisted_files": [
".htaccess"
],
"cipher": "AES-256-CFB",
"minimum.supported.desktop.version": "1.7.0",
"default_language": "fr",
"knowledgebaseenabled": true,
"enable_avatars": true,
"allow_user_to_change_display_name": true,
"remember_login_cookie_lifetime": 1296000,
"session_lifetime": 86400,
"session_keepalive": true,
"skeletondirectory": "\/var\/www\/tools\/core\/skeleton",
"mail_smtpsecure": "tls",
"forceSSLforSubdomains": true,
"config_is_read_only": false,
"maintenance": false,
"log_type": "owncloud",
"logfile": "\/var\/www\/tools\/data\/logs\/nextcloud.log",
"log_rotate_size": 104857600,
"log_query": false,
"logdateformat": "d F, Y H:i:s",
"logtimezone": "Europe\/Paris",
"theme": "",
"trashbin_retention_obligation": "auto",
"htaccess.RewriteBase": "\/",
"updater.release.channel": "stable",
"integrity.check.disabled": true,
"preview_libreoffice_path": "\/usr\/bin\/libreoffice",
"preview_office_cl_parameters": " --headless --nologo --nofirststartwizard --invisible --norestore -convert-to pdf -outdir ",
"enabledPreviewProviders": [
"OC\Preview\Image",
"OC\Preview\MP3",
"OC\Preview\TXT",
"OC\Preview\MarkDown",
"OC\Preview\Epub",
"OC\Preview\PDF",
"OC\Preview\OpenDocument",
"OC\Preview\StarOffice",
"OC\Preview\MSOfficeDoc",
"OC\Preview\MSOffice2003",
"OC\Preview\MSOffice2007",
"OC\Preview\FB2"
],
"mail_smtpauthtype": "LOGIN",
"mail_sendmailmode": "smtp",
"app_install_overwrite": [
"files_downloadactivity",
"admin_notifications",
"defaultlinkopen",
"files_reader"
],
"data-fingerprint": "af50d5660c925c5032cadd1d17440cf4",
"updater.secret": "REMOVED SENSITIVE VALUE"
}
} - Nextcloud external user backend: none
Additional context
Add any other context about the problem here.
Updating
Doctrine\DBAL\Exception\UniqueConstraintViolationException: An exception occurred while executing 'INSERT INTO "oc_bookmarks_tree" ("id", "type", "parent_folder", "index") VALUES(?, ?, ?, ?)' with params [69, "bookmark", 11, 1]:
SQLSTATE[23505]: Unique violation: 7 ERREUR: la valeur d'une clé dupliquée rompt la contrainte unique « oc_bookmarks_tree_pkey »
DETAIL: La clé « (id, type, parent_folder)=(69, bookmark, 11) » existe déjà .
Update failed
Web server error log
Insert your webserver log here
**Nextcloud log (nextcloud/data/nextcloud.log)**
{"reqId":"xLtDCee3Nw1RaMcSpfMr","level":3,"time":"02 May, 2020 16:59:13","remoteAddr":"2a01:e0a:1ef:7591::900","user":"--","app":"core","method":"GET","url":"/core/ajax/update.php?requesttoken=xKC3c%2FZrSnOaMJ39ZAi2HEfZTNQ7QW%2Bh0ra9OYfhl4Y%3D%3ApejmBLggOx7uaOqnF3%2F1UXezNr4NABXFo9yIfPaI3bY%3D","message":{"Exception":"Doctrine\DBAL\Exception\UniqueConstraintViolationException","Message":"An exception occurred while executing 'INSERT INTO \"oc_bookmarks_tree\" (\"id\", \"type\", \"parent_folder\", \"index\") VALUES(?, ?, ?, ?)' with params [9, \"folder\", 8, 18]:\n\nSQLSTATE[23505]: Unique violation: 7 ERREUR: la valeur d'une cl\u00e9 dupliqu\u00e9e rompt la contrainte unique \u00ab oc_bookmarks_tree_pkey \u00bb\nDETAIL: La cl\u00e9 \u00ab (id, type, parent_folder)=(9, folder, 8) \u00bb existe d\u00e9j\u00e0.","Code":0,"Trace":[{"file":"/var/www/tools/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php","line":169,"function":"convertException","class":"Doctrine\DBAL\Driver\AbstractPostgreSQLDriver","type":"->","args":["An exception occurred while executing 'INSERT INTO \"oc_bookmarks_tree\" (\"id\", \"type\", \"parent_folder\", \"index\") VALUES(?, ?, ?, ?)' with params [9, \"folder\", 8, 18]:\n\nSQLSTATE[23505]: Unique violation: 7 ERREUR: la valeur d'une cl\u00e9 dupliqu\u00e9e rompt la contrainte unique \u00ab oc_bookmarks_tree_pkey \u00bb\nDETAIL: La cl\u00e9 \u00ab (id, type, parent_folder)=(9, folder, 8) \u00bb existe d\u00e9j\u00e0.",{"errorInfo":["23505",7,"ERREUR: la valeur d'une cl\u00e9 dupliqu\u00e9e rompt la contrainte unique \u00ab oc_bookmarks_tree_pkey \u00bb\nDETAIL: La cl\u00e9 \u00ab (id, type, parent_folder)=(9, folder, 8) \u00bb existe d\u00e9j\u00e0."],"__class__":"Doctrine\DBAL\Driver\PDOException"}]},{"file":"/var/www/tools/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php","line":145,"function":"wrapException","class":"Doctrine\DBAL\DBALException","type":"::","args":[{"__class__":"Doctrine\DBAL\Driver\PDOPgSql\Driver"},{"errorInfo":["23505",7,"ERREUR: la valeur d'une cl\u00e9 dupliqu\u00e9e rompt la contrainte unique \u00ab oc_bookmarks_tree_pkey \u00bb\nDETAIL: La cl\u00e9 \u00ab (id, type, parent_folder)=(9, folder, 8) \u00bb existe d\u00e9j\u00e0."],"__class__":"Doctrine\DBAL\Driver\PDOException"},"An exception occurred while executing 'INSERT INTO \"oc_bookmarks_tree\" (\"id\", \"type\", \"parent_folder\", \"index\") VALUES(?, ?, ?, ?)' with params [9, \"folder\", 8, 18]:\n\nSQLSTATE[23505]: Unique violation: 7 ERREUR: la valeur d'une cl\u00e9 dupliqu\u00e9e rompt la contrainte unique \u00ab oc_bookmarks_tree_pkey \u00bb\nDETAIL: La cl\u00e9 \u00ab (id, type, parent_folder)=(9, folder, 8) \u00bb existe d\u00e9j\u00e0."]},{"file":"/var/www/tools/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php","line":1063,"function":"driverExceptionDuringQuery","class":"Doctrine\DBAL\DBALException","type":"::","args":[{"__class__":"Doctrine\DBAL\Driver\PDOPgSql\Driver"},{"errorInfo":["23505",7,"ERREUR: la valeur d'une cl\u00e9 dupliqu\u00e9e rompt la contrainte unique \u00ab oc_bookmarks_tree_pkey \u00bb\nDETAIL: La cl\u00e9 \u00ab (id, type, parent_folder)=(9, folder, 8) \u00bb existe d\u00e9j\u00e0."],"__class__":"Doctrine\DBAL\Driver\PDOException"},"INSERT INTO \"oc_bookmarks_tree\" (\"id\", \"type\", \"parent_folder\", \"index\") VALUES(?, ?, ?, ?)",{"1":9,"2":"folder","3":8,"4":18}]},{"file":"/var/www/tools/lib/private/DB/Connection.php","line":220,"function":"executeUpdate","class":"Doctrine\DBAL\Connection","type":"->","args":["INSERT INTO \"oc_bookmarks_tree\" (\"id\", \"type\", \"parent_folder\", \"index\") VALUES(?, ?, ?, ?)",{"1":9,"2":"folder","3":8,"4":18},{"1":2,"2":2,"3":2,"4":2}]},{"file":"/var/www/tools/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php","line":203,"function":"executeUpdate","class":"OC\DB\Connection","type":"->","args":["INSERT INTO \"oc_bookmarks_tree\" (\"id\", \"type\", \"parent_folder\", \"index\") VALUES(?, ?, ?, ?)",{"1":9,"2":"folder","3":8,"4":18},{"1":2,"2":2,"3":2,"4":2}]},{"file":"/var/www/tools/lib/private/DB/QueryBuilder/QueryBuilder.php","line":215,"function":"execute","class":"Doctrine\DBAL\Query\QueryBuilder","type":"->","args":[]},{"file":"/var/www/tools/apps/bookmarks/lib/Migration/Version003000000Date20191123094721.php","line":209,"function":"execute","class":"OC\DB\QueryBuilder\QueryBuilder","type":"->","args":[]},{"file":"/var/www/tools/lib/private/DB/MigrationService.php","line":493,"function":"postSchemaChange","class":"OCA\Bookmarks\Migration\Version003000000Date20191123094721","type":"->","args":[{"__class__":"OC\Migration\SimpleOutput"},{"__class__":"Closure"},{"tablePrefix":"oc_"}]},{"file":"/var/www/tools/lib/private/DB/MigrationService.php","line":414,"function":"executeStep","class":"OC\DB\MigrationService","type":"->","args":["003000000Date20191123094721",false]},{"file":"/var/www/tools/lib/private/legacy/app.php","line":915,"function":"migrate","class":"OC\DB\MigrationService","type":"->","args":[]},{"file":"/var/www/tools/lib/private/Updater.php","line":376,"function":"updateApp","class":"OC_App","type":"::","args":["* sensitive parameters replaced "]},{"file":"/var/www/tools/lib/private/Updater.php","line":255,"function":"doAppUpgrade","class":"OC\Updater","type":"->","args":[]},{"file":"/var/www/tools/lib/private/Updater.php","line":131,"function":"doUpgrade","class":"OC\Updater","type":"->","args":["18.0.4.2","18.0.4.2"]},{"file":"/var/www/tools/core/ajax/update.php","line":213,"function":"upgrade","class":"OC\Updater","type":"->","args":[]}],"File":"/var/www/tools/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractPostgreSQLDriver.php","Line":51,"Previous":{"Exception":"Doctrine\DBAL\Driver\PDOException","Message":"SQLSTATE[23505]: Unique violation: 7 ERREUR: la valeur d'une cl\u00e9 dupliqu\u00e9e rompt la contrainte unique \u00ab oc_bookmarks_tree_pkey \u00bb\nDETAIL: La cl\u00e9 \u00ab (id, type, parent_folder)=(9, folder, 8) \u00bb existe d\u00e9j\u00e0.","Code":"23505","Trace":[{"file":"/var/www/tools/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php","line":1054,"function":"execute","class":"Doctrine\DBAL\Driver\PDOStatement","type":"->","args":[]},{"file":"/var/www/tools/lib/private/DB/Connection.php","line":220,"function":"executeUpdate","class":"Doctrine\DBAL\Connection","type":"->","args":["INSERT INTO \"oc_bookmarks_tree\" (\"id\", \"type\", \"parent_folder\", \"index\") VALUES(?, ?, ?, ?)",{"1":9,"2":"folder","3":8,"4":18},{"1":2,"2":2,"3":2,"4":2}]},{"file":"/var/www/tools/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php","line":203,"function":"executeUpdate","class":"OC\DB\Connection","type":"->","args":["INSERT INTO \"oc_bookmarks_tree\" (\"id\", \"type\", \"parent_folder\", \"index\") VALUES(?, ?, ?, ?)",{"1":9,"2":"folder","3":8,"4":18},{"1":2,"2":2,"3":2,"4":2}]},{"file":"/var/www/tools/lib/private/DB/QueryBuilder/QueryBuilder.php","line":215,"function":"execute","class":"Doctrine\DBAL\Query\QueryBuilder","type":"->","args":[]},{"file":"/var/www/tools/apps/bookmarks/lib/Migration/Version003000000Date20191123094721.php","line":209,"function":"execute","class":"OC\DB\QueryBuilder\QueryBuilder","type":"->","args":[]},{"file":"/var/www/tools/lib/private/DB/MigrationService.php","line":493,"function":"postSchemaChange","class":"OCA\Bookmarks\Migration\Version003000000Date20191123094721","type":"->","args":[{"__class__":"OC\Migration\SimpleOutput"},{"__class__":"Closure"},{"tablePrefix":"oc_"}]},{"file":"/var/www/tools/lib/private/DB/MigrationService.php","line":414,"function":"executeStep","class":"OC\DB\MigrationService","type":"->","args":["003000000Date20191123094721",false]},{"file":"/var/www/tools/lib/private/legacy/app.php","line":915,"function":"migrate","class":"OC\DB\MigrationService","type":"->","args":[]},{"file":"/var/www/tools/lib/private/Updater.php","line":376,"function":"updateApp","class":"OC_App","type":"::","args":[" sensitive parameters replaced "]},{"file":"/var/www/tools/lib/private/Updater.php","line":255,"function":"doAppUpgrade","class":"OC\Updater","type":"->","args":[]},{"file":"/var/www/tools/lib/private/Updater.php","line":131,"function":"doUpgrade","class":"OC\Updater","type":"->","args":["18.0.4.2","18.0.4.2"]},{"file":"/var/www/tools/core/ajax/update.php","line":213,"function":"upgrade","class":"OC\Updater","type":"->","args":[]}],"File":"/var/www/tools/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php","Line":119,"Previous":{"Exception":"PDOException","Message":"SQLSTATE[23505]: Unique violation: 7 ERREUR: la valeur d'une cl\u00e9 dupliqu\u00e9e rompt la contrainte unique \u00ab oc_bookmarks_tree_pkey \u00bb\nDETAIL: La cl\u00e9 \u00ab (id, type, parent_folder)=(9, folder, 8) \u00bb existe d\u00e9j\u00e0.","Code":"23505","Trace":[{"file":"/var/www/tools/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php","line":117,"function":"execute","class":"PDOStatement","type":"->","args":[null]},{"file":"/var/www/tools/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php","line":1054,"function":"execute","class":"Doctrine\DBAL\Driver\PDOStatement","type":"->","args":[]},{"file":"/var/www/tools/lib/private/DB/Connection.php","line":220,"function":"executeUpdate","class":"Doctrine\DBAL\Connection","type":"->","args":["INSERT INTO \"oc_bookmarks_tree\" (\"id\", \"type\", \"parent_folder\", \"index\") VALUES(?, ?, ?, ?)",{"1":9,"2":"folder","3":8,"4":18},{"1":2,"2":2,"3":2,"4":2}]},{"file":"/var/www/tools/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php","line":203,"function":"executeUpdate","class":"OC\DB\Connection","type":"->","args":["INSERT INTO \"oc_bookmarks_tree\" (\"id\", \"type\", \"parent_folder\", \"index\") VALUES(?, ?, ?, ?)",{"1":9,"2":"folder","3":8,"4":18},{"1":2,"2":2,"3":2,"4":2}]},{"file":"/var/www/tools/lib/private/DB/QueryBuilder/QueryBuilder.php","line":215,"function":"execute","class":"Doctrine\DBAL\Query\QueryBuilder","type":"->","args":[]},{"file":"/var/www/tools/apps/bookmarks/lib/Migration/Version003000000Date20191123094721.php","line":209,"function":"execute","class":"OC\DB\QueryBuilder\QueryBuilder","type":"->","args":[]},{"file":"/var/www/tools/lib/private/DB/MigrationService.php","line":493,"function":"postSchemaChange","class":"OCA\Bookmarks\Migration\Version003000000Date20191123094721","type":"->","args":[{"__class__":"OC\Migration\SimpleOutput"},{"__class__":"Closure"},{"tablePrefix":"oc_"}]},{"file":"/var/www/tools/lib/private/DB/MigrationService.php","line":414,"function":"executeStep","class":"OC\DB\MigrationService","type":"->","args":["003000000Date20191123094721",false]},{"file":"/var/www/tools/lib/private/legacy/app.php","line":915,"function":"migrate","class":"OC\DB\MigrationService","type":"->","args":[]},{"file":"/var/www/tools/lib/private/Updater.php","line":376,"function":"updateApp","class":"OC_App","type":"::","args":[" sensitive parameters replaced *"]},{"file":"/var/www/tools/lib/private/Updater.php","line":255,"function":"doAppUpgrade","class":"OC\Updater","type":"->","args":[]},{"file":"/var/www/tools/lib/private/Updater.php","line":131,"function":"doUpgrade","class":"OC\Updater","type":"->","args":["18.0.4.2","18.0.4.2"]},{"file":"/var/www/tools/core/ajax/update.php","line":213,"function":"upgrade","class":"OC\Updater","type":"->","args":[]}],"File":"/var/www/tools/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php","Line":117}},"CustomMessage":"--"},"userAgent":"Mozilla/5.0 (X11; Linux x86_64; rv:75.0) Gecko/20100101 Firefox/75.0","version":"18.0.4.2"}
```
upgrade done manually via command line :
sudo -u www-data php occ app:update bookmarks
thank you for your help
pra
pra75011
All 28 comments
Did you get errors before when upgrading to other 3.x versions? It looks like this is a follow up error, as there were no new migrations introduced in v3.0.5.
marcelklehr
on 2 May 2020
Got the same issue updating to 18.0.3 via web-frontend:
Doctrine\DBAL\Exception\UniqueConstraintViolationException: An exception occurred while executing 'INSERT INTO oc_bookmarks_tree (id, type, parent_folder, index) VALUES(?, ?, ?, ?)' with params ["3", "folder", "23", "0"]: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '3-folder-23' for key 'PRIMARY'
Checked oc_bookmarks_tree database via DBeaver but cannot find a duplicate entry
Stiefmeister
on 2 May 2020
So, as the update is currently failing because the db is in limbo state for you, let's get it back to its initial state:
Assuming
- you have a backup of your db
- the table
oc_bookmarks_folders_bookmarksstill exists
and assuming the following query returns the exact same output
select * from oc_migrations where app = 'bookmarks'`
+-----------+-----------------------------+
| app | version |
+-----------+-----------------------------+
| bookmarks | 000014000Date20181002094721 |
| bookmarks | 000014000Date20181029094721 |
| bookmarks | 000016005Date20190402094721 |
| bookmarks | 001000007Date20190721094721 |
+-----------+-----------------------------+
try running the following sql queries:
# This will delete all root folders
# 1.a MySQL version
DELETE f FROM oc_bookmarks_folders f JOIN oc_bookmarks_root_folders r ON r.folder_id = f.id;
# 1.b Postgresql version
DELETE FROM oc_bookmarks_folders f USING oc_bookmarks_root_folders r WHERE r.folder_id = f.id;
# 2. This will delete the root folder to user id mapping table
DROP TABLE oc_bookmarks_root_folders;
# 3. This will delete the tree constructed during the previous update
DROP TABLE oc_bookmarks_tree;
If you then run the update all those tables above should be repopulated.
UPDATE: If anything of the above does not work, post the result here and don't attempt it again.
marcelklehr
on 3 May 2020
Thanks a lot for the quick reply.
Executed all queries as stated above and restarted the update.
Sadly now I am receiving another duplicate error in oc_bookmarks_tree:
Doctrine\DBAL\Exception\UniqueConstraintViolationException: An exception occurred while executing 'INSERT INTO oc_bookmarks_tree (id, type, parent_folder, index) VALUES(?, ?, ?, ?)' with params ["208", "bookmark", "30", "3"]: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '208-bookmark-30' for key 'PRIMARY'
UPDATE: After running update via web-frontend again, a different error appeared:
Doctrine\DBAL\Exception\NotNullConstraintViolationException: An exception occurred while executing 'INSERT INTO oc_bookmarks_tree (id, type, parent_folder, index) VALUES(?, ?, ?, ?)' with params ["77", "folder", null, "0"]: SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'parent_folder' cannot be null
Stiefmeister
on 3 May 2020
hello thank you for your reply
@marcelklehr
Did you get errors before when upgrading to other 3.x versions?
=> no
you have a backup of your db => yes
the table oc_bookmarks_folders_bookmarks still exists => yes
i needed to restore my db an reply the upgrade from 18.0.3 to 18.0.4 (nextcloud core)
cloud8=# select * from oc_migrations where app = 'bookmarks'
cloud8-# ;
app | version
-----------+-----------------------------
bookmarks | 000014000Date20181002094721
bookmarks | 000014000Date20181029094721
bookmarks | 000016005Date20190402094721
bookmarks | 001000007Date20190721094721
(4 lignes)
thank you ,
not sure i will can restore data in the table if i run your query
cloud8=# DELETE f FROM oc_bookmarks_folders f LEFT JOIN oc_bookmarks_root_folders r ON r.folder_id = f.id;
ERREUR: erreur de syntaxe sur ou près de « f »
LIGNE 1 : DELETE f FROM oc_bookmarks_folders f LEFT JOIN oc_bookmarks_...
cloud8=# DROP TABLE oc_bookmarks_root_folders;
DROP TABLE
cloud8=# DROP TABLE oc_bookmarks_tree;
DROP TABLE
rerunning the upgrade give :
Une erreur est survenue durant la requête. Impossible de traiter la demande.
An exception occurred while executing 'INSERT INTO "oc_bookmarks_tree" ("id", "type", "parent_folder", "index") VALUES(?, ?, ?, ?)' with params [13, "folder", null, 0]: SQLSTATE[23502]: Not null violation: 7 ERREUR: une valeur NULL viole la contrainte NOT NULL de la colonne « parent_folder » DETAIL: La ligne en échec contient (folder, 13, null, 0)
pra75011
on 3 May 2020
cloud8=# \d oc_bookmarks_folders
Table « public.oc_bookmarks_folders »
Colonne | Type | Collationnement | NULL-able | Par défaut
---------------+-------------------------+-----------------+-----------+--------------------------------------------------
id | bigint | | not null | nextval('oc_bookmarks_folders_id_seq'::regclass)
parent_folder | bigint | | |
title | character varying(4096) | | not null | ''::character varying
user_id | character varying(64) | | not null | ''::character varying
index | bigint | | not null | 0
Index :
"oc_bookmarks_folders_pkey" PRIMARY KEY, btree (id)
"idx_60f60c03a76ed395" btree (user_id)
cloud8=# \d oc_bookmarks_root_folders
Table « public.oc_bookmarks_root_folders »
Colonne | Type | Collationnement | NULL-able | Par défaut
-----------+-----------------------+-----------------+-----------+------------
user_id | character varying(64) | | not null |
folder_id | bigint | | not null |
Index :
"oc_bookmarks_root_folders_pkey" PRIMARY KEY, btree (user_id, folder_id)
"bookmarks_root_folder" btree (folder_id)
"bookmarks_user_root" btree (user_id)
cloud8=# \d oc_bookmarks_tree
Table « public.oc_bookmarks_tree »
Colonne | Type | Collationnement | NULL-able | Par défaut
---------------+-----------------------+-----------------+-----------+------------
type | character varying(20) | | not null |
id | bigint | | not null |
parent_folder | bigint | | not null |
index | smallint | | not null |
Index :
"oc_bookmarks_tree_pkey" PRIMARY KEY, btree (id, type, parent_folder)
"bookmarks_tree_parent" btree (parent_folder)
"bookmarks_tree_parent_i" btree (parent_folder, index)
cloud8=# select * from oc_bookmarks_folders
cloud8-# ;
id | parent_folder | title | user_id | index
----+---------------+------------+---------+-------
2 | -1 | softs | pra | 3
5 | -1 | Hardware | pra | 7
6 | -1 | Securité | pra | 9
7 | -1 | boulot | pra | 13
1 | -1 | Linux | pra | 2
8 | -1 | cours | pra | 17
9 | 8 | sql | pra | 18
4 | 2 | messagerie | pra | 6
3 | 2 | video | pra | 5
10 | 8 | scripts | pra | 3
11 | 10 | bash | pra | 0
12 | 10 | python | pra | 1
13 | | | pra | 0
14 | | | pra | 0
(14 lignes)
cloud8=# select * from oc_bookmarks_root_folders;
user_id | folder_id
---------+-----------
pra | 13
pra | 14
(2 lignes)
select * from oc_bookmarks_tree;
type | id | parent_folder | index
----------+----+---------------+-------
folder | 2 | 13 | 3
folder | 5 | 13 | 7
folder | 6 | 13 | 9
folder | 7 | 13 | 13
folder | 1 | 13 | 2
folder | 8 | 13 | 17
folder | 9 | 8 | 18
folder | 4 | 2 | 6
folder | 3 | 2 | 5
folder | 10 | 8 | 3
folder | 11 | 10 | 0
folder | 12 | 10 | 1
bookmark | 1 | 13 | 3
bookmark | 1 | 1 | 0
bookmark | 1 | 2 | 0
bookmark | 4 | 13 | 4
bookmark | 4 | 2 | 3
bookmark | 4 | 3 | 0
bookmark | 5 | 13 | 4
bookmark | 5 | 2 | 3
bookmark | 5 | 3 | 1
bookmark | 6 | 13 | 5
bookmark | 6 | 4 | 0
bookmark | 7 | 13 | 6
bookmark | 7 | 1 | 1
bookmark | 8 | 13 | 8
bookmark | 8 | 5 | 0
bookmark | 12 | 13 | 14
bookmark | 12 | 7 | 0
bookmark | 11 | 13 | 14
bookmark | 11 | 6 | 2
bookmark | 10 | 13 | 14
bookmark | 10 | 6 | 2
bookmark | 9 | 13 | 14
bookmark | 9 | 6 | 2
bookmark | 35 | 13 | 38
bookmark | 24 | 13 | 38
bookmark | 13 | 13 | 17
bookmark | 13 | 7 | 1
bookmark | 13 | 1 | 2
bookmark | 13 | 2 | 5
bookmark | 15 | 13 | 18
bookmark | 15 | 1 | 3
bookmark | 15 | 7 | 2
bookmark | 17 | 5 | 1
bookmark | 18 | 13 | 20
bookmark | 18 | 1 | 4
bookmark | 19 | 13 | 21
bookmark | 23 | 13 | 38
bookmark | 37 | 13 | 39
bookmark | 22 | 13 | 26
bookmark | 21 | 13 | 26
bookmark | 38 | 13 | 40
bookmark | 39 | 13 | 41
bookmark | 25 | 13 | 27
bookmark | 40 | 13 | 42
bookmark | 26 | 13 | 28
bookmark | 41 | 13 | 43
bookmark | 27 | 13 | 29
bookmark | 42 | 13 | 44
bookmark | 43 | 13 | 45
bookmark | 29 | 13 | 31
bookmark | 44 | 13 | 46
bookmark | 30 | 13 | 32
bookmark | 31 | 13 | 33
bookmark | 28 | 13 | 33
bookmark | 32 | 13 | 34
bookmark | 33 | 13 | 35
bookmark | 34 | 13 | 36
bookmark | 36 | 13 | 38
bookmark | 45 | 13 | 47
bookmark | 46 | 13 | 48
bookmark | 47 | 13 | 49
bookmark | 48 | 13 | 50
bookmark | 20 | 7 | 4
bookmark | 49 | 13 | 50
bookmark | 50 | 13 | 51
bookmark | 51 | 13 | 52
bookmark | 52 | 13 | 53
bookmark | 53 | 13 | 54
bookmark | 16 | 13 | 54
bookmark | 16 | 7 | 4
bookmark | 54 | 13 | 55
bookmark | 55 | 13 | 56
bookmark | 57 | 11 | 0
bookmark | 58 | 13 | 57
bookmark | 59 | 13 | 58
bookmark | 60 | 13 | 59
bookmark | 61 | 13 | 60
bookmark | 62 | 13 | 61
bookmark | 63 | 13 | 62
bookmark | 65 | 13 | 64
bookmark | 66 | 13 | 65
bookmark | 67 | 13 | 65
bookmark | 68 | 13 | 66
bookmark | 69 | 11 | 1
folder | 2 | 14 | 3
folder | 5 | 14 | 7
folder | 6 | 14 | 9
folder | 7 | 14 | 13
folder | 1 | 14 | 2
folder | 8 | 14 | 17
(102 lignes)
cloud8=# select id,user_id from oc_bookmarks order by id;
id | user_id
----+---------
1 | pra
4 | pra
5 | pra
6 | pra
7 | pra
8 | pra
9 | pra
10 | pra
11 | pra
12 | pra
13 | pra
14 | pra
15 | pra
16 | pra
17 | pra
18 | pra
19 | pra
20 | pra
21 | pra
22 | pra
23 | pra
24 | pra
25 | pra
26 | pra
27 | pra
28 | pra
29 | pra
30 | pra
31 | pra
32 | pra
33 | pra
34 | pra
35 | pra
36 | pra
37 | pra
38 | pra
39 | pra
40 | pra
41 | pra
42 | pra
43 | pra
44 | pra
45 | pra
46 | pra
47 | pra
48 | pra
49 | pra
50 | pra
51 | pra
52 | pra
53 | pra
54 | pra
55 | pra
57 | pra
58 | pra
59 | pra
60 | pra
61 | pra
62 | pra
63 | pra
65 | pra
66 | pra
67 | pra
68 | pra
69 | pra
(65 lignes)
i hope it will help you to understand what is wrong
thank you for your help
regards /stay safe
pra
pra75011
on 3 May 2020
Did you get errors before when upgrading to other 3.x versions? => no
I see. Then I will try to fix the upgrade process to account for these inconsistencies in the database, which were apparently introduced by bugs in previous versions.
marcelklehr
on 3 May 2020
@marcelklehr
how i can help ?
remember having an error message once when registering a new favors or when creating a sub-directory
trying if i see it in log (probably purged)
pra75011
on 3 May 2020
Not sure if it helps, but was the first upgrade for me. Just recently (3-4 weeks ago!?) installed nextcloud + bookmarks app
Stiefmeister
on 3 May 2020
Alright, I've released a new patch based on your feedback here.
So, as the db might still be in a kind of limbo state for you, let's fix that partially:
Assuming
- you have a backup of your db
- the table
oc_bookmarks_folders_bookmarksstill exists
and assuming the following query returns the exact same output
select * from oc_migrations where app = 'bookmarks'`
+-----------+-----------------------------+
| app | version |
+-----------+-----------------------------+
| bookmarks | 000014000Date20181002094721 |
| bookmarks | 000014000Date20181029094721 |
| bookmarks | 000016005Date20190402094721 |
| bookmarks | 001000007Date20190721094721 |
+-----------+-----------------------------+
try running the following sql queries:
# This will delete all root folders (see the version that applies to your DBMS)
# 1.a MySQL version:
DELETE f FROM oc_bookmarks_folders f JOIN oc_bookmarks_root_folders r ON r.folder_id = f.id;
# 1.b Postgresql version:
DELETE FROM oc_bookmarks_folders f USING oc_bookmarks_root_folders r WHERE r.folder_id = f.id;
# 2. This will delete the root folder to user id mapping table
DROP TABLE oc_bookmarks_root_folders;
# 3. This will delete the tree constructed during the previous update
DROP TABLE oc_bookmarks_tree;
If you then run the update those tables above should be repopulated.
UPDATE: If anything of the above does not work, stop following the instructions, post the result here and don't attempt it again.
marcelklehr
on 3 May 2020
Thanks!
After executing those queries still had the same error so I restored a backup of nextcloud and mariadb, updated nextcloud from 18.0.3 to 18.0.4 and during this process bookmarks was successfully updated to 3.0.6
Stiefmeister
on 3 May 2020
Hello
thank you for your help :
cloud8=# DELETE FROM oc_bookmarks_folders f USING oc_bookmarks_root_folders r WHERE r.folder_id = f.id;
DELETE 1
cloud8=# DROP TABLE oc_bookmarks_root_folders;
DROP TABLE
cloud8=# DROP TABLE oc_bookmarks_tree;
DROP TABLE
upgrading from 3.0.5 to 3.0.6 all good no issue
thank you for your hard work
regards
take care and stay safe
pra75011
on 3 May 2020
@marcelklehr
hoping that this would fix my db situation, I tried this solution as it makes sense (re-running last migrations). It looks like it does try to recreate the tables and populate content but got this at some point:
'INSERT INTO `oc_bookmarks_tree` (`id`, `type`, `parent_folder`, `index`) VALUES(?, ?, ?, ?)' with params ["102144", "bookmark", "18922", "97141"]:
SQLSTATE[22003]: Numeric value out of range: 1264 Out of range value for column 'index' at row 1
muppeth
on 4 May 2020
@muppeth :eyes: Somebody has 97141 bookmarks in their account?
Can you check if that happens often?
SELECT * from oc_bookmarks_folders_bookmarks WHERE index > 1000;
@marcelklehr
This is my top 10 bookmark horders. So dont have that crazy horders although still ton of people just littering in my opinion (hence the early request to put some limit on amounts).
MariaDB [nextcloud]> select count(*) as count from oc_bookmarks group by user_id order by count DESC limit 10;
+-------+
| count |
+-------+
| 8038 |
| 4849 |
| 3953 |
| 3882 |
| 3566 |
| 3432 |
| 3167 |
| 2698 |
| 2559 |
| 2297 |
+-------+
10 rows in set (0.072 sec)
However running your query gives crazy results. I had to increace to > 90000 cause above 1000 I got 7376 results. Weird thing is that mostly the folder_id is -1. For over 90000 index I have atm: 313 items.
If you look at the folder_id and how many belong to -1 folder in comparison to the rest. it looks like its half of total bookmarks:
MariaDB [nextcloud]> SELECT count(*) from oc_bookmarks_folders_bookmarks WHERE folder_id = "-1";
+----------+
| count(*) |
+----------+
| 84277 |
+----------+
1 row in set (0.098 sec)
MariaDB [nextcloud]> SELECT count(*) from oc_bookmarks_folders_bookmarks WHERE folder_id != "-1";
+----------+
| count(*) |
+----------+
| 71872 |
+----------+
1 row in set (0.094 sec)
Below is result of query showing bookmark folders with index above 90000:
https://bin.disroot.org/?140d8c94108d6ef8#6bN1W4BytCZ4mQZSd8wLv6GAC7m9v9DKvYU1qA4y5EQD
muppeth
on 5 May 2020
Ok, so it's unlikely that there are actually that many bookmarks in the system. Can you verify that all these bookmarks actually exist? i.e. by joining with oc_bookmarks? And if they don't you might be able to remove those rows from oc_bookmarks_folders_bookmarks and run the update again, after dropping oc_bookmarks_tree.
marcelklehr
on 5 May 2020
above 1000 I got 7376 results
Still this probably means that the current column type is too small. I'll change it to a bigint. That will probably relieve you of the need to have to manually remove those entries because there's a clean up step for superfluous tree entries now, anyway.
Update: v3.0.9 is out which changes the index column to bigint and should maybe finally make your update go through.
marcelklehr
on 5 May 2020
@marcelklehr brilliant! Do I still need to drop tables and delete stuff as posted by you above?
# This will delete all root folders (see the version that applies to your DBMS)
# 1.a MySQL version:
DELETE f FROM oc_bookmarks_folders f JOIN oc_bookmarks_root_folders r ON r.folder_id = f.id;
# 1.b Postgresql version:
DELETE FROM oc_bookmarks_folders f USING oc_bookmarks_root_folders r WHERE r.folder_id = f.id;
# 2. This will delete the root folder to user id mapping table
DROP TABLE oc_bookmarks_root_folders;
# 3. This will delete the tree constructed during the previous update
DROP TABLE oc_bookmarks_tree;
@muppeth That shouldn't be necessary, no.
marcelklehr
on 5 May 2020
@marcelklehr getting duplicate entry when trying to update like:
An exception occurred while executing 'INSERT INTO `oc_bookmarks_tree` (`id`, `type`, `parent_folder`, `index`) VALUES(?, ?, ?, ?)' with params ["99383", "bookmark", "18952", "0"]:
SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '99383-bookmark-18952' for key 'PRIMARY'
I tried directly to upgrade, also first drop tables and last two migrations (before 3.0.10) but getting the same issue. It's with different ones because if I remove the bookmark in question and try to enable the app, I get more errors for next ones.
muppeth
on 6 May 2020
@muppeth With the latest release? :thinking:
marcelklehr
on 6 May 2020
@marcelklehr yup.
muppeth
on 6 May 2020
@marcelklehr I did manage to update to 3.0.10 but does not look like anything has changed. Still root folder displays only new items and the counter does only append event when you delete bookmark, and import/export still gives error.
EDIT:
On export I get error parentFolder is not a valid attribute
I will do some further investigation around midday and provide with some findings/information in regards to the situation to give better picture.
muppeth
on 6 May 2020
parentFolder is not a valid attribute
This probably only indicates that the migrations have not been run correctly, as you reported in https://github.com/nextcloud/bookmarks/issues/1008#issuecomment-624366156.
and last two migrations (before 3.0.10)
Which migrations did you drop exactly?
Can you confirm that following these instructions doesn't work with the latest release, not even when starting from a backup of before v3.0.0? https://github.com/nextcloud/bookmarks/issues/1008#issuecomment-623155740
marcelklehr
on 6 May 2020
Hi
Today I have the same issue, bookmark app is 3.0.10 and it stops working (not syncing anymore) on all browsers chrome, edge chromium, firefox.
I did update this app and with no issues, after update app stops working I removed it from nextcloud and installs again, issue is still.
E018: Couldn't authenticate with the server
I can login with that account with no problem :( certificate is ok.
Can somebody give me an hint how to deal with this?
Regards
uriel198
on 6 May 2020
@uriel198 You're looking for this issue: https://github.com/nextcloud/bookmarks/issues/994
marcelklehr
on 6 May 2020
Thanks to @muppeth I was able to fix another migration issue in v3.0.12.
marcelklehr
on 8 May 2020
Bottom line I had the following method:
- On a test installation (Nextcloud 18.0.4) I imported the bookmarks in the 2.x incarnation
- on this test installation I was able to upgrade to bookmarks 3.0.
- I exported (mysqldump) the tables in SQL
- On the production site:
- removed ALL bookmarks tables
- removed ALL upgrade lines from oc_migrations
- removed the bookmarks app 2.x
- installed the bookmakrs app 3.0.12
- imported the tables exported in th 3rd bullet
linuxpete
on 10 May 2020
Related issues
NWiogrhkt
·
4Comments
kc853
·
3Comments
GambaJo
·
5Comments
marcelklehr
·
6Comments
saywebsolutions
·
6Comments
Most helpful comment
Alright, I've released a new patch based on your feedback here.
So, as the db might still be in a kind of limbo state for you, let's fix that partially:
Assuming
oc_bookmarks_folders_bookmarksstill existsand assuming the following query returns the exact same output
try running the following sql queries:
If you then run the update those tables above should be repopulated.
UPDATE: If anything of the above does not work, stop following the instructions, post the result here and don't attempt it again.