Bookmarks: Upgrade to 3.0.0 fails

[vfauth]

Describe the bug
When updating to 3.0.0, the upgrade fail with the following error:

Doctrine\DBAL\Exception\NotNullConstraintViolationException: An exception occurred while executing 'INSERT INTO "oc_bookmarks_tree" ("id", "type", "parent_folder", "index") VALUES(?, ?, ?, ?)' with params [27, "folder", null, 0]: SQLSTATE[23502]: Not null violation: 7 ERROR: null value in column "parent_folder" violates not-null constraint DETAIL: Failing row contains (folder, 27, null, 0).

To Reproduce
Update the app to 3.0.0 with the Nextcloud app interface or occ upgrade

Server:

• OS: Alpine (official nextcloud fpm-alpine Docker image)
• Database PostgreSQL 12.2
• PHP version: 7.3
• Nextcloud version: 18.0.4
• Bookmarks app version: 3.0.0
• Activated Nextcloud Apps:

Enabled:
  - activity: 2.11.0
  - announcementcenter: 3.7.0
  - apporder: 0.9.0
  - bookmarks: 2.3.4
  - breezedark: 18.0.13
  - bruteforcesettings: 1.6.0
  - calendar: 2.0.3
  - cloud_federation_api: 1.1.0
  - comments: 1.8.0
  - contacts: 3.3.0
  - cookbook: 0.6.4
  - dav: 1.14.0
  - documentserver_community: 0.1.5
  - event_update_notification: 1.0.1
  - external: 3.5.0
  - extract: 1.2.4
  - federatedfilesharing: 1.8.0
  - files: 1.13.1
  - files_downloadactivity: 1.7.0
  - files_external: 1.9.0
  - files_pdfviewer: 1.7.0
  - files_rightclick: 0.15.2
  - files_sharing: 1.10.1
  - files_trashbin: 1.8.0
  - files_versions: 1.11.0
  - files_videoplayer: 1.7.0
  - firstrunwizard: 2.7.0
  - forms: 1.1.1
  - logreader: 2.3.0
  - lookup_server_connector: 1.6.0
  - maps: 0.1.6
  - metadata: 0.11.1
  - music: 0.14.0
  - 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
  - polls: 1.3.0
  - previewgenerator: 2.3.0
  - provisioning_api: 1.8.0
  - quota_warning: 1.7.0
  - serverinfo: 1.8.0
  - settings: 1.0.0
  - sharebymail: 1.8.0
  - spreed: 8.0.8
  - survey_client: 1.6.0
  - suspicious_login: 3.1.0
  - systemtags: 1.8.0
  - tasks: 0.12.2
  - text: 2.0.0
  - theming: 1.9.0
  - twofactor_backupcodes: 1.7.0
  - twofactor_totp: 4.1.3
  - unsplash: 1.1.5
  - updatenotification: 1.8.0
  - viewer: 1.2.0
  - workflowengine: 2.0.0
Disabled:
  - accessibility
  - admin_audit
  - encryption
  - federation
  - onlyoffice
  - privacy
  - recommendations
  - support
  - user_ldap

• Nextcloud configuration:

{
    "system": {
        "memcache.local": "\\OC\\Memcache\\APCu",
        "memcache.distributed": "\\OC\\Memcache\\Redis",
        "memcache.locking": "\\OC\\Memcache\\Redis",
        "redis": {
            "host": "***REMOVED SENSITIVE VALUE***",
            "port": 6379
        },
        "apps_paths": [
            {
                "path": "\/var\/www\/html\/apps",
                "url": "\/apps",
                "writable": false
            },
            {
                "path": "\/var\/www\/html\/custom_apps",
                "url": "\/custom_apps",
                "writable": true
            }
        ],
        "instanceid": "***REMOVED SENSITIVE VALUE***",
        "passwordsalt": "***REMOVED SENSITIVE VALUE***",
        "secret": "***REMOVED SENSITIVE VALUE***",
        "trusted_domains": [
            "cloud.fauth.eu",
            "nextcloud"
        ],
        "datadirectory": "***REMOVED SENSITIVE VALUE***",
        "dbtype": "pgsql",
        "version": "18.0.3.0",
        "overwrite.cli.url": "https:\/\/cloud.fauth.eu",
        "dbname": "***REMOVED SENSITIVE VALUE***",
        "dbhost": "***REMOVED SENSITIVE VALUE***",
        "dbport": "",
        "dbtableprefix": "oc_",
        "dbuser": "***REMOVED SENSITIVE VALUE***",
        "dbpassword": "***REMOVED SENSITIVE VALUE***",
        "installed": true,
        "mail_from_address": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpmode": "smtp",
        "mail_sendmailmode": "smtp",
        "mail_domain": "***REMOVED SENSITIVE VALUE***",
        "theme": "",
        "maintenance": true,
        "mail_smtphost": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpport": "25",
        "ldapIgnoreNamingRules": false,
        "ldapProviderFactory": "OCA\\User_LDAP\\LDAPProviderFactory",
        "loglevel": 2,
        "preview_max_x": "2048",
        "preview_max_y": "2048",
        "jpeg_quality": "60"
    }
}

