Corrupt Database - Movies & TV can't be marked as 'watched'

[AdricArcop]

Server Version#: 1.25.4.5487
Player Version#: Web Version 4.69.1

So I’ve left this issue run for several months (I think it started in ~July 2021) since it wasn’t blocking me from watching things, but it’s finally reached the point where it’s annoying enough for me to troubleshoot

Symptoms:

1. Lost a few movie collections, some partially some completely (movie files still exist)
2. When I finish watching a movie or episode of TV, it does not get marked as ‘watched’. It stays at ~95% watched.
3. Can’t manually mark TV episodes, seasons, shows, or Movies as ‘watched’

Having a peek at the logs, it looks like there is a DB corruption issue, which makes sense. In the Plex Settings > Scheduled Tasks I have the box ticked to backup the database every 3 days. When I go to the ‘Backup directory’ it looks like the last com.plexapp.plugins.library.db backup was made on 2021-05-13. The last com.plexapp.plugins.library.blobs.db backup was 2022-02-01 (and there are three more going back to 2022-01-23).

[/share/CACHEDEV1_DATA/.qpkg/PlexMediaServer/Library/Plex Media Server/Plug-in Support/Databases] # ls -ltr
total 9238896
-rw-r--r-- 1 admin administrators    8024064 2019-12-22 23:46 com.plexapp.plugins.trakttv.db
-rw-r--r-- 1 admin administrators      32768 2019-12-23 09:25 com.plexapp.plugins.trakttv.db-shm
-rw-r--r-- 1 admin administrators    4157112 2019-12-23 09:25 com.plexapp.plugins.trakttv.db-wal
-rw-r--r-- 1 admin administrators  760367104 2021-05-04 02:01 com.plexapp.plugins.library.db-2021-05-04
-rw-r--r-- 1 admin administrators  760367104 2021-05-07 02:00 com.plexapp.plugins.library.db-2021-05-07
-rw-r--r-- 1 admin administrators  754630656 2021-05-10 02:00 com.plexapp.plugins.library.db-2021-05-10
-rw-r--r-- 1 admin administrators  754765824 2021-05-13 02:00 com.plexapp.plugins.library.db-2021-05-13
-rw-r--r-- 1 admin administrators 1089975296 2022-01-23 02:04 com.plexapp.plugins.library.blobs.db-2022-01-23
-rw-r--r-- 1 admin administrators 1090806784 2022-01-26 02:03 com.plexapp.plugins.library.blobs.db-2022-01-26
-rw-r--r-- 1 admin administrators 1090965504 2022-01-29 02:03 com.plexapp.plugins.library.blobs.db-2022-01-29
-rw-r--r-- 1 admin administrators 1125001216 2022-02-01 02:05 com.plexapp.plugins.library.blobs.db-2022-02-01
drwxr-xr-x 2 admin administrators       4096 2022-02-01 02:10 Music Analysis 3/
-rw-r--r-- 1 admin administrators 1125062656 2022-02-01 18:41 com.plexapp.plugins.library.blobs.db
-rw-r--r-- 1 admin administrators          0 2022-02-01 19:53 com.plexapp.plugins.library.blobs.db-wal
-rw-r--r-- 1 admin administrators      32768 2022-02-01 20:29 com.plexapp.plugins.library.blobs.db-shm
-rw-r--r-- 1 admin administrators    2150400 2022-02-01 20:29 com.plexapp.dlna.db
-rw-r--r-- 1 admin administrators      32768 2022-02-01 20:29 com.plexapp.dlna.db-shm
-rw-r--r-- 1 admin administrators    2385512 2022-02-01 20:29 com.plexapp.dlna.db-wal
-rw-r--r-- 1 admin administrators  890702848 2022-02-01 20:37 com.plexapp.plugins.library.db
-rw-r--r-- 1 admin administrators    1056416 2022-02-01 20:50 com.plexapp.plugins.library.db-wal
-rw-r--r-- 1 admin administrators      32768 2022-02-01 20:50 com.plexapp.plugins.library.db-shm

I followed this process to run the auto check and repairs on the db, but the check returned ‘OK’ pretty much immediately and the manual repair tool finished and produced a 55b ‘dump.sql’ file pretty much immediately as well.

I tried restoring the DB by following this process, but when I started Plex back up it wouldn’t load in my browser and I was getting some ‘corrupt database’ errors in the logs. I tried all of my backup .db files but they all had the same result.

[/share/CACHEDEV1_DATA/.qpkg/PlexMediaServer/Library/Plex Media Server/Logs] # cat "Plex Media Server.3.log2: 
> 
[/share/CACHEDEV1_DATA/.qpkg/PlexMediaServer/Library/Plex Media Server/Logs] # cat "Plex Media Server.3.log" 
Feb 01, 2022 19:51:47.651 [0x7fc5782a1b38] INFO - Plex Media Server v1.25.4.5468-989df2310 - QNAP TVS-1282 x86_64 - build: linux-x86_64 qnap - GMT 11:00
Feb 01, 2022 19:51:47.651 [0x7fc5782a1b38] INFO - Linux version: QTS 5.0.0.1891, language: en-US
Feb 01, 2022 19:51:47.651 [0x7fc5782a1b38] INFO - Processor Intel(R) Core(TM) i7-6700 CPU @ 3.40GHz
Feb 01, 2022 19:51:47.651 [0x7fc5782a1b38] INFO - Compiler is - Clang 11.0.1 (https://plex.tv b587490162c22e078c314e3f7dc560c691d126aa)
Feb 01, 2022 19:51:47.651 [0x7fc5782a1b38] INFO - ./Plex Media Server
Feb 01, 2022 19:51:47.653 [0x7fc57b93fb58] ERROR - SQLITE3:0x80000001, 11, database corruption at line 68176 of [1b256d97b5]
Feb 01, 2022 19:51:47.653 [0x7fc57b93fb58] ERROR - SQLITE3:0x80000001, 11, database disk image is malformed in "PRAGMA cache_size=2000"
Feb 01, 2022 19:51:47.653 [0x7fc57b93fb58] ERROR - Database corruption: sqlite3_statement_backend::prepare: database disk image is malformed for SQL: PRAGMA cache_size=2000
Feb 01, 2022 19:51:47.653 [0x7fc57b93fb58] ERROR - Error: Unable to set up server: sqlite3_statement_backend::prepare: database disk image is malformed for SQL: PRAGMA cache_size=2000 (N4soci10soci_errorE)

Losing my watched history isn’t that big of a deal, but I had invested a good chunk of time building some kickass Movie collections and Playlists, and it’d be pretty devastating to have to start over on them.

I’ve attached some logs from when Plex starts up with the ‘usable’ corrupt database. Does anyone have any ideas on how to repair this DB or any of the DB backup files?
plex.log (13.3 KB)

[dane22]

You are welcome to zip all the backup databases as well as the current one and upload somewhere

Then DM me a download link

[AdricArcop]

Thanks Dane!

DM’d

[dane22]

Hopefully fixed your database

DM’ed a download link, and let me know when downloaded, so I can nuke it again from DropBox

You now need to do the following:

In QNAP AppCenter, shut down PMS

In the database directory, create a directory named backup, and move all files starting with
com.plexapp.plugins.library. to that directory

Then copy in the database I fixed, and then start PMS again

Wait for at least 2 min, and then see if you from the WebClient can browse to it
If so, then rescan all your libraries one by one, but wait until one finish, until starting the next one!
When all is hopefully done, do an optimize of your database

You should then be fine, and for the next couple of days, check that butler scheduled tasks do make new backups of your database

[dane22]

Ohh…And forgot…

Recommended just in case to also do a Refresh all Metadata on all libs afterwards, to avoid missing posters etc.

[AdricArcop]

Thanks I got it! But there still seems to be a problem with the DB…

I did:

1. In QNAP AppCenter, shut down PMS

2. In the database directory, create a directory named backup, and move all files starting with
   com.plexapp.plugins.library. to that directory

[/share/CACHEDEV1_DATA/.qpkg/PlexMediaServer/Library/Plex Media Server/Plug-in Support/Databases] # ls -l
total 32796
drwxr-xr-x 2 admin administrators     4096 2022-02-02 12:05 backup/
-rw-r--r-- 1 admin administrators  2150400 2022-02-01 22:39 com.plexapp.dlna.db
-rw-r--r-- 1 admin administrators    32768 2022-02-01 22:39 com.plexapp.dlna.db-shm
-rw-r--r-- 1 admin administrators  2385512 2022-02-01 22:39 com.plexapp.dlna.db-wal
-rw-r--r-- 1 admin administrators  8024064 2019-12-22 23:46 com.plexapp.plugins.trakttv.db
-rw-r--r-- 1 admin administrators    32768 2019-12-23 09:25 com.plexapp.plugins.trakttv.db-shm
-rw-r--r-- 1 admin administrators  4157112 2019-12-23 09:25 com.plexapp.plugins.trakttv.db-wal
drwxr-xr-x 2 admin administrators     4096 2022-02-02 02:20 Music Analysis 3/

3. Then copy in the database I fixed, and then start PMS again

[/share/CACHEDEV1_DATA/.qpkg/PlexMediaServer/Library/Plex Media Server/Plug-in Support/Databases] # ls -l
total 32796
drwxr-xr-x 2 admin administrators     4096 2022-02-02 12:05 backup/
-rw-r--r-- 1 admin administrators  2150400 2022-02-01 22:39 com.plexapp.dlna.db
-rw-r--r-- 1 admin administrators    32768 2022-02-01 22:39 com.plexapp.dlna.db-shm
-rw-r--r-- 1 admin administrators  2385512 2022-02-01 22:39 com.plexapp.dlna.db-wal
-rw-r--r-- 1 admin administrators 16777216 2022-02-02 12:08 com.plexapp.plugins.library.db
-rw-r--r-- 1 admin administrators  8024064 2019-12-22 23:46 com.plexapp.plugins.trakttv.db
-rw-r--r-- 1 admin administrators    32768 2019-12-23 09:25 com.plexapp.plugins.trakttv.db-shm
-rw-r--r-- 1 admin administrators  4157112 2019-12-23 09:25 com.plexapp.plugins.trakttv.db-wal
drwxr-xr-x 2 admin administrators     4096 2022-02-02 02:20 Music Analysis 3/

[/share/CACHEDEV1_DATA/.qpkg/PlexMediaServer/Library/Plex Media Server/Plug-in Support/Databases] # md5sum com.plexapp.plugins.library.db 
f8f04866001643340fc0c166271e3bc4  com.plexapp.plugins.library.db

4. Wait for at least 2 min, and then see if you from the WebClient can browse to it

I waited 10 minutes, but can’t get Plex to load in the WebClient. I get an Unable to Connect: Firefox can’t establish a connection to the server at 192.168.4.3:32400 error.

I checked on the logs and it looks like the DB still might be corrupt:

[/share/CACHEDEV1_DATA/.qpkg/PlexMediaServer/Library/Plex Media Server/Logs] # tail -f Plex\ Media\ Server.log 
Feb 02, 2022 12:10:06.838 [0x7f7b33bd4b38] INFO - Plex Media Server v1.25.4.5487-648a8f9f9 - QNAP TVS-1282 x86_64 - build: linux-x86_64 qnap - GMT 11:00
Feb 02, 2022 12:10:06.838 [0x7f7b33bd4b38] INFO - Linux version: QTS 5.0.0.1891, language: en-US
Feb 02, 2022 12:10:06.838 [0x7f7b33bd4b38] INFO - Processor Intel(R) Core(TM) i7-6700 CPU @ 3.40GHz
Feb 02, 2022 12:10:06.838 [0x7f7b33bd4b38] INFO - Compiler is - Clang 11.0.1 (https://plex.tv b587490162c22e078c314e3f7dc560c691d126aa)
Feb 02, 2022 12:10:06.838 [0x7f7b33bd4b38] INFO - ./Plex Media Server
Feb 02, 2022 12:10:06.888 [0x7f7b37272b58] ERROR - SQLITE3:0x80000001, 11, database corruption at line 68176 of [1b256d97b5]
Feb 02, 2022 12:10:06.888 [0x7f7b37272b58] ERROR - SQLITE3:0x80000001, 11, database disk image is malformed in "PRAGMA cache_size=2000"
Feb 02, 2022 12:10:06.888 [0x7f7b37272b58] ERROR - Database corruption: sqlite3_statement_backend::prepare: database disk image is malformed for SQL: PRAGMA cache_size=2000

[dane22]

Okay, so let’s try it again from the beginning…

On my QNAP, PMS is stopped

Making sure that there’s no runaway threads

Have a completely empty database directory

Copy in your broken database

Check if it’s broken

Yep, it’s broken, so need to fix it

Remove broken database, so only the dump.sql file is left in there

If trying to rebuild the database now from the dump.sql file, we’ll sadly fail due to memory, since QNAP stores tmp files in Ram !!!

So we need to redirect this to disk instead

mkdir /share/CACHEDEV1_DATA/.qpkg/PlexMediaServer/Library/Plex\ Media\ Server/Plug-in\ Support/Databases/tmp

export TMPDIR=/share/CACHEDEV1_DATA/.qpkg/PlexMediaServer/Library/Plex\ Media\ Server/Plug-in\ Support/Databases/tmp

export TMP=$TMPDIR

Verify they are set:

Rebuild database from dump.sql file

After it’s done, check it

After that’s done and just for your ref, here’s the MD5Sum

Now remove the tmp dir and the dump.sql file, and then exit the SSH session, so the tmp exports will not trick us

Start PMS again from a new SSH session

Verify it’s up and running again

Profit

[AdricArcop]

It worked! Database is working:

1. Movie Collections are restored
2. Movies and Shows are marked as ‘watched’ when I finish watching them
3. I can manually mark Movies and Shows as ‘watched’ again

Thanks so much for your help and fantastically detailed instructions!