DBRepair development

ChuckPa · October 4, 2025, 5:48pm

Here we go. This is what it looks like.

Anything obvious before I share the TAR file for folks to kick it around?

root@lizum:/usb/plex/PMS-save/Plug-in Support/Databases/databases.2025.05.17# ./DBRepair.sh --sqlite /usr/lib/plexmediaserver/ --databases "$(pwd)" deflate auto exit
 
 
 
      Database Repair Utility for Plex Media Server  (User Defined)
                       Version v1.12.00
 
[2025-10-04 13.29.10]       PlexSQLite = '/usr/lib/plexmediaserver//Plex SQLite'
[2025-10-04 13.29.10]       Databases  = '/usb/plex/PMS-save/Plug-in Support/Databases/databases.2025.05.17'
 
[2025-10-04 13.29.10] Check and Deflate started.
[2025-10-04 13.29.10] 
[2025-10-04 13.29.10] Checking the PMS databases
[2025-10-04 13.38.46] Check complete.  PMS main database is OK.
[2025-10-04 13.38.46] Check complete.  PMS blobs database is OK.
[2025-10-04 13.38.46] 
[2025-10-04 13.38.46] Backup current databases with '-BACKUP-2025-10-04_13.38.46' timestamp.
[2025-10-04 13.38.59] Starting Deflate.  (There will be no output until complete.)
[2025-10-04 13.40.08] PMS main database successfully repaired.
[2025-10-04 13.40.08] Reducing main database size.
[2025-10-04 13.40.09] PMS main database size reduced.
[2025-10-04 13.40.09] Verifying PMS main database.
[2025-10-04 13.40.10] Verification complete.  PMS main database is OK.
[2025-10-04 13.40.10] PMS main database reduced from 31586 MB to 206 MB (153 %)
[2025-10-04 13.40.10] PMS main database deflate completed.
[2025-10-04 13.40.10] Saving current main database with '-BLOATED-2025-10-04_13.38.46'
[2025-10-04 13.40.10] Making deflated database active
[2025-10-04 13.40.10] Deflate complete. Please check your library settings and contents for completeness.
[2025-10-04 13.40.10] Deflate successful.
[2025-10-04 13.40.10] Recommend running Auto next to complete optimization of new database.
 
[2025-10-04 13.40.10] Automatic Check,Repair,Index started.
[2025-10-04 13.40.10] 
[2025-10-04 13.40.10] Checking the PMS databases
[2025-10-04 13.40.11] Check complete.  PMS main database is OK.
[2025-10-04 13.40.11] Check complete.  PMS blobs database is OK.
[2025-10-04 13.40.11] 
[2025-10-04 13.40.11] Exporting current databases using timestamp: 2025-10-04_13.40.10
[2025-10-04 13.40.11] Exporting Main DB
[2025-10-04 13.40.14] Exporting Blobs DB
[2025-10-04 13.40.32] Successfully exported the main and blobs databases.
[2025-10-04 13.40.32] Start importing into new databases.
[2025-10-04 13.40.32] Importing Main DB.
[2025-10-04 13.40.38] Importing Blobs DB.
[2025-10-04 13.40.40] Successfully imported databases.
[2025-10-04 13.40.40] Verifying databases integrity after importing.
[2025-10-04 13.40.41] Verification complete.  PMS main database is OK.
[2025-10-04 13.40.41] Verification complete.  PMS blobs database is OK.
[2025-10-04 13.40.41] Saving current databases with '-BACKUP-2025-10-04_13.40.10'
[2025-10-04 13.40.41] Making repaired databases active
[2025-10-04 13.40.41] Repair complete. Please check your library settings and contents for completeness.
[2025-10-04 13.40.41] Recommend:  Scan Files and Refresh all metadata for each library section.
[2025-10-04 13.40.41] 
[2025-10-04 13.40.41] Backing up of databases
[2025-10-04 13.40.41] Backup current databases with '-BACKUP-2025-10-04_13.40.41' timestamp.
[2025-10-04 13.40.41] Reindexing main database
[2025-10-04 13.40.43] Reindexing main database successful.
[2025-10-04 13.40.43] Reindexing blobs database
[2025-10-04 13.40.43] Reindexing blobs database successful.
[2025-10-04 13.40.43] Reindex complete.
[2025-10-04 13.40.43] Automatic Check, Repair/optimize, & Index successful.
 
root@lizum:/usb/plex/PMS-save/Plug-in Support/Databases/databases.2025.05.17#

PS: bug found. “153 %” should be “15300 %” (shell math error)

nibbles · October 4, 2025, 6:19pm

Or you could switch from percent to multiples.

PMS main database reduced from 31586 MB to 206 MB (153x)

Or maybe it’s enough to just give the before and after size, lol

