insert or update on table "exif" violates foreign key constraint

[mio-19]

The bug

I saw a recent update resolved issues with Samsung Motion Photo so I decided to re-run "extract metadata" for all photos. Then I got the error

The OS that Immich Server is running on

Arch

Version of Immich Server

1.94.1

Version of Immich Mobile App

1.94.1

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: # uncomment this section for hardware acceleration - see https://immich.app/docs/features/hardware-transcoding
      file: hwaccel.transcoding.yml
      service: quicksync # set to one of [nvenc, quicksync, rkmpp, vaapi, vaapi-wsl] for accelerated transcoding
    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
    # For hardware acceleration, add one of -[armnn, cuda, openvino] to the image tag.
    # Example tag: ${IMMICH_VERSION:-release}-cuda
    image: ghcr.io/immich-app/immich-machine-learning:${IMMICH_VERSION:-release}
    extends: # uncomment this section for hardware acceleration - see https://immich.app/docs/features/ml-hardware-acceleration
      file: hwaccel.ml.yml
      service: openvino # set to one of [armnn, cuda, openvino, openvino-wsl] for accelerated inference - use the `-wsl` version for WSL2 where applicable
    volumes:
      - model-cache:/cache
    env_file:
      - .env
    restart: always

  redis:
    container_name: immich_redis
    image: redis:6.2-alpine@sha256:afb290a0a0d0b2bd7537b62ebff1eb84d045c757c1c31ca2ca48c79536c0de82
    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:
      - /mnt/*****:/var/lib/postgresql/data
    restart: always

volumes:
  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=*****

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

# Connection secret for postgres. You should change it to a random password
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. Re-run "extract metedata"

Additional information

log

immich_microservices     | [Nest] 7  - 02/13/2024, 1:55:49 PM     LOG [MetadataService] Removed old motion photo video asset (19bb561f-224f-4f00-82f6-80c6647db9a7)
immich_microservices     | [Nest] 7  - 02/13/2024, 1:55:52 PM     LOG [MetadataService] Removed old motion photo video asset (6e9861ec-f4ed-4e82-bff7-4ab1c2aeec4a)
immich_microservices     | [Nest] 7  - 02/13/2024, 1:55:52 PM     LOG [MetadataService] Removed old motion photo video asset (8a7b4047-78b6-4290-8998-da95bf422e8b)
immich_microservices     | [Nest] 7  - 02/13/2024, 1:55:52 PM   ERROR [JobService] Unable to run job handler (metadataExtraction/metadata-extraction): QueryFailedError: insert or update on table "exif" violates foreign key constraint "FK_c0117fdbc50b917ef9067740c44"
immich_microservices     | [Nest] 7  - 02/13/2024, 1:55:52 PM   ERROR [JobService] QueryFailedError: insert or update on table "exif" violates foreign key constraint "FK_c0117fdbc50b917ef9067740c44"
immich_microservices     |     at PostgresQueryRunner.query (/usr/src/app/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:219:19)
immich_microservices     |     at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
immich_microservices     |     at async InsertQueryBuilder.execute (/usr/src/app/node_modules/typeorm/query-builder/InsertQueryBuilder.js:106:33)
immich_microservices     |     at async AssetRepository.upsertExif (/usr/src/app/dist/infra/repositories/asset.repository.js:43:9)
immich_microservices     |     at async MetadataService.handleMetadataExtraction (/usr/src/app/dist/domain/metadata/metadata.service.js:172:9)
immich_microservices     |     at async /usr/src/app/dist/domain/job/job.service.js:116:37
immich_microservices     |     at async Worker.processJob (/usr/src/app/node_modules/bullmq/dist/cjs/classes/worker.js:394:28)
immich_microservices     |     at async Worker.retryIfFailed (/usr/src/app/node_modules/bullmq/dist/cjs/classes/worker.js:581:24)
immich_microservices     | [Nest] 7  - 02/13/2024, 1:55:52 PM   ERROR [JobService] Object:
immich_microservices     | {
immich_microservices     |   "id": "8a7b4047-78b6-4290-8998-da95bf422e8b"
immich_microservices     | }
immich_microservices     |
immich_microservices     | [Nest] 7  - 02/13/2024, 1:55:54 PM     LOG [MetadataService] Removed old motion photo video asset (d68f9db3-c787-4a2d-b9bc-81b457c7572a)
immich_microservices     | [Nest] 7  - 02/13/2024, 1:55:55 PM   ERROR [JobService] Unable to run job handler (metadataExtraction/metadata-extraction): QueryFailedError: insert or update on table "exif" violates foreign key constraint "FK_c0117fdbc50b917ef9067740c44"
immich_microservices     | [Nest] 7  - 02/13/2024, 1:55:55 PM   ERROR [JobService] QueryFailedError: insert or update on table "exif" violates foreign key constraint "FK_c0117fdbc50b917ef9067740c44"
immich_microservices     |     at PostgresQueryRunner.query (/usr/src/app/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:219:19)
immich_microservices     |     at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
immich_microservices     |     at async InsertQueryBuilder.execute (/usr/src/app/node_modules/typeorm/query-builder/InsertQueryBuilder.js:106:33)
immich_microservices     |     at async AssetRepository.upsertExif (/usr/src/app/dist/infra/repositories/asset.repository.js:43:9)
immich_microservices     |     at async MetadataService.handleMetadataExtraction (/usr/src/app/dist/domain/metadata/metadata.service.js:172:9)
immich_microservices     |     at async /usr/src/app/dist/domain/job/job.service.js:116:37
immich_microservices     |     at async Worker.processJob (/usr/src/app/node_modules/bullmq/dist/cjs/classes/worker.js:394:28)
immich_microservices     |     at async Worker.retryIfFailed (/usr/src/app/node_modules/bullmq/dist/cjs/classes/worker.js:581:24)
immich_microservices     | [Nest] 7  - 02/13/2024, 1:55:55 PM   ERROR [JobService] Object:
immich_microservices     | {
immich_microservices     |   "id": "d68f9db3-c787-4a2d-b9bc-81b457c7572a"
immich_microservices     | }
immich_microservices     |

[aviv926]

Duplicate of #6854

[alextran1502]

https://github.com/immich-app/immich/discussions/6854