Plex database corruption about updatetool HOT 15 CLOSED

Which version of the tool are you using?

from updatetool.

Hi @mynttt,

Last version. I just installed it 2 hours ago on unraid

from updatetool.

okay, after restoring from a backup. I did a second attempt to run the update tool script. The plex database got corrupted again after the update tool script finished the job.

Do you have discord? Maybe we can chat?

from updatetool.

Well that is an important issue that needs to be adressed. In the past there were some corruption issues that were caused by inserting the current date in the field metadata_items.changed_at. This caused index corruption issues in rare cases with some sqlite versions. To fix this UpdateTool does not change the changed_at field anymore.

In your case the issue is a new one that I've never seen before. My discord is myntt#7938, add me there and we can try to reproduce it and find the root of this issue!

from updatetool.

Yeah, lets have a chat. i added you on discord ;)

from updatetool.

Update so far: I've received the corrupted database and running PRAGMA integrity_check returns no such collation sequence: naturalsort.

Using the following commands:

DROP index 'index_title_sort_naturalsort'
DELETE from schema_migrations where version='20180501000000'

Have at least on my system let the integrity check pass again. At this point it is still unknown if the issue was present before running updatetool and caused an issue in combination with updatetool or if updatetool is the only cause of this issue.

Further investigations will be done tomorrow in cooperation with @potatoquality to find the cause of this corruption.

from updatetool.

So, After some testing. I'm not sure what the best steps are to move forward.

I used 3 different plex database backups on different days to check out if we can find a link with updatetool.
Two backups got corrupted after running update tool, repairing with the drop index fixed the problem temporarily.
If I rerun the update tool after the database repair, it gots corrupted again.
I don't see this behavior with 1 backup. I doesnt get corrupted and works fine.

Im confused

from updatetool.

Okay, the 3e database got also corrupted. Need to rollback again

from updatetool.

Hmm lets try the following:

1.) Shutdown Plex / UpdateTool docker
2.) Restore database from backup
3.) Run this on the restored backup database

PRAGMA integrity_check;

Does it output ok? If it returns Error: no such collation sequence: naturalsort run:

DROP index 'index_title_sort_naturalsort';
DELETE from schema_migrations where version='20180501000000';

and then try again with

PRAGMA integrity_check;

Does it return ok now or shows another error?

4.) Since the database should return ok now start Plex and wait until the docker is up. Then start UpdateTool and let it finish its run until it says Completed imdb-docker in .... You can then shutdown update tool.

5.) Shutdown Plex as well. Restart Plex and check if it boots correctly. Does it show a database corruption again? If so please run

PRAGMA integrity_check;

and report what it found.

Then the corruption can at least be traced to a specific integrity check violation.

from updatetool.

Hi @mynttt,

I followed your instructions. When i did the integrity_check on the backup database before using it on the plex
C:\Users\xxxxx\sqlite-tools-win32-x86-3330000\sqlite-tools-win32-x86-3330000>sqlite3.exe com.plexapp.plugins.library.db "PRAGMA integrity_check"
Error: no such collation sequence: naturalsort

So i followed steps. After the updatetool is completed, i shutdown plex and the update tool and did the integrity check again.
See the last line for the outcome:

from updatetool.

So basically:

• database backup failed the integrity check
• running the commands
• integrity check passes and plex works well
• running updatetool
• now the integrity check fails again

If you now run the drop and delete command again does the database work with plex again? This issue is really mysterious, something is causing plex to act weird. I don't believe in magic but the database is kinda hexed since nobody had that issue before.

Maybe try repairing the database again to the point where the check passes with ok and then try this to basically force sqlite to create a new database with the same schemas and data:

sqlite3 com.plexapp.plugins.library.db .dump > dump.sql
rm com.plexapp.plugins.library.db
sqlite3 com.plexapp.plugins.library.db < dump.sql

Maybe this works. If not than I have sadly no clue what could cause these issues.

from updatetool.

Just to give an update: Both of us have no clue what the issue is. In some rare cases @potatoquality is able to run the tool without corruption but most of the time a successfull run is not possible. I can however run the tool on his database with the same container (binhex-plex) without issues. It's probably a really strange and hard to debug system/plex/container instance related issue.

The corruption is not a serious one, using

DROP index 'index_title_sort_naturalsort';
DELETE from schema_migrations where version='20180501000000';

fixes it. But thats sadly annoying to do. So if someone else runs into this issue and finds something, post it in this issue thread.

from updatetool.

This is happening to me as well. I have had UpdateTool for a long time and it just started happening over a month ago. My Plex database would get corrupted every day, and I didn't know why. I ran UpdateTool earlier because some shows had no ratings, and others were old. Immediately after it finished, Plex wouldn't show any libraries. So I used a backup and got Plex back up. Then I restarted UpdateTool, and same thing. Plex log would say database corruption. I'm on the latest unRAID docker version (1.4.6). My Plex docker repo is plexinc/pms-docker:plexpass.

from updatetool.

@AwesomeAustn Hmm that's really weird, especially that it just started out of nowhere a month ago. If a lot of others ran into that issue I would've probably gotten at least a few issues regarding it, so I'm not sure what's happening there.

The original corruption that was discussed in this issue was actually solved, so I'm even more surprised that it happens to you with the newest version. Maybe if you send me the uncorrupted and corrupted file via mail I could take a look at it but else I have no clue what could cause it.

from updatetool.

@AwesomeAustn @Magikarplvl4 If you want to give it a try again with Plex Version 1.22.1.4200 there has been a change to the database schema. Maybe it works now for you if you want to try. #56

from updatetool.