plex-fix-db.sh

#!/bin/bash

# https://gist.github.com/scrathe
# https://www.reddit.com/r/PleX/comments/vcc95i/database_repair_script_for_common_plex_db_errors/

# what is this? performs the steps outlined in Plex's garbage instructions on how to "Manually Repair the Database" https://support.plex.tv/articles/repair-a-corrupted-database/

# notes
# although this script will backup your Plex DB, i would suggest making your own backup.
# this script assumes you are comfortable with copying/pasting/running random things from the internet.
# this script assumes a DOCKER ENVIRONMENT (unRAID and Linuxserver.io Plex docker). modify accordingly to suit your needs.
# thank you 🙏 SpaceInvaderOne! you put a ton of effort into the unRAID community and we all appreciate it! https://www.youtube.com/watch?v=2ZDX7Gq7UFU
# leave your Plex docker running while running this script

# release notes
# 2022-11-03 updated execution from "Plex SQLite" to ./Plex\ SQLite
# 2022-06-14 bash script version released
# 2021-11-09 shared a fix which seems to be helping those of us suffering Plex DB corruption issues. https://www.reddit.com/r/PleX/comments/qitj8k/comment/hjzhslj/

# example errors this script can potentially fix
# Error: Unable to set up server: sqlite3_statement_backend::prepare: no such table: schema_migrations for SQL: select version from schema_migrations order by version (N4soci10soci_errorE)
# ERROR - Exception inside transaction (inside=1) (/data/jenkins/server/3533902054/Library/MediaItem.cpp:908): sqlite3_statement_backend::loadOne: database disk image is malformed
# WARN - Caught exception while scanning Movies: sqlite3_statement_backend::loadOne: database disk image is malformed
# WARN - Took too long (0.200000 seconds) to start a transaction on

# start user defined variables

# the "name" of your Plex docker colon(:) and "path to plexmediaserver" relative to inside the docker filesystem
# plexDocker="plex:/usr/lib/plexmediaserver/"
plexDocker="plex:/usr/lib/plexmediaserver/"

# new/empty working directory
# sqlCmdDir="/mnt/cache/appdata/plex-sqlite"
sqlCmdDir="/mnt/cache/appdata/plex-sqlite"

# full path to the 'directory' containing the Plex DB relative to the host running the script. NOT the path relative to inside the docker filesystem.
# plexDBpath="/mnt/cache/appdata/plex/Library/Application Support/Plex Media Server/Plug-in Support/Databases"
plexDBpath="/mnt/cache/appdata/plex/Library/Application Support/Plex Media Server/Plug-in Support/Databases"

# Plex DB filename
# plexDBfile="com.plexapp.plugins.library.db"
plexDBfile="com.plexapp.plugins.library.db"

# end user defined variables

