[BUG] Failed Backup - Duplicate keys

[palitu]

Describe the bug
When attempting to backups files, ~10% (350) failed with the reason being:
QueryFailedError: duplicate key value violates unique constraint "UQ_b599ab0bd9574958acb0b30a90e
note, there maybe a typo in the GUID above!

See attached screenshot.

Task List

Please complete the task list below. We need this information to help us reproduce the bug or point out problems in your setup. You are not providing enough info may delay our effort to help you.

• I have read thoroughly the README setup and installation instructions.
• I have included my docker-compose file.
• I have included my redacted .env file.
• I have included information on my machine, and environment.

To Reproduce
I am not too sure. All i know is that i started to upload default backups (recent on android), then i changed i excluded recent and enabled camera folder. This got the backups into a 'funky' state, where it was showing negative numbers for the number of files backed up. After exiting the app, killing it and restarting, it started backing up as expected, but it started giving the failed backup error for duplicate keys (all of which were exactly the same)

Expected behavior
No failed backups.

Screenshots
If applicable, add screenshots to help explain your problem.

System

• Phone OS [Android]: 12
• Server Version: 1.19.0
• Mobile App Version: 1.18.0 Build 27

Additional context
Brand new install.

services:

  immich-server:
    container_name: immich-server
    image: altran1502/immich-server:release
    entrypoint: ["/bin/sh", "./start-server.sh"]
    volumes:
      - ${UPLOAD_LOCATION}:/usr/src/app/upload
    env_file:
      - ./immich/.env
    environment:
      - NODE_ENV=production
    depends_on:
      - immich-redis
      - immich-postgres
    restart: always

  immich-microservices:
    container_name: immich-microservices
    image: altran1502/immich-server:release
    entrypoint: ["/bin/sh", "./start-microservices.sh"]
    volumes:
      - ${UPLOAD_LOCATION}:/usr/src/app/upload
    env_file:
      - ./immich/.env
    environment:
      - NODE_ENV=production
    depends_on:
      - immich-redis
      - immich-postgres
    restart: always

  immich-web:
    container_name: immich-web
    image: altran1502/immich-web:release
    entrypoint: ["/bin/sh", "./entrypoint.sh"]
    env_file:
      - ./immich/.env
    restart: always

  immich-redis:
    container_name: immich-redis
    image: redis:6.2
    restart: always

  immich-postgres:
    container_name: immich-postgres

    image: postgres:14
    env_file:
      - ./immich/.env
    environment:
      POSTGRES_PASSWORD: ${DB_PASSWORD}
      POSTGRES_USER: ${DB_USERNAME}
      POSTGRES_DB: ${DB_DATABASE_NAME}
      PG_DATA: /var/lib/postgresql/data
    volumes:
      - ./immich/pgdata:/var/lib/postgresql/data
    restart: always

  immich-proxy:
    container_name: immich-proxy
    image: altran1502/immich-proxy:release
    ports:
      - 2283:80
    logging:
      driver: none
    depends_on:
      - immich-server
    restart: always
  radicale:
    image: tomsquest/docker-radicale
    container_name: radicale
    ports:
      - 5232:5232
    restart: unless-stopped
    volumes:
      - ./radicale/data:/data
    labels:
      - "com.centurylinklabs.watchtower.enable=true"

.env

DB_HOSTNAME=immich-postgres
DB_USERNAME=postgres
DB_PASSWORD=postgres
DB_DATABASE_NAME=immich

# Optional Database settings:
# DB_PORT=5432

REDIS_HOSTNAME=immich-redis

# Optional Redis settings:
# REDIS_PORT=6379
# REDIS_DBINDEX=0
# REDIS_PASSWORD=
# REDIS_SOCKET=

UPLOAD_LOCATION=/home/leigh/appdata/immich/uploads

JWT_SECRET=<redacted>

# ENABLE_MAPBOX is either true of false -> if true, you have to provide MAPBOX_KEY
ENABLE_MAPBOX=false
MAPBOX_KEY=

VITE_SERVER_ENDPOINT=http://<redacted>:2283/api

VITE_LOGIN_PAGE_MESSAGE=

[alextran1502]

I've seen this error before and interestingly it only happens on Android. I will keep you updated once this is resolved. In the meantime, can you help me nuke the setup and restart and try just to backup from the camera folder from the beginning to see if it still running into this issue?

[alextran1502]

I found the problem. So the Recent album contains everything in it; if you exclude it, once the app tries to perform its check, it detects there are no assets (since Recent is excluded), so it adds the default, which is the Recent album back and duplicates some of the assets in the included list. I hope my explanation makes sense.

So I made a change not to allow to exclude Recent album. It will fix this issue.

[20pins]

This is happening to me on iOS. So far it's only for one .mov clip. Do I make a separate issue?

[alextran1502]

@20pins Which version of the app are you on? Was there any action you perform in the album selection?

[20pins]

@20pins Which version of the app are you on? Was there any action you perform in the album selection?

Selected only the "Recent" album (all photos). App version 1.22.0 build 42. Server version 1.22.0.

[alextran1502]

@20pins Can you help me check if that asset is actually duplicated? (Already uploaded)

[20pins]

@20pins Can you help me check if that asset is actually duplicated? (Already uploaded)

Just took a look and the asset is uploaded. Also, there error is no longer showing in the app. I can find the error in the log's for the server container but not sure where else to look to find supporting information.

[20pins]

Caught another instance. On my iOS setup it only appears to happen with .mov files. In this case I immediately checked and did see the video already added. The error is exactly the same as @palitu's, including the UQ code.

[palitu]

Sorry to only just get back to this. I wanted to avoid recent as I think it also includes things like screenshots, which I do not want backed up.

Note I excluded recent and added camera.so there should be plenty of resources!

[20pins]

Here is the full error from postgres...
2022-08-13 20:13:05.916 UTC [2388] ERROR: duplicate key value violates unique constraint "UQ_b599ab0bd9574958acb0b30a90e" 2022-08-13 20:13:05.916 UTC [2388] DETAIL: Key ("deviceAssetId", "userId", "deviceId")=(8AFB900D-A566-4EFF-9ACD-346E5FBD9DB3/L0/001, f4a2ed9f-1ba8-4b2f-9afc-965e780471f4, e4026147ca3870742bd30a38535afcc8969c40a41f6b021c887022762caf3f9a) already exists. 2022-08-13 20:13:05.916 UTC [2388] STATEMENT: INSERT INTO "assets"("id", "deviceAssetId", "userId", "deviceId", "type", "originalPath", "resizePath", "webpPath", "encodedVideoPath", "createdAt", "modifiedAt", "isFavorite", "mimeType", "duration") VALUES (DEFAULT, $1, $2, $3, $4, $5, DEFAULT, DEFAULT, DEFAULT, $6, $7, $8, $9, $10) RETURNING "id", "webpPath", "encodedVideoPath", "isFavorite"

[baswag]

Also having this problem while trying to import with the CLI (0.18.0, Server 1.25.0). The image is not duplicated and not uploaded

[baswag]

@alextran1502 I tried tracing the error when using the CLI and it seems like that some of my files create this conflict when the ctime mtime and birthtime of the files are so close together or simply just by chance add to the same rounded asset id.

E.g. for one file it was 1661373708387.721 1572640388000 1661373708376.7214 and for the other 1661373665385.2417 1572640474000 1661373665379.242 leading to both having 4895387804764 as their assetId and therefore the constraint being violated. Maybe something similar is happening in the iOS app but as my assets were not uploaded I'm not sure.

[alextran1502]

@baswag Thank you for the report. We are implementing a method to check for deduplication of upload files. Once that feature works, we will clean up the upload method to not check for those values in a file that lead to potential collisions like in your case.