• Nextcloud external user backend: none

[marcelklehr]

Hello @vfauth
I'm sorry for the trouble. This is a weird error as it indicates that your database was in an unexpected state before the update.

Could you run the following SQL query and paste the result (anonymized)?

SELECT * from oc_bookmarks_folders WHERE parent_folder IS NULL;

[vfauth]

Thank you for your quick answer!

Here's the command outpout, which indeed does not seems good:

 id | parent_folder | title | user_id | index 
----+---------------+-------+---------+-------
 27 |               |       | vfauth    |     0
 28 |               |       | vfauth    |     0
 29 |               |       | vfauth    |     0
 30 |               |       | vfauth    |     0
 31 |               |       | vfauth    |     0
 32 |               |       | vfauth    |     0

If I go to the bookmarks app in the browser, there are only the "good" folders (the rest of the table is ok).

I only use the bookmarks app as a sync backend for the Floccus extension with the Vivaldi browser.

EDIT: I will drop these rows, but the DB should indeed never have been in that state.

[ressit]

Upgrade to 3.0.0 failed, too on 17.0.5, sqlite, using Floccus to sync bookmarks

Doctrine\DBAL\Exception\UniqueConstraintViolationException: An exception occurred while executing 'INSERT INTO "oc_bookmarks_tree" ("id", "type", "parent_folder", "index") VALUES(?, ?, ?, ?)' with params ["1", "folder", "2", "13"]: SQLSTATE[23000]: Integrity constraint violation: 19 UNIQUE constraint failed: oc_bookmarks_tree.id, oc_bookmarks_tree.type, oc_bookmarks_tree.parent_folder

[marcelklehr]

@ressit Is it possible you ran the update twice, perhaps because it errored the first time? Migrations are not designed to run twice and will fail if you do so. If a migration fails, I recommend resetting the db state to a backup, fixing whatever caused the error and then running the update again.

[ressit]

Yes, but my intention was to update to 17.0.6 and removed the bookmarks app.
After update to 17.0.6 it is still not possible to install the bookmarks app again without
running into an exception. Possibly because the oc_bookmarks_folders_bookmarks table is not empty?

Could you advice which tables to delete and files or directories to remove to cleanly install the app again, after removal, if this is possible?

[marcelklehr]

If you don't care about data loss do this:

drop table oc_bookmarks;
drop table oc_bookmarks_tags;
drop table oc_bookmarks_folders;
drop table oc_bookmarks_root_folders;
drop table oc_bookmarks_shared_folders;
drop table oc_bookmarks_shares;
drop table oc_bookmarks_tree;
drop table oc_bookmarks_folders_public;
delete from oc_migrations where app = 'bookmarks';

Some tables may not exist, yet, but that doesn't matter.

[ressit]

Intention is to sync the bookmarks in again from the browser using Floccus.

First try failed, installed was bookmarks 3.0.1, offered an update to 3.0.4, but anyway
the error message is:
The column 'index' on table 'oc_bookmarks_folders_bookmarks' already exists.

Shouldn't this table be deleted as well?

[marcelklehr]

