[BUG] Immich not launching following data restoration after update to v1.91

SCrid2000 commented 11 months ago

The bug

After an update to v1.91, I am getting the following error when running docker compose up:

immich_postgres          | 2023-12-16 00:01:04.874 UTC [114] ERROR:  assertion failed: !array.contains_nulls()
immich_postgres          | 2023-12-16 00:01:04.874 UTC [114] STATEMENT:
immich_postgres          |              INSERT INTO smart_search("assetId", embedding)
immich_postgres          |              SELECT si."assetId", si."clipEmbedding"
immich_postgres          |              FROM smart_info si
immich_postgres          |              WHERE "clipEmbedding" IS NOT NULL

After repeated failures, I get the following error:

immich_server            | [Nest] 7  - 12/15/2023, 11:53:29 PM   ERROR [TypeOrmModule] Unable to connect to the database. Retrying (1)...
immich_server            | QueryFailedError: assertion failed: !array.contains_nulls()
immich_server            |     at PostgresQueryRunner.query (/usr/src/app/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:211:19)
immich_server            |     at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
immich_server            |     at async UsePgVectors1700713871511.up (/usr/src/app/dist/infra/migrations/1700713871511-UsePgVectors.js:28:9)
immich_server            |     at async MigrationExecutor.executePendingMigrations (/usr/src/app/node_modules/typeorm/migration/MigrationExecutor.js:225:17)

The immich webserver does not load.

I have attempted the following: Delete all data and perform backup Manually enable pgvecto.rs as described at https://github.com/immich-app/immich/discussions/5723#discussioncomment-7866476 Alter extensions following restoring the backup as described at https://github.com/immich-app/immich/issues/5630#issuecomment-1851015572 Repeat the same with different backups.

Deleting all immich data does allow the server to start. Restoring does not seem to work.

The OS that Immich Server is running on

Ubuntu 23.10 on Raspberry Pi 4

Version of Immich Server

1.91

Version of Immich Mobile App

1.91

Platform with the issue

[X] Server
[ ] Web
[ ] Mobile

Your docker-compose.yml content

version: "3.8"

#
# WARNING: Make sure to use the docker-compose.yml of the current release:
#
# https://github.com/immich-app/immich/releases/latest/download/docker-compose.yml
#
# The compose file on main may not be compatible with the latest release.
#

name: immich

services:
  immich-server:
    container_name: immich_server
    image: ghcr.io/immich-app/immich-server:${IMMICH_VERSION:-release}
    command: [ "start.sh", "immich" ]
    volumes:
      - ${UPLOAD_LOCATION}:/usr/src/app/upload
      - /etc/localtime:/etc/localtime:ro
    env_file:
      - .env
    ports:
      - 2283:3001
    depends_on:
      - redis
      - database
    restart: always

  immich-microservices:
    container_name: immich_microservices
    image: ghcr.io/immich-app/immich-server:${IMMICH_VERSION:-release}
    # extends:
    #   file: hwaccel.yml
    #   service: hwaccel
    command: [ "start.sh", "microservices" ]
    volumes:
      - ${UPLOAD_LOCATION}:/usr/src/app/upload
      - /etc/localtime:/etc/localtime:ro
    env_file:
      - .env
    depends_on:
      - redis
      - database
    restart: always

  immich-machine-learning:
    container_name: immich_machine_learning
    image: ghcr.io/immich-app/immich-machine-learning:${IMMICH_VERSION:-release}
    volumes:
      - model-cache:/cache
    env_file:
      - .env
    restart: always

  redis:
    container_name: immich_redis
    image: redis:6.2-alpine@sha256:b6124ab2e45cc332e16398022a411d7e37181f21ff7874835e0180f56a09e82a
    restart: always

  database:
    container_name: immich_postgres
    image: tensorchord/pgvecto-rs:pg14-v0.1.11@sha256:0335a1a22f8c5dd1b697f14f079934f5152eaaa216c09b61e293be285491f8ee
    env_file:
      - .env
    environment:
      POSTGRES_PASSWORD: ${DB_PASSWORD}
      POSTGRES_USER: ${DB_USERNAME}
      POSTGRES_DB: ${DB_DATABASE_NAME}
    volumes:
      - pgdata:/var/lib/postgresql/data
    restart: always

