Update to 20.0.6: Invalid text representation: 7 ERROR: invalid input syntax for type boolean: "bookmark"

[basvandenbrink]

Describe the bug
Updating from 20.0.5 to 20.0.6 failed. I had to manually disable the bookmarks app to make Nextcloud work again. I got the following error.

An exception occurred while executing 'SELECT "b"."id" FROM "bookmarks" "b" LEFT JOIN "bookmarks_tree" "t" ON b.id = t.id AND ? WHERE "t"."id" IS NULL' with params ["bookmark"]: SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for type boolean: "bookmark" 

To Reproduce
Steps to reproduce the behavior:

1. Update Nextcloud from 20.0.5 to 20.0.6 by pulling new Docker image and recreating the container.

Expected behavior
Updating correctly with Bookmarks enabled.

Desktop (please complete the following information):

• OS: Windows 10 20H2
• Browser: Firefox
• Version: 84.0.2

Server (please complete the following information):

• OS: Fedora CoreOS 33
• HTTP server: latest Nginx
• Database: PostgreSQL v13
• PHP version: v7? Using upstream Nextcloud PHP image
• Nextcloud version: 20.0.6
• Bookmarks app version: 4.0.7
• Activated Nextcloud Apps:

Enabled:
 - accessibility: 1.6.0
 - activity: 2.13.4
 - admin_audit: 1.10.0
 - audioplayer: 3.0.0
 - bruteforcesettings: 2.0.1
 - calendar: 2.1.3
 - cloud_federation_api: 1.3.0
 - comments: 1.10.0
 - contacts: 3.4.3
 - contactsinteraction: 1.1.0
 - cookbook: 0.7.10
 - dashboard: 7.0.0
 - dav: 1.16.2
 - deck: 1.2.3
 - federatedfilesharing: 1.10.2
 - federation: 1.10.1
 - files: 1.15.0
 - files_pdfviewer: 2.0.1
 - files_rightclick: 0.17.0
 - files_sharing: 1.12.2
 - files_trashbin: 1.10.1
 - files_versions: 1.13.0
 - files_videoplayer: 1.9.0
 - firstrunwizard: 2.9.0
 - groupfolders: 8.2.0
 - logreader: 2.5.0
 - lookup_server_connector: 1.8.0
 - nextcloud_announcements: 1.9.0
 - notes: 4.0.2
 - notifications: 2.8.0
 - oauth2: 1.8.0
 - password_policy: 1.10.1
 - passwords: 2021.1.2
 - photos: 1.2.3
 - privacy: 1.4.0
 - provisioning_api: 1.10.0
 - recommendations: 0.8.0
 - serverinfo: 1.10.0
 - settings: 1.2.0
 - sharebymail: 1.10.0
 - spreed: 10.0.5
 - support: 1.3.0
 - survey_client: 1.8.0
 - systemtags: 1.10.0
 - tasks: 0.13.6
 - text: 3.1.0
 - theming: 1.11.0
 - twofactor_backupcodes: 1.9.0
 - twofactor_totp: 5.0.0
 - updatenotification: 1.10.0
 - user_status: 1.0.1
 - viewer: 1.4.0
 - weather_status: 1.0.0
 - workflowengine: 2.2.0

• Nextcloud configuration: [the output of running sudo -u www-data php occ config:list system]

{
    "system": {
        "memcache.local": "\\OC\\Memcache\\APCu",
        "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": [
            "***REMOVED SENSITIVE VALUE***"
        ],
        "datadirectory": "***REMOVED SENSITIVE VALUE***",
        "overwrite.cli.url": "***REMOVED SENSITIVE VALUE***",
        "dbtype": "pgsql",
        "version": "20.0.6.1",
        "dbname": "***REMOVED SENSITIVE VALUE***",
        "dbhost": "***REMOVED SENSITIVE VALUE***",
        "dbport": "",
        "dbtableprefix": "",
        "dbuser": "***REMOVED SENSITIVE VALUE***",
        "dbpassword": "***REMOVED SENSITIVE VALUE***",
        "installed": true,
        "loglevel": 0,
        "maintenance": false,
        "mail_smtpmode": "smtp",
        "mail_smtpauthtype": "LOGIN",
        "mail_smtpauth": 1,
        "mail_from_address": "***REMOVED SENSITIVE VALUE***",
        "mail_domain": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpsecure": "ssl",
        "mail_smtphost": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpport": "465",
        "theme": "",
        "trusted_proxies": "***REMOVED SENSITIVE VALUE***",
        "overwriteprotocol": "https",
        "app_install_overwrite": [
            "cookbook"
        ]
    }
}

• Nextcloud external user backend: none

Nextcloud log (nextcloud/data/nextcloud.log)
This happens when trying to enable the app:

