[BUG] Unable to delete file permanently

[Snickers333]

The bug

Hello, i have an issue where i cannot delete some files permanently from trash / empty the trash.
This is not happening to every file only a select few.

The following appears in immich_microservices log:

[Nest] 7  - 03/22/2024, 8:51:10 PM   ERROR [JobService] Unable to run job handler (backgroundTask/asset-deletion): QueryFailedError: update or delete on table "assets" violates foreign key constraint "FK_91704e101438fd0653f582426dc" on table "asset_stack"
[Nest] 7  - 03/22/2024, 8:51:10 PM   ERROR [JobService] QueryFailedError: update or delete on table "assets" violates foreign key constraint "FK_91704e101438fd0653f582426dc" on table "asset_stack"
    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 DeleteQueryBuilder.execute (/usr/src/app/node_modules/typeorm/query-builder/DeleteQueryBuilder.js:52:33)
    at async SubjectExecutor.executeRemoveOperations (/usr/src/app/node_modules/typeorm/persistence/SubjectExecutor.js:461:17)
    at async SubjectExecutor.execute (/usr/src/app/node_modules/typeorm/persistence/SubjectExecutor.js:104:9)
    at async EntityPersistExecutor.execute (/usr/src/app/node_modules/typeorm/persistence/EntityPersistExecutor.js:140:21)
    at async AssetRepository.remove (/usr/src/app/dist/infra/repositories/asset.repository.js:196:9)
    at async AssetService.handleAssetDeletion (/usr/src/app/dist/domain/asset/asset.service.js:352:9)
    at async /usr/src/app/dist/domain/job/job.service.js:137:36
    at async Worker.processJob (/usr/src/app/node_modules/bullmq/dist/cjs/classes/worker.js:394:28)
[Nest] 7  - 03/22/2024, 8:51:10 PM   ERROR [JobService] Object:
{
  "id": "e2114f00-88e5-43a4-a9df-40bdc1845773"
}

and the following in immich_postgres:

2024-03-22 20:51:10.334 UTC [107] STATEMENT:  DELETE FROM "assets" WHERE "id" = $1
2024-03-22 20:51:10.338 UTC [108] ERROR:  update or delete on table "assets" violates foreign key constraint "FK_91704e101438fd0653f582426dc" on table "asset_stack"
2024-03-22 20:51:10.338 UTC [108] DETAIL:  Key (id)=(e2114f00-88e5-43a4-a9df-40bdc1845773) is still referenced from table "asset_stack".
2024-03-22 20:51:10.338 UTC [108] STATEMENT:  DELETE FROM "assets" WHERE "id" = $1

The OS that Immich Server is running on

Debian 6.1.55-1 Docker

Version of Immich Server

v1.99.0

Version of Immich Mobile App

v1.99.0

Platform with the issue

• Server
• Web
• Mobile

Your docker-compose.yml content

version: '3.8'

name: immich

services:
  immich-server:
    container_name: immich_server
    image: ghcr.io/immich-app/immich-server:${IMMICH_VERSION:-release}
    command: ['start.sh', 'immich']
    volumes:
      - ${LIBRARY_LOCATION}:/usr/src/app/upload/library
      - ${UPLOAD_LOCATION}:/usr/src/app/upload/upload
      - ${THUMBS_LOCATION}:/usr/src/app/upload/thumbs
      - ${PROFILE_LOCATION}:/usr/src/app/upload/profile
      - ${VIDEO_LOCATION}:/usr/src/app/upload/encoded-video
      - /etc/localtime:/etc/localtime:ro
    env_file:
      - stack.env
    ports:
      - 2283:3001
    depends_on:
      - redis
      - database
    restart: always

  immich-microservices:
    container_name: immich_microservices
    image: ghcr.io/immich-app/immich-server:${IMMICH_VERSION:-release}
    devices:
      - /dev/dri:/dev/dri
    command: ['start.sh', 'microservices']
    volumes:
      - ${LIBRARY_LOCATION}:/usr/src/app/upload/library
      - ${UPLOAD_LOCATION}:/usr/src/app/upload/upload
      - ${THUMBS_LOCATION}:/usr/src/app/upload/thumbs
      - ${PROFILE_LOCATION}:/usr/src/app/upload/profile
      - ${VIDEO_LOCATION}:/usr/src/app/upload/encoded-video
      - /etc/localtime:/etc/localtime:ro
    env_file:
      - stack.env
    depends_on:
      - redis
      - database
    restart: always

  immich-machine-learning:
    container_name: immich_machine_learning
    image: ghcr.io/immich-app/immich-machine-learning:${IMMICH_VERSION:-release}
    volumes:
      - model-cache:/cache
    env_file:
      - stack.env
    restart: always

  redis:
    container_name: immich_redis
    image: redis:6.2-alpine@sha256:51d6c56749a4243096327e3fb964a48ed92254357108449cb6e23999c37773c5
    restart: always

  database:
    container_name: immich_postgres
    image: tensorchord/pgvecto-rs:pg14-v0.2.0@sha256:90724186f0a3517cf6914295b5ab410db9ce23190a2d9d0b9dd6463e3fa298f0
    env_file:
      - stack.env
    environment:
      POSTGRES_PASSWORD: ${DB_PASSWORD}
      POSTGRES_USER: ${DB_USERNAME}
      POSTGRES_DB: ${DB_DATABASE_NAME}
    volumes:
      - pgdata:/var/lib/postgresql/data
    restart: always


