[Bug]: More than 1000 expressions in a list are not allowed on Oracle. (postgresql, in files_reminders / cacheFolder)

[biredel]

⚠️ 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

"More than 1000 expressions in a list are not allowed on Oracle." logged on PROPFIND

Steps to reproduce

1. view directory in browser
2. observe logs

Expected behavior

No errors.

Nextcloud Server version

31

Operating system

Debian/Ubuntu

PHP engine version

PHP 8.1

Web server

Nginx

Database engine version

PostgreSQL

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

Updated from a MINOR version (ex. 32.0.1 to 32.0.2)

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": {
        "version": "31.0.6.2",
        "config_is_read_only": true,
        "connectivity_check_domains": [],
        "check_for_working_wellknown_setup": false,
        "secret": "***REMOVED SENSITIVE VALUE***",
        "passwordsalt": "***REMOVED SENSITIVE VALUE***",
        "trusted_domains": [
            "***WHY DID occ config:list system NOT CONSIDER THIS SENSITIVE?***"
        ],
        "datadirectory": "***REMOVED SENSITIVE VALUE***",
        "dbtype": "pgsql",
        "dbname": "***REMOVED SENSITIVE VALUE***",
        "dbhost": "***REMOVED SENSITIVE VALUE***",
        "dbport": "",
        "dbtableprefix": "oc_",
        "dbuser": "***REMOVED SENSITIVE VALUE***",
        "dbpassword": "***REMOVED SENSITIVE VALUE***",
        "installed": true,
        "maintenance_window_start": 21,
        "debug": false,
        "default_phone_region": "DE",
        "instanceid": "***REMOVED SENSITIVE VALUE***",
        "updatechecker": false,
        "upgrade.disable-web": true,
        "updater.server.url": "***REMOVED SENSITIVE VALUE***",
        "updater.release.channel": "stable",
        "skeletondirectory": "",
        "default_language": "de",
        "overwrite.cli.url": "***WHY DID occ config:list system NOT CONSIDER THIS SENSITIVE?***",
        "htaccess.RewriteBase": "\/",
        "allow_local_remote_servers": true,
        "loglevel": 2,
    "log_type": "syslog",
        "syslog_tag": "nextcloud",
        "logfile": "",
        "logdateformat": "Y-m-d H:i:s",
        "logtimezone": "UTC",
        "log.condition": {
            "apps": [
                "admin_audit"
            ]
        },
        "log_type_audit": "syslog",
        "syslog_tag_audit": "nextcloud",
        "logfile_audit": "",
        "memcache.local": "\\OC\\Memcache\\APCu",
        "memcache.distributed": "\\OC\\Memcache\\Memcached",
        "memcache.locking": "\\OC\\Memcache\\Memcached",
        "memcached_servers": [
            [
                "\/run\/nextcloud\/memcached.sock",
                0
            ]
        ],
        "mail_from_address": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpmode": "smtp",
        "maintenance": false,
        "knowledgebaseenabled": false,
        "enable_previews": true,
        "allow_user_to_change_display_name": false,
        "mail_domain": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpauthtype": "LOGIN",
        "mail_smtpsecure": "ssl",
        "mail_smtpauth": 1,
        "trashbin_retention_obligation": "14, 60",
        "mail_send_plaintext_only": true,
        "theme": "",
        "has_internet_connection": false
    }

List of activated Apps

Enabled:
  - activity: 4.0.0
  - admin_audit: 1.21.0
  - app_api: 5.0.2
  - calendar: 5.3.2
  - circles: 31.0.0
  - cloud_federation_api: 1.14.0
  - comments: 1.21.0
  - contactsinteraction: 1.12.0
  - dav: 1.33.0
  - federatedfilesharing: 1.21.0
  - files: 2.3.1
  - files_accesscontrol: 2.0.0
  - files_automatedtagging: 2.0.0
  - files_downloadlimit: 4.0.0
  - files_reminders: 1.4.0
  - files_retention: 2.0.1
  - files_sharing: 1.23.1
  - files_trashbin: 1.21.0
  - files_versions: 1.24.0
  - logreader: 4.0.0
  - lookup_server_connector: 1.19.0
  - notifications: 4.0.0
  - oauth2: 1.19.1
  - privacy: 3.0.0
  - profile: 1.0.0
  - provisioning_api: 1.21.0
  - related_resources: 2.0.0
  - richdocuments: 8.7.0
  - serverinfo: 3.0.0
  - settings: 1.14.0
  - systemtags: 1.21.1
  - tasks: 0.16.1
  - text: 5.0.0
  - theming: 2.6.1
  - twofactor_backupcodes: 1.20.0
  - user_saml: 6.6.0
  - viewer: 4.0.0
  - webhook_listeners: 1.2.0
  - workflowengine: 2.13.0
