Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction in PDOStatement.php

Hi,
I have installed nextcloud 13.0.4 (centos 7, apache 2.4.27 + php 7.1 (mod_php) + mariadb-server-5.5.53). It works but I see intermittent but persistent errors in logs like this:

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

Additional config info:

  • default-storage-engine = INNODB
  • transaction_isolation = READ-COMMITTED
  • binlog_format=row (I cannot disable binlog because I am using a master/slave mariadb cluster)
  • ldap authentication

The requests causing theses errors seem to be always requests like this:
UPDATE oc_file_locks SET lock = lock -1 WHERE (key IN (‘files/ad7a5fc3ac762b0184ff05cd4a8f7e1a’, ‘files/4aa8bdd3dfd2a72345818e66951d7240’, ‘files/978b7f1854a93cea27b9e58460b8c1c5’, ‘files/a619516dc0706358f266e873ab1eef97’, ‘files/15b5a6c7abf6be5211c23f64b6c11988’, ‘files/42eb610ac323c7c99208f787e3ab7eb7’, ‘files/4e66c81f48a454bbe868f3f2b2ba2fa4’, ‘files/ded8b5e5380bc55643d0d710aa1ab07a’, ‘files/ff7ea957c98f5ae3669ab20f5011098c’, ‘files/97d4b810deb1644f153122b347bab0ec’))
(I see that with “SHOW ENGINE INNODB STATUS” request).

I am the only user for now (cronjobs are also configured using crontab). I am just testing basic nextcloud feature using the web interface. I have seen these bug reports which may be related:


Do you have any suggestion?
Thanks in advance,
Mathieu

I’m watching this thread, while I’m also affected by this problem and am very interested in a solution.
When I freshly installed the server with NC 11.0.x back then I had no such error messages at all. I’m really wondering where they are coming from; do NC updates (currently 13.0.4) or the OS updates result in such issues with locked files or bad performance when watching photos in the web GUI.

I followed the Github issues you posted, read a lot in the NC docs and tried to understand the suggestions about how to set up the database.
What I stumbled upon during research for guidance about what to do in the end, was a slight difference between the NC install guides spread on the Internet and the NC doc (for MariaDB in my case):

Guide on the Internet:

Add the following three lines in [mysqld] section.

log-bin = /var/log/mysql/mariadb-bin
log-bin-index = /var/log/mysql/mariadb-bin.index
binlog_format = mixed

The format of binary log must be mixed. Save and close the file. Then restart MariaDB service.



NC13-Doc:

Database requirements for MySQL / MariaDB

The following is currently required if you’re running Nextcloud together with a MySQL / MariaDB database:

Because I strictly the guide I believed to be the best guide, I have binlog_format set to “mixed” of course and haven’t changed any DB settings since. That’s why I’m wondering where this is coming from out of the sudden.
Right now I’m not sure, should I simply switch to ROW or should I rather disable binlog_format? Seeing that binlog_format=ROW didn’t help you to solve that issue, it is not creating much hope.

I have tested neither of these values yet, and therefore I’m not sure if there could be some improvements for me. Will probably do a full system backup the next days and test both.

For now, I just wanted to share, that you are not alone with this issue and maybe this gives a little push to this topic.
I hope somebody can help here.

Server config

Operating system: Linux 4.14.47-1-lts #1 SMP Wed May 30 21:09:23 UTC 2018 x86_64

Webserver: nginx/1.14.0 (fpm-fcgi)

Database: MySQL 10.1.33

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

Nextcloud version: 13.0.4 - 13.0.4.0

Hi,
Thanks for the response. According to mysql documentation on binlog_format (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.” The 6899 NextCloud bug report suggests to disable bnary log but it is not an option for me. AFAIK, it is required when using replication. For a single instance, it helps also for restoration operations and makes the db more resilient to unexpected crashs. See https://dev.mysql.com/doc/refman/5.7/en/binary-log.html. I will keep you inform if I find a workaround. I am afraid I cannot go to production with these errors occurring while I am the only user…
Mathieu

Hi,
I finally put a term to this error, but it’s not a real solution.
It’s seems that since NC 12, this strange error occurs. It’s like a problem with MariaDB/Mysql in tweaking or the way the Database handle things…
The fact is, using PostgreSQL works better.

See again this threat : https://github.com/nextcloud/server/issues/6899
Some people and me, tested the migration to PostgreSQL and don’t have this error again.

@jospoortvliet @MorrisJobke Did you know what can cause that ? Are you on the way to found a solution ? Good luck

Nextcloud 12 is no longer supported in public, so - I don’t know and we won’t work on it unless it is for a customer (we offer LTS to customers only, so they get further updates and bugfixes).

Hi @jospoortvliet

He only wrote that this issue started with NC12, but it actually persists until the latest version of Nextcloud; NC14.0.3.
In the linked github issue you find quite a few users trying out every NC update for a while and still suffer from this problem with DB deadlocks. The discussion is interesting, because some users figured out, the problem doesn’t occur when they switch the DB from MySQL/ MariaDB to PostgreSQL.
Even @enoch85 from techandme.se who provides the NC VM had hard times with this issue and created a new VM with PostgreSQL as DB to avoid this issue finally.
I can totally understand all users to desperately seek for help. I suffered this issue myself (posted that on Github) and it is so frustrating to have a server which is suddenly hardly usable anymore.

@Nemskiller nonetheless I think this issue is better handled on Github. I don’t think the forum here can really help.

Yes that’s why i put the link of it again in my answer.

Even if it’s not a bug that kill the server, it’s a bug who is really annoying for everyone. The user might thing, oh no ! It didn’t work, oh wait yes… maybe i don’t know…