[BUG] insert or update on table "person" violates foreign key constraint

maltokyo commented 10 months ago

The bug

After upgrading to v1.90.2, I can no longer start the container, even after waiting 15-20 minutes for potential migrations etc. Detailed log file attached below, but not sure what to do to resolve this. The only thing I did was run docker compose pull

The OS that Immich Server is running on

Debian 12 latest updates applied

Version of Immich Server

v1.90.2

Version of Immich Mobile App

N/A

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
      - typesense
    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
      - typesense
    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

#   immich-web:
#     container_name: immich_web
#     image: ghcr.io/immich-app/immich-web:${IMMICH_VERSION:-release}
#     env_file:
#       - .env
#     restart: always

  typesense:
    container_name: immich_typesense
    image: typesense/typesense:0.24.1@sha256:9bcff2b829f12074426ca044b56160ca9d777a0c488303469143dd9f8259d4dd
    environment:
      - TYPESENSE_API_KEY=${TYPESENSE_API_KEY}
      - TYPESENSE_DATA_DIR=/data
      # remove this to get debug messages
      - GLOG_minloglevel=1
    volumes:
      - tsdata:/data
    restart: always

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

  database:
    container_name: immich_postgres
    image: postgres:14-alpine@sha256:6a0e35296341e676fe6bd8d236c72afffe2dfe3d7eb9c2405c0f3fc04500cd07
    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:
  tsdata:

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/sWes1000/z_immich_msi_metadata

# 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=REDACTED
DB_PASSWORD=REDACTED

# 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

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

LOG_LEVEL=verbose

Reproduction steps

1. docker compose pull to latest version v1.90.2
2. docker compose up -d

...

Additional information

Logs from docker compose logs - this just repeats over and over endlessly:

immich_server            | [Nest] 7  - 12/09/2023, 9:58:06 AM   ERROR [TypeOrmModule] Unable to connect to the database. Retrying (4)...
immich_server            | QueryFailedError: insert or update on table "person" violates foreign key constraint "FK_2bbabe31656b6778c6b87b61023"
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 EditFaceAssetForeignKey1699727044012.up (/usr/src/app/dist/infra/migrations/1699727044012-EditFaceAssetForeignKey.js:11:9)
immich_server            |     at async MigrationExecutor.executePendingMigrations (/usr/src/app/node_modules/typeorm/migration/MigrationExecutor.js:225:17)
immich_server            |     at async DataSource.runMigrations (/usr/src/app/node_modules/typeorm/data-source/DataSource.js:260:35)
immich_server            |     at async DataSource.initialize (/usr/src/app/node_modules/typeorm/data-source/DataSource.js:148:17)
immich_postgres          | 2023-12-09 09:58:10.844 UTC [76] ERROR:  insert or update on table "person" violates foreign key constraint "FK_2bbabe31656b6778c6b87b61023"
immich_postgres          | 2023-12-09 09:58:10.844 UTC [76] DETAIL:  Key (faceAssetId)=(1b3b7403-a52f-494c-a0aa-60ced5623285) is not present in table "asset_faces".
immich_postgres          | 2023-12-09 09:58:10.844 UTC [76] STATEMENT:  ALTER TABLE "person" ADD CONSTRAINT "FK_2bbabe31656b6778c6b87b61023" FOREIGN KEY ("faceAssetId") REFERENCES "asset_faces"("id") ON DELETE SET NULL ON UPDATE NO ACTION
immich_microservices     | [Nest] 6  - 12/09/2023, 9:58:10 AM   ERROR [TypeOrmModule] Unable to connect to the database. Retrying (4)...
immich_microservices     | QueryFailedError: insert or update on table "person" violates foreign key constraint "FK_2bbabe31656b6778c6b87b61023"
immich_microservices     |     at PostgresQueryRunner.query (/usr/src/app/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:211:19)
immich_microservices     |     at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
immich_microservices     |     at async EditFaceAssetForeignKey1699727044012.up (/usr/src/app/dist/infra/migrations/1699727044012-EditFaceAssetForeignKey.js:11:9)
immich_microservices     |     at async MigrationExecutor.executePendingMigrations (/usr/src/app/node_modules/typeorm/migration/MigrationExecutor.js:225:17)
immich_microservices     |     at async DataSource.runMigrations (/usr/src/app/node_modules/typeorm/data-source/DataSource.js:260:35)
immich_microservices     |     at async DataSource.initialize (/usr/src/app/node_modules/typeorm/data-source/DataSource.js:148:17)
immich_postgres          | 2023-12-09 09:58:14.495 UTC [77] ERROR:  insert or update on table "person" violates foreign key constraint "FK_2bbabe31656b6778c6b87b61023"
immich_postgres          | 2023-12-09 09:58:14.495 UTC [77] DETAIL:  Key (faceAssetId)=(1b3b7403-a52f-494c-a0aa-60ced5623285) is not present in table "asset_faces".
immich_postgres          | 2023-12-09 09:58:14.495 UTC [77] STATEMENT:  ALTER TABLE "person" ADD CONSTRAINT "FK_2bbabe31656b6778c6b87b61023" FOREIGN KEY ("faceAssetId") REFERENCES "asset_faces"("id") ON DELETE SET NULL ON UPDATE NO ACTION
immich_server            | [Nest] 7  - 12/09/2023, 9:58:14 AM   ERROR [TypeOrmModule] Unable to connect to the database. Retrying (5)...
immich_server            | QueryFailedError: insert or update on table "person" violates foreign key constraint "FK_2bbabe31656b6778c6b87b61023"
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 EditFaceAssetForeignKey1699727044012.up (/usr/src/app/dist/infra/migrations/1699727044012-EditFaceAssetForeignKey.js:11:9)
immich_server            |     at async MigrationExecutor.executePendingMigrations (/usr/src/app/node_modules/typeorm/migration/MigrationExecutor.js:225:17)
immich_server            |     at async DataSource.runMigrations (/usr/src/app/node_modules/typeorm/data-source/DataSource.js:260:35)
immich_server            |     at async DataSource.initialize (/usr/src/app/node_modules/typeorm/data-source/DataSource.js:148:17)
immich_typesense         | W20231209 09:58:15.255029   214 controller.cpp:1454] SIGINT was installed with 1
immich_typesense         | W20231209 09:58:15.255084   214 raft_server.cpp:570] Single-node with no leader. Resetting peers.
immich_typesense         | W20231209 09:58:15.255098   214 node.cpp:894] node default_group:172.19.0.5:8107:8108 set_peer from 172.19.0.7:8107:8108 to 172.19.0.5:8107:8108
immich_postgres          | 2023-12-09 09:58:17.889 UTC [78] ERROR:  insert or update on table "person" violates foreign key constraint "FK_2bbabe31656b6778c6b87b61023"
immich_postgres          | 2023-12-09 09:58:17.889 UTC [78] DETAIL:  Key (faceAssetId)=(1b3b7403-a52f-494c-a0aa-60ced5623285) is not present in table "asset_faces".
immich_postgres          | 2023-12-09 09:58:17.889 UTC [78] STATEMENT:  ALTER TABLE "person" ADD CONSTRAINT "FK_2bbabe31656b6778c6b87b61023" FOREIGN KEY ("faceAssetId") REFERENCES "asset_faces"("id") ON DELETE SET NULL ON UPDATE NO ACTION

