Server: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction

[mxschmitt]

Steps to reproduce

1. Reinstalled fresh nextcloud
2. Idk what I did. My webdav client was syncing files with the official nextcloud client parallel.

Expected behaviour

No error

Actual behaviour

Doctrine\DBAL\Exception\DriverException: An exception occurred while executing 'INSERT INTO `oc_file_locks` (`key`,`lock`,`ttl`) SELECT ?,?,? FROM `oc_file_locks` WHERE `key` = ? HAVING COUNT(*) = 0' with params ["files\/b7aec8354372d9ccd750400d8d03800f", 1, 1508666706, "files\/b7aec8354372d9ccd750400d8d03800f"]: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction
/var/www/cloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php - line 128: Doctrine\DBAL\Driver\AbstractMySQLDriver->convertException('An exception oc...', Object(Doctrine\DBAL\Driver\PDOException))
/var/www/cloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php - line 1015: Doctrine\DBAL\DBALException driverExceptionDuringQuery(Object(Doctrine\DBAL\Driver\PDOMySql\Driver), Object(Doctrine\DBAL\Driver\PDOException), 'INSERT INTO `oc...', Array)
/var/www/cloud/lib/private/DB/Connection.php - line 213: Doctrine\DBAL\Connection->executeUpdate('INSERT INTO `oc...', Array, Array)
/var/www/cloud/lib/private/DB/Adapter.php - line 114: OC\DB\Connection->executeUpdate('INSERT INTO `oc...', Array)
/var/www/cloud/lib/private/DB/Connection.php - line 251: OC\DB\Adapter->insertIfNotExist('*PREFIX*file_lo...', Array, Array)
/var/www/cloud/lib/private/Lock/DBLockingProvider.php - line 118: OC\DB\Connection->insertIfNotExist('*PREFIX*file_lo...', Array, Array)
/var/www/cloud/lib/private/Lock/DBLockingProvider.php - line 163: OC\Lock\DBLockingProvider->initLockField('files/b7aec8354...', 1)
/var/www/cloud/lib/private/Files/Storage/Common.php - line 704: OC\Lock\DBLockingProvider->acquireLock('files/b7aec8354...', 1)
/var/www/cloud/lib/private/Files/Storage/Wrapper/Wrapper.php - line 588: OC\Files\Storage\Common->acquireLock('files/01-Arbeit...', 1, Object(OC\Lock\DBLockingProvider))
/var/www/cloud/lib/private/Files/View.php - line 1908: OC\Files\Storage\Wrapper\Wrapper->acquireLock('files/01-Arbeit...', 1, Object(OC\Lock\DBLockingProvider))
/var/www/cloud/lib/private/Files/View.php - line 2018: OC\Files\View->lockPath('01-Arbeit/01-Be...', 1, false)
/var/www/cloud/lib/private/Files/View.php - line 1309: OC\Files\View->lockFile('01-Arbeit/01-Be...', 1)
/var/www/cloud/lib/private/Files/View.php - line 1356: OC\Files\View->getCacheEntry(Object(OCA\Files_Trashbin\Storage), 'files/01-Arbeit...', '01-Arbeit/01-Be...')
/var/www/cloud/apps/dav/lib/Connector/Sabre/ObjectTree.php - line 160: OC\Files\View->getFileInfo('/maxibanki/file...')
/var/www/cloud/apps/dav/lib/Connector/Sabre/LockPlugin.php - line 59: OCA\DAV\Connector\Sabre\ObjectTree->getNodeForPath('01-Arbeit/01-Be...')
[internal function] OCA\DAV\Connector\Sabre\LockPlugin->getLock(Object(Sabre\HTTP\Request), Object(Sabre\HTTP\Response))
/var/www/cloud/3rdparty/sabre/event/lib/EventEmitterTrait.php - line 105: call_user_func_array(Array, Array)
/var/www/cloud/3rdparty/sabre/dav/lib/DAV/Server.php - line 466: Sabre\Event\EventEmitter->emit('beforeMethod', Array)
/var/www/cloud/3rdparty/sabre/dav/lib/DAV/Server.php - line 254: Sabre\DAV\Server->invokeMethod(Object(Sabre\HTTP\Request), Object(Sabre\HTTP\Response))
/var/www/cloud/apps/dav/appinfo/v1/webdav.php - line 76: Sabre\DAV\Server->exec()
/var/www/cloud/remote.php - line 162: require_once('/var/www/cloud....')
{main}

Server configuration

Operating system: Debian 9.1 PXE:

Web server: nginx/1.10.3

Database: MariaDB

PHP version: 7.1.9

Nextcloud version: 12.0.3

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

Where did you install Nextcloud from: https://download.nextcloud.com/server/releases/nextcloud-12.0.3.zip

Signing status:

Signing status

No errors have been found.

List of activated apps:

App list

Enabled:
  - activity: 2.5.2
  - bruteforcesettings: 1.0.2
  - comments: 1.2.0
  - dav: 1.3.0
  - federatedfilesharing: 1.2.0
  - federation: 1.2.0
  - files: 1.7.2
  - files_pdfviewer: 1.1.1
  - files_sharing: 1.4.0
  - files_texteditor: 2.4.1
  - files_trashbin: 1.2.0
  - files_versions: 1.5.0
  - files_videoplayer: 1.1.0
  - firstrunwizard: 2.1
  - gallery: 17.0.0
  - logreader: 2.0.0
  - lookup_server_connector: 1.0.0
  - nextcloud_announcements: 1.1
  - notifications: 2.0.0
  - oauth2: 1.0.5
  - password_policy: 1.2.2
  - provisioning_api: 1.2.0
  - serverinfo: 1.2.0
  - sharebymail: 1.2.0
  - survey_client: 1.0.0
  - systemtags: 1.2.0
  - theming: 1.3.0
  - twofactor_backupcodes: 1.1.1
  - updatenotification: 1.2.0
  - workflowengine: 1.2.0
Disabled:
  - admin_audit
  - encryption
  - files_external
  - user_external
  - user_ldap

Nextcloud configuration:

Config report

{
    "system": {
        "instanceid": "ocqw4m1v6kk0",
        "passwordsalt": "***REMOVED SENSITIVE VALUE***",
        "secret": "***REMOVED SENSITIVE VALUE***",
        "trusted_domains": [
            "cloud"
        ],
        "datadirectory": "\/var\/www\/cloud\/data",
        "overwrite.cli.url": "https:\/\/cloud",
        "dbtype": "mysql",
        "version": "12.0.3.3",
        "dbname": "nextcloud",
        "dbhost": "10.10.10.6",
        "dbport": "",
        "dbtableprefix": "oc_",
        "mysql.utf8mb4": true,
        "dbuser": "***REMOVED SENSITIVE VALUE***",
        "dbpassword": "***REMOVED SENSITIVE VALUE***",
        "installed": true,
        "mail_from_address": "cloud",
        "mail_smtpmode": "php",
        "mail_smtpauthtype": "LOGIN",
        "mail_domain": "schmitt.mx"
    }
}

Are you using external storage, if yes which one: local/smb/sftp/.. No.

Are you using encryption: No

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

Client configuration

Browser: Chrome 62

Operating system: Windows 7 Enterprise

Logs

Web server error log

Web server error log

No relevant logs

Nextcloud log (data/nextcloud.log)

Nextcloud log

{
    "reqId": "aGDjWevMFpuYXpWX3mH7",
    "level": 4,
    "time": "2017-10-22T08:38:10+00:00",
    "remoteAddr": "",
    "user": "maxibanki",
    "app": "webdav",
    "method": "PUT",
    "url": "\/remote.php\/webdav\/01-Arbeit\/01-Berufsschule\/00-Archiv\/Steinam-XSLTalt\/.git\/objects\/f1\/08a244c01ab24b81e8a17bf0576734776a3e81",
    "message": "Exception: {\"Exception\":\"Doctrine\\\\DBAL\\\\Exception\\\\DriverException\",\"Message\":\"An exception occurred while executing 'INSERT INTO `oc_file_locks` (`key`,`lock`,`ttl`) SELECT ?,?,? FROM `oc_file_locks` WHERE `key` = ? HAVING COUNT(*) = 0' with params [\\\"files\\\\\\\/3d31700630071b7bfc95805f8a422ab1\\\", 1, 1508665090, \\\"files\\\\\\\/3d31700630071b7bfc95805f8a422ab1\\\"]:\\n\\nSQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction\",\"Code\":0,\"Trace\":\"#0 \\\/var\\\/www\\\/cloud\\\/3rdparty\\\/doctrine\\\/dbal\\\/lib\\\/Doctrine\\\/DBAL\\\/DBALException.php(128): Doctrine\\\\DBAL\\\\Driver\\\\AbstractMySQLDriver->convertException('An exception oc...', Object(Doctrine\\\\DBAL\\\\Driver\\\\PDOException))\\n#1 \\\/var\\\/www\\\/cloud\\\/3rdparty\\\/doctrine\\\/dbal\\\/lib\\\/Doctrine\\\/DBAL\\\/Connection.php(1015): Doctrine\\\\DBAL\\\\DBALException::driverExceptionDuringQuery(Object(Doctrine\\\\DBAL\\\\Driver\\\\PDOMySql\\\\Driver), Object(Doctrine\\\\DBAL\\\\Driver\\\\PDOException), 'INSERT INTO `oc...', Array)\\n#2 \\\/var\\\/www\\\/cloud\\\/lib\\\/private\\\/DB\\\/Connection.php(213): Doctrine\\\\DBAL\\\\Connection->executeUpdate('INSERT INTO `oc...', Array, Array)\\n#3 \\\/var\\\/www\\\/cloud\\\/lib\\\/private\\\/DB\\\/Adapter.php(114): OC\\\\DB\\\\Connection->executeUpdate('INSERT INTO `oc...', Array)\\n#4 \\\/var\\\/www\\\/cloud\\\/lib\\\/private\\\/DB\\\/Connection.php(251): OC\\\\DB\\\\Adapter->insertIfNotExist('*PREFIX*file_lo...', Array, Array)\\n#5 \\\/var\\\/www\\\/cloud\\\/lib\\\/private\\\/Lock\\\/DBLockingProvider.php(118): OC\\\\DB\\\\Connection->insertIfNotExist('*PREFIX*file_lo...', Array, Array)\\n#6 \\\/var\\\/www\\\/cloud\\\/lib\\\/private\\\/Lock\\\/DBLockingProvider.php(163): OC\\\\Lock\\\\DBLockingProvider->initLockField('files\\\/3d3170063...', 1)\\n#7 \\\/var\\\/www\\\/cloud\\\/lib\\\/private\\\/Files\\\/Storage\\\/Common.php(704): OC\\\\Lock\\\\DBLockingProvider->acquireLock('files\\\/3d3170063...', 1)\\n#8 \\\/var\\\/www\\\/cloud\\\/lib\\\/private\\\/Files\\\/Storage\\\/Wrapper\\\/Wrapper.php(588): OC\\\\Files\\\\Storage\\\\Common->acquireLock('files\\\/01-Arbeit...', 1, Object(OC\\\\Lock\\\\DBLockingProvider))\\n#9 \\\/var\\\/www\\\/cloud\\\/lib\\\/private\\\/Files\\\/View.php(1908): OC\\\\Files\\\\Storage\\\\Wrapper\\\\Wrapper->acquireLock('files\\\/01-Arbeit...', 1, Object(OC\\\\Lock\\\\DBLockingProvider))\\n#10 \\\/var\\\/www\\\/cloud\\\/lib\\\/private\\\/Files\\\/View.php(2022): OC\\\\Files\\\\View->lockPath('\\\/01-Arbeit\\\/01-B...', 1)\\n#11 \\\/var\\\/www\\\/cloud\\\/lib\\\/private\\\/Files\\\/View.php(1309): OC\\\\Files\\\\View->lockFile('01-Arbeit\\\/01-Be...', 1)\\n#12 \\\/var\\\/www\\\/cloud\\\/lib\\\/private\\\/Files\\\/View.php(1356): OC\\\\Files\\\\View->getCacheEntry(Object(OCA\\\\Files_Trashbin\\\\Storage), 'files\\\/01-Arbeit...', '01-Arbeit\\\/01-Be...')\\n#13 \\\/var\\\/www\\\/cloud\\\/apps\\\/dav\\\/lib\\\/Connector\\\/Sabre\\\/ObjectTree.php(160): OC\\\\Files\\\\View->getFileInfo('\\\/maxibanki\\\/file...')\\n#14 \\\/var\\\/www\\\/cloud\\\/apps\\\/dav\\\/lib\\\/Connector\\\/Sabre\\\/LockPlugin.php(59): OCA\\\\DAV\\\\Connector\\\\Sabre\\\\ObjectTree->getNodeForPath('01-Arbeit\\\/01-Be...')\\n#15 [internal function]: OCA\\\\DAV\\\\Connector\\\\Sabre\\\\LockPlugin->getLock(Object(Sabre\\\\HTTP\\\\Request), Object(Sabre\\\\HTTP\\\\Response))\\n#16 \\\/var\\\/www\\\/cloud\\\/3rdparty\\\/sabre\\\/event\\\/lib\\\/EventEmitterTrait.php(105): call_user_func_array(Array, Array)\\n#17 \\\/var\\\/www\\\/cloud\\\/3rdparty\\\/sabre\\\/dav\\\/lib\\\/DAV\\\/Server.php(466): Sabre\\\\Event\\\\EventEmitter->emit('beforeMethod', Array)\\n#18 \\\/var\\\/www\\\/cloud\\\/3rdparty\\\/sabre\\\/dav\\\/lib\\\/DAV\\\/Server.php(254): Sabre\\\\DAV\\\\Server->invokeMethod(Object(Sabre\\\\HTTP\\\\Request), Object(Sabre\\\\HTTP\\\\Response))\\n#19 \\\/var\\\/www\\\/cloud\\\/apps\\\/dav\\\/appinfo\\\/v1\\\/webdav.php(76): Sabre\\\\DAV\\\\Server->exec()\\n#20 \\\/var\\\/www\\\/cloud\\\/remote.php(162): require_once('\\\/var\\\/www\\\/cloud....')\\n#21 {main}\",\"File\":\"\\\/var\\\/www\\\/cloud\\\/3rdparty\\\/doctrine\\\/dbal\\\/lib\\\/Doctrine\\\/DBAL\\\/Driver\\\/AbstractMySQLDriver.php\",\"Line\":116}",
    "userAgent": "--",
    "version": "12.0.3.3"
}

Browser log

Not relevant

[blizzz]

@icewind1991

[furai]

I'm affected by same issue, Nextcloud 12.0.3 with MariaDB/nginx and latest php 7.1.

[mduller]

I am also seeing this. Nextcloud 12.0.4 with MariaDB 10.2.10, PHP 7.1.12. Redis enabled for caching and locking.

[snozberries-ln]

This is affecting a fresh install using Nextcloud VM for me as well. I've seen that transaction isolated has been the cause of this message in the past, but mine is set to READ-COMMITED.

[enoch85]

but mine is set to READ-COMMITED.

Yup, the VM is by the book. :)

[snozberries-ln]

I had to set mine to READ-COMMITTED manually.

Has anyone figured out the cause and fix?

[enoch85]

I had to set mine to READ-COMMITTED manually.

Hmm, that shouldn't be the case.

[Greek64]

I too am getting Deadlock errors consistently by using the nextcloud client.

Operating system: Nextcloud Docker
Nextcloud version: 12.0.5
PHP version: 7.1.13
Database: MariaDB 10.2.12

I have also enabled File Locking via Redis.

The interesting thing is, that when I try to find the root cause of the Deadlock, the mysql database has not detected any deadlocks.
Normally executing SHOW ENGINE INNODB STATUS in the mysql database, outputs a section called "LATEST DETECTED DEADLOCK", which analyzes the two Quaries that lead to the Deadlock. But my database does not detect (or better said report) any Deadlock that is caused by the nextcloud client syncing.
On the other hand, when I try to make a manual Deadlock, it is reported as expected.

Can anybody else test if their database detects/reports the Deadlock?

[snozberries-ln]

Hey Greek64,

Same for me - there's no entry in LATEST DETECTED DEADLOCK.

I'm wondering if this issue has something to do with READ_COMMITED being incompatible with row-based logging:

https://dev.mysql.com/doc/refman/5.7/en/binary-log-setting.html
If you are using InnoDB tables and the transaction isolation level is READ COMMITTED or READ UNCOMMITTED, only row-based logging can be used. It is possible to change the logging format to STATEMENT, but doing so at runtime leads very rapidly to errors because InnoDB can no longer perform inserts.

Reading the Nextcloud docco, it's pretty straight up that READ COMMITTED is the only way to go:

https://docs.nextcloud.com/server/14/admin_manual/configuration_database/linux_database_configuration.html#db-binlog-label As discussed above Nextcloud is using the TRANSACTION_READ_COMMITTED transaction isolation level.

The other is to change the BINLOG_FORMAT = STATEMENT in your database configuration file, or possibly in your database startup script, to BINLOG_FORMAT = MIXED.

I might try disabling binary logging altogether and see how that goes. Will keep you posted.

[Greek64]

@snozberries-ln
Yes, you have to disable/modify binary logging.
On my Database it was disabled by default, so I didn't think to mention it.

[snozberries-ln]

I installed nextcloud using @enoch85's VM - which is really well put together and has been largely hands-free, aside from this minor issue (ty enoch for maintaining the repo!).

With binary logging enabled (which it was by default), I could trigger the issue very easily by copying a folder with ~150 odd photos into a Nextcloud sync folder. The photos would be uploaded, but a few files would not be saved by Nextcloud due to the deadlock error and would be re-uploaded. That's fine for photos that are a couple of MB, but would be murder for large videos or other files that are a couple of GB in size.

Disabling the binary logging has helped massively. I'm still getting the error, but it only triggers after uploading ~2,000 odd files. So binary logging wasn't the culprit, but disabling it has reduced the frequency of the deadlocks.

I'm a DB noob (hence using the Nextcloud VM auto-build repo), so I'm not exactly sure what to troubleshoot next...

[fractos]

Just hit this problem with NextCloud v12.0.5 backed by MySql 5.6.35 on Amazon RDS. In this case it is not an option to turn off binary logging as that appears to be a requirement for automated snapshot backup to operate.

I'm going to have to see if we can use a containerised version of MySql instead so that we have control of the binary logging and schedule sql dumps as backups instead.

This is obviously _really_ far from ideal.

------------------------
LATEST DETECTED DEADLOCK
------------------------
2018-04-27 13:20:05 2b10e63fa700
*** (1) TRANSACTION:
TRANSACTION 18854137, ACTIVE 0 sec inserting
mysql tables in use 2, locked 1
LOCK WAIT 3 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 1143054, OS thread handle 0x2b10fef03700, query id 12906640 10.0.1.60 archivematica executing
INSERT INTO `oc_filecache` (`mimepart`,`mimetype`,`mtime`,`size`,`etag`,`storage_mtime`,`permissions`,`checksum`,`path_hash`,`path`,`parent`,`name`,`storage`) SELECT '1','2','1524745799','-1','5ae3238520699','1524745799','23','','d41d8cd98f00b204e9800998ecf8427e','','-1','','8' FROM `oc_filecache` WHERE `storage` = '8' AND `path_hash` = 'd41d8cd98f00b204e9800998ecf8427e' HAVING COUNT(*) = 0
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 645 page no 4 n bits 112 index `fs_storage_path_hash` of table `NEXTCLOUD`.`oc_filecache` trx id 18854137 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 40 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000009; asc     ;;
 1: len 30; hex 306163393366383431313734373866373962306666653663653935326131; asc 0ac93f84117478f79b0ffe6ce952a1; (total 32 bytes);
 2: len 4; hex 80000027; asc    ';;

*** (2) TRANSACTION:
TRANSACTION 18853864, ACTIVE 2 sec setting auto-inc lock
mysql tables in use 2, locked 2
7 lock struct(s), heap size 1184, 47 row lock(s), undo log entries 37
MySQL thread id 1143053, OS thread handle 0x2b10e63fa700, query id 12906644 10.0.1.60 archivematica executing
INSERT INTO `oc_filecache` (`mimepart`,`mimetype`,`mtime`,`size`,`etag`,`storage_mtime`,`permissions`,`parent`,`checksum`,`path_hash`,`path`,`name`,`storage`) SELECT '3','9','1513616097','334066','ac824ddf86a0d72a14f203d719e3c58d','1513616097','27','39','','8848486f593b029ff33429a373c1a74d','data/RCM-reimport/10.1016/j.puhe.2015.12.005/PH16 v01.pdf','PH16 v01.pdf','9' FROM `oc_filecache` WHERE `storage` = '9' AND `path_hash` = '8848486f593b029ff33429a373c1a74d' HAVING COUNT(*) = 0
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 645 page no 4 n bits 80 index `fs_storage_path_hash` of table `NEXTCLOUD`.`oc_filecache` trx id 18853864 lock mode S locks gap before rec

[warnerbryce]

I'm having the same problem:
Nextcloud 13.0.2.1
PHP 7.0.28
MariaDB 10.2
Binary Log disabled
Redis 4.0.9 (if i disable it, the problem is still there)

I see deadlocks in the nextcloud logs but not in the Mysql command SHOW ENGINE INNODB STATUS

[MorrisJobke]

In more recent versions (13.0.4 and 12.0.9) we 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: use Redis as locking backend

[fractos]

@morrisjobke this was while using redis for locking.

[mduller]

@MorrisJobke same here as @fractos - having this issue despite using redis for locking

[MorrisJobke]

@fractos @mduller Thanks for the feedback. Also redis got hammered with requests before those versions and just was less likely to hit this problem. So it should be better by now. Still there seem to be some race conditions for SQL based locking: #9305 and Redis #8239

Let's continue in them just to get all the information in one place.

Thanks :)

[mduller]

I have been following #8239 and the recent improvements in locking. Version 13.0.5 still did not resolve the issues I observe with clients that upload in parallel. Files are silently not successfully uploaded to the Nextcloud server, i.e., there is no client-visible error message and the user is left with the impression that his files were uploaded successfully - effectively resulting in data loss.

I have conducted the following tests on an idle system. No other clients were uploading files. The tests were conducted sequentially on the test client machine, which is on the same Gigabit Ethernet switch as the Nextcloud server. The only parallelism (if any) originated from the application running on the test client machine itself. The client machine is running Windows 10 with Firefox 61.0.1. I have also confirmed binlog_format is MIXED and tx_isolation is READ-COMMITED. Redis is used for caching and locking.

Firefox:

• uploading 29 photos (303MB) using drag-and-drop from Windows Explorer: only 26 files are uploaded but no error is displayed in Firefox (no message on top of window).
• uploading 82 photos (1.12GB) using drag-and-drop from Windows Explorer: only 54 files are uploaded but no error is displayed in Firefox (no message on top of window).

Hopic Explorer (uploads files in parallel):

• uploading 73 photos (745MB) using "Upload Files" menu option: only 61 files are uploaded but no error is displayed in Hopic Explorer.

