[BUG] immich_server fails to start, QueryFailedError: insert or update on table "asset_faces" violates foreign key constraint

The bug

After upgrading from 1.92.1 to 1.93.0, about 2 weeks ago, I got an error in which the immich_server container would not start. I would stay in "restarting" state after calling docker-compose up -d. I inspected the logs, and after searching online I found this bug report (https://github.com/immich-app/immich/issues/5580), and I proceeded as suggested there:

Execute docker exec -it immich_postgres psql -U postgres -d immich -c 'UPDATE person SET "faceAssetId"=NULL;'

Unfortunately, this didn't fix the issue back then. My solution was to revert back fro 1.92.1, which allowed the server to start and I could use immich again.

Now, I just upgraded to 1.94.1, making sure to re-run the "Recognize faces" for all, as suggested in #5580, to prevent the same issue from happening again. Unfortunately, the same problem occurred: the immich_server container remains in "restarting" state. The logs produce the same as when I updated to 1.93.0 two weeks ago, which I paste below (this is the output of docker logs immich_server):

Node.js v20.11.0
Migration "SetAssetFaceNullOnPersonDelete1704943345360" failed, error: insert or update on table "asset_faces" violates foreign key constraint "FK_95ad7106dd7b484275443f580f9"
/usr/src/app/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:219
            throw new QueryFailedError_1.QueryFailedError(query, parameters, err);
                  ^

QueryFailedError: insert or update on table "asset_faces" violates foreign key constraint "FK_95ad7106dd7b484275443f580f9"
    at PostgresQueryRunner.query (/usr/src/app/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:219:19)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async SetAssetFaceNullOnPersonDelete1704943345360.up (/usr/src/app/dist/infra/migrations/1704943345360-SetAssetFaceNullOnPersonDelete.js:6:9)
    at async MigrationExecutor.executePendingMigrations (/usr/src/app/node_modules/typeorm/migration/MigrationExecutor.js:225:17)
    at async DataSource.runMigrations (/usr/src/app/node_modules/typeorm/data-source/DataSource.js:263:35)
    at async DatabaseRepository.runMigrations (/usr/src/app/dist/infra/repositories/database.repository.js:42:9)
    at async DatabaseService.init (/usr/src/app/dist/domain/database/database.service.js:33:9)
    at async AppService.init (/usr/src/app/dist/immich/app.service.js:56:9)
    at async AppModule.onModuleInit (/usr/src/app/dist/immich/app.module.js:32:9)
    at async callModuleInitHook (/usr/src/app/node_modules/@nestjs/core/hooks/on-module-init.hook.js:51:9) {
  query: '\n' +
    '            ALTER TABLE "asset_faces"\n' +
    '            DROP CONSTRAINT "FK_95ad7106dd7b484275443f580f9",\n' +
    '            ADD CONSTRAINT "FK_95ad7106dd7b484275443f580f9" \n' +
    '            FOREIGN KEY ("personId") REFERENCES "person"("id")\n' +
    '            ON DELETE SET NULL ON UPDATE CASCADE\n' +
    '    ',
  parameters: undefined,
  driverError: error: insert or update on table "asset_faces" violates foreign key constraint "FK_95ad7106dd7b484275443f580f9"
      at /usr/src/app/node_modules/pg/lib/client.js:526:17
      at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
      at async PostgresQueryRunner.query (/usr/src/app/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:184:25)
      at async SetAssetFaceNullOnPersonDelete1704943345360.up (/usr/src/app/dist/infra/migrations/1704943345360-SetAssetFaceNullOnPersonDelete.js:6:9)
      at async MigrationExecutor.executePendingMigrations (/usr/src/app/node_modules/typeorm/migration/MigrationExecutor.js:225:17)
      at async DataSource.runMigrations (/usr/src/app/node_modules/typeorm/data-source/DataSource.js:263:35)
      at async DatabaseRepository.runMigrations (/usr/src/app/dist/infra/repositories/database.repository.js:42:9)
      at async DatabaseService.init (/usr/src/app/dist/domain/database/database.service.js:33:9)
      at async AppService.init (/usr/src/app/dist/immich/app.service.js:56:9)
      at async AppModule.onModuleInit (/usr/src/app/dist/immich/app.module.js:32:9) {
    length: 315,
    severity: 'ERROR',
    code: '23503',
    detail: 'Key (personId)=(619c4ae4-fc77-4c0a-aaba-a2b9d0bbfb6a) is not present in table "person".',
    hint: undefined,
    position: undefined,
    internalPosition: undefined,
    internalQuery: undefined,
    where: undefined,
    schema: 'public',
    table: 'asset_faces',
    column: undefined,
    dataType: undefined,
    constraint: 'FK_95ad7106dd7b484275443f580f9',
    file: 'ri_triggers.c',
    line: '2528',
    routine: 'ri_ReportViolation'
  },
  length: 315,
  severity: 'ERROR',
  code: '23503',
  detail: 'Key (personId)=(619c4ae4-fc77-4c0a-aaba-a2b9d0bbfb6a) is not present in table "person".',
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: 'public',
  table: 'asset_faces',
  column: undefined,
  dataType: undefined,
  constraint: 'FK_95ad7106dd7b484275443f580f9',
  file: 'ri_triggers.c',
  line: '2528',
  routine: 'ri_ReportViolation'
}

The OS that Immich Server is running on

debian 6.1.0

Version of Immich Server

v1.94.1

Version of Immich Mobile App

v1.93.2

Platform with the issue

[X] Server
[ ] Web
[ ] Mobile

Your docker-compose.yml content

version: "3.8"

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:70a7a5b641117670beae0d80658430853896b5ef269ccf00d1827427e3263fa3
    restart: always

  database:
    container_name: immich_postgres
    image: tensorchord/pgvecto-rs:pg14-v0.1.11
    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

# You can find documentation for all the supported env variables at https://immich.app/docs/install/environment-variables

# The location where your uploaded files are stored
UPLOAD_LOCATION=/mnt/Immich

# The Immich version to use. You can pin this to a specific version like "v1.71.0"
IMMICH_VERSION=release

# Connection secrets for postgres and typesense. You should change these to random passwords
TYPESENSE_API_KEY=****
DB_PASSWORD=****

# The values below this line do not need to be changed
###################################################################################
DB_HOSTNAME=immich_postgres
DB_USERNAME=postgres
DB_DATABASE_NAME=immich

REDIS_HOSTNAME=immich_redis

Reproduction steps

1. Upgrade from 1.92.1 to 1.94.1 (`docker-compose pull`)
2. Update the containers (`docker-compose up -d`)
3. `immich_server` won't start. Output of `docker-compose ps` shows that `immich_server` is in state "restarting". Logs produce the output pasted previously

Additional information

No response

immich-app / immich