martabal commented 10 months ago

Re-run the RECOGNIZE FACES job before updating immich should fix it

maltokyo commented 10 months ago

Re-run the RECOGNIZE FACES job before updating immich should fix it

Thanks, but now the container won't start at all, so no way to rerun it.. (?)

martabal commented 10 months ago

Just downgrade to v1.89

maltokyo commented 10 months ago

ok, downgraded. Do I have to rerun for ALL faces? I have nearly 1million faces, it will take days.. wondering if you think run for "missing" would be enough?

martabal commented 10 months ago

Well, if you can't upgrade it's because the migration script for v1.90 fails. I couldn't reproduce this issue so I suspect there's an error in one of your tables and the migration script miss your edge case. Unless you can see the error in the migration script / in your tables, I don't think there's a easy solution. You will have to wipe everything with "all", "missing" is not enough

maltokyo commented 10 months ago

tried with just "missing", as you said it did not fix it. That is a pain. All I did was upgrade, strange that it now has a DB error. Will try all faces and report back in a few days ;(

maltokyo commented 10 months ago

Oh no...

I have spent hours tagging people. I don't want to lose this work... is anyone else able to help with a way to fix the db error? Will wait a few days and see.

martabal commented 10 months ago

Well, what you can do is to set faceAssetId to null with UPDATE person SET "faceAssetId"=NULL;. Make a backup of your database before. It's the id of the asset used to generate the feature photo for your people. Unless you reassign a face to a person it won't change your current people feature photos.

maltokyo commented 10 months ago

Thank you - this worked and upgraded environment started no issues: UPDATE person SET "faceAssetId"=NULL;

Now in person I have nothing in faceAssetId (clearly), but I do not understand the impact of this. The face feature photos seem to display fine. Could you please explain how this could affect me long term, or why it is no problem to leave like this?

Thanks again for all of your help @martabal

martabal commented 10 months ago

Yes, to generate the feature photo for the person thumbnail, we have to know which asset to use. Your people already have a feature photo so it does not affect them.

I think it will only affect the new re-assign feature : let's say you have person A and person B. If you reassign a face from person A to person B, the server will check if person B has a feature photo (because you don't want a person without a feature photo). In your case person B has a feature photo but the server will consider it has not so it will change the feature photo from person B with a random face of person B. The solution to avoid this is it to manually set the feature photo with "Change feature photo" on the person page.

martabal commented 10 months ago

Really sorry the inconvenience, I don't really understand what could have caused this issue :thinking:

Out of curiosity, since when do you run Immich ? I suspect this issue to affect users who run Immich since the person feature was introduced

maltokyo commented 10 months ago

I started running it about 6 months ago. Using much more (actually it's now how I look at my photos each day!) that Libraries are available, as all my pictures are on the filesystem with directory names curated. But, to answer your question, I made a fresh setup from scratch and rescanned all of my photos after the Libraries feature came. So, since then... (when did the person feature come?)

Thank you so much for your kind help and explanations - really appreciated.

wololo-wololo commented 10 months ago

Hey, if I may post to this thread; I am having the exact same issue. I am using Immich for 2-3 Months now and started from scratch around 1 month ago. So I went through all faces again and now it won't let me update because of the issues that come up as stated in the opening post.

May I ask how I execute UPDATE person SET "faceAssetId"=NULL;? Do I need to input something before executing it?

martabal commented 10 months ago

Well both of you are recent Immich users. I'm wondering what I missed in the migration script then.

To solve this issue there's 2 solutions :

Re-run the "Recognize faces" for all
Execute docker exec -it immich_postgres psql -U postgres -d immich -c 'UPDATE person SET "faceAssetId"=NULL;'

wololo-wololo commented 10 months ago

Thanks for your help. I used the second option and it works.

EDIT: I noticed that editing locations do not work, I assume it has to do with those complications as well.. (?)

martabal commented 10 months ago

No I don't think it has something to do with this issue. Note that you can not edit metadata for assets which are in external libraries

immich-app / immich