Jump to content
Rich Minear

Downgraded back to 6.6.7 due to Sqlite corruption

195 posts in this topic Last Reply

Recommended Posts

Posted (edited)
On 6/20/2019 at 4:43 PM, RaihaX said:

I wouldn't say this fix this issue but it mitigate/reduce the corruption as mine just corrupted again last night (but its been 3 weeks since the last one and the previous corruption before then was about 2-3 weeks too). From what I can tell you guys are experiencing corruption a lot more frequent?

 

From what I can tell it seems that it only gets corrupted when plex does too many queries/update/insert in a short span of time. By only doing hourly scan it seems to be less prone to corruption.

 

I only notice this when I was redownloading all my files to 265 and I would get corruption every 30-60 min which lead me to the above and it seems to work.

 

So obviously there is a bigger underlying issue that still need to be fixed but at least now it has become less annoying...

 

Hopefully one the dev can figure it out.

 

ps. I have given up in using backup - now i just dump the db and rebuild the db from the dump. A lot easier to restore it and I can probably make a script to trigger this but i haven't been bothered as it doesn't happen as often.

Rip i set it to scan the library's every 2 days and it only lasted 24 hours lol

 

log being spammed with 

Jun 23, 2019 21:42:08.128 [0x14eb10b44700] ERROR - SQLITE3:(nil), 11, statement aborts at 9: [select statistics_bandwidth.id as 'statistics_bandwidth_id', statistics_bandwidth.account_id as 'statistics_bandwidth_account_id', statistics_bandwidth.device_id as 'statistics_bandwidt
Jun 23, 2019 21:42:08.128 [0x14eb10b44700] ERROR - Thread: Uncaught exception running async task which was spawned by thread 0x14eb314d1700: sqlite3_statement_backend::loadOne: database disk image is malformed
Jun 23, 2019 21:42:08.129 [0x14eb10742700] ERROR - SQLITE3:(nil), 11, database corruption at line 79051 of [bf8c1b2b7a]
Jun 23, 2019 21:42:08.129 [0x14eb10742700] ERROR - SQLITE3:(nil), 11, statement aborts at 9: [select statistics_bandwidth.id as 'statistics_bandwidth_id', statistics_bandwidth.account_id as 'statistics_bandwidth_account_id', statistics_bandwidth.device_id as 'statistics_bandwidt
Jun 23, 2019 21:42:08.129 [0x14eb10742700] ERROR - Thread: Uncaught exception running async task which was spawned by thread 0x14eb312d0700: sqlite3_statement_backend::loadOne: database disk image is malformed
Jun 23, 2019 21:42:08.130 [0x14ea9d71c700] ERROR - SQLITE3:(nil), 11, database corruption at line 79051 of [bf8c1b2b7a]
Jun 23, 2019 21:42:08.130 [0x14ea9d71c700] ERROR - SQLITE3:(nil), 11, statement aborts at 9: [select statistics_bandwidth.id as 'statistics_bandwidth_id', statistics_bandwidth.account_id as 'statistics_bandwidth_account_id', statistics_bandwidth.device_id as 'statistics_bandwidt
Jun 23, 2019 21:42:08.130 [0x14ea9d71c700] ERROR - Thread: Uncaught exception running async task which was spawned by thread 0x14eb312d0700: sqlite3_statement_backend::loadOne: database disk image is malformed
Jun 23, 2019 21:42:08.131 [0x14eb137f5700] ERROR - SQLITE3:(nil), 11, database corruption at line 79051 of [bf8c1b2b7a]
Jun 23, 2019 21:42:08.131 [0x14eb137f5700] ERROR - SQLITE3:(nil), 11, statement aborts at 9: [select statistics_bandwidth.id as 'statistics_bandwidth_id', statistics_bandwidth.account_id as 'statistics_bandwidth_account_id', statistics_bandwidth.device_id as 'statistics_bandwidt
Jun 23, 2019 21:42:08.131 [0x14eb137f5700] ERROR - Thread: Uncaught exception running async task which was spawned by thread 0x14eb314d1700: sqlite3_statement_backend::loadOne: database disk image is malformed

I should maybe get my SAS card and SSD's this week so hope moving to cache fixes the issue 

 

instead of restoring a backup, I'll look at repairing it tomorrow https://support.plex.tv/articles/201100678-repair-a-corrupt-database/

Edited by Addy

Share this post


Link to post
1 minute ago, Addy said:

Rip i set it to scan the library's every 2 days and it only lasted 24 hours lol

 

log being spammed with 


Jun 23, 2019 21:42:08.128 [0x14eb10b44700] ERROR - SQLITE3:(nil), 11, statement aborts at 9: [select statistics_bandwidth.id as 'statistics_bandwidth_id', statistics_bandwidth.account_id as 'statistics_bandwidth_account_id', statistics_bandwidth.device_id as 'statistics_bandwidt
Jun 23, 2019 21:42:08.128 [0x14eb10b44700] ERROR - Thread: Uncaught exception running async task which was spawned by thread 0x14eb314d1700: sqlite3_statement_backend::loadOne: database disk image is malformed
Jun 23, 2019 21:42:08.129 [0x14eb10742700] ERROR - SQLITE3:(nil), 11, database corruption at line 79051 of [bf8c1b2b7a]
Jun 23, 2019 21:42:08.129 [0x14eb10742700] ERROR - SQLITE3:(nil), 11, statement aborts at 9: [select statistics_bandwidth.id as 'statistics_bandwidth_id', statistics_bandwidth.account_id as 'statistics_bandwidth_account_id', statistics_bandwidth.device_id as 'statistics_bandwidt
Jun 23, 2019 21:42:08.129 [0x14eb10742700] ERROR - Thread: Uncaught exception running async task which was spawned by thread 0x14eb312d0700: sqlite3_statement_backend::loadOne: database disk image is malformed
Jun 23, 2019 21:42:08.130 [0x14ea9d71c700] ERROR - SQLITE3:(nil), 11, database corruption at line 79051 of [bf8c1b2b7a]
Jun 23, 2019 21:42:08.130 [0x14ea9d71c700] ERROR - SQLITE3:(nil), 11, statement aborts at 9: [select statistics_bandwidth.id as 'statistics_bandwidth_id', statistics_bandwidth.account_id as 'statistics_bandwidth_account_id', statistics_bandwidth.device_id as 'statistics_bandwidt
Jun 23, 2019 21:42:08.130 [0x14ea9d71c700] ERROR - Thread: Uncaught exception running async task which was spawned by thread 0x14eb312d0700: sqlite3_statement_backend::loadOne: database disk image is malformed
Jun 23, 2019 21:42:08.131 [0x14eb137f5700] ERROR - SQLITE3:(nil), 11, database corruption at line 79051 of [bf8c1b2b7a]
Jun 23, 2019 21:42:08.131 [0x14eb137f5700] ERROR - SQLITE3:(nil), 11, statement aborts at 9: [select statistics_bandwidth.id as 'statistics_bandwidth_id', statistics_bandwidth.account_id as 'statistics_bandwidth_account_id', statistics_bandwidth.device_id as 'statistics_bandwidt
Jun 23, 2019 21:42:08.131 [0x14eb137f5700] ERROR - Thread: Uncaught exception running async task which was spawned by thread 0x14eb314d1700: sqlite3_statement_backend::loadOne: database disk image is malformed

I should maybe get my SAS card and SSD's this week so hope moving to cache fixes the issue 

Ah that sucks - with so many variation definitely to pin point exactly what is going on.

 

I haven't done any of the suggested fix as I am still waiting for my coffee lake replacement to come in (US to Aus from Amazon is taking unusually long gonna take almost 4 weeks....)

 

Once I have fully rebuild my unRaid server with proper parts i can join in the testing.

Share this post


Link to post
Posted (edited)

@testdasi

 

What CPU? How much RAM? Array config?

I7-2700, 12gb, 15TB XFS – no cache drive installed, 4TB free

 

Roughly how large is your collection? I think file count, even a rough estimate, is more important here.

movies = 2.47TB / 3076 files

tv = 2.3TB / 1739 files

photos = 33gb / 29000 files

 

 

Have you set your appdata to /mnt/cache (or for those without cache, /mnt/disk1)? If you haven't, we'll ignore you.

ive tried 

mnt/disk1/appdata

mnt/disk1/plexdata

mnt/usr/appdata

 

I have no cache

 

Do you have a link between Sonarr and Plex? If yes, have you disable it? If you haven't, we'll ignore you.

dont think so, but they will corrupt at the same time.. sometimes.. 

The only link I could think of is that they scan the same folders, they are also set to mnt/disk1/appdata

 

Do you have automatic library update on change / partial change? If yes, have you set it to hourly? If you haven't we'll ignore you.

Was set to update when changes are found then I tried 2 hour intervals which seemed to bring corruption faster

 

This is more controversial. Can you rebuild your db from scratch?

please no

 

 

Edited by Addy

Share this post


Link to post

I just upgraded to 6.7.1 only because of this statement in the announcement:

 

"We are also still trying to track down the source of SQLite Database corruption.  It will also be very helpful for those affected by this issue to also upgrade to this release ."

 

 

Share this post


Link to post
On 6/19/2019 at 11:43 PM, RaihaX said:

From what I can tell it seems that it only gets corrupted when plex does too many queries/update/insert in a short span of time. By only doing hourly scan it seems to be less prone to corruption.

 

I have suspected that as well. On my last rebuild -- maybe 5 days ago -- I disabled automatic scanning all together. I have only manually ran it on a single movie library (I used to have 3 categorized movie libraries). I have not re-added Music nor TV Shows back yet. So far, so good.

That makes me wonder... could multiple libraries make it more likely too corrupt? That was part of my reasoning to combine my three separate movie libraries.

 

On 6/19/2019 at 11:43 PM, RaihaX said:

From what I can tell it seems that it only gets corrupted when plex does too many queries/update/insert in a short span of time. By only doing hourly scan it seems to be less prone to corruption.

 

ps. I have given up in using backup - now i just dump the db and rebuild the db from the dump. A lot easier to restore it and I can probably make a script to trigger this but i haven't been bothered as it doesn't happen as often.

 

I would recommend keeping backups once in a while as well just in case you are unable to recover the database. I tried at least 6 separate times, and each time the test at the command line after re-importing the data reported problems. So, I have given up on that process.

Share this post


Link to post

Could scans occurring while a file is being written be causing the sqlite3 corruption?

Share this post


Link to post

Another person having issues with corruption who isn’t even aware of what’s happening in unraid. They run unraid and their db was corrupted. 

 

 

Share this post


Link to post
11 minutes ago, runraid said:

Another person having issues with corruption who isn’t even aware of what’s happening in unraid. They run unraid and their db was corrupted. 

 

 

 

Quoting others issue from unknown sources isn't really helping fix the issue.

Share this post


Link to post

@saarg but it helps get a better sense of the magnitude of this impact and maybe just maybe see patterns that might help. 

 

I was able to dump my database and reimport and get back up and running. The issue on the cache drive was different from /mnt/disk, doing the integrity check complained of a missing index (this was after a fresh install and after half a day of scanning media). I’m up and running again and will report back with any new issues. 

Share this post


Link to post
Posted (edited)
On 6/20/2019 at 3:09 AM, testdasi said:

How about this - for anyone who reports the problem, also report:

  1. What CPU? How much RAM? Array config?
  2. Roughly how large is your collection? I think file count, even a rough estimate, is more important here.
  3. Have you set your appdata to /mnt/cache (or for those without cache, /mnt/disk1)? If you haven't, we'll ignore you.
  4. Do you have a link between Sonarr and Plex? If yes, have you disable it? If you haven't, we'll ignore you.
  5. Do you have automatic library update on change / partial change? If yes, have you set it to hourly? If you haven't we'll ignore you.
  6. This is more controversial. Can you rebuild your db from scratch?

These are good questions, thank you for forcing me to think them through @testdasi!

 

  1. I bought a used Dell Precision T1700 PC and added a brand new Blu-Ray drive, two brand new 10tb shucked hard drives and one older shucked 4tb hard drive which had been in light use for a couple of years.
     
    • Intel Xeon E3-1226 v3 @ 3.30GHz (info)
    • 24 GiB DDR3
    • GTX 1050ti 4GB
    • 2 Western Digital 10tb drives
    • 1 Western Digital 4tb Drive
    • LG Internal Blu-Ray Drive (WH16NS40)
    • Motherboard: Dell Inc. 0JVY7H Version A00

      BIOS: Dell Inc. Version A27. Dated: 10/24/2018
       

  2. Only the Movies Library is currently indexed. I originally had 3 Movie libraries (those are now combined into one), a modest Music library, and a small TV Show Library.
     
    • /media/media/Movies: 4.1 TiB, 1005 files, 164 subfolders
    • /media/media/Music: 32.4 GiB, 2870 files, 179 subfolders
    • /media/media/Photos: 264.8 GiB, 122548 files, 12708 subfolders
    • /media/media/TV Shows: 112.1 GiB, 229 files, 19 subfolders
       
  3. Currently: /mnt/user/appdata/binhex-plexpass.
     
    • I believe I previously set it to /mnt/disk1/appdata/binhex-plexpass, and corruption still occurred.
    • I personally do not believe changing this helps.
       
  4. I am not sure if this is what you mean but: Sonarr, Radarr, and Ripper are all currently set to automatically copy completed files to the appropriate folders.
     
    • I almost exclusively acquire media using the Ripper docker app.
    • I do not currently have a TV Shows library setup, so Sonarr specifically is not currently a factor.
    • Only a single Movie library is currently being indexed.
       
  5. Automatic scans are currently disabled on my Movie library, so my scans are only initiated manually when I beleive all file copying activity to be complete.
     
    • I have added less than 10 movies to my library in the past week.
    • I installed a Blu-ray drive at the same time as I setup unRaid (maybe 5 weeks ago).
    • I have probably scanned about 200 Blu-ray movies in the first 3-4 weeks.
    • I have not had a corruption in the past 5 days since I have rebuilt my database, switched to manual only scanning, and slowed movie additions.
    • I suspect careful use of manual scanning is the key, but my slowing collection growth also means less changes are happening.
       
  6. So far I have exclusively rebuilt my database from scratch.
     
    • Unfortunately I was never successful following the sqlite3 dump and rebuild guide.
    • The last time though I finally was able to slowly but surely complete the reorganization of my approximately 250 movie collection, so I stopped my Plex docker and manually copied the sqlite3 .db file.
       
  7. Other relevant information:
    • unRaid 6.7.0 -- until I installed 6.7.1 this morning
    • Global Share Settings > Tunable (allow Direct IO) is set to NO.
Edited by Brian H.
added: BIOS/mb info + "7. Other relevant information"

Share this post


Link to post

@runraid  To eliminate the possibility, have you tied updating your BIOS? the firmware on the SSD?

Share this post


Link to post

Are all of you having issues using plex for photos?

Share this post


Link to post
Posted (edited)
3 minutes ago, saarg said:

Are all of you having issues using plex for photos?


FWIW, I only added my photos folder for completeness. However, I only tried importing that folder the first time I set Plex up. I would like to re-add it someday, but I am trying to keep the number of libraries I have to rebuild down while troubleshooting.

Edited by Brian H.

Share this post


Link to post
Posted (edited)

unRaid 6.7.1 was just released. I installed it and am going to test and remain on that going forward (unless something unforeseen causes me to go back to 6.7.0).

Limetech's release notes have 3 highlighted items on Reddit, one of which was is this issue:
 

Quote
  • We are also still trying to track down the source of SQLite Database corruption.  It will also be very helpful for those affected by this issue to also upgrade to this release.


FYI I had to remove `--runtime=nvidia` from the Extra Parameters field in my @binhex-plexpass docker. I had previously followed a guide to enabling GPU acceleration. I believe that I only direct play movies so I don't think I need GPU acceleration right now. But I do have a GTX 1050 so I'd like to have the option at some point.

Edited by Brian H.
added limetech's release notes

Share this post


Link to post
14 minutes ago, runraid said:

This is my hardware 

 

I looked at the last set of diagnostics you posted, and your BIOS is over 4 years old, and the latest ver was released a couple of months ago.

Share this post


Link to post
Posted (edited)

I don't mean to send someone down a rabbit hole that doesn't need to be gone down; but after reading this thread, one question jumped out at me.

 

Does anyone who is experiencing this corruption have a setup involving raid cards (LSI or other)?

 

From what I'm seeing (with the limited listing of hardware specs provided thus far), it seems to be impacting people who directly connect drives to their motherboard. Not sure if this actually matters or not but just a thought.

Edited by GroxyPod

Share this post


Link to post
5 hours ago, GroxyPod said:

Does anyone who is experiencing this corruption have a setup involving raid cards (LSI or other)?

 

From what I'm seeing (with the limited listing of hardware specs provided thus far), it seems to be impacting people who directly connect drives to their motherboard. Not sure if this actually matters or not but just a thought.

My drives are connected directly to my motherboard.

Share this post


Link to post
5 hours ago, GroxyPod said:

Does anyone who is experiencing this corruption have a setup involving raid cards (LSI or other)? 

 

From what I'm seeing (with the limited listing of hardware specs provided thus far), it seems to be impacting people who directly connect drives to their motherboard. Not sure if this actually matters or not but just a thought.

Another data point in case it helps:  I do not have the sqlite corruption problem and all my data drives are connected to a Dell H310 (LSI 9211) controller; however, my SSD cache drive on which I have appdata/Plex installed is directly connected to the motherboard for TRIM support.

Share this post


Link to post
9 hours ago, Brian H. said:

I would recommend keeping backups once in a while as well just in case you are unable to recover the database. I tried at least 6 separate times, and each time the test at the command line after re-importing the data reported problems. So, I have given up on that process.

 

I should probably clarify I do keep the 3 days backup from Plex as well as a daily app data backup.

 

Is just restoring from backup is a pain as depending on how close I am to the 3 days backup I will lose tv shows progress (our household consume a lot) or I will need to extract the app data backup. Much easier for me to just run a single script to drop a couple of index, dump the db and rebuild as it will take me less then a minute.

Share this post


Link to post
6 hours ago, GroxyPod said:

I don't mean to send someone down a rabbit hole that doesn't need to be gone down; but after reading this thread, one question jumped out at me.

 

Does anyone who is experiencing this corruption have a setup involving raid cards (LSI or other)?

 

From what I'm seeing (with the limited listing of hardware specs provided thus far), it seems to be impacting people who directly connect drives to their motherboard. Not sure if this actually matters or not but just a thought.

directly to the motherboard here

 

I have a LSI card on the way though which I'm going to setup my cache on 

Share this post


Link to post
1 hour ago, RaihaX said:

 

I should probably clarify I do keep the 3 days backup from Plex as well as a daily app data backup.

 

Is just restoring from backup is a pain as depending on how close I am to the 3 days backup I will lose tv shows progress (our household consume a lot) or I will need to extract the app data backup. Much easier for me to just run a single script to drop a couple of index, dump the db and rebuild as it will take me less then a minute.

yeah, I've been keeping hourly backups of my database, makes it easier to restore to where I need to.  just script copying it hourly to a different folder, works fine.

 

I just have been running this command hourly (using user scripts addon). Obviously you'll need to create the dbbackups folder.

 

tar zcvf /mnt/cache/appdata/PlexMediaServer/Library/Application\ Support/Plex\ Media\ Server/Plug-in\ Support/Databases/dbbackups/com.plexapp.plugins.library.db-$(date +%A-%H%M).tar.gz /mnt/cache/appdata/PlexMediaServer/Library/Application\ Support/Plex\ Media\ Server/Plug-in\ Support/Databases/com.plexapp.plugins.library.db

 

Share this post


Link to post

I think you can update your script to run the integrity check first and only back up if the database is ok 

Share this post


Link to post
Posted (edited)
2 minutes ago, runraid said:

I think you can update your script to run the integrity check first and only back up if the database is ok 

yeah, just been lazy... was thinking of doing that as well as shooting out an email/sms message to my phone letting me know its corrupt. its been 10 or 11 days since my last corruption, so... motivation isn't all there either :)

Edited by Abzstrak

Share this post


Link to post

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.