[settings] Error: Doctrine\DBAL\Exception\DriverException: An exception occurred while executing 'SELECT "b"."id" FROM "bookmarks" "b" LEFT JOIN "bookmarks_tree" "t" ON b.id = t.id AND ? WHERE "t"."id" IS NULL' with params ["bookmark"]:

SQLSTATE[22P02]: Invalid text representation: 7 ERROR:  invalid input syntax for type boolean: "bookmark" at <<closure>>

 0. /var/www/html/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php line 169
    Doctrine\DBAL\Driver\AbstractPostgreSQLDriver->convertException("An exception oc ... "", Doctrine\DBAL\Dr ... ]})
 1. /var/www/html/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php line 149
    Doctrine\DBAL\DBALException::wrapException(Doctrine\DBAL\Driver\PDOPgSql\Driver {}, Doctrine\DBAL\Dr ... ]}, "An exception oc ... "")
 2. /var/www/html/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php line 914
    Doctrine\DBAL\DBALException::driverExceptionDuringQuery(Doctrine\DBAL\Driver\PDOPgSql\Driver {}, Doctrine\DBAL\Dr ... ]}, "SELECT \"b\".\" ... L", {1: "bookmark"})
 3. /var/www/html/lib/private/DB/Connection.php line 202
    Doctrine\DBAL\Connection->executeQuery("SELECT \"b\".\" ... L", {1: "bookmark"}, {1: 2}, null)
 4. /var/www/html/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php line 206
    OC\DB\Connection->executeQuery("SELECT \"b\".\" ... L", {1: "bookmark"}, {1: 2})
 5. /var/www/html/lib/private/DB/QueryBuilder/QueryBuilder.php line 217
    Doctrine\DBAL\Query\QueryBuilder->execute()
 6. /var/www/html/custom_apps/bookmarks/lib/Migration/OrphanedTreeItemsRepairStep.php line 119
    OC\DB\QueryBuilder\QueryBuilder->execute()
 7. /var/www/html/lib/private/Repair.php line 111
    OCA\Bookmarks\Migration\OrphanedTreeItemsRepairStep->run(OC\Repair {})
 8. /var/www/html/lib/private/legacy/OC_App.php line 1042
    OC\Repair->run()
 9. /var/www/html/lib/private/Installer.php line 160
    OC_App::executeRepairSteps("bookmarks", ["OCA\\Bookmarks ... "])
10. /var/www/html/apps/settings/lib/Controller/AppSettingsController.php line 448
    OC\Installer->installApp("bookmarks")
11. /var/www/html/lib/private/AppFramework/Http/Dispatcher.php line 169
    OCA\Settings\Controller\AppSettingsController->enableApps(["bookmarks"], [])
12. /var/www/html/lib/private/AppFramework/Http/Dispatcher.php line 100
    OC\AppFramework\Http\Dispatcher->executeController(OCA\Settings\Con ... {}, "enableApps")
13. /var/www/html/lib/private/AppFramework/App.php line 152
    OC\AppFramework\Http\Dispatcher->dispatch(OCA\Settings\Con ... {}, "enableApps")
14. /var/www/html/lib/private/Route/Router.php line 309
    OC\AppFramework\App::main("OCA\\Settings\\ ... r", "enableApps", OC\AppFramework\ ... {}, {_route: "settin ... "})
15. /var/www/html/lib/base.php line 1008
    OC\Route\Router->match("/settings/apps/enable")
16. /var/www/html/index.php line 37
    OC::handleRequest()

POST /settings/apps/enable
from ***REMOVED SENSITIVE VALUE*** by bas at 2021-01-26T19:55:47+00:00

[snapet]

Same here.
The system gets quite confused as upon trying to upgrade and failing it thinks it is trying to upgrade the entire Nextcloud system to 20.0.5. So surfing to your Nextcloud site states the uprade to 20.0.05 failed and offers to start over. Which continues to fail.

Below command solves it for now. (as reported by Bas)
sudo -u www-data php /var/www/nextcloud/occ app:disable bookmarks

Deleting and reinstalling yields the same (SQL) error mentioned in the original bug report, but the system continues to function.

[poVoq]

same here, on a quite fresh 20.0.5 installation.

[derritter88]

same issue.

[marcelklehr]

Update v4.0.7 had a bug, I've removed the build from the download section, publishing a new release as we speak.

Update: v4.0.8 is released.

[malehwi]

Update v4.0.7 had a bug, I've removed the build from the download section, publishing a new release as we speak.

Update: v4.0.8 is released.

Thank you!
Do you know perhaps when does it gets updated on Nextcloud?

[marcelklehr]

I'm not sure how often nextcloud polls the update server

[poVoq]

Works now. Thanks for fixing it so quickly!

[basvandenbrink]

Thanks for the fix!