[Bug]: PostgreSQL : Cronjob fails since the v30 upgrade, on an ARM system, with `Feature not supported: 7 ERROR: set-returning functions are not allowed in WHERE` #48828

adrienbricchi · 2024-10-21T15:41:39Z

⚠️ This issue respects the following points: ⚠️

This is a bug, not a question or a configuration/webserver/proxy issue.
This issue is not already reported on Github OR Nextcloud Community Forum (I've searched it).
Nextcloud Server is up to date. See Maintenance and Release Schedule for supported versions.
I agree to follow Nextcloud's Code of Conduct.

Bug description

Since the last version, the cron.php file seems to try to use a function that is not available for the PSQL server.

Next Doctrine\DBAL\Exception\DriverException: (...)An exception occurred while executing a query: SQLSTATE[0A000]: Feature not supported: 7 ERROR:  set-returning functions are not allowed in WHERE

I can't find how to enable this feature, or any workaround.

Steps to reproduce

Setup an up-to-date ARM Debian OS (VERSION="12 (bookworm)")
Install PostgreSQL 15.8 (Debian 15.8-0+deb12u1)
Use the nextcloud:30.0.1-fpm@sha256:e60116cd0e9bff3472a8459ecbd9ff7e202b9888e99ccb3d88f6a666ffdaf4ae through Docker
Try the cron.php by command line command : docker exec -u www-data -i nextcloud-fpm php /var/www/html/cron.php

Expected behavior

The cron script should not crash.

Nextcloud Server version

30

Operating system

Debian/Ubuntu

PHP engine version

PHP 8.2

Web server

Nginx

Database engine version

PostgreSQL

Is this bug present after an update or on a fresh install?

Upgraded to a MAJOR version (ex. 28 to 29)

Are you using the Nextcloud Server Encryption module?

Encryption is Disabled

What user-backends are you using?

Default user-backend (database)
LDAP/ Active Directory
SSO - SAML
Other

Configuration report

{
    "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
            }
        ],
        "memcache.distributed": "\\OC\\Memcache\\Redis",
        "memcache.locking": "\\OC\\Memcache\\Redis",
        "redis": {
            "host": "***REMOVED SENSITIVE VALUE***",
            "password": "***REMOVED SENSITIVE VALUE***",
            "port": 6379
        },
        "passwordsalt": "***REMOVED SENSITIVE VALUE***",
        "secret": "***REMOVED SENSITIVE VALUE***",
        "trusted_domains": [
            "***REMOVED SENSITIVE VALUE***"
        ],
        "datadirectory": "***REMOVED SENSITIVE VALUE***",
        "dbtype": "pgsql",
        "version": "30.0.1.2",
        "overwrite.cli.url": "http:\/\/localhost",
        "default_phone_region": "FR-fr",
        "dbname": "***REMOVED SENSITIVE VALUE***",
        "dbhost": "***REMOVED SENSITIVE VALUE***",
        "dbport": "",
        "dbtableprefix": "oc_",
        "dbuser": "***REMOVED SENSITIVE VALUE***",
        "dbpassword": "***REMOVED SENSITIVE VALUE***",
        "installed": true,
        "instanceid": "***REMOVED SENSITIVE VALUE***",
        "loglevel": 2,
        "maintenance": false,
        "maintenance_window_start": 100,
        "theme": "",
        "app_install_overwrite": [
            "files_rightclick"
        ]
    }
}

List of activated Apps