Ah, I'm sorry. oc_bookmarks_folders_bookmarks needs to be deleted as well. It's no longer in use in v3, that's why I forgot to include it.

[ressit]

OK, bookmarks 3.0.4 is installed now, Thanks for advice.
Possibly you find an explanation why the unused table hinders installation.

[marcelklehr]

The reason is that the list of migrations must be in sync with the current table schema. If that's not the case, migrations are run that assume a different schema and will fail.

[ressit]

Would it be possible to find and cleanup tables with the used namespace prefix upon removal of the app?

[marcelklehr]

I tried that, but it appears that nextcloud runs the uninstall hook also when you merely disable the app.

[neilgrover]

Mind posting a summary of what needs to be done to recover from this failed upgrade and NOT loose data? I have the data backed up if it comes to that.

[marcelklehr]

@neilgrover Different people have different problems. I don't know yours, yet. You can open a new issue and describe problem, ie. the error you're getting.

[neilgrover]

Sure will do. This might be a message show on subsequent upgrade attempts. I don't have the original error (if it differs). For posterity:

Doctrine\DBAL\Exception\UniqueConstraintViolationException: An exception occurred while executing 'INSERT INTO oc_bookmarks_tree (id, type, parent_folder, index) VALUES(?, ?, ?, ?)' with params ["11", "folder", "1", "0"]: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '11-folder-1' for key 'PRIMARY'
Detailed logs

Preparing update

Set log level to debug

Turned on maintenance mode

Repair step: Repair MySQL collation

Repair info: All tables already have the correct collation -> 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

Doctrine\DBAL\Exception\UniqueConstraintViolationException: An exception occurred while executing 'INSERT INTO oc_bookmarks_tree (id, type, parent_folder, index) VALUES(?, ?, ?, ?)' with params ["11", "folder", "1", "0"]: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '11-folder-1' for key 'PRIMARY'

The update was unsuccessful. Please report this issue to the Nextcloud community.

[ressit]

I'm not aware of a backup and restore button for the administrator for the bookmarks app.

For me the following worked:

ALL USERS need to create a BACKUP of their stored bookmarks.
I.e.

1. ALL USERS SYNC their nextcloud bookmarks to their browsers with Floccus
   Be very careful when you install Floccus the first time.
   Check Options of Floccus "new synchronisation"

press button sync now.

1. ALL USERS create a backup of their browser bookmarks !
   Manage Bookmarks-> Save and Restore

2. ALL USERS DEACTIVATE Floccus sync to all their browsers.

3. Remove the bookmarks app from nextcloud server

pushd /var/www/
sudo -u www-data php occ app:list
sudo -u www-data php occ app:remove bookmarks

1. repair database, open database
   pushd /var/www/

sudo -u www-data sqlite3

drop table oc_bookmarks;
drop table oc_bookmarks_tags;
drop table oc_bookmarks_folders;
drop table oc_bookmarks_root_folders;
drop table oc_bookmarks_shared_folders;
drop table oc_bookmarks_shares;
drop table oc_bookmarks_tree;
drop table oc_bookmarks_folders_public;
drop table oc_bookmarks_folders_bookmarks;
delete from oc_migrations where app = 'bookmarks';
.quit

1. install the bookmarks app again
   sudo -u www-data php occ app:install bookmarks

2. check that bookmarks app is available in nextcloud

3. ALL USERS check Options of Browser extension Floccus is at least set to "merge".
   Server must not succeed. Anyway you still have the browser backup file.

4. ALL USERS activate Floccus and press "sync now"

5. ALL USERS check bookmarks are available in nextcloud again

[marcelklehr]

I would not advise steps as radical as @ressit for now.

@neilgrover Generally, running the same update twice is unlikely to succeed if something during the update failed half way through. If you have a backup of the db from before v3, load that and run the upgrade to the latest version v3.0.4 again. Hopefully that helps.

[neilgrover]

I did exactly what @ressit did and it got me back and running again. It was the only thing I knew to try to get my nextcloud instance back and running. I know how to take it out of maintenance mode but even then I could not get past a screen that forced me to retry the bookmarks upgrade (only to fail again). Thanks guys and thanks for the solution. It might be aggressive but I'm back and running again.