Sever update from 1.112 to 1.113 fails citing duplicate key in migration. #12245

bigstusexy · 2024-09-02T21:14:11Z

The bug

The server only runs for about a minute then dies, it auto restarts but goes though the same thing. I see a complaint about a duplicate key in the migration and that it violates a policy.

The system has 16GB of ram and the initial import was done via another system and them moved in as it would take too long to build such a large cache of thumbnails and other metadata. The other system was WSL2 based.

After comparing the changes in the docker-compose.yml and seeing that there was nothing really new. I removed the containers, deleted the images and rebuilt the project, same has I have done since 1.110 (I think)

Will try going back to 1.112 like similar issue: 12243

The OS that Immich Server is running on

Synology DSM 7.2.1

Version of Immich Server

1.113.0

Version of Immich Mobile App

N/A

Platform with the issue

Server
Web
Mobile

Your docker-compose.yml content

#
# 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}
    # extends:
    #   file: hwaccel.transcoding.yml
    #   service: cpu # set to one of [nvenc, quicksync, rkmpp, vaapi, vaapi-wsl] for accelerated transcoding
    volumes:
      - ${UPLOAD_LOCATION}:/usr/src/app/upload
      - /etc/localtime:/etc/localtime:ro
      - /volume1/photo:/external/Storage:ro
      
    env_file:
      - .env
    ports:
      - 2283:3001
    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: 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

  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:
      - ${DB_DATA_LOCATION}:/var/lib/postgresql/data
    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

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/Chicago

# 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=[REDACTED]

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

Reproduction steps

1.Run Immich 1.112 and create system
2.Go through steps to update to 1.113
3.
...

Relevant log output

2024/09/02 15:55:59	stdout	Detected CPU Cores: 4				
						
2024/09/02 15:55:49	stderr	microservices worker exited with code 1				
						
2024/09/02 15:55:49	stderr	}				
						
2024/09/02 15:55:49	stderr	  routine: '_bt_check_unique'				
						
2024/09/02 15:55:49	stderr	  line: '663', 				
2024/09/02 15:55:49	stderr	  file: 'nbtinsert.c', 				
2024/09/02 15:55:49	stderr	  constraint: 'UQ_assetId_type', 				
2024/09/02 15:55:49	stderr	  dataType: undefined, 				
2024/09/02 15:55:49	stderr	  column: undefined, 				
2024/09/02 15:55:49	stderr	  table: 'asset_files', 				
2024/09/02 15:55:49	stderr	  schema: 'public', 				
2024/09/02 15:55:49	stderr	  where: undefined, 				
2024/09/02 15:55:49	stderr	  internalQuery: undefined, 				
2024/09/02 15:55:49	stderr	  internalPosition: undefined, 				
2024/09/02 15:55:49	stderr	  position: undefined, 				
2024/09/02 15:55:49	stderr	  hint: undefined, 				
2024/09/02 15:55:49	stderr	  detail: 'Key (\"assetId\	 type)=(d83e4200-a790-4788-9b0e-5280254b0586	 preview) already exists.'		
"						
2024/09/02 15:55:49	stderr	  code: '23505', 				
2024/09/02 15:55:49	stderr	  severity: 'ERROR', 				
2024/09/02 15:55:49	stderr	  length: 253, 				
2024/09/02 15:55:49	stderr	  }, 				
2024/09/02 15:55:49	stderr	    routine: '_bt_check_unique'				
						