Beyond Compare 4.2 (it's built-in WebDAV support appears to upload one file at a time):

• uploading 73 photos (745MB) using "Upload Files" menu option: all 73 files are uploaded correctly.

Errors in Nextcloud log seen for files missing in Firefox uploads:

{"reqId":"7kwlgSk3S0aEt1fd80Jr","level":4,"time":"2018-08-12T01:01:05+00:00","remoteAddr":"192.168.0.192","user":"XXXX","app":"webdav","method":"MKCOL","url":"\/remote.php\/dav\/uploads\/XXXX\/web-file-upload-843796611fd5e0a770e34465b5befb0f-1534035667906","message":"Exception: {\"Exception\":\"Doctrine\\\\DBAL\\\\Exception\\\\DriverException\",\"Message\":\"An exception occurred while executing 'INSERT INTO `oc_filecache` (`mimepart`,`mimetype`,`mtime`,`size`,`etag`,`storage_mtime`,`permissions`,`parent`,`checksum`,`path_hash`,`path`,`name`,`storage`) SELECT ?,?,?,?,?,?,?,?,?,?,?,?,? FROM `oc_filecache` WHERE `storage` = ? AND `path_hash` = ? HAVING COUNT(*) = 0' with params [\\\"1\\\", \\\"2\\\", 1534035665, -1, \\\"5b6f86d1000dd\\\", 1534035665, 31, 71596, \\\"\\\", \\\"1b954622a27d0ec0d0e28f761527d56d\\\", \\\"uploads\\\\\\\/web-file-upload-843796611fd5e0a770e34465b5befb0f-1534035667906\\\", \\\"web-file-upload-843796611fd5e0a770e34465b5befb0f-1534035667906\\\", 1, 1, \\\"1b954622a27d0ec0d0e28f761527d56d\\\"]:\\n\\nSQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction\",\"Code\":0,\"Trace\":\"#0 \\\/usr\\\/local\\\/www\\\/apache24\\\/data\\\/nextcloud\\\/3rdparty\\\/doctrine\\\/dbal\\\/lib\\\/Doctrine\\\/DBAL\\\/DBALException.php(128): Doctrine\\\\DBAL\\\\Driver\\\\AbstractMySQLDriver->convertException('An exception oc...', Object(Doctrine\\\\DBAL\\\\Driver\\\\PDOException))\\n#1 \\\/usr\\\/local\\\/www\\\/apache24\\\/data\\\/nextcloud\\\/3rdparty\\\/doctrine\\\/dbal\\\/lib\\\/Doctrine\\\/DBAL\\\/Connection.php(1015): Doctrine\\\\DBAL\\\\DBALException::driverExceptionDuringQuery(Object(Doctrine\\\\DBAL\\\\Driver\\\\PDOMySql\\\\Driver), Object(Doctrine\\\\DBAL\\\\Driver\\\\PDOException), 'INSERT INTO `oc...', Array)\\n#2 \\\/usr\\\/local\\\/www\\\/apache24\\\/data\\\/nextcloud\\\/lib\\\/private\\\/DB\\\/Connection.php(216): Doctrine\\\\DBAL\\\\Connection->executeUpdate('INSERT INTO `oc...', Array, Array)\\n#3 \\\/usr\\\/local\\\/www\\\/apache24\\\/data\\\/nextcloud\\\/lib\\\/private\\\/DB\\\/Adapter.php(114): OC\\\\DB\\\\Connection->executeUpdate('INSERT INTO `oc...', Array)\\n#4 \\\/usr\\\/local\\\/www\\\/apache24\\\/data\\\/nextcloud\\\/lib\\\/private\\\/DB\\\/Connection.php(254): OC\\\\DB\\\\Adapter->insertIfNotExist('*PREFIX*filecac...', Array, Array)\\n#5 \\\/usr\\\/local\\\/www\\\/apache24\\\/data\\\/nextcloud\\\/lib\\\/private\\\/Files\\\/Cache\\\/Cache.php(273): OC\\\\DB\\\\Connection->insertIfNotExist('*PREFIX*filecac...', Array, Array)\\n#6 \\\/usr\\\/local\\\/www\\\/apache24\\\/data\\\/nextcloud\\\/lib\\\/private\\\/Files\\\/Cache\\\/Cache.php(230): OC\\\\Files\\\\Cache\\\\Cache->insert('uploads\\\/web-fil...', Array)\\n#7 \\\/usr\\\/local\\\/www\\\/apache24\\\/data\\\/nextcloud\\\/lib\\\/private\\\/Files\\\/Cache\\\/Scanner.php(292): OC\\\\Files\\\\Cache\\\\Cache->put('uploads\\\/web-fil...', Array)\\n#8 \\\/usr\\\/local\\\/www\\\/apache24\\\/data\\\/nextcloud\\\/lib\\\/private\\\/Files\\\/Cache\\\/Scanner.php(221): OC\\\\Files\\\\Cache\\\\Scanner->addToCache('uploads\\\/web-fil...', Array, -1)\\n#9 \\\/usr\\\/local\\\/www\\\/apache24\\\/data\\\/nextcloud\\\/lib\\\/private\\\/Files\\\/Cache\\\/Scanner.php(336): OC\\\\Files\\\\Cache\\\\Scanner->scanFile('uploads\\\/web-fil...', 3, 71596, false, false)\\n#10 \\\/usr\\\/local\\\/www\\\/apache24\\\/data\\\/nextcloud\\\/lib\\\/private\\\/Files\\\/Cache\\\/Updater.php(124): OC\\\\Files\\\\Cache\\\\Scanner->scan('uploads\\\/web-fil...', false, 3, false)\\n#11 \\\/usr\\\/local\\\/www\\\/apache24\\\/data\\\/nextcloud\\\/lib\\\/private\\\/Files\\\/View.php(319): OC\\\\Files\\\\Cache\\\\Updater->update('uploads\\\/web-fil...', 1534035665)\\n#12 \\\/usr\\\/local\\\/www\\\/apache24\\\/data\\\/nextcloud\\\/lib\\\/private\\\/Files\\\/View.php(1163): OC\\\\Files\\\\View->writeUpdate(Object(OCA\\\\Files_Trashbin\\\\Storage), 'uploads\\\/web-fil...')\\n#13 \\\/usr\\\/local\\\/www\\\/apache24\\\/data\\\/nextcloud\\\/lib\\\/private\\\/Files\\\/View.php(267): OC\\\\Files\\\\View->basicOperation('mkdir', '\\\/web-file-uploa...', Array)\\n#14 \\\/usr\\\/local\\\/www\\\/apache24\\\/data\\\/nextcloud\\\/apps\\\/dav\\\/lib\\\/Connector\\\/Sabre\\\/Directory.php(189): OC\\\\Files\\\\View->mkdir('\\\/\\\/web-file-uplo...')\\n#15 \\\/usr\\\/local\\\/www\\\/apache24\\\/data\\\/nextcloud\\\/apps\\\/dav\\\/lib\\\/Upload\\\/UploadHome.php(47): OCA\\\\DAV\\\\Connector\\\\Sabre\\\\Directory->createDirectory('web-file-upload...')\\n#16 \\\/usr\\\/local\\\/www\\\/apache24\\\/data\\\/nextcloud\\\/3rdparty\\\/sabre\\\/dav\\\/lib\\\/DAV\\\/Server.php(1210): OCA\\\\DAV\\\\Upload\\\\UploadHome->createDirectory('web-file-upload...')\\n#17 \\\/usr\\\/local\\\/www\\\/apache24\\\/data\\\/nextcloud\\\/3rdparty\\\/sabre\\\/dav\\\/lib\\\/DAV\\\/CorePlugin.php(594): Sabre\\\\DAV\\\\Server->createCollection('uploads\\\/XXXX\\\/we...', Object(Sabre\\\\DAV\\\\MkCol))\\n#18 [internal function]: Sabre\\\\DAV\\\\CorePlugin->httpMkcol(Object(Sabre\\\\HTTP\\\\Request), Object(Sabre\\\\HTTP\\\\Response))\\n#19 \\\/usr\\\/local\\\/www\\\/apache24\\\/data\\\/nextcloud\\\/3rdparty\\\/sabre\\\/event\\\/lib\\\/EventEmitterTrait.php(105): call_user_func_array(Array, Array)\\n#20 \\\/usr\\\/local\\\/www\\\/apache24\\\/data\\\/nextcloud\\\/3rdparty\\\/sabre\\\/dav\\\/lib\\\/DAV\\\/Server.php(479): Sabre\\\\Event\\\\EventEmitter->emit('method:MKCOL', Array)\\n#21 \\\/usr\\\/local\\\/www\\\/apache24\\\/data\\\/nextcloud\\\/3rdparty\\\/sabre\\\/dav\\\/lib\\\/DAV\\\/Server.php(254): Sabre\\\\DAV\\\\Server->invokeMethod(Object(Sabre\\\\HTTP\\\\Request), Object(Sabre\\\\HTTP\\\\Response))\\n#22 \\\/usr\\\/local\\\/www\\\/apache24\\\/data\\\/nextcloud\\\/apps\\\/dav\\\/lib\\\/Server.php(287): Sabre\\\\DAV\\\\Server->exec()\\n#23 \\\/usr\\\/local\\\/www\\\/apache24\\\/data\\\/nextcloud\\\/apps\\\/dav\\\/appinfo\\\/v2\\\/remote.php(35): OCA\\\\DAV\\\\Server->exec()\\n#24 \\\/usr\\\/local\\\/www\\\/apache24\\\/data\\\/nextcloud\\\/remote.php(164): require_once('\\\/usr\\\/local\\\/www\\\/...')\\n#25 {main}\",\"File\":\"\\\/usr\\\/local\\\/www\\\/apache24\\\/data\\\/nextcloud\\\/3rdparty\\\/doctrine\\\/dbal\\\/lib\\\/Doctrine\\\/DBAL\\\/Driver\\\/AbstractMySQLDriver.php\",\"Line\":115}","userAgent":"Mozilla\/5.0 (Windows NT 10.0; Win64; x64; rv:61.0) Gecko\/20100101 Firefox\/61.0","version":"13.0.5.2"}

Errors in Nextcloud log seen for files missing in Hopic Explorer uploads:

{"reqId":"Aao8sJreukQ0aaiL2Liw","level":4,"time":"2018-08-12T01:36:20+00:00","remoteAddr":"192.168.0.192","user":"XXXX","app":"webdav","method":"PUT","url":"\/remote.php\/webdav\/XXXX\/Photos\/Inbox\/_manual%20uploads\/2018-08\/Sony\/DSC07637.JPG","message":"Exception: {\"Exception\":\"Doctrine\\\\DBAL\\\\Exception\\\\DriverException\",\"Message\":\"An exception occurred while executing 'INSERT INTO `oc_filecache` (`mimepart`,`mimetype`,`mtime`,`size`,`etag`,`storage_mtime`,`permissions`,`parent`,`checksum`,`path_hash`,`path`,`name`,`storage`) SELECT ?,?,?,?,?,?,?,?,?,?,?,?,? FROM `oc_filecache` WHERE `storage` = ? AND `path_hash` = ? HAVING COUNT(*) = 0' with params [\\\"10\\\", \\\"11\\\", 1534037780, 12713984, \\\"14480b4d8c77af91ebc2fef389cdd97a\\\", 1534037780, 27, 143409, \\\"\\\", \\\"49ca5cdb0d13ed51a8165287f1b63255\\\", \\\"files\\\\\\\/XXXX\\\\\\\/Photos\\\\\\\/Inbox\\\\\\\/_manual uploads\\\\\\\/2018-08\\\\\\\/Sony\\\\\\\/DSC07637.JPG\\\", \\\"DSC07637.JPG\\\", 1, 1, \\\"49ca5cdb0d13ed51a8165287f1b63255\\\"]:\\n\\nSQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction\",\"Code\":0,\"Trace\":\"#0 \\\/usr\\\/local\\\/www\\\/apache24\\\/data\\\/nextcloud\\\/3rdparty\\\/doctrine\\\/dbal\\\/lib\\\/Doctrine\\\/DBAL\\\/DBALException.php(128): Doctrine\\\\DBAL\\\\Driver\\\\AbstractMySQLDriver->convertException('An exception oc...', Object(Doctrine\\\\DBAL\\\\Driver\\\\PDOException))\\n#1 \\\/usr\\\/local\\\/www\\\/apache24\\\/data\\\/nextcloud\\\/3rdparty\\\/doctrine\\\/dbal\\\/lib\\\/Doctrine\\\/DBAL\\\/Connection.php(1015): Doctrine\\\\DBAL\\\\DBALException::driverExceptionDuringQuery(Object(Doctrine\\\\DBAL\\\\Driver\\\\PDOMySql\\\\Driver), Object(Doctrine\\\\DBAL\\\\Driver\\\\PDOException), 'INSERT INTO `oc...', Array)\\n#2 \\\/usr\\\/local\\\/www\\\/apache24\\\/data\\\/nextcloud\\\/lib\\\/private\\\/DB\\\/Connection.php(216): Doctrine\\\\DBAL\\\\Connection->executeUpdate('INSERT INTO `oc...', Array, Array)\\n#3 \\\/usr\\\/local\\\/www\\\/apache24\\\/data\\\/nextcloud\\\/lib\\\/private\\\/DB\\\/Adapter.php(114): OC\\\\DB\\\\Connection->executeUpdate('INSERT INTO `oc...', Array)\\n#4 \\\/usr\\\/local\\\/www\\\/apache24\\\/data\\\/nextcloud\\\/lib\\\/private\\\/DB\\\/Connection.php(254): OC\\\\DB\\\\Adapter->insertIfNotExist('*PREFIX*filecac...', Array, Array)\\n#5 \\\/usr\\\/local\\\/www\\\/apache24\\\/data\\\/nextcloud\\\/lib\\\/private\\\/Files\\\/Cache\\\/Cache.php(273): OC\\\\DB\\\\Connection->insertIfNotExist('*PREFIX*filecac...', Array, Array)\\n#6 \\\/usr\\\/local\\\/www\\\/apache24\\\/data\\\/nextcloud\\\/lib\\\/private\\\/Files\\\/Cache\\\/Cache.php(230): OC\\\\Files\\\\Cache\\\\Cache->insert('files\\\/XXXX\\\/Phot...', Array)\\n#7 \\\/usr\\\/local\\\/www\\\/apache24\\\/data\\\/nextcloud\\\/lib\\\/private\\\/Files\\\/Cache\\\/Scanner.php(292): OC\\\\Files\\\\Cache\\\\Cache->put('files\\\/XXXX\\\/Phot...', Array)\\n#8 \\\/usr\\\/local\\\/www\\\/apache24\\\/data\\\/nextcloud\\\/lib\\\/private\\\/Files\\\/Cache\\\/Scanner.php(221): OC\\\\Files\\\\Cache\\\\Scanner->addToCache('files\\\/XXXX\\\/Phot...', Array, -1)\\n#9 \\\/usr\\\/local\\\/www\\\/apache24\\\/data\\\/nextcloud\\\/lib\\\/private\\\/Files\\\/Cache\\\/Scanner.php(336): OC\\\\Files\\\\Cache\\\\Scanner->scanFile('files\\\/XXXX\\\/Phot...', 3, 143409, false, false)\\n#10 \\\/usr\\\/local\\\/www\\\/apache24\\\/data\\\/nextcloud\\\/lib\\\/private\\\/Files\\\/Cache\\\/Updater.php(124): OC\\\\Files\\\\Cache\\\\Scanner->scan('files\\\/XXXX\\\/Phot...', false, 3, false)\\n#11 \\\/usr\\\/local\\\/www\\\/apache24\\\/data\\\/nextcloud\\\/apps\\\/dav\\\/lib\\\/Connector\\\/Sabre\\\/File.php(232): OC\\\\Files\\\\Cache\\\\Updater->update('files\\\/XXXX\\\/Phot...')\\n#12 \\\/usr\\\/local\\\/www\\\/apache24\\\/data\\\/nextcloud\\\/apps\\\/dav\\\/lib\\\/Connector\\\/Sabre\\\/Directory.php(156): OCA\\\\DAV\\\\Connector\\\\Sabre\\\\File->put(Resource id #8)\\n#13 \\\/usr\\\/local\\\/www\\\/apache24\\\/data\\\/nextcloud\\\/3rdparty\\\/sabre\\\/dav\\\/lib\\\/DAV\\\/Server.php(1096): OCA\\\\DAV\\\\Connector\\\\Sabre\\\\Directory->createFile('DSC07637.JPG', Resource id #8)\\n#14 \\\/usr\\\/local\\\/www\\\/apache24\\\/data\\\/nextcloud\\\/3rdparty\\\/sabre\\\/dav\\\/lib\\\/DAV\\\/CorePlugin.php(529): Sabre\\\\DAV\\\\Server->createFile('XXXX\\\/Photos\\\/Inb...', Resource id #8, NULL)\\n#15 [internal function]: Sabre\\\\DAV\\\\CorePlugin->httpPut(Object(Sabre\\\\HTTP\\\\Request), Object(Sabre\\\\HTTP\\\\Response))\\n#16 \\\/usr\\\/local\\\/www\\\/apache24\\\/data\\\/nextcloud\\\/3rdparty\\\/sabre\\\/event\\\/lib\\\/EventEmitterTrait.php(105): call_user_func_array(Array, Array)\\n#17 \\\/usr\\\/local\\\/www\\\/apache24\\\/data\\\/nextcloud\\\/3rdparty\\\/sabre\\\/dav\\\/lib\\\/DAV\\\/Server.php(479): Sabre\\\\Event\\\\EventEmitter->emit('method:PUT', Array)\\n#18 \\\/usr\\\/local\\\/www\\\/apache24\\\/data\\\/nextcloud\\\/3rdparty\\\/sabre\\\/dav\\\/lib\\\/DAV\\\/Server.php(254): Sabre\\\\DAV\\\\Server->invokeMethod(Object(Sabre\\\\HTTP\\\\Request), Object(Sabre\\\\HTTP\\\\Response))\\n#19 \\\/usr\\\/local\\\/www\\\/apache24\\\/data\\\/nextcloud\\\/apps\\\/dav\\\/appinfo\\\/v1\\\/webdav.php(80): Sabre\\\\DAV\\\\Server->exec()\\n#20 \\\/usr\\\/local\\\/www\\\/apache24\\\/data\\\/nextcloud\\\/remote.php(164): require_once('\\\/usr\\\/local\\\/www\\\/...')\\n#21 {main}\",\"File\":\"\\\/usr\\\/local\\\/www\\\/apache24\\\/data\\\/nextcloud\\\/3rdparty\\\/doctrine\\\/dbal\\\/lib\\\/Doctrine\\\/DBAL\\\/Driver\\\/AbstractMySQLDriver.php\",\"Line\":115}","userAgent":"Mozilla\/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit\/537.36 (KHTML, like Gecko) Chrome\/64.0.3282.140 Safari\/537.36 Edge\/17.17134","version":"13.0.5.2"}

Please advise what additional information I can provide. I would really like to get this issue resolved as the current situation with de-facto data loss does not allow me to finally move to Nextcloud.

---

Michael

[mduller]

Two more details I forgot to add:

• like @warnerbryce I do see this deadlocks in the NC log but not in SHOW ENGINE INNODB STATUS (which I assume makes sense since I use redis for locking?)
• these deadlocks occur not only during uploading of files but also when simply browsing a folder and the thumbs are generated:

Doctrine\DBAL\Exception\DriverException: An  exception occurred while executing 'INSERT INTO `oc_filecache`  (`mimepart`,`mimetype`,`mtime`,`size`,`etag`,`storage_mtime`,`permissions`,`parent`,`checksum`,`path_hash`,`path`,`name`,`storage`)  SELECT ?,?,?,?,?,?,?,?,?,?,?,?,? FROM `oc_filecache` WHERE `storage` = ?  AND `path_hash` = ? HAVING COUNT(*) = 0' with params ["10", "11",  1534135612, 0, "2a111bc5c5fc82a09cd403dbb44df7ff", 1534135612, 27,  145021, "", "b8348ff37a1ad95ec8c5fb153b0e0d8d",  "appdata_oc6f6b0pnmee\/preview\/144981\/32-32-crop.jpg",  "32-32-crop.jpg", 2, 2, "b8348ff37a1ad95ec8c5fb153b0e0d8d"]:  SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying  to get lock; try restarting transaction

[warnerbryce]

I have this issue even without redis. It’s not a redis related issue i think

[warnerbryce]

Thanks for reopening it

[mduller]

Thanks @rullzer for reopening. Please let me know if there is anything I can collect, test, etc. that helps. I just dragged 18 files (712MB) from Explorer to NC in Firefox and only 4 made it. This even worse result (compared to my experiments described above) is likely due to an increase of php-fpm workers (it was set very low, at max. 5 workers, now it's max. 16).

[ghost]

I get the same error

Server configuration
Operating system: Ubuntu Server 16.04 LTS

Webserver: Apatche2

Database: MYSQL Version: 5.7.23

PHP Version 7.1.20

NC Version 13.0.5

[Redsandro]

I think I'm having the same issue.

Error transferring foo.xyz - server replied: Internal Server Error (An exception occurred while executing 'INSERT INTO `file_locks` (`key`,`lock`,`ttl`) SELECT ?,?,? FROM `file_locks` WHERE `key` = ? HAVING COUNT(*) = 0' with params ["files\/7afd116755b4b56ae2ff69d3d9ded168", -1, 1534881944, "files\/7afd116755b4b56ae2ff69d3d9ded168"]:

SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction)

Error transferring foo.xyz - server replied: Internal Server Error (An exception occurred while executing 'INSERT INTO `file_locks` (`key`,`lock`,`ttl`) SELECT ?,?,? FROM `file_locks` WHERE `key` = ? HAVING COUNT(*) = 0' with params ["files\/2e01076200b4857330c582d44830bc7b", -1, 1534882118, "files\/2e01076200b4857330c582d44830bc7b"]:

SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction)

Error transferring foo.xyz - server replied: Internal Server Error (An exception occurred while executing 'INSERT INTO `file_locks` (`key`,`lock`,`ttl`) SELECT ?,?,? FROM `file_locks` WHERE `key` = ? HAVING COUNT(*) = 0' with params ["files\/80d4fa4c193d20a8476037db69301c4b", -1, 1534882118, "files\/80d4fa4c193d20a8476037db69301c4b"]:

SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction)

Error transferring foo.xyz - server replied: Internal Server Error (An exception occurred while executing 'INSERT INTO `file_locks` (`key`,`lock`,`ttl`) SELECT ?,?,? FROM `file_locks` WHERE `key` = ? HAVING COUNT(*) = 0' with params ["files\/6cd05adfc586787c6cbe40f9854b765e", -1, 1534882123, "files\/6cd05adfc586787c6cbe40f9854b765e"]:

SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction)

Error transferring foo.xyz - server replied: Internal Server Error

Error transferring foo.xyz - server replied: Internal Server Error (An exception occurred while executing 'INSERT INTO `filecache` (`mimepart`,`mimetype`,`mtime`,`size`,`etag`,`storage_mtime`,`permissions`,`parent`,`checksum`,`path_hash`,`path`,`name`,`storage`) SELECT ?,?,?,?,?,?,?,?,?,?,?,?,? FROM `filecache` WHERE `storage` = ? AND `path_hash` = ? HAVING COUNT(*) = 0' with params ["3", "14", 1534878624, 73728, "3073bc2ebfa82e2e5f1ff3262aab7014", 1534878624, 27, 1985, "", "01d4e33459016e74c0b4af07fb8dc821", "files\/somedir\/foo.xyz", "foo.xyz", 2, 2, "01d4e33459016e74c0b4af07fb8dc821"]:

SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction)

Error transferring foo.xyz - server replied: Internal Server Error (An exception occurred while executing 'INSERT INTO `file_locks` (`key`,`lock`,`ttl`) SELECT ?,?,? FROM `file_locks` WHERE `key` = ? HAVING COUNT(*) = 0' with params ["files\/b5c8526c9db66764325f2b3d9c25c4ac", 1, 1534882264, "files\/b5c8526c9db66764325f2b3d9c25c4ac"]:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'files/b5c8526c9db66764325f2b3d9c25c4ac' for key 'lock_key_index')

Note that the majority of files transfer fine. But this keeps happening. And then the files are blacklisted in the Nextcloud Desktop Client.

(How to retry files that are ignored because of previous errors?) :question:

---

Server configuration

Operating system: Linux 4.9.0-4-amd64 #1 SMP Debian 4.9.65-3+deb9u1 (2017-12-23) x86_64

Webserver: nginx/1.15.2 (fpm-fcgi)

Database: mysql 10.3.9

PHP version: 7.2.8
Modules loaded: Core, date, libxml, openssl, pcre, sqlite3, zlib, ctype, curl, dom, fileinfo, filter, ftp, hash, iconv, json, mbstring, SPL, PDO, session, posix, readline, Reflection, standard, SimpleXML, pdo_sqlite, Phar, tokenizer, xml, xmlreader, xmlwriter, mysqlnd, cgi-fcgi, apcu, exif, gd, intl, ldap, memcached, pcntl, pdo_mysql, pdo_pgsql, redis, sodium, zip, Zend OPcache

Nextcloud version: 13.0.5 - 13.0.5.2

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

Where did you install Nextcloud from: docker-compose

List of activated apps

Enabled:
 - activity: 2.6.1
 - bruteforcesettings: 1.1.0
 - comments: 1.3.0
 - dav: 1.4.7
 - federatedfilesharing: 1.3.1
 - federation: 1.3.0
 - files: 1.8.0
 - files_sharing: 1.5.0
 - files_texteditor: 2.5.1
 - files_trashbin: 1.3.0
 - files_versions: 1.6.0
 - files_videoplayer: 1.2.0
 - firstrunwizard: 2.2.1
 - gallery: 18.0.0
 - issuetemplate: 0.3.0
 - logreader: 2.0.0
 - lookup_server_connector: 1.1.0
 - nextcloud_announcements: 1.2.0
 - notifications: 2.1.2
 - oauth2: 1.1.1
 - password_policy: 1.3.0
 - provisioning_api: 1.3.0
 - serverinfo: 1.3.0
 - sharebymail: 1.3.0
 - survey_client: 1.1.0
 - systemtags: 1.3.0
 - theming: 1.4.5
 - twofactor_backupcodes: 1.2.3
 - updatenotification: 1.3.0
 - workflowengine: 1.3.0
Disabled:
 - admin_audit
 - encryption
 - files_external
 - files_pdfviewer
 - user_external
 - user_ldap

Configuration (config/config.php)

{
    "memcache.local": "\\OC\\Memcache\\APCu",
    "apps_paths": [
        {
            "path": "\/var\/www\/html\/apps",
            "url": "\/apps",
            "writable": false
        },
        {
            "path": "\/var\/www\/html\/custom_apps",
            "url": "\/custom_apps",
            "writable": true
        }
    ],
    "dbtype": "mysql",
    "version": "13.0.5.2",
    "dbport": "",
    "dbtableprefix": "",
    "installed": true,
    "mail_smtpmode": "php",
    "mail_smtpauthtype": "LOGIN",
}

Are you using external storage, if yes which one: docker volume

Are you using encryption: no

Client configuration

Browser: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/70.0.3521.2 Safari/537.36

Operating system: Linux Mint 19 AKA Ubuntu 18.04

[martinber]

I'm having the same problem using the docker image, this is the first time I try Nextcloud but I made a really clean installation:

Steps to reproduce

1. Install Debian Stretch, Docker and docker-compose
2. Deploy a Nextcloud instance and a MariaDB following this official guide
3. Run it and upload lots of files using a PC client (I was uploading like 20GB of images)

Expected behaviour

The files should upload

Actual behaviour

Some files fail with this error:

Error transferring
https://my.server.com/remote.php/dav/files/myuser/sync/pictures/Camera/2012-11-15_16-15-10_256.jpg
- server replied: Internal Server Error (An exception occurred while
executing 'INSERT INTO `oc_file_locks` (`key`,`lock`,`ttl`) SELECT ?,?,?
FROM `oc_file_locks` WHERE `key` = ? HAVING COUNT(*) = 0' with params
["files\/80b926d5b22c8638f2a74be3882b2758", 1, 1535830501,
"files\/80b926d5b22c8638f2a74be3882b2758"]:

SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get
lock; try restarting transaction)

Server configuration

Operating system: Official docker image on Debian Stretch

Web server: Apache

Database: MariaDB

PHP version: Don't know, I think PHP it's not related to this error.

Nextcloud version: 13.0.6

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

Where did you install Nextcloud from: Official docker image

Signing status:

Signing status

No errors have been found.

List of activated apps:

App list

Default apps only

Nextcloud configuration:

Config report

<?php
$CONFIG = array (
  'htaccess.RewriteBase' => '/',
  'memcache.local' => '\\OC\\Memcache\\APCu',
  'apps_paths' => 
  array (
    0 => 
    array (
      'path' => '/var/www/html/apps',
      'url' => '/apps',
      'writable' => false,
    ),
    1 => 
    array (
      'path' => '/var/www/html/custom_apps',
      'url' => '/custom_apps',
      'writable' => true,
    ),
  ),
  'instanceid' => 'ocqyu2ne1kyt',
  'passwordsalt' => REDACTED,
  'secret' => REDACTED,
  'trusted_domains' => 
  array (
    0 => 'my.domain.com',
  ),
  'datadirectory' => '/var/www/html/data',
  'overwrite.cli.url' => 'https://my.domain.com',
  'dbtype' => 'mysql',
  'version' => '13.0.6.1',
  'dbname' => 'nextcloud',
  'dbhost' => 'db',
  'dbport' => '',
  'dbtableprefix' => 'oc_',
  'mysql.utf8mb4' => true,
  'dbuser' => 'nextcloud',
  'dbpassword' => REDACTED,
  'installed' => true,
);

Are you using external storage, if yes which one: No

Are you using encryption: No

Are you using an external user-backend, if yes which one: No

Client configuration

Browser: Not tested on browser

Operating system: Native client on both Windows and Linux

Logs

Web server error log

Web server error log

Nothing

Nextcloud log (data/nextcloud.log)

Nextcloud log

Already pasted example on top

[th3cube]

+1
I´m having the same Problem like @martinber
Using Nextcloud with Docker and MariaDB but my NC version is 14.0

[Qeynos]

same problem , nc 14.0 and docker install follow the offical guide, and "SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED", "SET sql_log_bin = 0;", the error still appears so many.

[furai]

Easiest way to reproduce on my end is to send multiple files at once (like 10 or more) which are bigger. Maybe it has to do something with chunking the files into smaller pieces for upload.

[adocampo]

Same here, also with dockerized NC + dockerized mariadb.
If anyone want to try, download this zip file from my NC, extract it and
try to upload it to your NC and see it crash.

https://cloud.docampo.net/index.php/s/Ke5HRcy4QcMErZA

[stefer09]

I have the same issue using the linuxserver docker on UNRAID and MariaDB

[mduller]

Thanks @adocampo for the repro. Your archive with "just" 209 files reproduces it nicely for me as well. Screenshot

and log nextcloud.log attached.
NC 13.0.5 on FreeBSD 11.2 with MariaDB 10.2.16 and Redis 4.0.10 (caching and locking).

[adocampo]

You're welcome. I put this because if causes a fail with any method (web, webdav, official NC client, android client) but if you create a much simpler structure with some folders (let's say 20) and several tens of tiny files in there (with touch) it also fails.

I tried also with my holidays photos (tens of directories and subdirectories and thousands files) and none failed. My wife, just uploaded more than 5000 PDFs in a complex structure and I saw no errors either.

So, the tinier the file, the more probable is it will fail. As the error suggest, MySQL is locked. I guess is because it tries to do an INSERT when that table is still locked by the former INSERT.

Don't know if PostgreSQL or Oracle DB have the same problem, it seems MariaDB/MySQL has.

[Redsandro]

I'm out of patience. I was charmed by the many passionate fans and contributors of NextCloud and it's feature set, but NextCloud Server is broken in certain common setups for almost a year now. We can't figure out the problem, and we don't have a solution.

I have transfered all my files to Seafile Server and removed NextCloud server. Seafile _just works™_.

[ak1n]

This has made nextcloud unusable for me as well despite previously running decently for years on freenas.

[adocampo]

Just upgraded to nextcloud 14. Problem still persisted, so I changed from MariaDB to PostgreSQL.
With PostgreSQL and NC14, the problem seems to be solved.
At least the folder I'd put on this thread which _always_ failed with MariaDB, now doesn't fail.
If anyone of you want to try, grab this file: https://cloud.docampo.net/index.php/s/Ke5HRcy4QcMErZA

[mduller]

Thanks @adocampo . I had switching to PostgreSQL on my list of things to try as well but didn't find time yet. You successful report gives me new hope and motivation to finally overcome this issue and get Nextcloud into productive use. I'll try this ASAP and will report back.

[enoch85]

For those interested in merging to PostgreSQL, first of all - GOOD CHOICE! Second, have a look at this: https://www.techandme.se/we-migrated-to-postgresql/ The script is not updated, but maybe you can get some inspiration from it at least.

[warnerbryce]

Thank you. I will give it a try on my personnal cloud first.
I know that Nextcloud recommands mysql/mariadb in order of getting support, because the team didn’t have ressources yet to say : safe to use with postgresql.
So we have to aim about mariadb because this bug is a misconfiguration of the sql server.
I hope it will be take seriously by the dev team because i have this bug on all my instances (6).

[enoch85]

MySQL/MariaDB is recommended because that's what the devs know best. But, there are no issues running PostgreSQL afaik, I've been having less issue with that actually.

[adocampo]

On my crusade to find the culprit, I didn't realized a backup of my calendar events nor my passman passwords. I didn't noticed that passman development was discontinued and unfortunately, the upgrade to NC 14 corrupted the mysql instance somehow and it deleted some tables.
So, don't do like me and backup everything before upgrading from 13 to 14 (if you didn't already).
Well, for my part at least I won't suffer this horrible bug, which in my opinion could be the death of the project itself.

On the other hand, after search on the web for this error concrete, it seems to be present on many mariadb-based applications, so I could think it's an upstream problem. That why it made me suspect of the mariadb engine and made me try PostgreSQL. I would like to test with Oracle MySQL suffers also the deadlocks.

[warnerbryce]

@adocampo So i tested your Registration API folder.
With Mysql/MariaDB instance : Get Serialization Deadlock etc....
With Postgresql instance : Get only two files not sync with reason : locked, but just get a yellow warning, not a big Red Attention icon. And the files gets retry before the end of the sync and then i get no problem.
So MariaDB/Mysql is the culprit.
@enoch85 You should update your Script code for Postgresql 10, no need to add a deb repo with Ubuntu 18.04, and you forget that you need to install php-pqsql to command occ to do the convertion. Thank You

[enoch85]

@warnerbryce You mean the script on the Tech and Me blog?

[warnerbryce]

Absolutly

[adocampo]

As far as I could read, it seems a "normal" operation of InnoDB. In order to write properly the indexes, it locks the database for a few milliseconds. That's why this problem happens more often with tiny files than with greater ones.
That is a bad technique and transactions are recommended instead locks, as per MySQL documentation. I didn't take a look to NC, but I would assume they aren't doing transactions _or_ they aren't handling the rollbacks at all.
https://dev.mysql.com/doc/refman/8.0/en/innodb-deadlocks.html

