immich duplicate key value violates unique constraint "UQ

The bug

Hello!

immich doesn't seem to recover gracefully from some kind of half-completed file upload/migration state. I see these in the logs:

[Nest] 2  - 02/26/2025, 6:17:19 PM     LOG [Microservices:APIKeyService] Attempting to finish incomplete move: upload/thumbs/c1c02cff-eb19-4fbf-bf9b-9425864a84e9/f9fcd15a-8954-4221-ab60-a735b2237c55.jpeg => upload/thumbs/c1c02cff-eb19-4fbf-bf9b-9425864a84e9/f9/fc/f9fcd15a-8954-4221-ab60-a735b2237c55-thumbnail.jpeg
[Nest] 2  - 02/26/2025, 6:17:19 PM     LOG [Microservices:APIKeyService] Attempting to finish incomplete move: upload/thumbs/c1c02cff-eb19-4fbf-bf9b-9425864a84e9/a34cf644-091d-4669-85a9-872a44e7bf41.jpeg => upload/thumbs/c1c02cff-eb19-4fbf-bf9b-9425864a84e9/a3/4c/a34cf644-091d-4669-85a9-872a44e7bf41-thumbnail.jpeg
[Nest] 2  - 02/26/2025, 6:17:19 PM     LOG [Microservices:APIKeyService] Attempting to finish incomplete move: upload/thumbs/c1c02cff-eb19-4fbf-bf9b-9425864a84e9/1f1e8730-3a60-4a6e-9645-9bd6b5b9f40d.jpeg => upload/thumbs/c1c02cff-eb19-4fbf-bf9b-9425864a84e9/1f/1e/1f1e8730-3a60-4a6e-9645-9bd6b5b9f40d-thumbnail.jpeg
[Nest] 2  - 02/26/2025, 6:17:19 PM     LOG [Microservices:APIKeyService] Attempting to finish incomplete move: upload/thumbs/c1c02cff-eb19-4fbf-bf9b-9425864a84e9/cf00e972-041f-4a74-8579-c7da667a5b3c.jpeg => upload/thumbs/c1c02cff-eb19-4fbf-bf9b-9425864a84e9/cf/00/cf00e972-041f-4a74-8579-c7da667a5b3c-thumbnail.jpeg
[Nest] 2  - 02/26/2025, 6:17:19 PM    WARN [Microservices:APIKeyService] Unable to complete move. File does not exist at either location.
[Nest] 2  - 02/26/2025, 6:17:19 PM    WARN [Microservices:APIKeyService] Unable to complete move. File does not exist at either location.
[Nest] 2  - 02/26/2025, 6:17:19 PM    WARN [Microservices:APIKeyService] Unable to complete move. File does not exist at either location.
[Nest] 2  - 02/26/2025, 6:17:19 PM    WARN [Microservices:APIKeyService] Unable to complete move. File does not exist at either location.
Query failed : {
  durationMs: 15.850073000008706,
  error: PostgresError: duplicate key value violates unique constraint "UQ_newPath"
      at ErrorResponse (/usr/src/app/node_modules/postgres/cjs/src/connection.js:788:26)
      at handle (/usr/src/app/node_modules/postgres/cjs/src/connection.js:474:6)
      at Socket.data (/usr/src/app/node_modules/postgres/cjs/src/connection.js:315:9)
      at Socket.emit (node:events:524:28)
      at addChunk (node:internal/streams/readable:561:12)
      at readableAddChunkPushByteMode (node:internal/streams/readable:512:3)
      at Readable.push (node:internal/streams/readable:392:5)
      at TCP.onStreamRead (node:internal/stream_base_commons:189:23) {
    severity_local: 'ERROR',
    severity: 'ERROR',
    code: '23505',
    detail: 'Key ("newPath")=(upload/thumbs/c1c02cff-eb19-4fbf-bf9b-9425864a84e9/f9/fc/f9fcd15a-8954-4221-ab60-a735b2237c55-thumbnail.jpeg) already exists.',
    schema_name: 'public',
    table_name: 'move_history',
    constraint_name: 'UQ_newPath',
    file: 'nbtinsert.c',
    line: '664',
    routine: '_bt_check_unique'
  },
  sql: 'insert into "move_history" ("entityId", "pathType", "oldPath", "newPath") values ($1, $2, $3, $4) returning *',
  params: [
    'f9fcd15a-8954-4221-ab60-a735b2237c55',
    'thumbnail',
    'upload/thumbs/c1c02cff-eb19-4fbf-bf9b-9425864a84e9/f9fcd15a-8954-4221-ab60-a735b2237c55.webp',
    'upload/thumbs/c1c02cff-eb19-4fbf-bf9b-9425864a84e9/f9/fc/f9fcd15a-8954-4221-ab60-a735b2237c55-thumbnail.jpeg'
  ]
}