volumes:
  pgdata:
  model-cache:

Your .env content

LIBRARY_LOCATION=/mnt/Apps/Immich/
THUMBS_LOCATION=/mnt/Apps/Immich-Cache/thumbs/
UPLOAD_LOCATION=/mnt/Apps/Immich-Cache/upload/
PROFILE_LOCATION=/mnt/Apps/Immich-Cache/profile/
VIDEO_LOCATION=/mnt/Apps/Immich-Cache/encoded-video/
IMMICH_VERSION=release
DB_PASSWORD=***
DB_HOSTNAME=immich_postgres
DB_USERNAME=***
DB_DATABASE_NAME=immich
REDIS_HOSTNAME=immich_redis

Reproduction steps

1. delete photo
2. go to trash
3. delete photo permanently / empty trash
...

Additional information

No response

[schuhbacca]

Are these in an external library? If so you won't be able too

[Snickers333]

All of the files I'm uploading manually to immich. No external libraries used.

[schuhbacca]

Interesting, were these assets stacked before you deleted one of them?

[Snickers333]

I also noticed one more thing.
Trying to stack these undeletable photos throws an immich server error.

[Nest] 7 - 03/27/2024, 4:05:03 PM ERROR [QueryFailedError: duplicate key value violates unique constraint "REL_91704e101438fd0653f582426d" 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 InsertQueryBuilder.execute (/usr/src/app/node_modules/typeorm/query-builder/InsertQueryBuilder.js:106:33) at async SubjectExecutor.executeInsertOperations (/usr/src/app/node_modules/typeorm/persistence/SubjectExecutor.js:260:42) at async SubjectExecutor.execute (/usr/src/app/node_modules/typeorm/persistence/SubjectExecutor.js:92:9) at async EntityPersistExecutor.execute (/usr/src/app/node_modules/typeorm/persistence/EntityPersistExecutor.js:140:21) at async AssetStackRepository.save (/usr/src/app/dist/infra/repositories/asset-stack.repository.js:47:24) at async AssetService.updateAll (/usr/src/app/dist/domain/asset/asset.service.js:292:25)] Failed to update assets [Nest] 7 - 03/27/2024, 4:05:03 PM ERROR [QueryFailedError: duplicate key value violates unique constraint "REL_91704e101438fd0653f582426d" 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 InsertQueryBuilder.execute (/usr/src/app/node_modules/typeorm/query-builder/InsertQueryBuilder.js:106:33) at async SubjectExecutor.executeInsertOperations (/usr/src/app/node_modules/typeorm/persistence/SubjectExecutor.js:260:42) at async SubjectExecutor.execute (/usr/src/app/node_modules/typeorm/persistence/SubjectExecutor.js:92:9) at async EntityPersistExecutor.execute (/usr/src/app/node_modules/typeorm/persistence/EntityPersistExecutor.js:140:21) at async AssetStackRepository.save (/usr/src/app/dist/infra/repositories/asset-stack.repository.js:47:24) at async AssetService.updateAll (/usr/src/app/dist/domain/asset/asset.service.js:292:25)] QueryFailedError: duplicate key value violates unique constraint "REL_91704e101438fd0653f582426d"

[Snickers333]

Interesting, were these assets stacked before you deleted one of them?

I'm sorry. This might be possible that they were stacked before.