2024/09/02 15:55:49	stderr	    line: '663', 				
2024/09/02 15:55:49	stderr	    file: 'nbtinsert.c', 				
2024/09/02 15:55:49	stderr	    constraint: 'UQ_assetId_type', 				
2024/09/02 15:55:49	stderr	    dataType: undefined, 				
2024/09/02 15:55:49	stderr	    column: undefined, 				
2024/09/02 15:55:49	stderr	    table: 'asset_files', 				
2024/09/02 15:55:49	stderr	    schema: 'public', 				
2024/09/02 15:55:49	stderr	    where: undefined, 				
2024/09/02 15:55:49	stderr	    internalQuery: undefined, 				
2024/09/02 15:55:49	stderr	    internalPosition: undefined, 				
2024/09/02 15:55:49	stderr	    position: undefined, 				
2024/09/02 15:55:49	stderr	    hint: undefined, 				
2024/09/02 15:55:49	stderr	    detail: 'Key (\"assetId\	 type)=(d83e4200-a790-4788-9b0e-5280254b0586	 preview) already exists.'		
"						
2024/09/02 15:55:49	stderr	    code: '23505', 				
2024/09/02 15:55:49	stderr	    severity: 'ERROR', 				
2024/09/02 15:55:49	stderr	    length: 253, 				
2024/09/02 15:55:49	stderr	      at async /usr/src/app/dist/repositories/database.repository.js:186:23 {				
						
2024/09/02 15:55:49	stderr	      at async /usr/src/app/dist/services/database.service.js:102:17				
						
2024/09/02 15:55:49	stderr	      at async DatabaseRepository.runMigrations (/usr/src/app/dist/repositories/database.repository.js:178:9)				
						
2024/09/02 15:55:49	stderr	      at async DataSource.runMigrations (/usr/src/app/node_modules/typeorm/data-source/DataSource.js:265:35)				
						
2024/09/02 15:55:49	stderr	      at async MigrationExecutor.executePendingMigrations (/usr/src/app/node_modules/typeorm/migration/MigrationExecutor.js:225:17)				
						
2024/09/02 15:55:49	stderr	      at async AddAssetFilesTable1724101822106.up (/usr/src/app/dist/migrations/1724101822106-AddAssetFilesTable.js:10:9)				
						
2024/09/02 15:55:49	stderr	      at async PostgresQueryRunner.query (/usr/src/app/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:184:25)				
						
2024/09/02 15:55:49	stderr	      at process.processTicksAndRejections (node:internal/process/task_queues:95:5)				
						
2024/09/02 15:55:49	stderr	      at /usr/src/app/node_modules/pg/lib/client.js:526:17				
						
2024/09/02 15:55:49	stderr	  driverError: error: duplicate key value violates unique constraint "UQ_assetId_type"				
						
2024/09/02 15:55:49	stderr	  parameters: undefined, 				
2024/09/02 15:55:49	stderr	  query: `INSERT INTO \"asset_files\" (\"assetId\	 \"type\"	 \"path\") SELECT \"id\"	 'preview'	 \"previewPath\" FROM \"assets\" WHERE \"previewPath\" IS NOT NULL AND \"previewPath\" != ''`
 2024/09/02 15:55:49,stderr,    at async /usr/src/app/dist/repositories/database.repository.js:186:23 {  2024/09/02 15:55:49,stderr,    at async /usr/src/app/dist/services/database.service.js:102:17  2024/09/02 15:55:49,stderr,    at async DatabaseRepository.runMigrations (/usr/src/app/dist/repositories/database.repository.js:178:9)  2024/09/02 15:55:49,stderr,    at async DataSource.runMigrations (/usr/src/app/node_modules/typeorm/data-source/DataSource.js:265:35)  2024/09/02 15:55:49,stderr,    at async MigrationExecutor.executePendingMigrations (/usr/src/app/node_modules/typeorm/migration/MigrationExecutor.js:225:17)  2024/09/02 15:55:49,stderr,    at async AddAssetFilesTable1724101822106.up (/usr/src/app/dist/migrations/1724101822106-AddAssetFilesTable.js:10:9)  2024/09/02 15:55:49,stderr,    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)  2024/09/02 15:55:49,stderr,    at PostgresQueryRunner.query (/usr/src/app/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:219:19)  2024/09/02 15:55:49,stderr,QueryFailedError: duplicate key value violates unique constraint "UQ_assetId_type						
						
2024/09/02 15:55:49	stdout	Migration \"AddAssetFilesTable1724101822106\" failed, error: duplicate key value violates unique constraint \"UQ_assetId_type\				
"						
2024/09/02 15:55:13	stdout	�[32m[Nest] 32  - �[39m09/02/2024, 8:55:13 PM �[32m    LOG�[39m �[33m[Api:EventRepository]�[39m �[32mInitialized websocket server�[39m 				
2024/09/02 15:55:12	stdout	�[32m[Nest] 8  - �[39m09/02/2024, 8:55:12 PM �[32m    LOG�[39m �[33m[Microservices:EventRepository]�[39m �[32mInitialized websocket server�[39m 				
2024/09/02 15:55:03	stdout	Starting microservices worker				
						
2024/09/02 15:55:03	stdout	Starting api worker				
						
2024/09/02 15:54:12	stdout	Detected CPU Cores: 4

Additional information

No response

bigstusexy · 2024-09-02T23:00:37Z

Update, going back to 1.112.1 images works just fine.

alextran1502 · 2024-09-02T23:41:46Z

Holla the experts of this matter @jrasm91 and @mertalev FYI. I think this is unrelated to the thumbnail generation PR that just got merged, correct?

mertalev · 2024-09-03T00:09:14Z

Yes, this is unrelated. It doesn't really make sense to me, though. This is a new table and there should be no way for that insert command to violate the unique constraint. It might indicate corruption.

mertalev · 2024-09-03T00:59:48Z

As a first step, I recommend backing up the database. Then try connecting to the DB and running the VACUUM FULL; SQL command. If the issue is caused by any corrupt indices, this should correct it. But it's unlikely that a query like this would use an index, and it won't help if the corruption is in the actual data. It's still worth a shot, though.

bigstusexy · 2024-09-03T01:33:13Z

Okay,

Stopped all services, made a file based backup (because I didn't really quickly see how to do a DB backup and figured that if anything, replacing all the DB files will take me back to where I was)

Restarted the services

I connected via ssh:

sudo docker exec -it immich_postgres psql --dbname=immich --username=postgres --command="VACUUM FULL;"
VACUUM

With that return should I try the update again?

mertalev · 2024-09-03T02:13:09Z

Yup, you can try again now.

bigstusexy · 2024-09-03T12:37:50Z

Nah, got the same thing I think (sorry, I'm reading remotely so I didn't intensely look at the logs)

2024/09/03 07:33:42 stdout Detected CPU Cores: 4
2024/09/03 07:33:28 stderr microservices worker exited with code 1
2024/09/03 07:33:28 stderr }
2024/09/03 07:33:28 stderr routine: '_bt_check_unique'
2024/09/03 07:33:28 stderr line: '663',
2024/09/03 07:33:28 stderr file: 'nbtinsert.c',
2024/09/03 07:33:28 stderr constraint: 'UQ_assetId_type',
2024/09/03 07:33:28 stderr dataType: undefined,
2024/09/03 07:33:28 stderr column: undefined,
2024/09/03 07:33:28 stderr table: 'asset_files',
2024/09/03 07:33:28 stderr schema: 'public',
2024/09/03 07:33:28 stderr where: undefined,
2024/09/03 07:33:28 stderr internalQuery: undefined,
2024/09/03 07:33:28 stderr internalPosition: undefined,
2024/09/03 07:33:28 stderr position: undefined,
2024/09/03 07:33:28 stderr hint: undefined,
2024/09/03 07:33:28 stderr detail: 'Key ("assetId", type)=(d83e4200-a790-4788-9b0e-5280254b0586, preview) already exists.',
2024/09/03 07:33:28 stderr code: '23505',
2024/09/03 07:33:28 stderr severity: 'ERROR',
2024/09/03 07:33:28 stderr length: 253,
2024/09/03 07:33:28 stderr },
2024/09/03 07:33:28 stderr routine: '_bt_check_unique'
2024/09/03 07:33:28 stderr line: '663',
2024/09/03 07:33:28 stderr file: 'nbtinsert.c',
2024/09/03 07:33:28 stderr constraint: 'UQ_assetId_type',
2024/09/03 07:33:28 stderr dataType: undefined,
2024/09/03 07:33:28 stderr column: undefined,
2024/09/03 07:33:28 stderr table: 'asset_files',
2024/09/03 07:33:28 stderr schema: 'public',
2024/09/03 07:33:28 stderr where: undefined,
2024/09/03 07:33:28 stderr internalQuery: undefined,
2024/09/03 07:33:28 stderr internalPosition: undefined,
2024/09/03 07:33:28 stderr position: undefined,
2024/09/03 07:33:28 stderr hint: undefined,
2024/09/03 07:33:28 stderr detail: 'Key ("assetId", type)=(d83e4200-a790-4788-9b0e-5280254b0586, preview) already exists.',
2024/09/03 07:33:28 stderr code: '23505',
2024/09/03 07:33:28 stderr severity: 'ERROR',
2024/09/03 07:33:28 stderr length: 253,
2024/09/03 07:33:28 stderr at async /usr/src/app/dist/repositories/database.repository.js:186:23 {
2024/09/03 07:33:28 stderr at async /usr/src/app/dist/services/database.service.js:102:17
2024/09/03 07:33:28 stderr at async DatabaseRepository.runMigrations (/usr/src/app/dist/repositories/database.repository.js:178:9)
2024/09/03 07:33:28 stderr at async DataSource.runMigrations (/usr/src/app/node_modules/typeorm/data-source/DataSource.js:265:35)
2024/09/03 07:33:28 stderr at async MigrationExecutor.executePendingMigrations (/usr/src/app/node_modules/typeorm/migration/MigrationExecutor.js:225:17)
2024/09/03 07:33:28 stderr at async AddAssetFilesTable1724101822106.up (/usr/src/app/dist/migrations/1724101822106-AddAssetFilesTable.js:10:9)
2024/09/03 07:33:28 stderr at async PostgresQueryRunner.query (/usr/src/app/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:184:25)
2024/09/03 07:33:28 stderr at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
2024/09/03 07:33:28 stderr at /usr/src/app/node_modules/pg/lib/client.js:526:17
2024/09/03 07:33:28 stderr driverError: error: duplicate key value violates unique constraint "UQ_assetId_type"
2024/09/03 07:33:28 stderr parameters: undefined,
2024/09/03 07:33:28 stderr query: INSERT INTO "asset_files" ("assetId", "type", "path") SELECT "id", 'preview', "previewPath" FROM "assets" WHERE "previewPath" IS NOT NULL AND "previewPath" != '',
2024/09/03 07:33:28 stderr at async /usr/src/app/dist/repositories/database.repository.js:186:23 {
2024/09/03 07:33:28 stderr at async /usr/src/app/dist/services/database.service.js:102:17
2024/09/03 07:33:28 stderr at async DatabaseRepository.runMigrations (/usr/src/app/dist/repositories/database.repository.js:178:9)
2024/09/03 07:33:28 stderr at async DataSource.runMigrations (/usr/src/app/node_modules/typeorm/data-source/DataSource.js:265:35)
2024/09/03 07:33:28 stderr at async MigrationExecutor.executePendingMigrations (/usr/src/app/node_modules/typeorm/migration/MigrationExecutor.js:225:17)
2024/09/03 07:33:28 stderr at async AddAssetFilesTable1724101822106.up (/usr/src/app/dist/migrations/1724101822106-AddAssetFilesTable.js:10:9)
2024/09/03 07:33:28 stderr at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
2024/09/03 07:33:28 stderr at PostgresQueryRunner.query (/usr/src/app/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:219:19)
2024/09/03 07:33:28 stderr QueryFailedError: duplicate key value violates unique constraint "UQ_assetId_type"
2024/09/03 07:33:28 stdout Migration "AddAssetFilesTable1724101822106" failed, error: duplicate key value violates unique constraint "UQ_assetId_type"
2024/09/03 07:33:10 stdout �[32m[Nest] 18 - �[39m09/03/2024, 7:33:10 AM �[32m LOG�[39m �[33m[Api:EventRepository]�[39m �[32mInitialized websocket server�[39m
2024/09/03 07:33:10 stdout �[32m[Nest] 8 - �[39m09/03/2024, 7:33:10 AM �[32m LOG�[39m �[33m[Microservices:EventRepository]�[39m �[32mInitialized websocket server�[39m
2024/09/03 07:33:03 stdout Starting microservices worker
2024/09/03 07:33:03 stdout Starting api worker
2024/09/03 07:32:56 stdout Detected CPU Cores: 4

hakanu · 2024-09-06T22:46:06Z

I'm having the same problem after upgrading to v1.114.0 :(

any workarounds?

[Nest] 8  - 09/06/2024, 10:44:01 PM     LOG [Microservices:EventRepository] Initialized websocket server
[Nest] 18  - 09/06/2024, 10:44:01 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)=(73de63ce-b820-4833-8b88-84b17a503627) 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)=(73de63ce-b820-4833-8b88-84b17a503627) 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'
}
microservices worker exited with code 1

hakanu · 2024-09-06T22:48:32Z

for folks hitting this issue like me, i downgraded to v1.112.1 by adding this into .env file

IMMICH_VERSION=v1.112.1

Then docker compose pull && docker compose up -d and then it works.

bigstusexy · 2024-09-09T17:39:35Z

Hello,

Update, but no luck.

I tried the steps under Docs/Administration/Backup and Restore $database
The error is different but no luck. There were a few key errors mentioned in the restore process but it kept going.
The high level steps were:
pg_dump, remove containers, rebuild on 1.114 target, unfortunately it runs after a build but stopped cotainers, removed postres folder, ran restore, let it process, waited until system seemed less busy, started all processes. Got error. To confirm I had a good restore, I tried killing containers, going to 1.112.1, this still works.

I did take the time to directly run immich on my insanely powerful desktop (I use another hypervisor that doesn't run with WSL) and let it chew through rebuilding since I had not yet really done anything that couldn't be redone. So while this has thumb data that is nearly 1TB and the new one is less than 100GB so far, I will keep this totally intact for a while in case there is want to revist this issue, but for now I'm going to run with known good. This second try was started on 1.113.1 and later that day 1.114 was released and after the two place days of work, it finished and upgraded easily.

[skippable]
Synology users, you'll have to have ssl access as far as I can tell. We don't have docker compose by default on dsm 7, but you can use the container manager if you don't want to convert to using docker commands directly. You can copy and paste the needed linux commands, remember to change paths.

[End-skip]

[Server output upgrading to 1.114]

2024/09/09 12:18:56 stdout Detected CPU Cores: 4

2024/09/09 12:18:44 stderr microservices worker exited with code 1

2024/09/09 12:18:44 stderr }

2024/09/09 12:18:44 stderr routine: 'transformFkeyCheckAttrs'

2024/09/09 12:18:44 stderr line: '11081',
2024/09/09 12:18:44 stderr file: 'tablecmds.c',
2024/09/09 12:18:44 stderr constraint: undefined,
2024/09/09 12:18:44 stderr dataType: undefined,
2024/09/09 12:18:44 stderr column: undefined,
2024/09/09 12:18:44 stderr table: undefined,
2024/09/09 12:18:44 stderr schema: undefined,
2024/09/09 12:18:44 stderr where: undefined,
2024/09/09 12:18:44 stderr internalQuery: undefined,
2024/09/09 12:18:44 stderr internalPosition: undefined,
2024/09/09 12:18:44 stderr position: undefined,
2024/09/09 12:18:44 stderr hint: undefined,
2024/09/09 12:18:44 stderr detail: undefined,
2024/09/09 12:18:44 stderr code: '42830',
2024/09/09 12:18:44 stderr severity: 'ERROR',
2024/09/09 12:18:44 stderr length: 152,
2024/09/09 12:18:44 stderr },
2024/09/09 12:18:44 stderr routine: 'transformFkeyCheckAttrs'

2024/09/09 12:18:44 stderr line: '11081',
2024/09/09 12:18:44 stderr file: 'tablecmds.c',
2024/09/09 12:18:44 stderr constraint: undefined,
2024/09/09 12:18:44 stderr dataType: undefined,
2024/09/09 12:18:44 stderr column: undefined,
2024/09/09 12:18:44 stderr table: undefined,
2024/09/09 12:18:44 stderr schema: undefined,
2024/09/09 12:18:44 stderr where: undefined,
2024/09/09 12:18:44 stderr internalQuery: undefined,
2024/09/09 12:18:44 stderr internalPosition: undefined,
2024/09/09 12:18:44 stderr position: undefined,
2024/09/09 12:18:44 stderr hint: undefined,
2024/09/09 12:18:44 stderr detail: undefined,
2024/09/09 12:18:44 stderr code: '42830',
2024/09/09 12:18:44 stderr severity: 'ERROR',
2024/09/09 12:18:44 stderr length: 152,
2024/09/09 12:18:44 stderr at async /usr/src/app/dist/repositories/database.repository.js:199:23 {

2024/09/09 12:18:44 stderr at async /usr/src/app/dist/services/database.service.js:105:17

2024/09/09 12:18:44 stderr at async DatabaseRepository.runMigrations (/usr/src/app/dist/repositories/database.repository.js:191:9)

2024/09/09 12:18:44 stderr at async DataSource.runMigrations (/usr/src/app/node_modules/typeorm/data-source/DataSource.js:265:35)

2024/09/09 12:18:44 stderr at async MigrationExecutor.executePendingMigrations (/usr/src/app/node_modules/typeorm/migration/MigrationExecutor.js:225:17)

2024/09/09 12:18:44 stderr at async AddAssetFilesTable1724101822106.up (/usr/src/app/dist/migrations/1724101822106-AddAssetFilesTable.js:9:9)

2024/09/09 12:18:44 stderr at async PostgresQueryRunner.query (/usr/src/app/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:184:25)

2024/09/09 12:18:44 stderr at process.processTicksAndRejections (node:internal/process/task_queues:95:5)

2024/09/09 12:18:44 stderr at /usr/src/app/node_modules/pg/lib/client.js:526:17

2024/09/09 12:18:44 stderr driverError: error: there is no unique constraint matching given keys for referenced table "assets"

2024/09/09 12:18:44 stderr parameters: undefined,
2024/09/09 12:18:44 stderr " query: 'ALTER TABLE "asset_files" ADD CONSTRAINT "FK_e3e103a5f1d8bc8402999286040" FOREIGN KEY ("assetId") REFERENCES "assets"("id") ON DELETE CASCADE ON UPDATE CASCADE'
2024/09/09 12:18:44,stderr, at async /usr/src/app/dist/repositories/database.repository.js:199:23 { 2024/09/09 12:18:44,stderr, at async /usr/src/app/dist/services/database.service.js:105:17 2024/09/09 12:18:44,stderr, at async DatabaseRepository.runMigrations (/usr/src/app/dist/repositories/database.repository.js:191:9) 2024/09/09 12:18:44,stderr, at async DataSource.runMigrations (/usr/src/app/node_modules/typeorm/data-source/DataSource.js:265:35) 2024/09/09 12:18:44,stderr, at async MigrationExecutor.executePendingMigrations (/usr/src/app/node_modules/typeorm/migration/MigrationExecutor.js:225:17) 2024/09/09 12:18:44,stderr, at async AddAssetFilesTable1724101822106.up (/usr/src/app/dist/migrations/1724101822106-AddAssetFilesTable.js:9:9) 2024/09/09 12:18:44,stderr, at process.processTicksAndRejections (node:internal/process/task_queues:95:5) 2024/09/09 12:18:44,stderr, at PostgresQueryRunner.query (/usr/src/app/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:219:19) 2024/09/09 12:18:44,stderr,QueryFailedError: there is no unique constraint matching given keys for referenced table "assets

2024/09/09 12:18:44 stdout Migration "AddAssetFilesTable1724101822106" failed, error: there is no unique constraint matching given keys for referenced table "assets
"
2024/09/09 12:18:36 stdout �[32m[Nest] 17 - �[39m09/09/2024, 12:18:36 PM �[32m LOG�[39m �[33m[Api:EventRepository]�[39m �[32mInitialized websocket server�[39m
2024/09/09 12:18:35 stdout �[32m[Nest] 7 - �[39m09/09/2024, 12:18:35 PM �[32m LOG�[39m �[33m[Microservices:EventRepository]�[39m �[32mInitialized websocket server�[39m
2024/09/09 12:18:26 stdout Starting microservices worker

2024/09/09 12:18:26 stdout Starting api worker

2024/09/09 12:18:20 stdout Detected CPU Cores: 4

[end-server output]

[pg_dump restore]

gunzip < "/volume1/docker/immich_bck.sql.gz" \

| sed "s/SELECT pg_catalog.set_config('search_path', '', false);/SELECT pg_catalog.set_config('search_path', 'public, pg_catalog', true);/g"
| docker exec -i immich_postgres psql --username=postgres
SET
SET
SET
DROP DATABASE
ERROR: current user cannot be dropped
ERROR: role "postgres" already exists
ALTER ROLE
SET
SET
SET
SET
SET
set_config

public, pg_catalog
(1 row)

SET
SET
SET
SET
UPDATE 1
DROP DATABASE
CREATE DATABASE
ALTER DATABASE
You are now connected to database "template1" as user "postgres".
SET
SET
SET
SET
SET
set_config

public, pg_catalog
(1 row)

SET
SET
SET
SET
COMMENT
ALTER DATABASE
You are now connected to database "template1" as user "postgres".
SET
SET
SET
SET
SET
set_config

public, pg_catalog
(1 row)

SET
SET
SET
SET
REVOKE
GRANT
SET
SET
SET
SET
SET
set_config

public, pg_catalog
(1 row)

SET
SET
SET
SET
CREATE DATABASE
ALTER DATABASE
You are now connected to database "immich" as user "postgres".
SET
SET
SET
SET
SET
set_config

public, pg_catalog
(1 row)

SET
SET
SET
SET
ALTER DATABASE
You are now connected to database "immich" as user "postgres".
SET
SET
SET
SET
SET
set_config

public, pg_catalog
(1 row)

SET
SET
SET
SET
CREATE SCHEMA
ALTER SCHEMA
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
CREATE FUNCTION
ALTER FUNCTION
CREATE FUNCTION
ALTER FUNCTION
SET
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
COMMENT
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
COMMENT
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
COPY 0
COPY 0
COPY 0
COPY 0
COPY 0
COPY 343952
COPY 181168
COPY 0
COPY 182445
COPY 0
COPY 171322
COPY 343952
COPY 206067
COPY 1
COPY 0
COPY 0
COPY 157
COPY 0
COPY 4274
COPY 1
COPY 24878
COPY 17
COPY 0
COPY 0
COPY 0
COPY 171435
COPY 0
COPY 0
COPY 5
COPY 0
COPY 0
COPY 1
COPY 2
setval

(1 row)

setval

(1 row)

setval

34192
(1 row)

setval

(1 row)

ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ERROR: could not create unique index "PK_da96729a8b113377cfb6a62439c"
DETAIL: Key (id)=(64171d32-a6be-45b5-8ed1-7660b14f9f70) is duplicated.
CONTEXT: parallel worker
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
ERROR: could not create unique index "UQ_assets_owner_library_checksum"
DETAIL: Key ("ownerId", "libraryId", checksum)=(da7e135d-1ce0-43ea-8ea1-f09106f41c6c, abc770d7-a486-4024-891a-c4aa300cfc6d, \x365b38cce805cb9558bdce973242a797713de486) is duplicated.
CONTEXT: parallel worker
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
ERROR: there is no unique constraint matching given keys for referenced table "assets"
ERROR: there is no unique constraint matching given keys for referenced table "assets"
ALTER TABLE
ALTER TABLE
ERROR: there is no unique constraint matching given keys for referenced table "assets"
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ERROR: there is no unique constraint matching given keys for referenced table "assets"
ALTER TABLE
ERROR: there is no unique constraint matching given keys for referenced table "assets"
ALTER TABLE
ALTER TABLE
ALTER TABLE
ERROR: there is no unique constraint matching given keys for referenced table "assets"
ERROR: there is no unique constraint matching given keys for referenced table "assets"
ALTER TABLE
ERROR: there is no unique constraint matching given keys for referenced table "assets"
ALTER TABLE
ALTER TABLE
ALTER TABLE
ERROR: there is no unique constraint matching given keys for referenced table "assets"
ERROR: there is no unique constraint matching given keys for referenced table "assets"
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ERROR: there is no unique constraint matching given keys for referenced table "assets"
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ERROR: there is no unique constraint matching given keys for referenced table "assets"
ALTER TABLE
ERROR: there is no unique constraint matching given keys for referenced table "assets"
SET
SET
SET
SET
SET
set_config

public, pg_catalog
(1 row)

SET
SET
SET
SET
DROP DATABASE
CREATE DATABASE
ALTER DATABASE
You are now connected to database "postgres" as user "postgres".
SET
SET
SET
SET
SET
set_config

public, pg_catalog
(1 row)

SET
SET
SET
SET
COMMENT

[end pg_dump log]

harshit181 · 2024-09-17T14:20:21Z

was able to fix it by running
delete from "asset_job_status" WHERE not exits (select 1 from "asset" where id="asset_job_status"."assetId"
and running reindex on both table afterward

bigstusexy · 2024-09-17T16:36:26Z

Awesome @harshit181

I might be able to figure this figure this out but if you feel like posting a step by step, I'd be grateful. Once I try this, if successful I'll also note any changes for Synology.

harshit181 · 2024-09-17T16:45:04Z

Awesome @harshit181

I might be able to figure this figure this out but if you feel like posting a step by step, I'd be grateful. Once I try this, if successful I'll also note any changes for Synology.

docker exec -it immich_postgres bash
psql --dbname=<username> --username=<password>
delete from "asset_job_status" WHERE not exists (select 1 from "asset" where id="asset_job_status"."assetId" );
reindex "asset_job_status";
reindex "asset";

alextran1502 mentioned this issue Sep 3, 2024

Immich-Server Fails to Start Due to Foreign Key Constraint Violation in Migration Script #12243

Closed

3 tasks

immich-app locked and limited conversation to collaborators Sep 17, 2024

jrasm91 converted this issue into discussion #12760 Sep 17, 2024

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

This issue was moved to a discussion.

Sever update from 1.112 to 1.113 fails citing duplicate key in migration. #12245

Sever update from 1.112 to 1.113 fails citing duplicate key in migration. #12245

bigstusexy commented Sep 2, 2024

bigstusexy commented Sep 2, 2024

alextran1502 commented Sep 2, 2024

mertalev commented Sep 3, 2024

mertalev commented Sep 3, 2024

bigstusexy commented Sep 3, 2024

mertalev commented Sep 3, 2024

bigstusexy commented Sep 3, 2024

hakanu commented Sep 6, 2024

hakanu commented Sep 6, 2024

bigstusexy commented Sep 9, 2024

harshit181 commented Sep 17, 2024

bigstusexy commented Sep 17, 2024

harshit181 commented Sep 17, 2024 •

edited

Loading

This issue was moved to a discussion.

This issue was moved to a discussion.

Sever update from 1.112 to 1.113 fails citing duplicate key in migration. #12245

Sever update from 1.112 to 1.113 fails citing duplicate key in migration. #12245

Comments

bigstusexy commented Sep 2, 2024

The bug

The OS that Immich Server is running on

Version of Immich Server

Version of Immich Mobile App

Platform with the issue

Your docker-compose.yml content

Your .env content

Reproduction steps

Relevant log output

Additional information

bigstusexy commented Sep 2, 2024

alextran1502 commented Sep 2, 2024

mertalev commented Sep 3, 2024

mertalev commented Sep 3, 2024

bigstusexy commented Sep 3, 2024

mertalev commented Sep 3, 2024

bigstusexy commented Sep 3, 2024

hakanu commented Sep 6, 2024

hakanu commented Sep 6, 2024

bigstusexy commented Sep 9, 2024

SET SET SET SET UPDATE 1 DROP DATABASE CREATE DATABASE ALTER DATABASE You are now connected to database "template1" as user "postgres". SET SET SET SET SET set_config

SET SET SET SET COMMENT ALTER DATABASE You are now connected to database "template1" as user "postgres". SET SET SET SET SET set_config

SET SET SET SET REVOKE GRANT SET SET SET SET SET set_config

SET SET SET SET CREATE DATABASE ALTER DATABASE You are now connected to database "immich" as user "postgres". SET SET SET SET SET set_config

SET SET SET SET ALTER DATABASE You are now connected to database "immich" as user "postgres". SET SET SET SET SET set_config

setval

setval

setval

SET SET SET SET DROP DATABASE CREATE DATABASE ALTER DATABASE You are now connected to database "postgres" as user "postgres". SET SET SET SET SET set_config

harshit181 commented Sep 17, 2024

bigstusexy commented Sep 17, 2024

harshit181 commented Sep 17, 2024 • edited Loading

This issue was moved to a discussion.

SET
SET
SET
SET
UPDATE 1
DROP DATABASE
CREATE DATABASE
ALTER DATABASE
You are now connected to database "template1" as user "postgres".
SET
SET
SET
SET
SET
set_config

SET
SET
SET
SET
COMMENT
ALTER DATABASE
You are now connected to database "template1" as user "postgres".
SET
SET
SET
SET
SET
set_config

SET
SET
SET
SET
REVOKE
GRANT
SET
SET
SET
SET
SET
set_config

SET
SET
SET
SET
CREATE DATABASE
ALTER DATABASE
You are now connected to database "immich" as user "postgres".
SET
SET
SET
SET
SET
set_config

SET
SET
SET
SET
ALTER DATABASE
You are now connected to database "immich" as user "postgres".
SET
SET
SET
SET
SET
set_config

SET
SET
SET
SET
DROP DATABASE
CREATE DATABASE
ALTER DATABASE
You are now connected to database "postgres" as user "postgres".
SET
SET
SET
SET
SET
set_config

harshit181 commented Sep 17, 2024 •

edited

Loading