ChuckPa · October 4, 2025, 7:23pm

All:

For your consideration , DBRepair v1.12.00 “alpha 1”

Deflate works but would like to build more confidence.
– Alpha removed–

Would appreciate feedback. I can provide a 31GB sample DB if needed

ChuckPa · October 19, 2025, 12:28am

ALL:

Below please find DBRepair v1.12.00

Specific to v1.12.00

Deflate capability - Deflate a bloated PMS main database and retain bandwidth statistics.

Recommended usage: “Deflate” followed by “Auto” to optimize the DB after reduction.
Method changed to reconstruction of bandwidth table versus selective copy.
This results in correct operation of bandwidth statistics functionality
as well as significantly improved performance.

Purge/Prune merged - Purge and Prune commands have been merged. This was done because most folks will run both when cleaning up. This merge simplifies operation.

Internally, the code will silently support both commands as one for a few more releases.

As always, your comments greatly appreciated.

DBRepair-v1.12.00-beta.tar (170 KB)

nibbles · October 19, 2025, 5:59am

No issues on M1 macOS 15.7.1 or QNAP x64 QTS-4.5.4.2957.
Thanks!

ChuckPa · October 19, 2025, 6:18am

@nibbles

Did you try the deflate function?

Does it still show bandwidth data as it should?

nibbles · October 19, 2025, 8:12am

Yes I did use deflate on both. Both showed no errors. The QNAP run reported this:

Check and Deflate started.

Checking the PMS databases
Check complete.  PMS main database is OK.
Check complete.  PMS blobs database is OK.

Backup current databases with '-BACKUP-2025-10-18_18.56.40' timestamp.
Starting Deflate.  (There will be no output until complete.)
PMS main database successfully repaired.
Reducing main database size.
PMS main database size reduced.
Verifying PMS main database.
Verification complete.  PMS main database is OK.
PMS main database reduced from 122 MB to 120 MB
PMS main database deflate completed.
Saving current main database with '-BLOATED-2025-10-18_18.56.40'
Making deflated database active
Deflate complete. Please check your library settings and contents for completeness.
Deflate successful.
Recommend running Auto next to complete optimization of new database.

The Mac reported this:

Check and Deflate started.

Checking the PMS databases
Check complete.  PMS main database is OK.
Check complete.  PMS blobs database is OK.

Backup current databases with '-BACKUP-2025-10-18_18.48.59' timestamp.
Starting Deflate.  (There will be no output until complete.)
PMS main database successfully repaired.
Reducing main database size.
PMS main database size reduced.
Verifying PMS main database.
Verification complete.  PMS main database is OK.
PMS main database reduced from 1 MB to 1 MB
PMS main database deflate completed.
Saving current main database with '-BLOATED-2025-10-18_18.48.59'
Making deflated database active
Deflate complete. Please check your library settings and contents for completeness.
Deflate successful.
Recommend running Auto next to complete optimization of new database.

Reading the source code I see it would have output an error in case of bandwidth issues,
Output "Error: Could not correct statistics_bandwidth table (error $Result)".
which neither OS did. So I figure this means success.

JCHH · October 19, 2025, 9:21am

No problems on QNAP TS-664 QTS 5.2.7.3256

Enter command # -or- command name (4 char min) : 23
 
Check and Deflate started.

Checking the PMS databases
Check complete.  PMS main database is OK.
Check complete.  PMS blobs database is OK.

Backup current databases with '-BACKUP-2025-10-19_10.15.40' timestamp.
Starting Deflate.  (There will be no output until complete.)
PMS main database successfully repaired.
Reducing main database size.
PMS main database size reduced.
Verifying PMS main database.
Verification complete.  PMS main database is OK.
PMS main database reduced from 200 MB to 200 MB
PMS main database deflate completed.
Saving current main database with '-BLOATED-2025-10-19_10.15.40'
Making deflated database active
Deflate complete. Please check your library settings and contents for completeness.
Deflate successful.
Recommend running Auto next to complete optimization of new database.

btw, my DB was not inflated to begin with.

Tony_T · October 19, 2025, 10:11am

If the database isn’t bloated (i.e. the previous post reported PMS main database reduced from 200 MB to 200 MB) shouldn’t the process be skipped, even on a marginally bloated database?

JCHH · October 19, 2025, 10:42am

How would it know to skip without going through the process (genuine question)?

Tony_T · October 19, 2025, 11:00am

Maybe copy the database to a temp dir and run the process on the temp database and report similar to the prune report (Counting how many files can be removed. OK to prune 906 files? (Y/N) ? )

JCHH · October 19, 2025, 2:35pm

I’m not following your logic; the user has already asked for it to be done (by selecting option 23), so why ask again adding unnecessary complexity?

[edited for typos]

