Pain & suffering. Cleanup Jellyfin libraries

Hi!

Jellyfin (that awesome self-hosted media center, btw) has a peculiar behaviour regarding updating / deleting files from the filesystem (not from the Jellyfin UI) - it ignores such events. And that can mess up your libraries being scanned.

For example, you've added a folder with a TV show to a freshly created library. Then you've scanned it and everything worked as intended - the videos are there and playable. But then, after finishing the series from that folder you went and deleted the directory from the filesystem. Well, that's a mistake - now Jellyfin still has the references to those files, but they are absent in the filesystem. This breaks the library scanning and Jellyfin won't be able to recognize new files when you add them. Moreover, the library recreation doesn't work either - seems like it's just restored from the DB (it is, when you use the same names)

So, we'll need to remove those dangling references and rescan the library once again. General steps are:

1. Delete the troublesome folder from Jellyfin UI, if you have it there.
2. Stop Jellyfin service (go to Dashboard and hit Shutdown). Use systemctl status jellyfin / systemctl stop jellyfin commands if needed.
3. Locate and backup Jellyfin DB (it's a sqlite DB, mine was at /var/lib/jellyfin/data/jellyfin.db on NixOS).
4. Install sqlite3 if you don't have it already.
5. Search the DB for items in BaseItems table to delete, delete them with their children.
6. Start Jellyfin.
7. Run a library rescan, where that folder was. It should finish without error (check journalctl -xeu jellyfin for details).
8. Now you can add other files / directories on the filesystem and rescan should find them.

Here's a bash script to automate the deletion step (change JELLYFIN_DB variable to point to your Jellyfin DB file):

#!/usr/bin/env bash
# Removes provided item (folder / library / collection / etc) from Jellyfin DB.
# Used when files were deleted / renamed in filesystem and Jellyfin didn't caught that.

set -e

JELLYFIN_DB="/var/lib/jellyfin/data/jellyfin.db"


read -p "Have you stopped Jellyfin and backed up '$JELLYFIN_DB'? [y/N]: " bkp_ok
if [[ "$bkp_ok" != "y" ]]; then
  echo "Then go do it!"
  exit 0
fi

read -p "Item to search: " item
if [[ -z "$item" ]]; then
  echo "No name provided, exiting."
  exit 0
fi

printf "Searching for '$item'...\nFound (Id, Name, Type, Path, ChildrenCount):\n\n"
sqlite3 "$JELLYFIN_DB" -column "
  SELECT p.Id, p.Name, p.Type, p.Path, COUNT(ch.Id) as ChildrenCount
  FROM BaseItems as p
  LEFT JOIN BaseItems as ch on ch.ParentId == p.Id
  WHERE p.Name LIKE '%$item%'
  GROUP BY p.Id
  ORDER BY p.Type, p.Name;
"

echo ""
read -p "All of those items and their children will be deleted. Is this OK? [y/N]: " is_ok
if [[ "$is_ok" != "y" ]]; then
  printf "\nCancelled.\n"
  exit 0
fi

echo "Deleting items..."
sqlite3 "$JELLYFIN_DB" "
  BEGIN TRANSACTION;
    DELETE FROM BaseItems WHERE ParentId in (SELECT Id FROM BaseItems WHERE Name LIKE '%$item%');
    DELETE FROM BaseItems WHERE Id in (SELECT Id FROM BaseItems WHERE Name LIKE '%$item%');
  COMMIT;
"

printf "Searching for '$item' again (should be empty)...\nFound (Id, Name, Type, Path, ChildrenCount):\n\n"sqlite3 "$JELLYFIN_DB" -column "
  SELECT p.Id, p.Name, p.Type, p.Path, COUNT(ch.Id) as ChildrenCount
  FROM BaseItems as p
  LEFT JOIN BaseItems as ch on ch.ParentId == p.Id
  WHERE p.Name LIKE '%$item%'
  GROUP BY p.Id
  ORDER BY p.Type, p.Name;
"

echo "Finished."

The script above will ask you the partial name of items to delete - all found matches and their children will be deleted! If you need to delete only some of them, then go ahead and edit the deletion part of SQL in the script. For example, you can use only selected Ids (which were printed in the first column after Item to search prompt) like so:

# ...

# Before:
echo "Deleting items..."
sqlite3 "$JELLYFIN_DB" "
  BEGIN TRANSACTION;
    DELETE FROM BaseItems WHERE ParentId in (SELECT Id FROM BaseItems WHERE Name LIKE '%$item%');
    DELETE FROM BaseItems WHERE Id in (SELECT Id FROM BaseItems WHERE Name LIKE '%$item%');
  COMMIT;
"

# After (replace the ids in the parenthesis with yours):
echo "Deleting items..."
sqlite3 "$JELLYFIN_DB" "
  BEGIN TRANSACTION;
    DELETE FROM BaseItems WHERE ParentId in ('DDA3FC7D-2094-7132-2527-EEA2ADB8DAF9', '08DA936D-C99A-3E5F-30BD-B6AA1FE6D1A7');
    DELETE FROM BaseItems WHERE Id in ('DDA3FC7D-2094-7132-2527-EEA2ADB8DAF9', '08DA936D-C99A-3E5F-30BD-B6AA1FE6D1A7');
  COMMIT;
"

# ...

Shout out to @MrSimmo and @Zardoz8901 from this issue on github! Do read the issue for more context.

Bye!