What fascinates me is, how this bug hasn't been noticed by more users and administrators. I guess it is because it fails silently, which IMO is the worst problem. A year has passed and it isn't solved yet. When people and companies begins to realize they had lost hundreds (or thousands) of files, that hundreds of their holidays photos are corrupted, it can be the end of Nextcloud. And also of Owncloud if the bug is also on their implementation.

[Schmuuu]

Not sure if this information helps anybody. I was affected by this problem as well starting with a minor release of NC12 (may have been NC 12.2 or NC 12.3). I'm running MariaDB, too. I think I remember that some MariaDB updates slightly improved the situation and reduced the occurrence of DB deadlocks.
But the messages still came up, when I (quickly) scrolled through my photos in the gallery app.

At that time, I assumed that I was using Redis for file locking successfully, but I wasn't actually. When I finally noticed my Redis issue and fixed that, the deadlocks were completely gone.
So I'm still using MariaDB and haven't seen this issue again.

OS is ArchLinux LTS and MariaDB runs with version 10.1.36

[warnerbryce]

This is strange because if i use Nextcloud without redis, i still have this bug.
Could you download the test packet few comments up in this thread.
Unzip this folder in your cloud and see if the first sync goes well without deadlocks

[adocampo]

I don't use redis either. If the kernels worth the mention, I also used Arch Linux LTS, but it also failed, so I went back to vanilla kernel. I mentioned before I was on a dockerized scenario, but others suffered this on baremetal as well.
If you could please test with the zip some of us have tested, you would bring more tests to confirm o discard a culprit
https://cloud.docampo.net/index.php/s/Ke5HRcy4QcMErZA

[Greek64]

I too was getting Deadlock errors back in NC 12.0.5 when using the nextcloud client.
I circumvented this issue by configuring a FTPS server linked to NC and gave access to the NC users. (Way faster then nextcloud client)

Nevertheless I wanted to see if the Deadlock errors still exist, so I started nextcloud client and tried to sync your test folder.
I have similar results to @Schmuuu, meaning everything was fine (temporal upload issue of one file due to locking, which was solved during resync)

I currently run a dockernized NC 14.0.3 with MariaDB and Redis.

I will have to run nextcloud client a bit more to see if the Deadlocks are completely gone.

[Schmuuu]

I tested a sync with the NC Windows 10 app (from MS Store) and the zip file.
There were no DB deadlocks at all.
The only error message I received was:

Exception: The requested uri(/remote.php/webdav/Sync-Test/Registration%20API/pages/xwiki/PG%2BPlatform/remote.php/webdav/Sync-Test/Registration%20API/pages/xwiki/PG%2BPlatform/API/) cannot be processed by the script '/webdav/Sync-Test/Registration API/pages/xwiki/PG+Platform/API/')
    /var/www/nextcloud/lib/private/AppFramework/Http/Request.php - line 768:
    OC\AppFramework\Http\Request->getRawPathInfo()
    /var/www/nextcloud/remote.php - line 125:
    OC\AppFramework\Http\Request->getPathInfo()

