Plex: Change "Date added to library" based on file modification / creation time

Sometimes Plex is going crazy after re-indexing the library / metadata und updates the list of the last added movies, tv shows, etc.

This script:

• stops the Plex container (if needed)

• obtains the path to the database file

• obtains the SQLite binary

• fixes all dates by using the file modification time

• starts the Plex container (only if it was running before starting the script)

Usually it should be only needed to be executed after a manual re-indexing, but I'm executing this script once a week through the Users Scripts Plugin to be sure its fixing once in a while:

#!/bin/bash
# shellcheck disable=SC2317

# #################### #
# Sync Plex "added to library" date with file modification date
# This script updates the added_at timestamp in Plex to match the actual file modification date
# #################### #

# #################### #
# settings
# #################### #
plex_container="plex"
backup_suffix=".before.added.update"

# #################### #
# functions
# #################### #

syslog_exit() {
  local msg="${1//\\n/$'\n'}"  # convert literal \n to real newlines
  local script_name
  script_name="$(readlink -f "$0")"
  while IFS= read -r line; do
    [[ -n "$line" ]] && logger -t bash "$script_name: $line"
  done <<< "$msg"
  exit 1
}
fatal() { [[ "$1" == *"\n"* ]] && echo -ne "$1" >&2 || echo "$1" >&2; syslog_exit "$1"; }

# #################### #
# script
# #################### #

# check if container exists
if ! docker ps -a --format '{{.Names}}' | grep -q "^${plex_container}$"; then
  fatal "Error: Container '$plex_container' does not exist"
fi

# check if container is running
container_is_running=false
if docker ps --format '{{.Names}}' | grep -q "^${plex_container}$"; then
  container_is_running=true
  echo "Plex container is running"
else
  echo "Plex container is not running (won't be started after this script)"
fi
echo ""

# get plex config directory from container
echo -n "Getting Plex config directory from container: "
plex_config_dir=$(docker inspect "$plex_container" --format='{{range .Mounts}}{{if eq .Destination "/config"}}{{.Source}}{{end}}{{end}}')
if [[ ! "$plex_config_dir" ]]; then
  fatal "Error: Could not determine Plex config directory from container '$plex_container'"
fi
echo "$plex_config_dir"
echo ""

# find the plex database file
echo -n "Searching for Plex database file: "
plex_db_path=$(find "$plex_config_dir" -type f -name "com.plexapp.plugins.library.db" 2>/dev/null | head -n 1)
if [[ ! "$plex_db_path" ]]; then
  fatal "Error: Could not find Plex database file in '$plex_config_dir'"
fi
echo "$plex_db_path"
echo ""

# find plex sqlite binary
echo -n "Searching for Plex SQLite binary: "
plex_sqlite=$(find "$plex_config_dir" -type f -name "Plex SQLite" 2>/dev/null | head -n 1)
if [[ ! "$plex_sqlite" ]]; then
  fatal "Error: Could not find Plex SQLite binary in '$plex_config_dir'"
fi
echo "$plex_sqlite"
echo ""

# stop plex container if running
if [[ "$container_is_running" == true ]]; then
  echo -n "Stopping Plex container... "
  ! docker stop "$plex_container" && fatal "Error: Could not stop Plex container '$plex_container'"
  echo "stopped"
  echo ""
fi

# create backup
echo -n "Creating database backup... "
! cp -f "$plex_db_path" "${plex_db_path}${backup_suffix}" && fatal "Error: Could not create backup of Plex database"
echo "created ${plex_db_path}${backup_suffix}"
echo ""

# get all library section ids
echo "Retrieving library sections..."
library_ids=$("$plex_sqlite" "$plex_db_path" "SELECT id FROM library_sections;")
if [[ ! "$library_ids" ]]; then
  echo "Error: No library sections found!"
  if [[ "$container_is_running" == true ]]; then
    echo -n "Starting Plex container... "
    ! docker start "$plex_container" && fatal "Error: Could not start Plex container '$plex_container'"
    echo "started"
  fi
  syslog_exit "No library sections found"
fi
echo "Found library sections:"
"$plex_sqlite" "$plex_db_path" "SELECT id, name, section_type FROM library_sections;" | while IFS='|' read -r id name section_type; do
  echo "- ID: $id, Name: $name, Type: $section_type"
done
echo ""

# update each library section
echo "Updating added_at timestamps..."
total_updated=0
for library_id in $library_ids; do
  library_name=$("$plex_sqlite" "$plex_db_path" "SELECT name FROM library_sections WHERE id = $library_id;")
  echo -n "Update library: $library_name (ID: $library_id): "

  # set added_at to the minimum updated_at from media_items
  updated=$("$plex_sqlite" "$plex_db_path" "
    UPDATE metadata_items
    SET added_at = (
      SELECT MIN(m.updated_at)
      FROM media_items m
      WHERE m.metadata_item_id = metadata_items.id
    )
    WHERE library_section_id = $library_id
      AND EXISTS (SELECT 1 FROM media_items m WHERE m.metadata_item_id = metadata_items.id);
    SELECT changes();
  ")
  echo "$updated items updated"
  total_updated=$((total_updated + updated))

done

echo "Total items updated: $total_updated"
echo ""

# start plex container only if it was running before
if [[ "$container_is_running" == true ]]; then
  echo -n "Starting Plex container... "
  ! docker start "$plex_container" && fatal "Error: Could not start Plex container '$plex_container'"
  echo "started"
  echo ""
fi