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

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

Open mxschmitt opened this issue 6 years ago • 119 comments

Steps to reproduce

Reinstalled fresh nextcloud
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

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

Oct 22 '17 09:10 mxschmitt

@icewind1991

Oct 24 '17 10:10 blizzz

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

Dec 12 '17 20:12 furai

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

Dec 28 '17 20:12 mduller

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.

Jan 18 '18 22:01 snozberries-ln

but mine is set to READ-COMMITED.

Yup, the VM is by the book. :)

Jan 19 '18 08:01 enoch85

I had to set mine to READ-COMMITTED manually.

Has anyone figured out the cause and fix?

Jan 23 '18 11:01 snozberries-ln

I had to set mine to READ-COMMITTED manually.

Hmm, that shouldn't be the case.

Jan 23 '18 17:01 enoch85

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?

Feb 02 '18 12:02 Greek64

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.

Feb 24 '18 03:02 snozberries-ln

@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.

Feb 24 '18 11:02 Greek64

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...

Mar 03 '18 23:03 snozberries-ln

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

$fractos avatar$ Apr 27 '18 13:04 fractos

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

May 08 '18 13:05 warnerbryce

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

Jun 28 '18 08:06 MorrisJobke

@morrisjobke this was while using redis for locking.

$fractos avatar$ Jun 28 '18 08:06 fractos

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

Jun 28 '18 08:06 mduller

@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 :)

Jun 28 '18 08:06 MorrisJobke

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

Aug 12 '18 09:08 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

Aug 13 '18 04:08 mduller

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

Aug 13 '18 05:08 warnerbryce

Thanks for reopening it

Aug 15 '18 11:08 warnerbryce

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).

Aug 16 '18 14:08 mduller

I get the same error

[php7:error] [pid 7517] PHP Fatal error: Uncaught PDOException: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction in /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:105\nStack trace:\n#0 /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php(105): PDOStatement->execute(NULL)\n#1 /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(1006): Doctrine\DBAL\Driver\PDOStatement->execute()\n#2 /var/www/nextcloud/lib/private/DB/Connection.php(216): Doctrine\DBAL\Connection->executeUpdate('UPDATE oc_file...', Array, Array)\n#3 /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php(208): OC\\DB\\Connection->executeUpdate('UPDATE oc_file...', Array, Array)\n#4 /var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php(214): Doctrine\DBAL\Query\QueryBuilder->execute()\n#5 /var/www/nextcloud/lib/private/Lock/DBLockingProvider.php(280): OC\DB\QueryBuilder\QueryBuilder->execute()\n#6 [internal in /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php on line 115

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

Aug 21 '18 18:08 ghost

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

Aug 21 '18 19:08 Redsandro

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

Install Debian Stretch, Docker and docker-compose
Deploy a Nextcloud instance and a MariaDB following this official guide
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

Nothing

Nextcloud log (data/nextcloud.log)

Nextcloud log

Already pasted example on top

Sep 01 '18 21:09 martinber

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

Sep 09 '18 20:09 th3cube

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.

Sep 10 '18 08:09 Qeynos

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.

Sep 10 '18 08:09 furai

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

Sep 12 '18 15:09 adocampo

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

Sep 12 '18 22:09 stefer09

server server copied to clipboard

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

Steps to reproduce

Expected behaviour

Actual behaviour

Server configuration

Client configuration

Logs

Web server error log

Nextcloud log (data/nextcloud.log)

Browser log

Server configuration

Client configuration

Steps to reproduce

Expected behaviour

Actual behaviour

Server configuration

Client configuration

Logs

Web server error log

Nextcloud log (data/nextcloud.log)

server
server copied to clipboard