Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction #6899
Comments
|
I'm affected by same issue, Nextcloud 12.0.3 with MariaDB/nginx and latest php 7.1. |
|
I am also seeing this. Nextcloud 12.0.4 with MariaDB 10.2.10, PHP 7.1.12. Redis enabled for caching and locking. |
|
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. |
Yup, the VM is by the book. :) |
|
I had to set mine to READ-COMMITTED manually. Has anyone figured out the cause and fix? |
Hmm, that shouldn't be the case. |
|
I too am getting Deadlock errors consistently by using the nextcloud client. Operating system: Nextcloud Docker 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. Can anybody else test if their database detects/reports the Deadlock? |
|
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:
Reading the Nextcloud docco, it's pretty straight up that READ COMMITTED is the only way to go:
I might try disabling binary logging altogether and see how that goes. Will keep you posted. |
|
@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... |
|
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. |
|
I'm having the same problem: I see deadlocks in the nextcloud logs but not in the Mysql command SHOW ENGINE INNODB STATUS |
|
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 |
|
@MorrisJobke this was while using redis for locking. |
|
@MorrisJobke same here as @fractos - having this issue despite using redis for locking |
|
@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 :) |
|
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 Firefox:
Hopic Explorer (uploads files in parallel):
Beyond Compare 4.2 (it's built-in WebDAV support appears to upload one file at a time):
Errors in Nextcloud log seen for files missing in Firefox uploads: Errors in Nextcloud log seen for files missing in Hopic Explorer uploads: 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 |
|
Two more details I forgot to add:
|
|
This is just making nc completely useless as a file sync tool. Does anyone know the specific version where this bug was (re)introduced? |
|
@0x4A-0x41-0x4B |
|
Same problems with the latest 25.0.4
|
Hello, is it possible for you to give de command line you type ? I'm not very okay with db manipulations... |
Connect to the DB with your favourite DB Manager (e.g. phpMyAdmin) and execute the following : ALTER TABLE oc_filecache You have to DROP all indexes but PRIMARY, and ADD the path_hash, fs_parent. PS. |
Thank you ! I will see if that solve the problem |
Works on Server version: 10.5.18-MariaDB-0+deb11u1 Debian 11 |
is this the temporary fix? did anyone try this? |
|
Based on the thread, the issue is 5 years old, so calling it a "temporary fix" would be a stretch, I think 😢 |
|
Hello I Have the same problem Version 26.0.6; PHP 8.0.28, Mysql 8.0.33. Error : "An exception occurred while executing a query: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction"}} Its server from 24.0.2 to 26.0.2 step by step It cause lot problem with RAM and CPU usage |
|
@Jason-Linux currently this is the best approach i think: #6899 (comment) |
|
@obel1x i tried before but same result |
|
Still seeing these deadlock issues on 25.0.7, php 8.1.20, PostgreSQL 15.3. I'm due to update to version 26 in the near future, but i see others above still reporting it on that version. While removing indexes could help solve speed issues, I don't see how that would affect the deadlock problem that was originally reported. That's due entirely to the order that writes are being done to the database by concurrent transactions. I run a very small server, so I'm seeing almost zero performance issues even with the locks, it just creates a mess in the logs. And for PostgreSQL at least, it's a rather serious problem because of the conflicting transactions, there's no guarantee which one of them actually succeeds. |
|
Hi, there have been patches to address this issue that will land in 26.0.3. Can you please verify if 26.0.3-rc1 improves the situation around this issue? |
|
Just curious if you mean this patch? I'm not that familiar with this DB library in php, but just wondering if this is just ignoring the deadlock report from the database or actually preventing it? If not the latter, while this might remove the error from the nextcloud/php logs, you're not actually preventing the deadlock and the error will still spam the database logs and cause unpredictable behavior. |
|
@keithf4 I'm not certain which PRs @szaimen was referring to specifically, but, yes, those would be two relevant ones from the looks of them. The referenced PRs look to be for a specific case where Also, one of those PRs adds explicit logging (at the WARN level) when this happens. The logs for this particular case will be cleaner (i.e. no repeated unhandled exceptions and now only at a specific loglevel so there's some flexibility/control). Progress is often incremental. As this thread has shown, there are multiple contributing factors and therefore multiple non-mutually exclusive angles to attack this from. Reducing the impact of scenarios where deadlocks still can arise (where possible) is one such angle of attack. Obviously in addition to the other angle: reducing their frequency. The referenced PRs are an example of the former by my read. |
|
Appreciate that that is what it's doing and may help mitigate that it keeps retrying more often, but just on an initial read it simply looked like it was reclassifying the error and passing on it which was very concerning. While I hope it improves things, the source of the problem seems much more complex as noted below and part of me is a little concerned that it's not making clear in the Nextcloud logs that an actual deadlock event is happening. |
has this patch been implemented in the version 27? I have a clear new install and I have this issue still. |
|
@Badb0yBadb0y Yes, the recently referenced PRs in this thread are in NC27. Keep in mind #6899 (comment) - there are various circumstances deadlocks can occur under. These PRs are incremental improvements. You may still encounter deadlocks under various circumstances. P.S. Also, which PRs appear in which releases can be found at: https://nextcloud.com/changelog/ |
|
Seems like my issue was a galera cluster behind haproxy, I had to set 1 node out of the 2 as backup so can't happen double write as stated in one of the owncloud documentation. |
|
This issue has been automatically marked as stale because it has not had recent activity and seems to be missing some essential information. It will be closed if no further activity occurs. Thank you for your contributions. |
github-project-automation
bot
moved this from 📄 To do (~10 entries)
to ☑️ Done
in 📁 Files team
|
Are there any fixes to this issue? My instance now has a deadlock and I don't know how to fix the issue. I'm unable to delete files from a folder. |
|
After 1 year without issues and now having reached version 29.0.5, I decided to recreate the original structure of the oc_filecache table and test using the same sample with 20,000 small ico files. Immediately, DeadlockException, Serialization failure messages started again... as well as corrupted uploads. This happens on all three of our production servers (Debian 11, Apache 2.4.56, PHP 8.2, MariaDB 10.5.21) The only workaround that works for me is deleting the indexes as shown in post #6899 (comment) Specifically, the only three indexes I have now in table oc_filecache are the following: |
and others
Steps to reproduce
Expected behaviour
No error
Actual behaviour
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
Nextcloud configuration:
Config report
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
Nextcloud log (data/nextcloud.log)
Nextcloud log
Browser log
Not relevant
The text was updated successfully, but these errors were encountered: