immich icon indicating copy to clipboard operation
immich copied to clipboard

Published 20 hours ago • verified publisher icon immich-app

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

Open bigstusexy opened this issue 1 year ago • 10 comments
trafficstars

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

  • [X] 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 avatar Sep 02 '24 21:09 bigstusexy

Update, going back to 1.112.1 images works just fine.

bigstusexy avatar Sep 02 '24 23:09 bigstusexy

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?

alextran1502 avatar Sep 02 '24 23:09 alextran1502

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 avatar Sep 03 '24 00:09 mertalev

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.

mertalev avatar Sep 03 '24 00:09 mertalev

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?

bigstusexy avatar Sep 03 '24 01:09 bigstusexy

Yup, you can try again now.

mertalev avatar Sep 03 '24 02:09 mertalev

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

bigstusexy avatar Sep 03 '24 12:09 bigstusexy

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 avatar Sep 06 '24 22:09 hakanu

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.

hakanu avatar Sep 06 '24 22:09 hakanu

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

(1 row)

setval

788

(1 row)

setval

34192 (1 row)

setval

  1

(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]

bigstusexy avatar Sep 09 '24 17:09 bigstusexy

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

harshit181 avatar Sep 17 '24 14:09 harshit181

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.

bigstusexy avatar Sep 17 '24 16:09 bigstusexy

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";

harshit181 avatar Sep 17 '24 16:09 harshit181