# spinner source https://gist.github.com/marcom04/f944b2e40d32035e42cdaf928b7a9502
spinner(){
	local phrase="working..."
	local pid=$1
	local delay=0.5        # Adjust spinner speed
	local spinstr='|/-\'   # Spinner sequence
	while [ "$(ps a | awk '{print $1}' | grep $pid)" ]; do
		local temp=${spinstr#?}
		printf " %s [%c] " "$phrase" "$spinstr"
		local spinstr=$temp${spinstr%"$temp"}
		printf "\r"
		sleep $delay
	done
    printf "\n"     # remove this if you want to delete "phrase" when action is complete
    printf "\r"
}

doTheThings(){
    # strip trailing "/" from $plexDBpath
    plexDBpath=$(echo $plexDBpath | sed -r 's/\/$//g')
    plexDB="${plexDBpath}/${plexDBfile}"

    # check database file exists
    ls -l "$plexDB" > /dev/null 2>&1
    if [[ $? -ne 0 ]]; then
        echo "!!! ERROR, Plex DB file not found"
        exit 1
    fi

    # make working directory
    mkdir $sqlCmdDir
    # change into working directory
    cd $sqlCmdDir
    if [[ $? -ne 0 ]]; then
        echo "!!! ERROR, cd '$sqlCmdDir'"
        exit 1
    fi
    
    # copies the "Plex SQLite" utility from the docker to your local host environment
    echo "!!! INFO, copying container to working directory..."
    (
        docker cp $plexDocker $sqlCmdDir
        if [[ $? -ne 0 ]]; then
            echo "!!! ERROR, copying SQL Lite command from '$plexDocker' to '$sqlCmdDir'"
            exit 1
        fi
        sleep 10
    ) &
    spinner $!

    # all following commands assume you're in this directory
    cd "$sqlCmdDir/plexmediaserver"
    if [[ $? -ne 0 ]]; then
        echo "!!! ERROR, cd '$sqlCmdDir/plexmediaserver'"
        exit 1
    fi

    # check the database integrity
    ./Plex\ SQLite "$plexDB" "PRAGMA integrity_check"
    if [[ $? -ne 0 ]]; then
        echo "!!! ERROR, checking Plex DB integrity"
        exit 1
    fi
    echo ""
    echo "!!! INFO, if you see a errors above ^^^ this definatly most likely confirms your Plex DB probably isn't healthy 😁"
    echo ""

    # backup database
    now=$(date +"%Y-%m-%d-%H-%M")
    echo "!!! INFO, backing up Plex DB"
    backupPlexDB="${plexDB}-backup-${now}"
    cp "$plexDB" "$backupPlexDB"
    if [[ $? -ne 0 ]]; then
        echo "!!! ERROR, backing up Plex DB to '$backupPlexDB'"
        exit 1
    fi
    echo "!!! INFO, backup located here "$backupPlexDB""

    # recover database
    # this never seems to fix DB errors so i opt for "Manually Repair the Database" per the instructions here https://support.plex.tv/articles/repair-a-corrupted-database/
    # "Plex SQLite" "$plexDB" ".output recover.out" ".recover"
    # if [[ $? -ne 0 ]]; then
    #     echo "!!! ERROR, creating 'recover.out'"
    #     exit 1
    # fi

    # dump the database
    echo "!!! INFO, exporting Plex DB to 'dump.sql'... you may see errors..."
    (
        ./Plex\ SQLite "$plexDB" ".output dump.sql" ".dump"
        if [[ $? -ne 0 ]]; then
            echo "!!! ERROR, creating 'dump.sql'"
            exit 1
        fi
        sleep 10
    ) &
    spinner $!

    # it seems that Plex Devs fixed this bug, but running the command doesn't hurt anything. i can't remember where i got this fix from. thank you random person on the internet! ❤
    # replace ROLLBACK with COMMIT at the end of the 'dump.sql'
    sed -i 's/ROLLBACK;/COMMIT;/g' dump.sql
    if [[ $? -ne 0 ]]; then
        echo "!!! ERROR, modifying 'dump.sql'"
        exit 1
    fi

    # delete original database (ensure you have backup)
    echo "!!! INPUT, delete original Plex DB file? (you made a backup right?)"
    echo "!!! INFO, this script created a backup located here '$backupPlexDB'"
    rm -i "$plexDB"
    if [[ $? -ne 0 ]]; then
        echo "!!! ERROR, deleting original Plex DB"
        exit 1
    fi

    if [[ -e "$plexDB" ]]; then
        echo "!!! ERROR, PlexDB still exists. Did you answer 'y' to remove?"
        exit 1
    fi

    # import 'dump.sql' database
    echo "!!! INFO, recreating Plex DB by importing 'dump.sql'... you may see errors..."
    (
        ./Plex\ SQLite "$plexDB" ".read dump.sql"
        if [[ $? -ne 0 ]]; then
            echo "!!! ERROR, importing 'dump.sql'"
            exit 1
        fi
        sleep 10
    ) &
    spinner $!

    # database integrity check post-import
    ./Plex\ SQLite "$plexDB" "PRAGMA integrity_check"
    if [[ $? -ne 0 ]]; then
        echo "!!! ERROR, checking Plex DB integrity"
        exit 1
    fi
    echo "!!! INFO, hopefully you see no errors above ^^^"

    # delete these if they exist
    echo "!!! INFO, delete the following two (2) files IF THEY EXIST. (these will be recreated by Plex next restart)"
    echo "!!! INPUT, delete file?"
    rm -i "${plexDBpath}/com.plexapp.plugins.library.db-shm"
    echo "!!! INPUT, delete file?"
    rm -i "${plexDBpath}/com.plexapp.plugins.library.db-wal"

    # fix permissions of new database file (unRAID specific example)
    chown 99:100 "$plexDB"
    chmod 775 "$plexDB"
    ls -lh "$plexDB"

    echo "!!! DONE, highly recommend performing the following..."
    echo "          1) restart your Plex docker 'docker restart plex'"
    echo "          2) monitor docker output for errors 'docker logs plex --follow'"
    echo "          3) monitor Plex log file while performing the operations below 'tail -f \"Plex Media Server.log\"'"
    echo "          4) run 'Optimize Database' & 'Clean Bundles' from Plex Web UI under Settings, Manage, Troubleshooting"
    echo "          5) there are some left over files you may want to cleanup; dump.sql, $backupPlexDB, $sqlCmdDir"
}

doTheThings

exit

Where the command "Plex SQLite" is called on lines 102, 124, 133, 167 and 177 it needs to be changed to "./Plex SQLite". This is because "Plex SQLite" is not in the path so running the binary without specifying the location is not supported.

thank you for pointing this out!
updated execution from "Plex SQLite" to ./Plex\ SQLite

./plex-fix-db.sh: line 104: ./Plex SQLite: No such file or directory
!!! ERROR, checking Plex DB integrity

I think this is because the command listed in the script needs to be modified for it to run from within the container? I think.... any suggestions would be helpful.

This appears to be the same as this, but in script form, no?
https://support.plex.tv/articles/repair-a-corrupted-database/