Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
#!/bin/bash
# https://gist.github.com/scrathe
# 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-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/
# this script may potentially fix common Plex DB errors such-as
# 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment