Repository navigation
php occ files:scan exceeds maximal number of parameters while deleting files with PostgreSQL #25732
Description
How to use GitHub
- Please use the 👍 reaction to show that you are affected by the same issue.
- Please don't comment if you have no relevant information to add. It's just extra noise for everyone subscribed to this issue.
- Subscribe to receive notifications on status change and new comments.
Steps to reproduce
- Many files ((incl. previews)
- Run php occ files:scan
Expected behaviour
The filecache is cleaned up.
Actual behaviour
I'm getting this exception:
Exception during scan: An exception occurred while executing 'DELETE FROM "oc_filecache" WHERE "parent IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
… 1MB of query …
SQLSTATE[HY000]: General error: 7 number of parameters must be between 0 and 65535
#0 /srv/nc/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php(169): Doctrine\DBAL\Driver\AbstractPostgreSQLDriver->convertException('An exception oc...', Object(Doctrine\DBAL\Driver\PDOException))
#1 /srv/nc/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php(149): Doctrine\DBAL\DBALException::wrapException(Object(Doctrine\DBAL\Driver\PDOPgSql\Driver), Object(Doctrine\DBAL\Driver\PDOException), 'An exception oc...')
#2 /srv/nc/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(1071): Doctrine\DBAL\DBALException::driverExceptionDuringQuery(Object(Doctrine\DBAL\Driver\PDOPgSql\Driver), Object(Doctrine\DBAL\Driver\PDOException), 'DELETE FROM "oc...', Array)
#3 /srv/nc/lib/private/DB/Connection.php(222): Doctrine\DBAL\Connection->executeUpdate('DELETE FROM "oc...', Array, Array)
#4 /srv/nc/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php(209): OC\DB\Connection->executeUpdate('DELETE FROM "oc...', Array, Array)
#5 /srv/nc/lib/private/DB/QueryBuilder/QueryBuilder.php(217): Doctrine\DBAL\Query\QueryBuilder->execute()
#6 /srv/nc/lib/private/Files/Cache/Cache.php(591): OC\DB\QueryBuilder\QueryBuilder->execute()
#7 /srv/nc/lib/private/Files/Cache/Cache.php(537): OC\Files\Cache\Cache->removeChildren(Object(OC\Files\Cache\CacheEntry))
#8 /srv/nc/lib/private/Files/Cache/Scanner.php(273): OC\Files\Cache\Cache->remove('files_encryptio...')
#9 /srv/nc/lib/private/Files/Cache/Scanner.php(460): OC\Files\Cache\Scanner->removeFromCache('files_encryptio...')
#10 /srv/nc/lib/private/Files/Cache/Scanner.php(388): OC\Files\Cache\Scanner->handleChildren('', true, 3, 255, true, 0)
#11 /srv/nc/lib/private/Files/Cache/Scanner.php(340): OC\Files\Cache\Scanner->scanChildren('', true, 3, 255, true)
#12 /srv/nc/lib/private/Files/Utils/Scanner.php(260): OC\Files\Cache\Scanner->scan('', true, 3)
#13 /srv/nc/apps/files/lib/Command/Scan.php(152): OC\Files\Utils\Scanner->scan('/frlan', true, NULL)
#14 /srv/nc/apps/files/lib/Command/Scan.php(208): OCA\Files\Command\Scan->scanFiles('frlan', '/frlan', Object(Symfony\Component\Console\Output\ConsoleOutput), false, true, false)
#15 /srv/nc/apps/bookmarks/vendor/symfony/console/Command/Command.php(255): OCA\Files\Command\Scan->execute(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#16 /srv/nc/core/Command/Base.php(169): Symfony\Component\Console\Command\Command->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#17 /srv/nc/apps/bookmarks/vendor/symfony/console/Application.php(1009): OC\Core\Command\Base->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#18 /srv/nc/apps/bookmarks/vendor/symfony/console/Application.php(273): Symfony\Component\Console\Application->doRunCommand(Object(OCA\Files\Command\Scan), Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#19 /srv/nc/apps/bookmarks/vendor/symfony/console/Application.php(149): Symfony\Component\Console\Application->doRun(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#20 /srv/nc/lib/private/Console/Application.php(215): Symfony\Component\Console\Application->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#21 /srv/nc/console.php(100): OC\Console\Application->run()
#22 /srv/nc/occ(11): require_once('/srv/nc/console...')
#23 {main}
Server configuration
Operating system:
Debian Buster
Web server:
--
Database:
PostgrSQL
PHP version:
PHP 7.3.x
Nextcloud version: (see Nextcloud admin page)
20.0.7
Updated from an older Nextcloud/ownCloud or fresh install:
Updated
Where did you install Nextcloud from:
Signing status:
No errors have been found.
``
List of activated apps:
<details>
<summary>Enabled:
- accessibility: 1.6.0
- activity: 2.13.4
- admin_audit: 1.10.0
- apporder: 0.11.0
- bookmarks: 4.1.0
- bruteforcesettings: 2.0.1
- calendar: 2.1.3
- camerarawpreviews: 0.7.10
- cloud_federation_api: 1.3.0
- comments: 1.10.0
- contacts: 3.4.3
- contactsinteraction: 1.1.0
- cookbook: 0.8.1
- dashboard: 7.0.0
- dav: 1.16.2
- event_update_notification: 1.2.0
- federatedfilesharing: 1.10.2
- federation: 1.10.1
- files: 1.15.0
- files_accesscontrol: 1.10.2
- files_external: 1.11.1
- files_markdown: 2.3.1
- 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
- firstrunwizard: 2.9.0
- gpxpod: 4.2.6
- integration_discourse: 0.0.9
- integration_github: 0.0.20
- logreader: 2.5.0
- lookup_server_connector: 1.8.0
- maps: 0.1.8
- metadata: 0.12.0
- music: 1.0.2
- nextcloud_announcements: 1.9.0
- notes: 4.0.2
- notifications: 2.8.0
- oauth2: 1.8.0
- password_policy: 1.10.1
- photos: 1.2.3
- previewgenerator: 3.1.1
- privacy: 1.4.0
- provisioning_api: 1.10.0
- quota_warning: 1.9.1
- recommendations: 0.8.0
- serverinfo: 1.10.0
- settings: 1.2.0
- sharebymail: 1.10.0
- socialsharing_facebook: 2.1.0
- socialsharing_twitter: 2.1.0
- support: 1.3.0
- survey_client: 1.8.0
- systemtags: 1.10.0
- tasks: 0.13.6
- text: 3.1.0
- twofactor_backupcodes: 1.9.0
- updatenotification: 1.10.0
- user_status: 1.0.1
- viewer: 1.4.0
- weather_status: 1.0.0
- workflowengine: 2.2.0
Disabled:
- checksum
- encryption
- files_automatedtagging
- files_readmemd
- files_videoplayer
- gpxedit
- theming
- user_ldap</summary>
Nextcloud configuration:
Config report
If you have access to your command line run e.g.:
sudo -u www-data php occ config:list system
from within your Nextcloud installation folder
or
Insert your config.php content here.
Make sure to remove all sensitive content such as passwords. (e.g. database password, passwordsalt, secret, smtp password, …)
Are you using external storage, if yes which one: local/smb/sftp/...
Are you using encryption: yes/no
Are you using an external user-backend, if yes which one: LDAP/ActiveDirectory/Webdav/...
LDAP configuration (delete this part if not used)
LDAP config
With access to your command line run e.g.:
sudo -u www-data php occ ldap:show-config
from within your Nextcloud installation folder
Without access to your command line download the data/owncloud.db to your local
computer or access your SQL server remotely and run the select query:
SELECT * FROM `oc_appconfig` WHERE `appid` = 'user_ldap';
Eventually replace sensitive data as the name/IP-address of your LDAP server or groups.
Client configuration
Browser:
Operating system:
Logs
Web server error log
Web server error log
Insert your webserver log here
Nextcloud log (data/nextcloud.log)
Nextcloud log
Insert your Nextcloud log here
Browser log
Browser log
Insert your browser log here, this could for example include:
a) The javascript console log
b) The network log
c) ...
Reacted by Daniel Schemmel and priyankub
Activity
szaimen commentedon Jun 23, 2021
Is this Issue still valid in NC21.0.2? If not, please close this issue. Thanks! :)
danielschemmel commentedon Jun 27, 2021
I can verify that this issue (or at least a very similar one) is still valid in NC21.0.2 (derived from docker image
nextcloud:21-fpmversion8eb7ad57d08d):Server configuration
Operating system: Docker on QNAP
Web server: nginx (behind another nginx reverse proxy)
Database: PostgreSQL 13.3
PHP version: 7.4.20
Nextcloud version: 21.0.2
Updated from an older Nextcloud/ownCloud or fresh install:
Updated, original install was NC20 iirc.
Where did you install Nextcloud from:
Docker installation similar to https://github.com/nextcloud/docker/tree/6b6c68cabfc745ca94e3fe64145f5e04dffeecf3/.examples/docker-compose/with-nginx-proxy/postgres/fpm
Signing status:
Signing status
List of activated apps:
App list
Nextcloud configuration:
Config report
I replaced the actual domain name with `my.domain.name`.Are you using external storage, if yes which one: local+smb/cifs
Are you using encryption: no
Are you using an external user-backend, if yes which one: Not anymore (SAML SSO was in use for some time before)
Logs
Web server error log
Web server error log
Nextcloud log (data/nextcloud.log)
Nextcloud log
The logfiles have become very large, primarily due to entries taking up multiple *megabytes* for a single entry like the following originally ~15MB line. Notice the `[SNIP]`s to keep the report size incheck.Browser log
Browser log
No browser interactionszaimen commentedon Jun 27, 2021
Could you please add some steps how to reproduce? Thanks!
danielschemmel commentedon Jun 28, 2021
I did not yet have time to do a full from-scratch repro, but the easiest way to trigger the exception in my running instance is:
It takes a minute or two for the problem to occur, but so far it seems to happen every single time.
szaimen commentedon Jun 29, 2021
How many files do you have on your external storages and how many Nextcloud users that have access to those external storages?
danielschemmel commentedon Jun 29, 2021
There are many files but only a few users with access
szaimen commentedon Jun 29, 2021
How many is many (approx.)?
danielschemmel commentedon Jun 29, 2021
I should have expected that question: Roughly a million files distributed over some 65k folders. Number of users is <10.
szaimen commentedon Jun 29, 2021
Hm... that doesn't sound unreasonable, should be actually handleable.
szaimen commentedon Jun 29, 2021
cc @icewind1991
6 remaining items
priyankub commentedon Oct 16, 2021
Could this be reopened? The issue still exists.
szaimen commentedon Oct 16, 2021
@vijfhoek any update here? :)
priyankub commentedon Oct 16, 2021
I have Samba storages connected to NC. There were large changes in the files in the storage from clients outside NC. So when NC reconciled everything, it had to delete a huge chunk which likely was giving this error.
I queried oc_storages to find out the storage IDs for the SMB shares, and deleted all entries from oc_filecache for the SMB shares. The error does not appear anymore and I can view, delete files in those SMB shares from NC without any issues.
vijfhoek commentedon Oct 17, 2021
@szaimen I must admit that this issue kind of ended up on the backburner here 😅
I've just implemented the fix I suggested, currently trying to reproduce the issue again so I can confirm it indeed fixes the issue.
vijfhoek commentedon Oct 17, 2021
I managed to reproduce the issue, and confirmed my proposed fix fixes the issue.
I have also done some research on what would be a good limit, based on the maximum number of parameters in a query:
Interestingly, during some very unscientific testing I've found that a parameter count to 2048 has a significantly better performance on postgresql (2:58 min for the full scan) versus 32768 (3:07 min). This could be fully attributed to some variable (disk cache, background processes, whatever) but at least it means it isn't a major performance hit :)
I'll submit a pull request in a bit!