Disabled:
  - bruteforcesettings: 4.0.0 (installed 1.0.3)
  - dashboard: 7.11.0 (installed 7.0.0)
  - encryption: 2.19.0
  - federation: 1.21.0 (installed 1.2.0)
  - files_external: 1.23.0
  - files_pdfviewer: 4.0.0 (installed 2.10.0)
  - nextcloud_announcements: 3.0.0 (installed 1.1)
  - password_policy: 3.0.0 (installed 1.3.0)
  - photos: 4.0.0-dev.1 (installed 1.0.0)
  - recommendations: 4.0.0 (installed 0.8.0)
  - support: 3.0.0 (installed 1.1.0)
  - suspicious_login: 9.0.1
  - twofactor_nextcloud_notification: 5.0.0
  - twofactor_totp: 13.0.0-dev.0
  - updatenotification: 1.21.0 (installed 1.2.0)
  - user_ldap: 1.22.0
  - user_status: 1.11.0 (installed 1.8.1)
  - weather_status: 1.11.0 (installed 1.7.0)

Nextcloud Signing status

No errors have been found.

Nextcloud Logs

{"reqId":"**redacted**","level":3,"time":"**redacted**","remoteAddr":"**redacted**","user":"**redacted**","app":"core","method":"PROPFIND","url":"/remote.php/dav/files/**redacted**/**redacted**/","message":"{\"Exception\":\"Doctrine\\\\DBAL\\\\Query\\\\QueryException\",\"Message\":\"More than 1000 expressions in a list are not allowed on Oracle.\",\"Code\":0,\"Trace\":[{\"file\":\"/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php\",\"line\":284,\"function\":\"prepareForExecute\",\"class\":\"OC\\\\DB\\\\QueryBuilder\\\\QueryBuilder\",\"type\":\"->\"},{\"file\":\"/nextcloud/lib/public/AppFramework/Db/QBMapper.php\",\"line\":329,\"function\":\"executeQuery\",\"class\":\"OC\\\\DB\\\\QueryBuilder\\\\QueryBuilder\",\"type\":\"->\"},{\"file\":\"/nextcloud/apps/files_reminders/lib/Db/ReminderMapper.php\",\"line\":159,\"function\":\"findEntities\",\"class\":\"OCP\\\\AppFramework\\\\Db\\\\QBMapper\",\"type\":\"->\"},{\"file\":\"/nextcloud/apps/files_reminders/lib/Service/ReminderService.php\",\"line\":51,\"function\":\"findAllInFolder\",\"class\":\"OCA\\\\FilesReminders\\\\Db\\\\ReminderMapper\",\"type\":\"->\"},{\"file\":\"/nextcloud/apps/files_reminders/lib/Dav/PropFindPlugin.php\",\"line\":80,\"function\":\"cacheFolder\",\"class\":\"OCA\\\\FilesReminders\\\\Service\\\\ReminderService\",\"type\":\"->\"},{\"file\":\"/nextcloud/apps/files_reminders/lib/Dav/PropFindPlugin.php\",\"line\":53,\"function\":\"cacheFolder\",\"class\":\"OCA\\\\FilesReminders\\\\Dav\\\\PropFindPlugin\",\"type\":\"->\"},{\"file\":\"/nextcloud/3rdparty/sabre/event/lib/WildcardEmitterTrait.php\",\"line\":89,\"function\":\"propFind\",\"class\":\"OCA\\\\FilesReminders\\\\Dav\\\\PropFindPlugin\",\"type\":\"->\"},{\"file\":\"/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php\",\"line\":1052,\"function\":\"emit\",\"class\":\"Sabre\\\\DAV\\\\Server\",\"type\":\"->\"},{\"file\":\"/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php\",\"line\":984,\"function\":\"getPropertiesByNode\",\"class\":\"Sabre\\\\DAV\\\\Server\",\"type\":\"->\"},{\"file\":\"/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php\",\"line\":1664,\"function\":\"getPropertiesIteratorForPath\",\"class\":\"Sabre\\\\DAV\\\\Server\",\"type\":\"->\"},{\"file\":\"/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php\",\"line\":1649,\"function\":\"writeMultiStatus\",\"class\":\"Sabre\\\\DAV\\\\Server\",\"type\":\"->\"},{\"file\":\"/nextcloud/3rdparty/sabre/dav/lib/DAV/CorePlugin.php\",\"line\":346,\"function\":\"generateMultiStatus\",\"class\":\"Sabre\\\\DAV\\\\Server\",\"type\":\"->\"},{\"file\":\"/nextcloud/3rdparty/sabre/event/lib/WildcardEmitterTrait.php\",\"line\":89,\"function\":\"httpPropFind\",\"class\":\"Sabre\\\\DAV\\\\CorePlugin\",\"type\":\"->\"},{\"file\":\"/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php\",\"line\":472,\"function\":\"emit\",\"class\":\"Sabre\\\\DAV\\\\Server\",\"type\":\"->\"},{\"file\":\"/nextcloud/apps/dav/lib/Connector/Sabre/Server.php\",\"line\":49,\"function\":\"invokeMethod\",\"class\":\"Sabre\\\\DAV\\\\Server\",\"type\":\"->\"},{\"file\":\"/nextcloud/apps/dav/lib/Server.php\",\"line\":400,\"function\":\"start\",\"class\":\"OCA\\\\DAV\\\\Connector\\\\Sabre\\\\Server\",\"type\":\"->\"},{\"file\":\"/nextcloud/apps/dav/appinfo/v2/remote.php\",\"line\":21,\"function\":\"exec\",\"class\":\"OCA\\\\DAV\\\\Server\",\"type\":\"->\"},{\"file\":\"/nextcloud/remote.php\",\"line\":145,\"args\":[\"/nextcloud/apps/dav/appinfo/v2/remote.php\"],\"function\":\"require_once\"}],\"File\":\"/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php\",\"Line\":237,\"message\":\"More than 1000 expressions in a list are not allowed on Oracle.\",\"query\":\"SELECT `id`, `user_id`, `file_id`, `due_date`, `updated_at`, `created_at`, `notified` FROM `*PREFIX*files_reminders` WHERE (`user_id` = :dcValue1) AND (`file_id` IN (:dcValue2)) ORDER BY `due_date` ASC\",\"exception\":{},\"CustomMessage\":\"More than 1000 expressions in a list are not allowed on Oracle.\"}","userAgent":"**redacted**","version":"31.0.6.2"}

