search

immich-app / immich

High performance self-hosted photo and video management solution.
https://immich.app
GNU Affero General Public License v3.0
49.06k stars 2.57k forks source link

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

Closed bigstusexy closed 1 month ago

bigstusexy commented 1 month ago

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

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  [Nest] 32  - 09/02/2024, 8:55:13 PM     LOG [Api:EventRepository] Initialized websocket server              
2024/09/02 15:55:12 stdout  [Nest] 8  - 09/02/2024, 8:55:12 PM     LOG [Microservices:EventRepository] Initialized websocket server                 
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 commented 1 month ago

Update, going back to 1.112.1 images works just fine.

alextran1502 commented 1 month ago

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 commented 1 month ago

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 commented 1 month ago

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 commented 1 month ago

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 commented 1 month ago

Yup, you can try again now.

bigstusexy commented 1 month ago

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 [Nest] 18 - 09/03/2024, 7:33:10 AM  LOG [Api:EventRepository] Initialized websocket server 2024/09/03 07:33:10 stdout [Nest] 8 - 09/03/2024, 7:33:10 AM  LOG [Microservices:EventRepository] Initialized websocket server 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 commented 1 month ago

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 commented 1 month ago

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 commented 1 month ago

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 [Nest] 17 - 09/09/2024, 12:18:36 PM  LOG [Api:EventRepository] Initialized websocket server 2024/09/09 12:18:35 stdout [Nest] 7 - 09/09/2024, 12:18:35 PM  LOG [Microservices:EventRepository] Initialized websocket server 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]

harshit181 commented 1 month ago

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 commented 1 month ago

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 commented 1 month ago

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