which I think implies that some files in the DB do not exist on disk, what is the right way to correct this? :)

The OS that Immich Server is running on

Debian Bookworm (host and container)

Version of Immich Server

1.127.0

Version of Immich Mobile App

1.127.0

Platform with the issue

[x] Server
[ ] Web
[ ] Mobile

Your docker-compose.yml content

I run on kubernetes, but here is the deployment yaml:

apiVersion: apps/v1
kind: Deployment
metadata:
  annotations:
    deployment.kubernetes.io/revision: "63"
    kubectl.kubernetes.io/last-applied-configuration: |
      {"apiVersion":"apps/v1","kind":"Deployment","metadata":{"annotations":{},"name":"photos","namespace":"photos"},"spec":{"replicas":1,"selector":{"matchLabels":{"app":"photos"}},"strategy":{"type":"Recreate"},"template":{"metadata":{"labels":{"app":"photos"}},"spec":{"containers":[{"envFrom":[{"configMapRef":{>
  creationTimestamp: "2024-05-13T10:46:32Z"
  generation: 73
  name: photos
  namespace: photos
  resourceVersion: "78560301"
  uid: 8a2e3f75-673c-4be3-83fa-dbf925a80873
spec:
  progressDeadlineSeconds: 600
  replicas: 1
  revisionHistoryLimit: 10
  selector:
    matchLabels:
      app: photos
  strategy:
    type: Recreate
  template:
    metadata:
      creationTimestamp: null
      labels:
        app: photos
    spec:
      containers:
      - envFrom:
        - configMapRef:
            name: photos-config
        image: my-container-host/photos-server:206
        imagePullPolicy: IfNotPresent
        resources:
          requests:
            cpu: 250m
            memory: 4Gi
        terminationMessagePath: /dev/termination-log
        terminationMessagePolicy: File
        volumeMounts:
        - mountPath: /cache
          name: ml-cache
        - mountPath: /usr/src/app/upload
          name: photos
      dnsPolicy: ClusterFirst
      restartPolicy: Always
      schedulerName: default-scheduler
      securityContext:
        fsGroup: 3000
        runAsGroup: 3000
        runAsNonRoot: true
        runAsUser: 2000
      terminationGracePeriodSeconds: 30
      volumes:
      - name: ml-cache
        persistentVolumeClaim:
          claimName: photos-ml-cache
      - name: photos
        persistentVolumeClaim:
          claimName: photos-data

Your .env content

Kubernetes configmap:

apiVersion: v1
kind: ConfigMap
metadata:
  name: photos-config
  labels:
    app: photos
data:
  ###################################################################################
  # Database
  ###################################################################################
  DB_HOSTNAME: "postgresql.myhomedomain"
  DB_USERNAME: ""
  DB_PASSWORD: ""
  DB_DATABASE_NAME: ""


  ###################################################################################
  # Redis
  ###################################################################################

  REDIS_HOSTNAME: "redis.myhomedomain"


  ###################################################################################
  # Upload File Location
  #
  # This is the location where uploaded files are stored.
  ###################################################################################

  UPLOAD_LOCATION: "/usr/src/app/upload/"

  ###################################################################################
  # Reverse Geocoding
  #
  # Reverse geocoding is done locally which has a small impact on memory usage
  # This memory usage can be altered by changing the REVERSE_GEOCODING_PRECISION variable
  # This ranges from 0-3 with 3 being the most precise
  # 3 - Cities > 500 population: ~200MB RAM
  # 2 - Cities > 1000 population: ~150MB RAM
  # 1 - Cities > 5000 population: ~80MB RAM
  # 0 - Cities > 15000 population: ~40MB RAM
  ####################################################################################

  # DISABLE_REVERSE_GEOCODING=false
  # REVERSE_GEOCODING_PRECISION=3

  ####################################################################################
  # WEB - Optional
  #
  # Custom message on the login page, should be written in HTML form.
  # For example:
  # PUBLIC_LOGIN_PAGE_MESSAGE="This is a demo instance of Immich.<br><br>Email: <i>[email protected]</i><br>Password: <i>demo</i>"
  ####################################################################################

  PUBLIC_LOGIN_PAGE_MESSAGE: ""
  IMMICH_WEB_URL: "http://photos:3000"
  IMMICH_SERVER_URL: "http://photos:2283"
  IMMICH_MACHINE_LEARNING_URL: "http://photos:3003"

  ####################################################################################
  # Alternative API's External Address - Optional
  #
  # This is an advanced feature used to control the public server endpoint returned to clients during Well-known discovery.
  # You should only use this if you want mobile apps to access the immich API over a custom URL. Do not include trailing slash.
  # NOTE: At this time, the web app will not be affected by this setting and will continue to use the relative path: /api
  # Examples: http://localhost:2283, http://immich-api.example.com, etc
  ####################################################################################

  IMMICH_API_URL_EXTERNAL: "https://photos.myhomedomain"

  # Set the MatPlotLib temp var to stop it logging a warning on every startup
  MPLCONFIGDIR: "/tmp/matplotlib.temp/"

Reproduction steps

Go to /admin/jobs-status
Start the "Migration" task
Watch the server logs

Relevant log output

[Nest] 2  - 02/26/2025, 7:03:35 PM     LOG [Microservices:APIKeyService] Attempting to finish incomplete move: upload/thumbs/c1c02cff-eb19-4fbf-bf9b-9425864a84e9/c82767b7-a9e7-4f32-81bf-21ef9d3e1c84.jpeg => upload/thumbs/c1c02cff-eb19-4fbf-bf9b-9425864a84e9/c8/27/c82767b7-a9e7-4f32-81bf-21ef9d3e1c84-thumbnail.jpeg
[Nest] 2  - 02/26/2025, 7:03:35 PM    WARN [Microservices:APIKeyService] Unable to complete move. File does not exist at either location.
Query failed : {
  durationMs: 4.522349999751896,
  error: PostgresError: duplicate key value violates unique constraint "UQ_newPath"
      at ErrorResponse (/usr/src/app/node_modules/postgres/cjs/src/connection.js:788:26)
      at handle (/usr/src/app/node_modules/postgres/cjs/src/connection.js:474:6)
      at Socket.data (/usr/src/app/node_modules/postgres/cjs/src/connection.js:315:9)
      at Socket.emit (node:events:524:28)
      at addChunk (node:internal/streams/readable:561:12)
      at readableAddChunkPushByteMode (node:internal/streams/readable:512:3)
      at Readable.push (node:internal/streams/readable:392:5)
      at TCP.onStreamRead (node:internal/stream_base_commons:189:23) {
    severity_local: 'ERROR',
    severity: 'ERROR',
    code: '23505',
    detail: 'Key ("newPath")=(upload/thumbs/c1c02cff-eb19-4fbf-bf9b-9425864a84e9/c8/27/c82767b7-a9e7-4f32-81bf-21ef9d3e1c84-thumbnail.jpeg) already exists.',
    schema_name: 'public',
    table_name: 'move_history',
    constraint_name: 'UQ_newPath',
    file: 'nbtinsert.c',
    line: '664',
    routine: '_bt_check_unique'
  },
  sql: 'insert into "move_history" ("entityId", "pathType", "oldPath", "newPath") values ($1, $2, $3, $4) returning *',
  params: [
    'c82767b7-a9e7-4f32-81bf-21ef9d3e1c84',
    'thumbnail',
    'upload/thumbs/c1c02cff-eb19-4fbf-bf9b-9425864a84e9/c82767b7-a9e7-4f32-81bf-21ef9d3e1c84.webp',
    'upload/thumbs/c1c02cff-eb19-4fbf-bf9b-9425864a84e9/c8/27/c82767b7-a9e7-4f32-81bf-21ef9d3e1c84-thumbnail.jpeg'
  ]
}
[Nest] 2  - 02/26/2025, 7:03:35 PM   ERROR [Microservices:JobService] Unable to run job handler (migration/migrate-asset): PostgresError: duplicate key value violates unique constraint "UQ_newPath"
[Nest] 2  - 02/26/2025, 7:03:35 PM   ERROR [Microservices:JobService] PostgresError: duplicate key value violates unique constraint "UQ_newPath"
    at ErrorResponse (/usr/src/app/node_modules/postgres/cjs/src/connection.js:788:26)
    at handle (/usr/src/app/node_modules/postgres/cjs/src/connection.js:474:6)
    at Socket.data (/usr/src/app/node_modules/postgres/cjs/src/connection.js:315:9)
    at Socket.emit (node:events:524:28)
    at addChunk (node:internal/streams/readable:561:12)
    at readableAddChunkPushByteMode (node:internal/streams/readable:512:3)
    at Readable.push (node:internal/streams/readable:392:5)
    at TCP.onStreamRead (node:internal/stream_base_commons:189:23)
[Nest] 2  - 02/26/2025, 7:03:35 PM   ERROR [Microservices:JobService] Object(1) {
  id: 'c82767b7-a9e7-4f32-81bf-21ef9d3e1c84'

Additional information

No response

Feb 26 '25 19:02 Foritus

Same here Version: RELEASE.2025-02-07T23-21-09Z

Feb 26 '25 19:02 Morteza-Rastgoo

@zackpollard Do you mind looking at this again? Thank you

Feb 26 '25 20:02 alextran1502

🚨 This issue is really critical to solve as the upload/backup function is now useless. ⚠️ No upload is working.

To provide more context:

The Problem:

The critical issue is this error message:

Query failed : {
durationMs: 12.482947999960743,
error: PostgresError: duplicate key value violates unique constraint "UQ_assets_owner_checksum"
at ErrorResponse (/usr/src/app/node_modules/postgres/cjs/src/connection.js:788:26)
... (stack trace) ...
severity_local: 'ERROR',
severity: 'ERROR',
code: '23505',
detail: 'Key ("ownerId", checksum)=(a23077bf-8dbe-409c-b121-1c87f44eb345, \\xc40c612f050624d840428996fca60e257dbca79e) already exists.',
schema_name: 'public',
table_name: 'assets',
constraint_name: 'UQ_assets_owner_checksum',
file: 'nbtinsert.c',
line: '663',
routine: '_bt_check_unique'
},
sql: 'insert into "assets" ("ownerId", "libraryId", "checksum", "originalPath", "deviceAssetId", "deviceId", "fileCreatedAt", "fileModifiedAt", "localDateTime", "type", "isFavorite", "isArchived", "duration", "isVisible", "originalFileName") values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15) returning *',
params: [
'a23077bf-8dbe-409c-b121-1c87f44eb345',
null,
,
'upload/upload/a23077bf-8dbe-409c-b121-1c87f44eb345/69/00/69009f22-5764-4fa1-8032-3084e44a8892.MOV',
'8B353F72-FBEB-4FDC-9555-51D869918859/L0/001',
'ff27386f79b915954d6989b4515a050ccdc4e5645d701ce78318ed07985e1bc3',
2025-02-24T13:10:41.000Z,
2025-02-24T13:29:11.000Z,
2025-02-24T13:10:41.000Z,
'VIDEO',
false,
false,
'0:00:04.000000',
true,
'IMG_0113.MOV'
]

This indicates a problem inserting a new asset into the assets table in your PostgreSQL database. Specifically:

duplicate key value violates unique constraint "UQ_assets_owner_checksum": This means you are trying to insert a row with an ownerId and checksum combination that already exists in the table. The constraint UQ_assets_owner_checksum is there to prevent exactly this.

ownerId: 'a23077bf-8dbe-409c-b121-1c87f44eb345': This is the ID of the user who owns the asset.

checksum: \xc40c612f050624d840428996fca60e257dbca79e: This is a hash (likely SHA1 or similar) of the file's content. It's used to detect duplicates even if the file has a different name or is in a different location.

originalPath: 'upload/upload/a23077bf-8dbe-409c-b121-1c87f44eb345/69/00/69009f22-5764-4fa1-8032-3084e44a8892.MOV': This is the path of the file in your Immich storage.

originalFileName: 'IMG_0113.MOV': This is the file name.

deviceAssetId: '8B353F72-FBEB-4FDC-9555-51D869918859/L0/001': This is the ID of the asset on the device that uploaded it.

deviceId: 'ff27386f79b915954d6989b4515a050ccdc4e5645d701ce78318ed07985e1bc3': This is the ID of the device uploading it.

Possible Causes and Solutions:

Duplicate File Upload: The most likely cause is that you're trying to upload the exact same file (IMG_0113.MOV) for the same user (a23077bf-8dbe-409c-b121-1c87f44eb345) more than once. Immich uses the checksum to prevent duplicates, and it's working as intended.

Solution: Check if you have accidentally uploaded the same video file multiple times from the same device.

Checksum Collision (Highly Unlikely): It's theoretically possible, but extremely unlikely, that two different files have the same checksum. The chances of this happening with a good hashing algorithm (like SHA256, which Immich might be using) are astronomically small. If you suspect this, you'd need to investigate the files themselves.

Database Issue (Less Likely):

Data Corruption: Less likely, but possible, there could be some data corruption in your PostgreSQL database that is causing the uniqueness constraint to fail incorrectly.

Incorrect Checksum Calculation: There could be a bug in Immich that is leading to the same checksum being generated for different files.

Solution: Inspect the assets table in your PostgreSQL database. Look for other assets owned by the user a23077bf-8dbe-409c-b121-1c87f44eb345 that have the same checksum \xc40c612f050624d840428996fca60e257dbca79e.

You can use a tool like psql or a GUI like pgAdmin to query your database.

Troubleshooting Steps:

Verify File Duplicates: Double-check your Immich library for the same file (IMG_0113.MOV) uploaded by the same user. Delete any duplicates. Also, ensure that the same file isn't in the "Recently Uploaded" or "Queue" section in the web UI.

Check Database for Duplicates: Connect to your PostgreSQL database and run the following query:

SELECT * FROM assets WHERE "ownerId" = 'a23077bf-8dbe-409c-b121-1c87f44eb345' AND checksum = '\\xc40c612f050624d840428996fca60e257dbca79e';

If this query returns one or more rows, it confirms that an asset with the same ownerId and checksum already exists. You may need to investigate further why Immich is trying to upload it again.

Review Immich Configuration: Ensure your Immich instance is correctly configured and that there aren't any known issues related to duplicate detection in the Immich GitHub repository or discussions.

Examine the bulk-upload-check Response: You have a log line POST /api/assets/bulk-upload-check 200 3.09ms 195.67.5.230. It would be helpful to see the response to this request. The response should indicate whether Immich thinks the file already exists. If Immich incorrectly says the file doesn't exist, that points to a problem with the bulk-upload-check logic.

Consider Reporting a Bug: If you've ruled out the common causes (duplicate uploads, simple configuration issues) and suspect a bug in Immich's duplicate detection logic, consider reporting the issue on the Immich GitHub repository with as much detail as possible, including the logs, database query results, and steps to reproduce the problem.

The database error is preventing the asset from being added. Address the underlying cause to resolve the issue.

Feb 28 '25 09:02 Morteza-Rastgoo

@Morteza-Rastgoo your error is unrelated to this issue.

Feb 28 '25 14:02 bo0tzz

I don't understand why getting the same error could be unrelated but ok. error: PostgresError: duplicate key value violates unique constraint "UQ_assets_owner_checksum"

Feb 28 '25 22:02 Morteza-Rastgoo

It's not the same error. This issue is about constraint "UQ_newPath".

Mar 01 '25 00:03 bo0tzz

For completeness, I get the same error after upgrading to the otherwise-lovely 1.128.0 today :)

[Nest] 2  - 03/01/2025, 3:09:23 AM     LOG [Microservices:APIKeyService] Attempting to finish incomplete move: upload/thumbs/c1c02cff-eb19-4fbf-bf9b-9425864a84e9/c82767b7-a9e7-4f32-81bf-21ef9d3e1c84.jpeg => upload/thumbs/c1c02cff-eb19-4fbf-bf9b-9425864a84e9/c8/27/c82767b7-a9e7-4f32-81bf-21ef9d3e1c84-thumbnail.jpeg
[Nest] 2  - 03/01/2025, 3:09:23 AM    WARN [Microservices:APIKeyService] Unable to complete move. File does not exist at either location.
Query failed : {
  durationMs: 13.382319999858737,
  error: PostgresError: duplicate key value violates unique constraint "UQ_newPath"
      at ErrorResponse (/usr/src/app/node_modules/postgres/cjs/src/connection.js:788:26)
      at handle (/usr/src/app/node_modules/postgres/cjs/src/connection.js:474:6)
      at Socket.data (/usr/src/app/node_modules/postgres/cjs/src/connection.js:315:9)
      at Socket.emit (node:events:524:28)
      at addChunk (node:internal/streams/readable:561:12)
      at readableAddChunkPushByteMode (node:internal/streams/readable:512:3)
      at Readable.push (node:internal/streams/readable:392:5)
      at TCP.onStreamRead (node:internal/stream_base_commons:189:23) {
    severity_local: 'ERROR',
    severity: 'ERROR',
    code: '23505',
    detail: 'Key ("newPath")=(upload/thumbs/c1c02cff-eb19-4fbf-bf9b-9425864a84e9/c8/27/c82767b7-a9e7-4f32-81bf-21ef9d3e1c84-thumbnail.jpeg) already exists.',
    schema_name: 'public',
    table_name: 'move_history',
    constraint_name: 'UQ_newPath',
    file: 'nbtinsert.c',
    line: '664',
    routine: '_bt_check_unique'
  },
  sql: 'insert into "move_history" ("entityId", "pathType", "oldPath", "newPath") values ($1, $2, $3, $4) returning *',
  params: [
    'c82767b7-a9e7-4f32-81bf-21ef9d3e1c84',
    'thumbnail',
    'upload/thumbs/c1c02cff-eb19-4fbf-bf9b-9425864a84e9/c82767b7-a9e7-4f32-81bf-21ef9d3e1c84.webp',
    'upload/thumbs/c1c02cff-eb19-4fbf-bf9b-9425864a84e9/c8/27/c82767b7-a9e7-4f32-81bf-21ef9d3e1c84-thumbnail.jpeg'
  ]
}
[Nest] 2  - 03/01/2025, 3:09:23 AM   ERROR [Microservices:{"id":"c82767b7-a9e7-4f32-81bf-21ef9d3e1c84"}] Unable to run job handler (migration/migrate-asset): PostgresError: duplicate key value violates unique constraint "UQ_newPath"
PostgresError: duplicate key value violates unique constraint "UQ_newPath"
    at ErrorResponse (/usr/src/app/node_modules/postgres/cjs/src/connection.js:788:26)
    at handle (/usr/src/app/node_modules/postgres/cjs/src/connection.js:474:6)
    at Socket.data (/usr/src/app/node_modules/postgres/cjs/src/connection.js:315:9)
    at Socket.emit (node:events:524:28)
    at addChunk (node:internal/streams/readable:561:12)
    at readableAddChunkPushByteMode (node:internal/streams/readable:512:3)
    at Readable.push (node:internal/streams/readable:392:5)
    at TCP.onStreamRead (node:internal/stream_base_commons:189:23)

Mar 01 '25 03:03 Foritus

Yay! Thanks friends 💓

Mar 05 '25 19:03 Foritus

immich immich copied to clipboard

duplicate key value violates unique constraint "UQ_newPath"

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

immich
immich copied to clipboard