External SMB storage Error on opening directories with large amount of sub directories

Support intro

Sorry to hear you’re facing problems :slightly_frowning_face:

help.nextcloud.com is for home/non-enterprise users. If you’re running a business, paid support can be accessed via portal.nextcloud.com where we can ensure your business keeps running smoothly.

In order to help you as quickly as possible, before clicking Create Topic please provide as much of the below as you can. Feel free to use a pastebin service for logs, otherwise either indent short log examples with four spaces:

example

Or for longer, use three backticks above and below the code snippet:


Some or all of the below information will be requested if it isn’t supplied; for fastest response please provide as much as you can :heart:

Nextcloud version (eg, 20.0.5): 23.0.0
Operating system and version (eg, Ubuntu 20.04): CentOS Linux 7 (Core) Kernel 5.4.157-1-pve on an x86_64
Apache or nginx version (eg, Apache 2.4.25): apache
PHP version (eg, 7.4): PHP 7.4.27

The issue you are facing:
When opening a directory that has 1000 folders or more in it it errors and log shows above. It happens in any directory that has large amount of folders/files.
The directory is mounted with external drive over smb

Is this the first time you’ve seen this error? (Y/N):Y

Steps to replicate it:

  1. navigate from login to files> then goto folder that has large amount of items

The output of your Nextcloud log in Admin > Logging:

{"reqId":"Yk8GCeRB0@iKKKw8uKc8FgAAAAg","level":4,"time":"2022-04-07T15:40:58+00:00","remoteAddr":"172.16.255.254","user":"mparker","app":"webdav","method":"PROPFIND","url":"/remote.php/dav/files/mparker/S%20Drive/Mozaic/Interfaces/Customers/AMAZON.COM/archive/in","message":"An exception occurred while executing a query: SQLSTATE[HY000]: General error: 1 too many SQL variables","userAgent":"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/100.0.4896.75 Safari/537.36","version":"23.0.0.10","exception":{"Exception":"OC\\DB\\Exceptions\\DbalException","Message":"An exception occurred while executing a query: SQLSTATE[HY000]: General error: 1 too many SQL variables","Code":1,"Trace":[{"file":"/var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php","line":302,"function":"wrap","class":"OC\\DB\\Exceptions\\DbalException","type":"::"},{"file":"/var/www/nextcloud/lib/private/Comments/Manager.php","line":661,"function":"executeQuery","class":"OC\\DB\\QueryBuilder\\QueryBuilder","type":"->"},{"file":"/var/www/nextcloud/apps/dav/lib/Connector/Sabre/CommentPropertiesPlugin.php","line":88,"function":"getNumberOfUnreadCommentsForObjects","class":"OC\\Comments\\Manager","type":"->"},{"file":"/var/www/nextcloud/apps/dav/lib/Connector/Sabre/CommentPropertiesPlugin.php","line":116,"function":"cacheDirectory","class":"OCA\\DAV\\Connector\\Sabre\\CommentPropertiesPlugin","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/event/lib/WildcardEmitterTrait.php","line":89,"function":"handleGetProperties","class":"OCA\\DAV\\Connector\\Sabre\\CommentPropertiesPlugin","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php","line":1052,"function":"emit","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php","line":984,"function":"getPropertiesByNode","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php","line":1661,"function":"getPropertiesIteratorForPath","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php","line":1646,"function":"writeMultiStatus","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/CorePlugin.php","line":346,"function":"generateMultiStatus","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/event/lib/WildcardEmitterTrait.php","line":89,"function":"httpPropFind","class":"Sabre\\DAV\\CorePlugin","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php","line":472,"function":"emit","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php","line":253,"function":"invokeMethod","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php","line":321,"function":"start","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/apps/dav/lib/Server.php","line":339,"function":"exec","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/apps/dav/appinfo/v2/remote.php","line":35,"function":"exec","class":"OCA\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/remote.php","line":166,"args":["/var/www/nextcloud/apps/dav/appinfo/v2/remote.php"],"function":"require_once"}],"File":"/var/www/nextcloud/lib/private/DB/Exceptions/DbalException.php","Line":71,"Previous":{"Exception":"Doctrine\\DBAL\\Exception\\DriverException","Message":"An exception occurred while executing a query: SQLSTATE[HY000]: General error: 1 too many SQL variables","Code":1,"Trace":[{"file":"/var/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php","line":1780,"function":"convert","class":"Doctrine\\DBAL\\Driver\\API\\SQLite\\ExceptionConverter","type":"->"},{"file":"/var/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php","line":1719,"function":"handleDriverException","class":"Doctrine\\DBAL\\Connection","type":"->"},{"file":"/var/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php","line":1067,"function":"convertExceptionDuringQuery","class":"Doctrine\\DBAL\\Connection","type":"->"},{"file":"/var/www/nextcloud/lib/private/DB/Connection.php","line":236,"function":"executeQuery","class":"Doctrine\\DBAL\\Connection","type":"->"},{"file":"/var/www/nextcloud/3rdparty/doctrine/dbal/src/Query/QueryBuilder.php","line":345,"function":"executeQuery","class":"OC\\DB\\Connection","type":"->"},{"file":"/var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php","line":287,"function":"execute","class":"Doctrine\\DBAL\\Query\\QueryBuilder","type":"->"},{"file":"/var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php","line":300,"function":"execute","class":"OC\\DB\\QueryBuilder\\QueryBuilder","type":"->"},{"file":"/var/www/nextcloud/lib/private/Comments/Manager.php","line":661,"function":"executeQuery","class":"OC\\DB\\QueryBuilder\\QueryBuilder","type":"->"},{"file":"/var/www/nextcloud/apps/dav/lib/Connector/Sabre/CommentPropertiesPlugin.php","line":88,"function":"getNumberOfUnreadCommentsForObjects","class":"OC\\Comments\\Manager","type":"->"},{"file":"/var/www/nextcloud/apps/dav/lib/Connector/Sabre/CommentPropertiesPlugin.php","line":116,"function":"cacheDirectory","class":"OCA\\DAV\\Connector\\Sabre\\CommentPropertiesPlugin","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/event/lib/WildcardEmitterTrait.php","line":89,"function":"handleGetProperties","class":"OCA\\DAV\\Connector\\Sabre\\CommentPropertiesPlugin","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php","line":1052,"function":"emit","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php","line":984,"function":"getPropertiesByNode","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php","line":1661,"function":"getPropertiesIteratorForPath","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php","line":1646,"function":"writeMultiStatus","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/CorePlugin.php","line":346,"function":"generateMultiStatus","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/event/lib/WildcardEmitterTrait.php","line":89,"function":"httpPropFind","class":"Sabre\\DAV\\CorePlugin","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php","line":472,"function":"emit","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php","line":253,"function":"invokeMethod","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php","line":321,"function":"start","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/apps/dav/lib/Server.php","line":339,"function":"exec","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/apps/dav/appinfo/v2/remote.php","line":35,"function":"exec","class":"OCA\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/remote.php","line":166,"args":["/var/www/nextcloud/apps/dav/appinfo/v2/remote.php"],"function":"require_once"}],"File":"/var/www/nextcloud/3rdparty/doctrine/dbal/src/Driver/API/SQLite/ExceptionConverter.php","Line":82,"Previous":{"Exception":"Doctrine\\DBAL\\Driver\\PDO\\Exception","Message":"SQLSTATE[HY000]: General error: 1 too many SQL variables","Code":1,"Trace":[{"file":"/var/www/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Connection.php","line":75,"function":"new","class":"Doctrine\\DBAL\\Driver\\PDO\\Exception","type":"::"},{"file":"/var/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php","line":1054,"function":"prepare","class":"Doctrine\\DBAL\\Driver\\PDO\\Connection","type":"->"},{"file":"/var/www/nextcloud/lib/private/DB/Connection.php","line":236,"function":"executeQuery","class":"Doctrine\\DBAL\\Connection","type":"->"},{"file":"/var/www/nextcloud/3rdparty/doctrine/dbal/src/Query/QueryBuilder.php","line":345,"function":"executeQuery","class":"OC\\DB\\Connection","type":"->"},{"file":"/var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php","line":287,"function":"execute","class":"Doctrine\\DBAL\\Query\\QueryBuilder","type":"->"},{"file":"/var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php","line":300,"function":"execute","class":"OC\\DB\\QueryBuilder\\QueryBuilder","type":"->"},{"file":"/var/www/nextcloud/lib/private/Comments/Manager.php","line":661,"function":"executeQuery","class":"OC\\DB\\QueryBuilder\\QueryBuilder","type":"->"},{"file":"/var/www/nextcloud/apps/dav/lib/Connector/Sabre/CommentPropertiesPlugin.php","line":88,"function":"getNumberOfUnreadCommentsForObjects","class":"OC\\Comments\\Manager","type":"->"},{"file":"/var/www/nextcloud/apps/dav/lib/Connector/Sabre/CommentPropertiesPlugin.php","line":116,"function":"cacheDirectory","class":"OCA\\DAV\\Connector\\Sabre\\CommentPropertiesPlugin","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/event/lib/WildcardEmitterTrait.php","line":89,"function":"handleGetProperties","class":"OCA\\DAV\\Connector\\Sabre\\CommentPropertiesPlugin","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php","line":1052,"function":"emit","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php","line":984,"function":"getPropertiesByNode","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php","line":1661,"function":"getPropertiesIteratorForPath","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php","line":1646,"function":"writeMultiStatus","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/CorePlugin.php","line":346,"function":"generateMultiStatus","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/event/lib/WildcardEmitterTrait.php","line":89,"function":"httpPropFind","class":"Sabre\\DAV\\CorePlugin","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php","line":472,"function":"emit","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php","line":253,"function":"invokeMethod","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php","line":321,"function":"start","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/apps/dav/lib/Server.php","line":339,"function":"exec","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/apps/dav/appinfo/v2/remote.php","line":35,"function":"exec","class":"OCA\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/remote.php","line":166,"args":["/var/www/nextcloud/apps/dav/appinfo/v2/remote.php"],"function":"require_once"}],"File":"/var/www/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Exception.php","Line":26,"Previous":{"Exception":"PDOException","Message":"SQLSTATE[HY000]: General error: 1 too many SQL variables","Code":"HY000","Trace":[{"file":"/var/www/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Connection.php","line":70,"function":"prepare","class":"PDO","type":"->"},{"file":"/var/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php","line":1054,"function":"prepare","class":"Doctrine\\DBAL\\Driver\\PDO\\Connection","type":"->"},{"file":"/var/www/nextcloud/lib/private/DB/Connection.php","line":236,"function":"executeQuery","class":"Doctrine\\DBAL\\Connection","type":"->"},{"file":"/var/www/nextcloud/3rdparty/doctrine/dbal/src/Query/QueryBuilder.php","line":345,"function":"executeQuery","class":"OC\\DB\\Connection","type":"->"},{"file":"/var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php","line":287,"function":"execute","class":"Doctrine\\DBAL\\Query\\QueryBuilder","type":"->"},{"file":"/var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php","line":300,"function":"execute","class":"OC\\DB\\QueryBuilder\\QueryBuilder","type":"->"},{"file":"/var/www/nextcloud/lib/private/Comments/Manager.php","line":661,"function":"executeQuery","class":"OC\\DB\\QueryBuilder\\QueryBuilder","type":"->"},{"file":"/var/www/nextcloud/apps/dav/lib/Connector/Sabre/CommentPropertiesPlugin.php","line":88,"function":"getNumberOfUnreadCommentsForObjects","class":"OC\\Comments\\Manager","type":"->"},{"file":"/var/www/nextcloud/apps/dav/lib/Connector/Sabre/CommentPropertiesPlugin.php","line":116,"function":"cacheDirectory","class":"OCA\\DAV\\Connector\\Sabre\\CommentPropertiesPlugin","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/event/lib/WildcardEmitterTrait.php","line":89,"function":"handleGetProperties","class":"OCA\\DAV\\Connector\\Sabre\\CommentPropertiesPlugin","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php","line":1052,"function":"emit","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php","line":984,"function":"getPropertiesByNode","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php","line":1661,"function":"getPropertiesIteratorForPath","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php","line":1646,"function":"writeMultiStatus","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/CorePlugin.php","line":346,"function":"generateMultiStatus","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/event/lib/WildcardEmitterTrait.php","line":89,"function":"httpPropFind","class":"Sabre\\DAV\\CorePlugin","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php","line":472,"function":"emit","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php","line":253,"function":"invokeMethod","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php","line":321,"function":"start","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/apps/dav/lib/Server.php","line":339,"function":"exec","class":"Sabre\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/apps/dav/appinfo/v2/remote.php","line":35,"function":"exec","class":"OCA\\DAV\\Server","type":"->"},{"file":"/var/www/nextcloud/remote.php","line":166,"args":["/var/www/nextcloud/apps/dav/appinfo/v2/remote.php"],"function":"require_once"}],"File":"/var/www/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Connection.php","Line":70}}},"CustomMessage":"--"},"id":"624f0a3c9b2d8"}