Additional info

• At some point after the error was thrown, the referenced table was found to be empty:

psql <<<'SELECT COUNT(*) FROM oc_files_reminders;'
 count 
-------
     0
(1 row)

• (Does this guarantee the app is entirely unused and can be safely disabled?)

• Unconditionally blaming Oracle is misleading. In this case, Nextcloud is emitting this for postgresql, which defaults to somewhat higher limits.

• Some relevant bug reports may be easier to find using the ORA-01795 code.

  • Most recently fixed previous instance: [Bug]: More than 1000 expressions in a list are not allowed on Oracle #41605

[kesselb]

Thanks for your bug report 👍

I think I can confirm the issue, even though I haven't reproduced it myself.

server/apps/files_reminders/lib/Db/ReminderMapper.php

Lines 140 to 159 in e11ecaa

	$fileIds = array_values(array_filter(array_map(
	function (Node $node) {
	try {
	return $node->getId();
	} catch (NotFoundException $e) {
	return null;
	}
	},
	$folder->getDirectoryListing(),
	)));
	$qb = $this->db->getQueryBuilder();
	$qb->select('id', 'user_id', 'file_id', 'due_date', 'updated_at', 'created_at', 'notified')
	->from($this->getTableName())
	->where($qb->expr()->eq('user_id', $qb->createNamedParameter($user->getUID(), IQueryBuilder::PARAM_STR)))
	->andWhere($qb->expr()->in('file_id', $qb->createNamedParameter($fileIds, IQueryBuilder::PARAM_INT_ARRAY)))
	->orderBy('due_date', 'ASC');
	return $this->findEntities($qb);

The above block triggers a warning if a user has a folder with more than 1000 files. So we need chunking or a smarter way to obtain the reminders for a folder.

Unconditionally blaming Oracle is misleading. In this case, Nextcloud is emitting this for postgresql, which defaults to somewhat higher limits.

The check, if a query has more than 1000 parameters, runs everywhere, not only on Oracle. The purpose is to raise awareness, that we are missing chunking somewhere.

[solracsf]

I've made a tentative PR at #53548 feel free to comment/review.