Strange SQL problems; unable to run occ files:scan

[wwbumpas]

Trying to address an error I've had for several days. Client upload failing on certain files, which seem "stuck" - unable to change or delete through client or web interface. It seems to be some kind of SQL issue. Nextcloud log in Admin > Logging has the following message repeated many times:
OC\Encryption\Exceptions\DecryptionFailedException: Can not decrypt this file, probably this is a shared file. Please ask the file owner to reshare the file with you.

Thinking it might be that the files had become improperly locked somehow, I followed the instructions here to remove all locks. The command seemed to run successfully but the problem persisted.

Next I tried sudo -u www-data php occ files:scan —all and got the following output: https://pastebin.com/YfERsfpM. Ran it again and got this: https://pastebin.com/sXQyt8qp

I originally posted this on the Nextcloud forums and was advised to post here instead.

Server configuration

Operating system: Linux ******** 4.4.0-79-generic #100-Ubuntu SMP Wed May 17 19:58:14 UTC 2017 x86_64

Web server: Apache/2.4.18 (Ubuntu) (apache2handler)

Database: mysql 5.7.18

PHP version: 7.0.15-0ubuntu0.16.04.4
Modules loaded: Core, date, libxml, openssl, pcre, zlib, filter, hash, Reflection, SPL, session, standard, apache2handler, mysqlnd, PDO, xml, apcu, bz2, calendar, ctype, curl, dom, mbstring, fileinfo, ftp, gd, gettext, iconv, imagick, intl, json, exif, mcrypt, mysqli, pdo_mysql, Phar, posix, readline, shmop, SimpleXML, sockets, sysvmsg, sysvsem, sysvshm, tokenizer, wddx, xmlreader, xmlwriter, xsl, zip, Zend OPcache

Nextcloud version: 11.0.2 (stable) - 11.0.2.7

Updated from an older Nextcloud/ownCloud or fresh install: Fresh install

Where did you install Nextcloud from: Package

Signing status:

Signing status

[]

List of activated apps:

App list

Enabled:

• activity: 2.4.1
• comments: 1.1.0
• dav: 1.1.1
• encryption: 1.4.1
• federatedfilesharing: 1.1.1
• federation: 1.1.1
• files: 1.6.1
• files_external: 1.1.2
• files_pdfviewer: 1.0.1
• files_sharing: 1.1.1
• files_texteditor: 2.2
• files_trashbin: 1.1.0
• files_versions: 1.4.0
• files_videoplayer: 1.0.0
• firstrunwizard: 2.0
• gallery: 16.0.0
• issuetemplate: 0.2.1
• logreader: 2.0.0
• lookup_server_connector: 1.0.0
• nextcloud_announcements: 1.0
• notifications: 1.0.1
• password_policy: 1.1.0
• provisioning_api: 1.1.0
• serverinfo: 1.1.1
• sharebymail: 1.0.1
• survey_client: 0.1.5
• systemtags: 1.1.3
• theming: 1.1.1
• twofactor_backupcodes: 1.0.0
• updatenotification: 1.1.1
• workflowengine: 1.1.1

Disabled:

• admin_audit
• external
• files_accesscontrol
• files_automatedtagging
• files_retention
• templateeditor
• user_external
• user_ldap
• user_saml

The content of config/config.php:

Config report

{
    "instanceid": "ocvpj7ulz4ys",
    "passwordsalt": "***REMOVED SENSITIVE VALUE***",
    "secret": "***REMOVED SENSITIVE VALUE***",
    "trusted_domains": [
        "*******",
        "*******"
    ],
    "datadirectory": "\/mnt\/volume-fra1-01-part1\/nextcloud\/data",
    "overwrite.cli.url": "https:\/\/*******\/nextcloud",
    "dbtype": "mysql",
    "version": "11.0.2.7",
    "dbname": "nextcloud",
    "dbhost": "localhost",
    "dbport": "",
    "dbtableprefix": "oc_",
    "dbuser": "***REMOVED SENSITIVE VALUE***",
    "dbpassword": "***REMOVED SENSITIVE VALUE***",
    "logtimezone": "UTC",
    "installed": true
}

Are you using external storage, if yes which one:

(
    [0] => \OC\Files\Storage\Local
    [1] => \OCA\Files_External\Lib\Storage\FTP
    [2] => \OC\Files\Storage\DAV
    [3] => \OCA\Files_External\Lib\Storage\OwnCloud
    [4] => \OCA\Files_External\Lib\Storage\SFTP
    [5] => \OCA\Files_External\Lib\Storage\AmazonS3
    [6] => \OCA\Files_External\Lib\Storage\Dropbox
    [7] => \OCA\Files_External\Lib\Storage\Google
    [8] => \OCA\Files_External\Lib\Storage\Swift
    [9] => \OCA\Files_External\Lib\Storage\SFTP
)

Are you using encryption: yes

Are you using an external user-backend, if yes which one: LDAP/ActiveDirectory/Webdav/...

Client configuration

Browser: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_5) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.110 Safari/537.36 OPR/45.0.2552.898

Operating system:

Logs

Web server error log

Web server error log

``` Insert your webserver log here ```

Nextcloud log (data/nextcloud.log)

Nextcloud log

``` The file is large and contains some personal information, PM me and I'll be happy to share ```

Browser log

Browser log

``` Insert your browser log here, this could for example include:

a) The javascript console log
b) The network log
c) ...
```

[MorrisJobke]

cc @nextcloud/encryption @nextcloud/sharing @schiessle

[wwbumpas]

Thanks, Morris. I had temporarily given up on this as I'm busy with other things, but I still have the install if you need me to check something else.

[MorrisJobke]

In more recent versions (13.0.4 and 12.0.9) we also reduced drastically the amount of lock statements which should avoid most of the problems in here. Thus I will close it. If there is still stuff that breaks have a look at #9305 and it's linked issues.

Another workaround to make those errors even less likely: use Redis as locking backend