volumes:
  pgdata:
  model-cache:

Your .env content

###################################################################################
# Database
###################################################################################

DB_HOSTNAME=immich_postgres
DB_USERNAME=postgres
DB_PASSWORD=postgres
DB_DATABASE_NAME=immich

# Optional Database settings:
# DB_PORT=5432

###################################################################################
# Redis
###################################################################################

REDIS_HOSTNAME=immich_redis

# Optional Redis settings:

# Note: these parameters are not automatically passed to the Redis Container
# to do so, please edit the docker-compose.yml file as well. Redis is not configured
# via environment variables, only redis.conf or the command line

# REDIS_PORT=6379
# REDIS_DBINDEX=0
# REDIS_PASSWORD=
# REDIS_SOCKET=

###################################################################################
# Upload File Location
#
# This is the location where uploaded files are stored.
###################################################################################

UPLOAD_LOCATION=/media/PortableSSD/

###################################################################################
# Log message level - [simple|verbose]
###################################################################################

LOG_LEVEL=log

###################################################################################
# Typesense
###################################################################################
# TYPESENSE_ENABLED=false
  TYPESENSE_API_KEY=some-random-text
# TYPESENSE_HOST: typesense
# TYPESENSE_PORT: 8108
# TYPESENSE_PROTOCOL: http

###################################################################################
# Reverse Geocoding
#
# Reverse geocoding is done locally which has a small impact on memory usage
# This memory usage can be altered by changing the REVERSE_GEOCODING_PRECISION variable
# This ranges from 0-3 with 3 being the most precise
# 3 - Cities > 500 population: ~200MB RAM
# 2 - Cities > 1000 population: ~150MB RAM
# 1 - Cities > 5000 population: ~80MB RAM
# 0 - Cities > 15000 population: ~40MB RAM
####################################################################################

# DISABLE_REVERSE_GEOCODING=false
# REVERSE_GEOCODING_PRECISION=3

####################################################################################
# WEB - Optional
#
# Custom message on the login page, should be written in HTML form.
# For example:
# PUBLIC_LOGIN_PAGE_MESSAGE="This is a demo instance of Immich.<br><br>Email: <i>demo@demo.de</i><br>Password: <i>demo</i>"
####################################################################################

PUBLIC_LOGIN_PAGE_MESSAGE="Photos and Videos Backup Server"

###################################################################################
# Immich Version - Optional
#
# This allows all immich docker images to be pinned to a specific version. By default,
# the version is "release" but could be a specific version, like "v1.59.0".
###################################################################################

#IMMICH_VERSION=

Reproduction steps

1.
2.
3.
...

Additional information

No response

mertalev commented 11 months ago

Hmm, that's a weird assertion to fail. There's seems to be an invalid embedding in your database. Could you run this query to see if there really is an embedding that contains a null?

SELECT * FROM immich.public.smart_info si 
INNER JOIN immich.public.assets a 
ON si."assetId" = a.id
WHERE array_position(si."clipEmbedding", NULL) IS NOT NULL;

SCrid2000 commented 11 months ago

There is indeed, and the same line existed in my backups: 1ed2b8e7-bd78-4008-8385-02cb34dc1f75 \N \N {512 occurrences of NULL separated by commas} I manually removed that line from the backup file, restored the backup, redid the manual pgvecto.rs and altered extensions as described in the original comment, and it seems that everything is up and running again.

Is there a way to

Identify which, if any, photograph that string was attached to so it can be restored? and
Prevent that from happening in the future?

mertalev commented 11 months ago

Glad to hear it worked out!

If you only removed the row in the smart_info table, then you can just run this query to re-add the image to this table: INSERT INTO immich.public.smart_info VALUES ('1ed2b8e7-bd78-4008-8385-02cb34dc1f75', array[]::text[], NULL);.
This can't happen anymore because pgvecto.rs doesn't let you insert an invalid embedding into the column.

immich-app / immich