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

šŸš§ Installation
1

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

2

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

1 Like
3

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

4

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

5

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

6

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.

7

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ā€¦

1 Like
8

I am posting this on 2022 and this issue still persists.
Thereā€™s no way to avoid it. Iā€™ve configured the following server:
HW:

  • 2 X SSD Raid 1 system drive
  • 2 X mirrored zfs data
  • 1 x M.2 nvme zil & l2arc cache

SW:

  • Debian 11 Bullseye updated
  • Apache2 updated 2.4.54
  • Php 8.1.13-cli
  • Nextcloud hub 3
  • Mariadb 10.5.15

ā€” Configured Mariadb transaction isolation to READ COMMITTED, and ROW binlog format
ā€” Configured with innodb parameters specified in official docs

ā€” All php modules installed (php-mysqli instead of pdo_mysql because it was deprecated)
ā€” configured php.ini at /etc/php/8.1/apache2/php.ini max filesize uploads and max post to 600 G. Also set max execution time and max input time to 1 day
ā€” Configured php.ini cli on /etc/php/8.1/cli/php.ini max upload size and max post size to 600G. also set max execution time and max input time to 1 day

When uploading multiple big files the serer raises an exception 500 internal server error, mysql state 40001. Easily repeatable trying to upload a bunch of 500 nef files (raw pictures) to a folder.
I am stuck at this point, and noone on internet is able to solve the issue. Please, if somebody has the same issue, try to help.
Iā€™ve followed the full install documentation and the issue is still there.

2 Likes
9

We experience this on Nextcloud 27.1.4 with 10.11.4-1~deb12u1 on Debian 12.