mat-ti
commented
3 years ago There are 24.000 files needs to be restore. It would be great when somebody is able to help me 👍
Open mat-ti opened 3 years ago
mat-ti
commented
3 years ago There are 24.000 files needs to be restore. It would be great when somebody is able to help me 👍
chainria
commented
3 years ago It's actually the same when deleting files from the trashbin. When sniffing MySQL, literally millions of queries like this happen:
SELECT `filecache`.`fileid`, `storage`, `path`, `path_hash`, `filecache`.`parent`, `name`, `mimetype`, `mimepart`, `size`, `mtime`, `storage_mtime`, `encrypted`, `etag`, `permissions`, `checksum`, `metadata_etag`, `creation_time`, `upload_time` FROM `oc_filecache` `filecache` LEFT JOIN `oc_filecache_extended` `fe` ON `filecache`.`fileid` = `fe`.`fileid` WHERE (`storage` = 2) AND (`path_hash` = 'bb474b75b468eb289840615e509da716')It seems that Nextcloud rebuilds some kind of cache when doing this? Pretty annoying since this table is huge. And it seems to do it for EVERY. SINGLE. FILE. Please, help?
This is happening with NC 19.0.7.
Edit: The results seem to point towards versions. Maybe something weird is happening there? Would be very interested if this is the same problem or just another one. ANY interaction with the trashbin seems to trigger this behaviour. Being it moving, deleting or restoring. I started moving out 226 files when I wrote this. 21 of them are moved out by now. Which makes a whopping 15 MINUTES per file.
chainria
commented
3 years ago FOUND it! After some digging and retracing I could finde the problem. It really is like I stated. NC simply recashes every version every time. Which is a BAD idea for bigger datastores. I have around 250k files in versions. Mostly however due to the bug which causes random deletions.
--- DIFF FOLLOWS ---
--- apps/files_trashbin/lib/Trashbin.orig.php 2021-03-14 17:31:32.000000000 +0100
+++ apps/files_trashbin/lib/Trashbin.php 2021-03-14 17:16:02.000000000 +0100
@@ -933,7 +933,7 @@
//force rescan of versions, local storage may not have updated the cache
if (!self::$scannedVersions) {
- $storage->getScanner()->scan('files_trashbin/versions');
+ //$storage->getScanner()->scan('files_trashbin/versions');
self::$scannedVersions = true;
}
--- DIFF ENDS ---should get you going. I don't see any side effects but PLEASE make a backup of the database and data directory first. And ensure your cache is up to date, which means the cronjob works. For me it does, so this step is just silly. Maybe a dev can chime in how to solve this in a better way?
The first batch of files is moved out. It takes about 3 seconds per file after this fix. Which is absolutely fine by me.
Edit: YES this also effects restoring, this code gets called when restoring or deleting both.
mat-ti
commented
3 years ago Wow great news, i will have a look and report it
solracsf
commented
1 year ago Should be fixed after https://github.com/nextcloud/server/commit/7f913dec032f0f8ff83eb7c9038ac0bc40bc6d87
solracsf
commented
1 year ago Reopening as even after applying https://github.com/nextcloud/server/pull/36746 problem stills, hundreds of thousands of MySQL requests to restore a SINGLE file, resulting in average 73 seconds per file to restore !!!
MOVE "/remote.php/dav/trashbin/username/trash/file.pdf.d1382021063" code=201 req=73.007Example query:
SELECT `f`.`fileid`, `mapping_type`, `mapping_id`, `mask`, `a`.`permissions`, `path` FROM `oc_group_folders_acl` `a` INNER JOIN `oc_filecache` `f` ON `f`.`fileid` = `a`.`fileid` WHERE (`path_hash` IN ('215fe7c43b20725393d7c5663c75961f', '15856166eb4f48488f412b9060f40797', 'dc603d7d8b07be1f47443d2b2bfa2ac8', '2eab5b0aa7b425ac679741448e4c4c7e', '76d49fcea7025f09657511048f78a1fa', '68736bf3b28198d665a5071b976a3446', '13482d5aced737348db8a2763864ddda', '29ff0edf73a32cb03e437d88fd049245', 'd41d8cd98f00b204e9800998ecf8427e')) AND (`storage` = 2) AND (((`mapping_type` = 'user') AND (`mapping_id` = 'username')) OR ((`mapping_type` = 'group') AND (`mapping_id` = 'groupname')))
Steps to reproduce
Expected behaviour
Restoring files from trashbin, selecting 1.000 Files to restore
Actual behaviour
Restoring files below 150 takes long time and above i receive connetcion errors due to massive cpu load.
Server configuration
Operating system: Ubuntu 18.04 LTS
Web server: Apache
Database: MySQL 5.7.32
PHP version: 7.4
Nextcloud version: 20.0.2
Updated from an older Nextcloud/ownCloud or fresh install: fresh install
Where did you install Nextcloud from: download from your website nextcloud.com
Signing status:
Signing status
``` No errors have been found. ```List of activated apps:
App list
``` Enabled: - accessibility: 1.6.0 - activity: 2.13.3 - bruteforcesettings: 2.0.1 - cloud_federation_api: 1.3.0 - comments: 1.10.0 - contactsinteraction: 1.1.0 - dav: 1.16.1 - federatedfilesharing: 1.10.1 - federation: 1.10.1 - files: 1.15.0 - files_pdfviewer: 2.0.1 - files_rightclick: 0.17.0 - files_sharing: 1.12.0 - files_trashbin: 1.10.1 - files_versions: 1.13.0 - files_videoplayer: 1.9.0 - firstrunwizard: 2.9.0 - groupfolders: 8.2.0 - impersonate: 1.7.0 - logreader: 2.5.0 - lookup_server_connector: 1.8.0 - nextcloud_announcements: 1.9.0 - notifications: 2.8.0 - oauth2: 1.8.0 - password_policy: 1.10.1 - photos: 1.2.0 - privacy: 1.4.0 - provisioning_api: 1.10.0 - recommendations: 0.8.0 - serverinfo: 1.10.0 - settings: 1.2.0 - sharebymail: 1.10.0 - support: 1.3.0 - systemtags: 1.10.0 - text: 3.1.0 - theming: 1.11.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: - admin_audit - dashboard - encryption - files_external - survey_client - user_ldap ```Nextcloud configuration:
Config report
``` { "system": { "instanceid": "***REMOVED SENSITIVE VALUE***", "passwordsalt": "***REMOVED SENSITIVE VALUE***", "secret": "***REMOVED SENSITIVE VALUE***", "trusted_domains": [ "nhvske.dyndns.org", "cloud.vske.de", "localhost" ], "datadirectory": "***REMOVED SENSITIVE VALUE***", "dbtype": "mysql", "version": "20.0.2.2", "overwrite.cli.url": "https:\/\/cloud.vske.de\/nextcloud", "overwriteprotocol": "https", "dbname": "***REMOVED SENSITIVE VALUE***", "dbhost": "***REMOVED SENSITIVE VALUE***", "dbport": "", "dbtableprefix": "oc_", "mysql.utf8mb4": true, "dbuser": "***REMOVED SENSITIVE VALUE***", "dbpassword": "***REMOVED SENSITIVE VALUE***", "installed": true, "maintenance": false, "loglevel": 2, "logtimezone": "Europe\/Berlin", "theme": "", "versions_retention_obligation": "disabled", "updater.release.channel": "stable", "filelocking.enabled": "true", "memcache.local": "\\OC\\Memcache\\APCu", "memcache.locking": "\\OC\\Memcache\\Redis", "memcache.distributed": "\\OC\\Memcache\\Redis", "redis": { "host": "***REMOVED SENSITIVE VALUE***", "port": 0, "timeout": 0 }, "mail_smtpmode": "smtp", "mail_smtpsecure": "ssl", "mail_sendmailmode": "smtp", "mail_smtpauthtype": "LOGIN" } } ```Are you using external storage, if yes which one: none
Are you using encryption: no
Are you using an external user-backend, if yes which one: no
Client configuration
Browser: Firefox / Chrome
Operating system: Windows
Logs
Web server error log
Web server error log
``` [Tue Dec 15 11:58:55.911093 2020] [proxy_fcgi:error] [pid 23249:tid 140614605711104] [client 82.135.104.163:24024] AH01071: Got error 'PHP message: PHP Fatal error: Uncaught Doctrine\\DBAL\\DBALException: Failed to connect to the database: An exception occurred in driver: SQLSTATE[HY000] [2002] No such file or directory in /var/www/html/nextcloud/lib/private/DB/Connection.php:72\nStack trace:\n#0 /var/www/html/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(1449): OC\\DB\\Connection->connect()\n#1 /var/www/html/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(892): Doctrine\\DBAL\\Connection->getWrappedConnection()\n#2 /var/www/html/nextcloud/lib/private/DB/Connection.php(202): Doctrine\\DBAL\\Connection->executeQuery()\n#3 /var/www/html/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php(206): OC\\DB\\Connection->executeQuery()\n#4 /var/www/html/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php(217): Doctrine\\DBAL\\Query\\QueryBuilder->execute()\n#5 /var/www/html/nextcloud/lib/private/AppConfig.php(345): OC\\DB\\QueryBuilder\\QueryBuilder->execute()\n#6 /var/www/html/nextcloud/lib/private/...' [Tue Dec 15 11:58:55.916178 2020] [proxy_fcgi:error] [pid 23249:tid 140614462912256] [client 82.135.104.163:24024] AH01071: Got error 'PHP message: PHP Fatal error: Uncaught Doctrine\\DBAL\\DBALException: Failed to connect to the database: An exception occurred in driver: SQLSTATE[HY000] [2002] No such file or directory in /var/www/html/nextcloud/lib/private/DB/Connection.php:72\nStack trace:\n#0 /var/www/html/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(1449): OC\\DB\\Connection->connect()\n#1 /var/www/html/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(892): Doctrine\\DBAL\\Connection->getWrappedConnection()\n#2 /var/www/html/nextcloud/lib/private/DB/Connection.php(202): Doctrine\\DBAL\\Connection->executeQuery()\n#3 /var/www/html/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php(206): OC\\DB\\Connection->executeQuery()\n#4 /var/www/html/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php(217): Doctrine\\DBAL\\Query\\QueryBuilder->execute()\n#5 /var/www/html/nextcloud/lib/private/AppConfig.php(345): OC\\DB\\QueryBuilder\\QueryBuilder->execute()\n#6 /var/www/html/nextcloud/lib/private/...' [Tue Dec 15 11:58:55.919075 2020] [proxy_fcgi:error] [pid 23249:tid 140614656067328] [client 82.135.104.163:24024] AH01071: Got error 'PHP message: PHP Fatal error: Uncaught Doctrine\\DBAL\\DBALException: Failed to connect to the database: An exception occurred in driver: SQLSTATE[HY000] [2002] No such file or directory in /var/www/html/nextcloud/lib/private/DB/Connection.php:72\nStack trace:\n#0 /var/www/html/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(1449): OC\\DB\\Connection->connect()\n#1 /var/www/html/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(892): Doctrine\\DBAL\\Connection->getWrappedConnection()\n#2 /var/www/html/nextcloud/lib/private/DB/Connection.php(202): Doctrine\\DBAL\\Connection->executeQuery()\n#3 /var/www/html/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php(206): OC\\DB\\Connection->executeQuery()\n#4 /var/www/html/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php(217): Doctrine\\DBAL\\Query\\QueryBuilder->execute()\n#5 /var/www/html/nextcloud/lib/private/AppConfig.php(345): OC\\DB\\QueryBuilder\\QueryBuilder->execute()\n#6 /var/www/html/nextcloud/lib/private/...' [Tue Dec 15 11:58:55.941057 2020] [proxy_fcgi:error] [pid 23249:tid 140614555322112] [client 82.135.104.163:24024] AH01071: Got error 'PHP message: PHP Fatal error: Uncaught Doctrine\\DBAL\\DBALException: Failed to connect to the database: An exception occurred in driver: SQLSTATE[HY000] [2002] No such file or directory in /var/www/html/nextcloud/lib/private/DB/Connection.php:72\nStack trace:\n#0 /var/www/html/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(1449): OC\\DB\\Connection->connect()\n#1 /var/www/html/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(892): Doctrine\\DBAL\\Connection->getWrappedConnection()\n#2 /var/www/html/nextcloud/lib/private/DB/Connection.php(202): Doctrine\\DBAL\\Connection->executeQuery()\n#3 /var/www/html/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php(206): OC\\DB\\Connection->executeQuery()\n#4 /var/www/html/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php(217): Doctrine\\DBAL\\Query\\QueryBuilder->execute()\n#5 /var/www/html/nextcloud/lib/private/AppConfig.php(345): OC\\DB\\QueryBuilder\\QueryBuilder->execute()\n#6 /var/www/html/nextcloud/lib/private/...' [Tue Dec 15 11:58:55.952385 2020] [proxy_fcgi:error] [pid 23249:tid 140614639281920] [client 82.135.104.163:24024] AH01071: Got error 'PHP message: PHP Fatal error: Uncaught Doctrine\\DBAL\\DBALException: Failed to connect to the database: An exception occurred in driver: SQLSTATE[HY000] [2002] No such file or directory in /var/www/html/nextcloud/lib/private/DB/Connection.php:72\nStack trace:\n#0 /var/www/html/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(1449): OC\\DB\\Connection->connect()\n#1 /var/www/html/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(892): Doctrine\\DBAL\\Connection->getWrappedConnection()\n#2 /var/www/html/nextcloud/lib/private/DB/Connection.php(202): Doctrine\\DBAL\\Connection->executeQuery()\n#3 /var/www/html/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php(206): OC\\DB\\Connection->executeQuery()\n#4 /var/www/html/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php(217): Doctrine\\DBAL\\Query\\QueryBuilder->execute()\n#5 /var/www/html/nextcloud/lib/private/AppConfig.php(345): OC\\DB\\QueryBuilder\\QueryBuilder->execute()\n#6 /var/www/html/nextcloud/lib/private/...' [Tue Dec 15 12:03:14.462069 2020] [proxy_fcgi:error] [pid 23249:tid 140614471313152] [client 82.135.104.163:42224] AH01071: Got error 'PHP message: PHP Fatal error: Uncaught Doctrine\\DBAL\\DBALException: Failed to connect to the database: An exception occurred in driver: SQLSTATE[HY000] [2002] No such file or directory in /var/www/html/nextcloud/lib/private/DB/Connection.php:72\nStack trace:\n#0 /var/www/html/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(1449): OC\\DB\\Connection->connect()\n#1 /var/www/html/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(892): Doctrine\\DBAL\\Connection->getWrappedConnection()\n#2 /var/www/html/nextcloud/lib/private/DB/Connection.php(202): Doctrine\\DBAL\\Connection->executeQuery()\n#3 /var/www/html/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php(206): OC\\DB\\Connection->executeQuery()\n#4 /var/www/html/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php(217): Doctrine\\DBAL\\Query\\QueryBuilder->execute()\n#5 /var/www/html/nextcloud/lib/private/AppConfig.php(345): OC\\DB\\QueryBuilder\\QueryBuilder->execute()\n#6 /var/www/html/nextcloud/lib/private/...' [Tue Dec 15 12:25:50.000003 2020] [proxy_fcgi:error] [pid 23249:tid 140614488114944] [client 82.135.104.163:12810] AH01071: Got error 'PHP message: PHP Fatal error: Uncaught Doctrine\\DBAL\\DBALException: Failed to connect to the database: An exception occurred in driver: SQLSTATE[HY000] [2002] No such file or directory in /var/www/html/nextcloud/lib/private/DB/Connection.php:72\nStack trace:\n#0 /var/www/html/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(1449): OC\\DB\\Connection->connect()\n#1 /var/www/html/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(892): Doctrine\\DBAL\\Connection->getWrappedConnection()\n#2 /var/www/html/nextcloud/lib/private/DB/Connection.php(202): Doctrine\\DBAL\\Connection->executeQuery()\n#3 /var/www/html/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php(206): OC\\DB\\Connection->executeQuery()\n#4 /var/www/html/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php(217): Doctrine\\DBAL\\Query\\QueryBuilder->execute()\n#5 /var/www/html/nextcloud/lib/private/AppConfig.php(345): OC\\DB\\QueryBuilder\\QueryBuilder->execute()\n#6 /var/www/html/nextcloud/lib/private/...' [Tue Dec 15 12:34:14.408386 2020] [proxy_fcgi:error] [pid 23248:tid 140614588925696] [client 82.135.104.163:61996] AH01071: Got error 'PHP message: PHP Fatal error: Uncaught Doctrine\\DBAL\\DBALException: Failed to connect to the database: An exception occurred in driver: SQLSTATE[HY000] [2002] No such file or directory in /var/www/html/nextcloud/lib/private/DB/Connection.php:72\nStack trace:\n#0 /var/www/html/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(1449): OC\\DB\\Connection->connect()\n#1 /var/www/html/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(892): Doctrine\\DBAL\\Connection->getWrappedConnection()\n#2 /var/www/html/nextcloud/lib/private/DB/Connection.php(202): Doctrine\\DBAL\\Connection->executeQuery()\n#3 /var/www/html/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php(206): OC\\DB\\Connection->executeQuery()\n#4 /var/www/html/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php(217): Doctrine\\DBAL\\Query\\QueryBuilder->execute()\n#5 /var/www/html/nextcloud/lib/private/AppConfig.php(345): OC\\DB\\QueryBuilder\\QueryBuilder->execute()\n#6 /var/www/html/nextcloud/lib/private/...' [Tue Dec 15 12:34:41.288594 2020] [access_compat:error] [pid 23248:tid 140614471313152] [client 82.135.104.163:61996] AH01797: client denied by server configuration: /var/www/html/nextcloud/data/.ocdata ```Nextcloud log (data/nextcloud.log)
Nextcloud log
``` {"reqId":"LU83btBDEWqklXQuNPU5","level":3,"time":"2020-12-15T12:34:14+01:00","remoteAddr":"