Check all assets' encoded video files - if a file is missing, set to empty in the database so that it can be re-encoded

immich-validate-encoded-videos.sh

#!/bin/bash

# Instructions for running inside docker:
#  1. Exec into a container, i.e. with: docker exec -it c0nta1ner1d bash
#  2. Copy or create the script and make it executable with: chmod +x script.sh
#  3. Install deps with: apt update && apt install -y postgresql-client
#  4. Run it and wait until finished.
#  5. Go to Immich web and into Administration
#  6. Go to Jobs and scroll down to TRANSCODE VIDEOS
#  7. Click MISSING

# Default values when running immich in a docker container
DB_NAME="immich"
DB_USER="postgres"
DB_PASSWORD="postgres"
DB_HOST="db"
DB_PORT="5432"
PATH_PREFIX="/usr/src/app/"

# Export the password for non-interactive authentication
export PGPASSWORD="$DB_PASSWORD"

# Query the assets table to find records with non-empty encodedVideoPath
query="SELECT id, \"encodedVideoPath\" FROM assets WHERE \"encodedVideoPath\" IS NOT NULL AND \"encodedVideoPath\" != '';"

# Execute the query and iterate over each record
psql -U "$DB_USER" -d "$DB_NAME" -h "$DB_HOST" -p "$DB_PORT" -t -c "$query" | while read -r line; do
  IFS='|' read -r id encodedVideoPath <<< "$(echo "$line" | xargs)"
  id=$(echo "$id" | xargs)
  encodedVideoPath=$(echo "$encodedVideoPath" | xargs)

  # Check if the file exists
  fullPath="${PATH_PREFIX}${encodedVideoPath}"
  if [ ! -f "$fullPath" ]; then
    # If file does not exist, update the encodedVideoPath to an empty string
    update_query="UPDATE assets SET \"encodedVideoPath\" = '' WHERE id = '$id';"
    psql -U "$DB_USER" -d "$DB_NAME" -h "$DB_HOST" -p "$DB_PORT" -c "$update_query"
    echo "🔴 Video missing for asset id $id - set encodedVideoPath to an empty string."
  else
    echo "🟢 Video exists for asset id $id."
  fi
done

# Unset the password variable for security
unset PGPASSWORD