Tony_T · October 19, 2025, 3:38pm

How do I know if my database is bloated? What’s the point of running the process if it isn’t?

JCHH · October 19, 2025, 3:42pm

I didn’t know if mine was bloated or not, so I ran the option.

Tony_T · October 19, 2025, 4:05pm

My logic is, if you were able to know beforehand that it wasn’t bloated, you wouldn’t need to run the option

ChuckPa · October 19, 2025, 5:00pm

All,
This is a good point. So the question is: When should “Auto” run “Deflate” on its own?

In my experience with this problem, I’ve seen:

Some users have a lot of media with 600-700 MB databases
Bloated databases usually starting over 1GB up through the 1TB range but there are some smaller media library users with bloat at 500 MB.

Would it therefore make sense for “auto” to perform a deflate pass by default if the DB size is greater than 500 MB as a precaution ?

-or-

Would it make sense for “auto” to perform a deflate pass if the number of media items is low (as compared to the DB size). By this I mean 25,000 media items and 200 MB are normal. If the DB size and the number of media items in the DB isn’t within that range, then automatically perform the deflate ?

I’ve been struggling with how to incorporate Deflate into Auto since I started this work.

In one of my test DBs,

49091 - media items
31 GB - original DB size
207 MB - final DB size

Ratios: PMS normally has 4K bytes per media item

I think the logic should be (but VERY MUCH would like input for this)

If DB size > 2GB – do deflate
If number of media items * 4K < Current size – do deflate

Everyone’s thoughts?
(these are just example / possible thresholds. Whatever works best will be used)

Insomnic_1 · October 19, 2025, 5:58pm

I appreciate all the due consideration being given here. Thanks!

Personally I like #2 as logic for an auto function. Having some aspect of a ratio calculation rather than using a hard threshold seems like the way to go and the #2 option feels like a good way to check against a “normal” expectation for a DB.

ChuckPa · October 19, 2025, 6:13pm

@Insomnic_1

I like using the ratio as well.

I want to include the hard threshold because when we see a 300+ GB database, we already know there’s a bloat problem so it makes no sense to see what the ratio is.

Here’s the logic I have so far. This will be part of “Auto”

        fi

        # See if Deflate needed
        DBSize="$(stat $STATFMT $STATBYTES "${DBDIR}/$CPPL.db")"
        DBSize="$(($DBSize / 1000000))"  # Size in MB

        DeflateNeeded=0
        # If size > 1 GB, or ratio not 4K:1 then force the check
        if [ $DBSize -lt 1000 ]; then
         Items="$("$PLEX_SQLITE" "$CPPL.db" 'select count(*) from media_parts;')"
         [ $(($Items \* 4000)) -lt $DBSize ] && DeflateNeeded=1

        else
          # DBSize is large ( > 2 GB )
          [ $DBSize -gt 2000 ] && DeflateNeeded=1
        fi

        # Is deflate needed

The only value to be determined is that hard size threshold
Is 2GB a good number ?

JCHH · October 19, 2025, 6:22pm

I also like the ratio but, if 4k the average, perhaps trigger at 6k?

ChuckPa · October 19, 2025, 6:51pm

Triggering at 6KB is fine too.

It’s definitely something easily adjusted.

I did just restructure the trigger logic to be more legible
This block shows:

If the DB > 1.5 GB then force the deflate pass
If the DB size is more than 6K:1 then force the deflate pass

This seem to make more sense with automatic ?


        # See if Deflate needed
        DBSize="$(stat $STATFMT $STATBYTES "${DBDIR}/$CPPL.db")"
        DBSize="$(($DBSize / 1000000))"  # Size in MB
        NeedDeflate=0

        # If size > 1.5 GB, or ratio not 6K:1 then force the check
        if [ $DBSize -gt 1500 ]; then
          NeedDeflate=1

        else
         Items="$("$PLEX_SQLITE" "$CPPL.db" 'select count(*) from media_parts;')"
        [ $DBSize -ge $(($Items * 6000)) ] && NeedDeflate=1

        fi

        # Is deflate needed
        if [$NeedDeflate -eq 1 ]; then

Topic		Replies	Views
PMS db corrupted - running in BINHEX docker on Unraid Metadata & Adding Files server-unraid	64	378	September 16, 2023
Support Doc for Database Cache Size (MB)? Plex Features server-linux	222	11240	March 28, 2024
Recovering from failed Blobs DB Desktops & Laptops server-linux	26	415	January 16, 2025
Plex Media Server keeps crashing Plex Media Server server-linux	26	225	February 24, 2024
Would it be possible to get some help with -[Req#a34/Database optimization/com.plexapp.plugins.libra Remote Access server-synology , media-optimizer	48	383	October 10, 2023

DBRepair development

All:

ALL:

Related topics