Nextcloud version: 16.0.4
Operating system and version: Linux 4.4.0-137-generic #163-Ubuntu SMP Mon Sep 24 13:14:43 UTC 2018 x86_64
Apache or nginx version: Apache/2.4.10 (Ubuntu)
PHP version: 7.2.0
The issue you are facing:
NextCloud is hosted on a shared host at online.net and has been working fine for a long time, until yesterday. No change / no upgrade just before on our side.
When trying to open some folders (but not all) in NC, we get the message “this folder is not available…”, in french: “Ce dossier n’est pas disponible, consultez les logs ou contactez votre administrateur”
Is this the first time you’ve seen this error? (Y/N): Y
Steps to replicate it:
- click on a folder in NC
- get the error message
- NC returns to home directory
The output of your Nextcloud log in Admin > Logging:
Doctrine\DBAL\Exception\DriverException: An exception occurred while executing 'SELECT `f`.`fileid`, COUNT(`c`.`id`) AS `num_ids` FROM `oc_filecache` `f` LEFT JOIN `oc_comments` `c` ON `f`.`fileid` = `c`.`object_id` LEFT JOIN `oc_comments_read_markers` `m` ON `c`.`object_id` = `m`.`object_id` WHERE (`f`.`parent` = ?) AND ((`c`.`object_type` = ?) OR (`c`.`object_type` IS NULL)) AND ((`m`.`object_type` = ?) OR (`m`.`object_type` IS NULL)) AND ((`m`.`user_id` = ?) OR (`m`.`user_id` IS NULL)) AND ((`c`.`creation_timestamp` > `m`.`marker_datetime`) OR (`m`.`marker_datetime` IS NULL)) GROUP BY `f`.`fileid`' with params [460090, "files", "files", "Manuel"]: SQLSTATE[42000]: Syntax error or access violation: 1104 The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay
The output of your config.php file in /path/to/nextcloud (make sure you remove any identifiable information!):
<?php
$CONFIG = array (
'overwriteprotocol' => 'https',
'overwrite.cli.url' => 'https://cloud.combustible.fr',
'instanceid' => 'oczaneshdy8g',
'passwordsalt' => 'xxxx',
'secret' => 'sssss',
'trusted_domains' =>
array (
0 => 'cloud.combustible.fr',
),
'datadirectory' => '/flex/storage/combustible.fr/site/cloud/data',
'dbtype' => 'mysql',
'version' => '16.0.4.1',
'dbname' => 'dbname',
'dbhost' => 'dbname.sql-pro.online.net:3306',
'dbport' => '',
'dbtableprefix' => 'oc_',
'dbuser' => 'dbuser',
'dbpassword' => 'xxxx',
'mysql.utf8mb4' => true,
'installed' => true,
'mail_from_address' => 'cloud',
'mail_smtpmode' => 'smtp',
'mail_smtpauthtype' => 'LOGIN',
'mail_domain' => 'combustible.fr',
'loglevel' => 0,
'mail_smtpsecure' => 'ssl',
'mail_smtphost' => 'smtpauth.online.net',
'mail_smtpport' => '465',
'mail_smtpauth' => 1,
'mail_smtpname' => 'smtpname',
'mail_smtppassword' => 'xxxx',
'maintenance' => false,
'theme' => '',
'updater.release.channel' => 'stable',
'app_install_overwrite' =>
array (
0 => 'calendar',
1 => 'ownpad',
2 => 'occweb',
),
'ldapIgnoreNamingRules' => false,
'ldapProviderFactory' => 'OCA\\User_LDAP\\LDAPProviderFactory',
'default_language' => 'fr',
'force_language' => 'fr',
);
Note that files remains accessible via webdav.
Guess the key is in “The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay”…
MAX_JOIN_SIZE is set to 300000 and SQL_BIG_SELECTS is OFF
Precision: oc_filecache is InnoDB / utf8mb4_bin with 25 179 lines and 15,7 Mio
