The Immich-server fails to start during the deployment process due to a database migration error. The specific migration "AddThumbnailJobStatus1724080823160" encounters a foreign key constraint violation when attempting to insert or update records in the asset_job_status table.
This error occurs when I try to update from v1.112.1 to v1.113.0. If I roll back to v1.112.1, the Immich-server works again without issues. The problem only arises when I attempt to run the command to update to v1.113.0.
The OS that Immich Server is running on
Debian GNU/Linux bookworm 12.7 aarch64, Raspberry
Version of Immich Server
v.1.113.0
Version of Immich Mobile App
v.1.113.0 build.156
Platform with the issue
[X] Server
[ ] Web
[ ] Mobile
Your docker-compose.yml content
name: immich
services:
immich-server:
container_name: immich_server
image: ghcr.io/immich-app/immich-server:${IMMICH_VERSION:-release}
# extends:
# file: hwaccel.transcoding.yml
# service: cpu # set to one of [nvenc, quicksync, rkmpp, vaapi, vaapi-wsl] for accelerated transcoding
volumes:
# Do not edit the next line. If you want to change the media storage location on your system, edit the value of UPLOAD_LOCATION in the .env file
- ${UPLOAD_LOCATION}:/usr/src/app/upload
- /etc/localtime:/etc/localtime:ro
env_file:
- .env
ports:
- 2283:3001
depends_on:
- redis
- database
restart: always
healthcheck:
disable: false
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: cpu # 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
healthcheck:
disable: false
redis:
container_name: immich_redis
image: docker.io/redis:6.2-alpine@sha256:e3b17ba9479deec4b7d1eeec1548a253acc5374d68d3b27937fcfe4df8d18c7e
healthcheck:
test: redis-cli ping || exit 1
restart: always
database:
container_name: immich_postgres
image: docker.io/tensorchord/pgvecto-rs:pg14-v0.2.0@sha256:90724186f0a3517cf6914295b5ab410db9ce23190a2d9d0b9dd6463e3fa298f0
environment:
POSTGRES_PASSWORD: ${DB_PASSWORD}
POSTGRES_USER: ${DB_USERNAME}
POSTGRES_DB: ${DB_DATABASE_NAME}
POSTGRES_INITDB_ARGS: '--data-checksums'
volumes:
# Do not edit the next line. If you want to change the database storage location on your system, edit the value of DB_DATA_LOCATION in the .env file
- ${DB_DATA_LOCATION}:/var/lib/postgresql/data
healthcheck:
test: pg_isready --dbname='${DB_DATABASE_NAME}' --username='${DB_USERNAME}' || exit 1; Chksum="$$(psql --dbname='${DB_DATABASE_NAME}' --username='${DB_USERNAME}' --tuples-only --no-align --command='SELECT COALESCE(SUM(checksum_failures), 0) FROM pg_stat_database')"; echo "checksum failure count is $$Chksum"; [ "$$Chksum" = '0' ] || exit 1
interval: 5m
start_interval: 30s
start_period: 5m
command: ["postgres", "-c", "shared_preload_libraries=vectors.so", "-c", 'search_path="$$user", public, vectors', "-c", "logging_collector=on", "-c", "max_wal_size=2GB", "-c", "shared_buffers=512MB", "-c", "wal_compression=on"]
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=./library
# The location where your database files are stored
DB_DATA_LOCATION=./postgres
# To set a timezone, uncomment the next line and change Etc/UTC to a TZ identifier from this list: https://en.wikipedia.org/wiki/List_of_tz_database_time_zones#List
TZ=America/Sao_Paulo
# The Immich version to use. You can pin this to a specific version like "v1.71.0"
IMMICH_VERSION=release
# Connection secret for postgres. You should change it to a random password
DB_PASSWORD=<THE RANDOM PASSWORD>
# The values below this line do not need to be changed
###################################################################################
DB_USERNAME=postgres
DB_DATABASE_NAME=immich
Reproduction steps
Run docker compose down
Run docker compose pull
Run docker compose up -d
Relevant log output
Starting api worker
Starting microservices worker
[Nest] 7 - 09/02/2024, 5:02:25 PM LOG [Microservices:EventRepository] Initialized websocket server
[Nest] 17 - 09/02/2024, 5:02:26 PM LOG [Api:EventRepository] Initialized websocket server
Migration "AddThumbnailJobStatus1724080823160" failed, error: insert or update on table "asset_job_status" violates foreign key constraint "FK_420bec36fc02813bddf5c8b73d4"
QueryFailedError: insert or update on table "asset_job_status" violates foreign key constraint "FK_420bec36fc02813bddf5c8b73d4"
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 AddThumbnailJobStatus1724080823160.up (/usr/src/app/dist/migrations/1724080823160-AddThumbnailJobStatus.js:10: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:265:35)
at async DatabaseRepository.runMigrations (/usr/src/app/dist/repositories/database.repository.js:178:9)
at async /usr/src/app/dist/services/database.service.js:102:17
at async /usr/src/app/dist/repositories/database.repository.js:186:23 {
query: 'UPDATE "asset_job_status" SET "thumbnailAt" = NOW() FROM "assets" WHERE "assetId" = "assets"."id" AND "assets"."thumbnailPath" IS NOT NULL',
parameters: undefined,
driverError: error: insert or update on table "asset_job_status" violates foreign key constraint "FK_420bec36fc02813bddf5c8b73d4"
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 AddThumbnailJobStatus1724080823160.up (/usr/src/app/dist/migrations/1724080823160-AddThumbnailJobStatus.js:10: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:265:35)
at async DatabaseRepository.runMigrations (/usr/src/app/dist/repositories/database.repository.js:178:9)
at async /usr/src/app/dist/services/database.service.js:102:17
at async /usr/src/app/dist/repositories/database.repository.js:186:23 {
length: 324,
severity: 'ERROR',
code: '23503',
detail: 'Key (assetId)=(2c1813a0-536b-4565-9b1b-6247bfbca44a) is not present in table "assets".',
hint: undefined,
position: undefined,
internalPosition: undefined,
internalQuery: undefined,
where: undefined,
schema: 'public',
table: 'asset_job_status',
column: undefined,
dataType: undefined,
constraint: 'FK_420bec36fc02813bddf5c8b73d4',
file: 'ri_triggers.c',
line: '2528',
routine: 'ri_ReportViolation'
},
length: 324,
severity: 'ERROR',
code: '23503',
detail: 'Key (assetId)=(2c1813a0-536b-4565-9b1b-6247bfbca44a) is not present in table "assets".',
hint: undefined,
position: undefined,
internalPosition: undefined,
internalQuery: undefined,
where: undefined,
schema: 'public',
table: 'asset_job_status',
column: undefined,
dataType: undefined,
constraint: 'FK_420bec36fc02813bddf5c8b73d4',
file: 'ri_triggers.c',
line: '2528',
routine: 'ri_ReportViolation'
alextran1502
commented
1 month ago
The bug
The Immich-server fails to start during the deployment process due to a database migration error. The specific migration "AddThumbnailJobStatus1724080823160" encounters a foreign key constraint violation when attempting to insert or update records in the asset_job_status table.
This error occurs when I try to update from v1.112.1 to v1.113.0. If I roll back to v1.112.1, the Immich-server works again without issues. The problem only arises when I attempt to run the command to update to v1.113.0.
The OS that Immich Server is running on
Debian GNU/Linux bookworm 12.7 aarch64, Raspberry
Version of Immich Server
v.1.113.0
Version of Immich Mobile App
v.1.113.0 build.156
Platform with the issue
Your docker-compose.yml content
Your .env content
Reproduction steps
Relevant log output
Additional information
No response