immich-app/immich
1
0
Fork 0
mirror of https://github.com/immich-app/immich.git synced 2026-02-25 03:12:21 +03:00
Code Issues 783 Packages Projects Releases 12 Wiki Activity

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

New Issue
Closed
opened 2026-02-05 05:15:02 +03:00 by OVERLORD · 1 comment
OVERLORD commented 2026-02-05 05:15:02 +03:00
Owner
Copy Link

Originally created by @deescuderoo on GitHub (Feb 4, 2024).

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

  • 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

Originally created by @deescuderoo on GitHub (Feb 4, 2024). ### 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 ```YAML 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 ```Shell # 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 ```bash 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_
OVERLORD commented 2026-02-05 05:15:12 +03:00
Author
Owner
Copy Link

@deescuderoo commented on GitHub (Feb 4, 2024):

Update

I did run '"Recognize faces" for all', as indicated in #5580 when updating first. It didn't help. I ran it again and now it worked! v1.94.1 is up and running.

I'm betting new pictures were added between the job was finished and the update was done? In any case, just to reiterate: running recognize faces for all did solve the issue.

@deescuderoo commented on GitHub (Feb 4, 2024): **Update** I did run '"Recognize faces" for all', as indicated in #5580 when updating first. It didn't help. I ran it again and now it worked! v1.94.1 is up and running. I'm betting new pictures were added between the job was finished and the update was done? In any case, just to reiterate: running recognize faces for all did solve the issue.
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
accessibility
changelog:enhancement
changelog:security
changelog:skip
changelog:translation
cli
date-time
dependencies
documentation
external-library
format
good first issue
mobile-beta
mobile-beta
mobile-beta
needs-answer
nice to have
pull-request
Mirrored from GitHub Pull Request
 sharing
tech-debt
📱mobile
🖥️web
🗄️server
🧠machine-learning
No Label
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
OVERLORD
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: immich-app/immich#2126
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?