As a result the folder Registration API/pages/xwiki/PG+Platform/API/ was empty and didn't contain the sub folders and files. A resync didn't work for this folder as well. Same error all the time. I'm not sure if this is an issue with the Win10 app. I'll try again with the desktop client and let you know.

Nonetheless, no deadlocks. I checked all (system) logs.

Concluding: with Redis, no more DB deadlocks on my system so far.

Again some Information regarding my system:

• baremetal (no docker or something like that)
• ArchLinux LTS 4.14.78
• MariaDB 10.1.36 (precisely followed the NC docs regarding all mariadb instructions and suggestions)
• Redis 5.0.0
• nginx 1.14
• Nextcloud 14.0.3

Config:

  'filelocking.enabled' => true,
  'memcache.local' => '\\OC\\Memcache\\APCu',
  'memcache.locking' => '\\OC\\Memcache\\Redis',
  'redis' =>
  array (
    'host' => '/var/run/redis/redis.sock',
    'port' => 0,
),

As explanation regarding my previous redis issue: Redis was running and configured for file locking in the config. However, there was actually no connection to redis by Nextcloud. I noticed that I forgot a PHP module. After I enabled that, I received error messages, that a connection to redis was not possible. I had to move the socket file from default path to /var/run/redis/redis.sock. Afterwards redis was really handling NC requests and from that moment on, no more deadlocks. Hope this helps somebody.

Please let me know if I can provide further information.

[adocampo]

Could you please use the web interface as well? Just drag the folder there

Glad it seems to work with redis (and sad you didn't posted it before I screwed my former NC installation xD )

[Schmuuu]

I tested again with the NC desktop client and with the Web GUI. Both ways worked perfectly fine and the whole package has been uploaded/ synched flawlessly. No deadlocks and no other messages in the NC log (log level = Info).
So regarding the previous error during synchronization, it was definitely an issue with the Win10 app.

I'm sorry I didn't write earlier. It was the higher activity in the issue which drew my attention now.

[kesselb]

When you are running mariadb/mysql could you enable https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_print_all_deadlocks? More information about the deadlock should be in the error log then. Please post it 👍

[Greek64]

@danielkesselberg
I don't know about others, but when I had deadlocks - in the sense that nextcloud said they were deadlocks in the logs - my database was of a different opinion (I also have the option already set)

Here a quote from my previous post:

The interesting thing is, that when I try to find the root cause of the Deadlock, the mysql database has not detected any deadlocks.
Normally executing _SHOW ENGINE INNODB STATUS_ in the mysql database, outputs a section called "LATEST DETECTED DEADLOCK", which analyzes the two Quaries that lead to the Deadlock. But my database does not detect (or better said report) any Deadlock that is caused by the nextcloud client syncing.
On the other hand, when I try to make a manual Deadlock, it is reported as expected.

[kesselb]

Thank you for clarification. @MorrisJobke @rullzer @nickvergessen i was wondering why nextcloud is using an old doctrine/dbal version? I guess they made some improvments (e.g https://github.com/doctrine/dbal/pull/718) to deadlocks with newer version. https://github.com/doctrine/dbal/pull/2422 has been merged a while ago.

[obel1x]

i still got that error on nc 14 + mariadb + linux client when uploading a lot of files many times. For me it looked like Statement "insert ... select" on the same table doesn't work fine in adapter.php - the doc of mysql says that i won't work at all. When i look at the issues, postgres is also affected, but more likely causes duplicate entries. To find out if the Statement was the issue, i did a quick workaround-version, as to find in my post of #6160 -> After those Changes for my no more Deadlocks.

Anyone who can test this Version on a testing Server? If so, pleas check my testing-Version and let me know if it solves that for you too.
Regards, Daniel

[rullzer]

Thank you for clarification. @MorrisJobke @rullzer @nickvergessen i was wondering why nextcloud is using an old doctrine/dbal version? I guess they made some improvments (e.g doctrine/dbal#718) to deadlocks with newer version. doctrine/dbal#2422 has been merged a while ago.

Because doctrine required php 7.1 starting at 2.6 and we still support php 7.0

[kesselb]

@obel1x thank you for investigating :+1:

I can confirm that the query below (which is the one generated by insertIfNotExist) leads to a deadlock with mariadb.

INSERT INTO `oc_filecache` (`mimepart`,`mimetype`,`mtime`,`size`,`etag`,`storage_mtime`,`permissions`,`parent`,`checksum`,`path_hash`,`path`,`name`,`storage`) SELECT '1','2','1541450083','-1','5be0afd65dd46','1541450083','31','305','','ad7abceaf8133707e29be350c4326422','appdata_ocy132w80pwx/css/files_trashbin','files_trashbin','2' FROM `oc_filecache` WHERE `storage` = '2' AND `path_hash` = 'ad7abceaf8133707e29be350c4326422' HAVING COUNT(*) = 0;

Howto collect more information

1) truncate oc_filecache
2) breakpoint in https://github.com/nextcloud/server/blob/a661f043e1a8764cb7c795f50df77b830d3e352b/lib/private/Files/Cache/Cache.php#L271
3) use scanner to trigger filecache again

[kesselb]

On my instance there is an unique index on storage and path_hash. If this is true for every instance i would use insert ignore (to avoid the select before insert) and use the query builder here: https://github.com/nextcloud/server/blob/a661f043e1a8764cb7c795f50df77b830d3e352b/lib/private/Files/Cache/Cache.php#L271

[MorrisJobke]

insert ignore

Is this supported on all DBs? (sqlite, Postgres, MariaDB, mysql and oracle)

[obel1x]

Thank you for the confirm. For me this seems to be the reason for a bunch of issues (see 6160) even making people think that nc is not good enough for production. I also had the problem, that if that Error occurred a few times on a specific file the sync won't work any more at all.
Have you tried my (3rd) version in Test? For me, this works without any errors, even if it is not nice to have. So maybe someone ca clean it up and bring it up to the next version?

The second proposal "Insert Ignore": that would be great but well - it seems that the changed function "insertIfNotExist" has intentionally not been designed to use indices, as compare-columns are given by and they don't have to be equal to any index. as far as i have seen, it will be called in different scenarios and on different tables (eg. file_locks if no redis/memchache), so we cannot ensure there is an corresponding index. So currently i would not know how to circumvent the select if not running into Deadlocks.
On the other hand - i am pretty sure that i saw selects to the tables after that function. So if i were nc developer i would see to change the return of the Function to include the results any way and to use them later on so prevent multiple selects to that table. Only a suggestion. Unfortunately i am in lack of knowledge and tools to have a look where this function is called from.

So lets first stick to some workaround. Anyone here to help us further?

[MorrisJobke]

Thank you for clarification. @MorrisJobke @rullzer @nickvergessen i was wondering why nextcloud is using an old doctrine/dbal version? I guess they made some improvments (e.g doctrine/dbal#718) to deadlocks with newer version. doctrine/dbal#2422 has been merged a while ago.

Do we want to port it to our dbal version and add this here?

$retryWrapper = new RetryWrapper(function () use ($con) {
    return $con->update('tablename', array('field' => 'data'), array('id' => 'myid'));
});

$affectedRows = $retryWrapper();

[obel1x]

are those dbal-changes (they are taking the sql-errorcode into more precise exceptions, right?) and Retrywrapper to solve the deadlock or a general proposal?
I would not recommend doing retries with that query as a solution for the deadlocks - now if we know the cause for the issues is the query itself and that it can be worked around.
As i said before: i had that error that many times, that files were blocked out. So it very likely that retries will fail too in those setups.

[mduller]

I just converted from MariaDB to Postgres and can also confirm that I haven't seen any deadlocks anymore. Even @adocampo 's test file syncs without any issues.
I think the Retrywrapper can be a good intermediate solution until the queries are rewritten and tested. Most important, though, is that error messages are being printed when files are silently ignored. When still on MariaDB, this was not the case for me in Firefox for a couple NC versions now (see my earlier post).

[kesselb]

insert ignore

Is this supported on all DBs? (sqlite, Postgres, MariaDB, mysql and oracle)

No :disappointed:

[MorrisJobke]

Part of the issues that were posted in here are most likely fixed by the change in #12366. The crucial point is that the stack trace contains initLockField. If this is the case, then the linked PR fixes most likely this issue. For the others I'm about to look into it.

[MorrisJobke]

I posted and overview in #12368 with details about the different stack traces and what you need to look for to find the one you are looking into.

The one that was posted by the OP is solved in #12366

[MorrisJobke]

The one that was posted by the OP is solved in #12366

There is a new version of this in #12371. Maybe you want to test this as well. It should solve at least all UniqueConstraintViolationExceptions.

[MorrisJobke]

The deadlocks for the "INSERT INTO" are fixed by #12411 (for the table oc_filecache) and #12413 (for the table oc_file_locks).