Enabled:
  - activity: 3.0.0
  - app_api: 4.0.0
  - bruteforcesettings: 3.0.0
  - cloud_federation_api: 1.13.0
  - dav: 1.31.1
  - federatedfilesharing: 1.20.0
  - files: 2.2.0
  - files_downloadlimit: 3.0.0
  - files_external: 1.22.0
  - files_pdfviewer: 3.0.0
  - files_reminders: 1.3.0
  - files_sharing: 1.22.0
  - files_trashbin: 1.20.1
  - logreader: 3.0.0
  - lookup_server_connector: 1.18.0
  - notifications: 3.0.0
  - oauth2: 1.18.1
  - password_policy: 2.0.0
  - photos: 3.0.2
  - privacy: 2.0.0
  - provisioning_api: 1.20.0
  - serverinfo: 2.0.0
  - settings: 1.13.0
  - support: 2.0.0
  - text: 4.1.0
  - theming: 2.5.0
  - twofactor_backupcodes: 1.19.0
  - updatenotification: 1.20.0
  - viewer: 3.0.0
  - webhook_listeners: 1.1.0-dev
  - workflowengine: 2.12.0
Disabled:
  - admin_audit: 1.20.0
  - circles: 30.0.0-dev (installed 24.0.0)
  - comments: 1.20.1 (installed 1.14.0)
  - contactsinteraction: 1.11.0 (installed 1.5.0)
  - dashboard: 7.10.0 (installed 7.4.0)
  - encryption: 2.18.0
  - federation: 1.20.0 (installed 1.14.0)
  - files_rightclick: 0.15.1 (installed 1.6.0)
  - files_versions: 1.23.0 (installed 1.17.0)
  - firstrunwizard: 3.0.0 (installed 2.13.0)
  - nextcloud_announcements: 2.0.0 (installed 1.13.0)
  - recommendations: 3.0.0 (installed 1.3.0)
  - related_resources: 1.5.0 (installed 1.0.0)
  - sharebymail: 1.20.0 (installed 1.14.0)
  - survey_client: 2.0.0 (installed 1.12.0)
  - suspicious_login: 8.0.0
  - systemtags: 1.20.0 (installed 1.14.0)
  - twofactor_nextcloud_notification: 4.0.0
  - twofactor_totp: 12.0.0-dev
  - user_ldap: 1.21.0
  - user_status: 1.10.0 (installed 1.4.0)
  - weather_status: 1.10.0 (installed 1.4.0)

Nextcloud Signing status

No errors have been found.

Nextcloud Logs