[webdav] Fatal: An exception occurred while executing a query: SQLSTATE[HY000]: General error: 1 too many SQL variables

PROPFIND /remote.php/dav/files/mparker/S%20Drive/Mozaic/Interfaces/Customers/AMAZON.COM/archive/in

The output of your config.php file in /path/to/nextcloud (make sure you remove any identifiable information!):

PASTE HERE

The output of your Apache/nginx/system log in /var/log/____:

PASTE HERE

PASTE HERE


Output errors in nextcloud.log in /var/www/ or as admin user in top right menu, filtering for errors. Use a pastebin service if necessary.

PASTE HERE

This problem exists for some versions of SQlite.

seem you have a known bug from sqlite where you are hitting the SQLITE_MAX_VARIABLE_NUMBER limit, which is 999.

Unfortunately, no fix i know of so far.

Update: I made a archive folder and moved 800 of the 100 folders to it.
Now the original folder with around 200 folder opens in nextcloud.
So it has something to do with the volume of folders.
I would like to not have to archive.

The bug is effectively related to the numbers of sql request !!! The more files, the more request…

SQLITE_MAX_VARIABLE_NUMBER limit, which is 999… by splitting the numbers ( from 1000 to 800, ) , you lower the sql request…lower request → no bug. Higher request → bug.

Solution, change you database engine to something else ( maridb or other compatible sql engine ), or wait for a bug fix allowing a value higher than 999

Thank you.
Yall rock.