PDOException: SQLSTATE[0A000]: Feature not supported: 7 ERROR:  set-returning functions are not allowed in WHERE
LINE 1: SELECT * FROM "oc_jobs" WHERE ("reserved_at" <= $1) AND ("la...
                                                     ^ in /var/www/html/3rdparty/doctrine/dbal/src/Driver/PDO/Statement.php:130
Stack trace:
#0 /var/www/html/3rdparty/doctrine/dbal/src/Driver/PDO/Statement.php(130): PDOStatement->execute(NULL)
#1 /var/www/html/3rdparty/doctrine/dbal/src/Connection.php(1099): Doctrine\DBAL\Driver\PDO\Statement->execute()
#2 /var/www/html/lib/private/DB/Connection.php(415): Doctrine\DBAL\Connection->executeQuery('SELECT * FROM "...', Array, Array, NULL)
#3 /var/www/html/lib/private/DB/ConnectionAdapter.php(50): OC\DB\Connection->executeQuery('SELECT * FROM "...', Array, Array)
#4 /var/www/html/lib/private/DB/QueryBuilder/QueryBuilder.php(289): OC\DB\ConnectionAdapter->executeQuery('SELECT * FROM `...', Array, Array)
#5 /var/www/html/lib/private/BackgroundJob/JobList.php(192): OC\DB\QueryBuilder\QueryBuilder->executeQuery()
#6 /var/www/html/cron.php(148): OC\BackgroundJob\JobList->getNext(false, NULL)
#7 {main}

Next Doctrine\DBAL\Driver\PDO\Exception: SQLSTATE[0A000]: Feature not supported: 7 ERROR:  set-returning functions are not allowed in WHERE
LINE 1: SELECT * FROM "oc_jobs" WHERE ("reserved_at" <= $1) AND ("la...
                                                     ^ in /var/www/html/3rdparty/doctrine/dbal/src/Driver/PDO/Exception.php:28
Stack trace:
#0 /var/www/html/3rdparty/doctrine/dbal/src/Driver/PDO/Statement.php(132): Doctrine\DBAL\Driver\PDO\Exception::new(Object(PDOException))
#1 /var/www/html/3rdparty/doctrine/dbal/src/Connection.php(1099): Doctrine\DBAL\Driver\PDO\Statement->execute()
#2 /var/www/html/lib/private/DB/Connection.php(415): Doctrine\DBAL\Connection->executeQuery('SELECT * FROM "...', Array, Array, NULL)
#3 /var/www/html/lib/private/DB/ConnectionAdapter.php(50): OC\DB\Connection->executeQuery('SELECT * FROM "...', Array, Array)
#4 /var/www/html/lib/private/DB/QueryBuilder/QueryBuilder.php(289): OC\DB\ConnectionAdapter->executeQuery('SELECT * FROM `...', Array, Array)
#5 /var/www/html/lib/private/BackgroundJob/JobList.php(192): OC\DB\QueryBuilder\QueryBuilder->executeQuery()
#6 /var/www/html/cron.php(148): OC\BackgroundJob\JobList->getNext(false, NULL)
#7 {main}

Next Doctrine\DBAL\Exception\DriverException: An exception occurred while executing a query: SQLSTATE[0A000]: Feature not supported: 7 ERROR:  set-returning functions are not allowed in WHERE
LINE 1: SELECT * FROM "oc_jobs" WHERE ("reserved_at" <= $1) AND ("la...
                                                     ^ in /var/www/html/3rdparty/doctrine/dbal/src/Driver/API/PostgreSQL/ExceptionConverter.php:87
Stack trace:
#0 /var/www/html/3rdparty/doctrine/dbal/src/Connection.php(1939): Doctrine\DBAL\Driver\API\PostgreSQL\ExceptionConverter->convert(Object(Doctrine\DBAL\Driver\PDO\Exception), Object(Doctrine\DBAL\Query))
#1 /var/www/html/3rdparty/doctrine/dbal/src/Connection.php(1881): Doctrine\DBAL\Connection->handleDriverException(Object(Doctrine\DBAL\Driver\PDO\Exception), Object(Doctrine\DBAL\Query))
#2 /var/www/html/3rdparty/doctrine/dbal/src/Connection.php(1106): Doctrine\DBAL\Connection->convertExceptionDuringQuery(Object(Doctrine\DBAL\Driver\PDO\Exception), 'SELECT * FROM "...', Array, Array)
#3 /var/www/html/lib/private/DB/Connection.php(415): Doctrine\DBAL\Connection->executeQuery('SELECT * FROM "...', Array, Array, NULL)
#4 /var/www/html/lib/private/DB/ConnectionAdapter.php(50): OC\DB\Connection->executeQuery('SELECT * FROM "...', Array, Array)
#5 /var/www/html/lib/private/DB/QueryBuilder/QueryBuilder.php(289): OC\DB\ConnectionAdapter->executeQuery('SELECT * FROM `...', Array, Array)
#6 /var/www/html/lib/private/BackgroundJob/JobList.php(192): OC\DB\QueryBuilder\QueryBuilder->executeQuery()
#7 /var/www/html/cron.php(148): OC\BackgroundJob\JobList->getNext(false, NULL)
#8 {main}

Next OC\DB\Exceptions\DbalException: An exception occurred while executing a query: SQLSTATE[0A000]: Feature not supported: 7 ERROR:  set-returning functions are not allowed in WHERE
LINE 1: SELECT * FROM "oc_jobs" WHERE ("reserved_at" <= $1) AND ("la...
                                                     ^ in /var/www/html/lib/private/DB/Exceptions/DbalException.php:54
Stack trace:
#0 /var/www/html/lib/private/DB/ConnectionAdapter.php(53): OC\DB\Exceptions\DbalException::wrap(Object(Doctrine\DBAL\Exception\DriverException))
#1 /var/www/html/lib/private/DB/QueryBuilder/QueryBuilder.php(289): OC\DB\ConnectionAdapter->executeQuery('SELECT * FROM `...', Array, Array)
#2 /var/www/html/lib/private/BackgroundJob/JobList.php(192): OC\DB\QueryBuilder\QueryBuilder->executeQuery()
#3 /var/www/html/cron.php(148): OC\BackgroundJob\JobList->getNext(false, NULL)
#4 {main}

Additional info

No response

The text was updated successfully, but these errors were encountered:

adrienbricchi · 2024-11-15T14:11:07Z

I actually got the same issue with a regular pg_dump.
This is completely Postgresql-related, the Nextcloud update timing was just a coincidence.

adrienbricchi added 0. Needs triage Pending check for reproducibility or if it fits our roadmap bug labels Oct 21, 2024

adrienbricchi changed the title ~~[Bug]:~~ [Bug]: cron.php fails since the v30 upgrade, on an ARM system Oct 21, 2024

szaimen added the 30-feedback label Oct 21, 2024

szaimen mentioned this issue Oct 21, 2024

update existing instances to Nextcloud 30 nextcloud/all-in-one#5249

Closed

34 tasks

solracsf changed the title ~~[Bug]: cron.php fails since the v30 upgrade, on an ARM system~~ [Bug]: PostgreSQL : Cronjonb fails since the v30 upgrade, on an ARM system Oct 21, 2024

solracsf changed the title ~~[Bug]: PostgreSQL : Cronjonb fails since the v30 upgrade, on an ARM system~~ [Bug]: PostgreSQL : Cronjob fails since the v30 upgrade, on an ARM system, with Feature not supported: 7 ERROR: set-returning functions are not allowed in WHERE Oct 22, 2024

adrienbricchi closed this as completed Nov 15, 2024

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

[Bug]: PostgreSQL : Cronjob fails since the v30 upgrade, on an ARM system, with `Feature not supported: 7 ERROR: set-returning functions are not allowed in WHERE` #48828

[Bug]: PostgreSQL : Cronjob fails since the v30 upgrade, on an ARM system, with `Feature not supported: 7 ERROR: set-returning functions are not allowed in WHERE` #48828

adrienbricchi commented Oct 21, 2024 •

edited

Loading

adrienbricchi commented Nov 15, 2024 •

edited

Loading

[Bug]: PostgreSQL : Cronjob fails since the v30 upgrade, on an ARM system, with Feature not supported: 7 ERROR: set-returning functions are not allowed in WHERE #48828

[Bug]: PostgreSQL : Cronjob fails since the v30 upgrade, on an ARM system, with Feature not supported: 7 ERROR: set-returning functions are not allowed in WHERE #48828

Comments

adrienbricchi commented Oct 21, 2024 • edited Loading

⚠️ This issue respects the following points: ⚠️

Bug description

Steps to reproduce

Expected behavior

Nextcloud Server version

Operating system

PHP engine version

Web server

Database engine version

Is this bug present after an update or on a fresh install?

Are you using the Nextcloud Server Encryption module?

What user-backends are you using?

Configuration report

List of activated Apps

Nextcloud Signing status

Nextcloud Logs

Additional info

adrienbricchi commented Nov 15, 2024 • edited Loading

[Bug]: PostgreSQL : Cronjob fails since the v30 upgrade, on an ARM system, with `Feature not supported: 7 ERROR: set-returning functions are not allowed in WHERE` #48828

[Bug]: PostgreSQL : Cronjob fails since the v30 upgrade, on an ARM system, with `Feature not supported: 7 ERROR: set-returning functions are not allowed in WHERE` #48828

adrienbricchi commented Oct 21, 2024 •

edited

Loading

adrienbricchi commented Nov 15, 2024 •

edited

Loading