duplicate key value violates unique constraint "UQ_newPath" #16354

Foritus · 2025-02-26T19:05:25Z

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

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>demo@demo.de</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

The text was updated successfully, but these errors were encountered:

Morteza-Rastgoo · 2025-02-26T19:26:16Z

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

alextran1502 · 2025-02-26T20:00:19Z

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

bo0tzz · 2025-02-28T14:17:50Z

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

Morteza-Rastgoo · 2025-02-28T22:49:57Z

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"

bo0tzz · 2025-03-01T00:59:07Z

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

Foritus · 2025-03-01T03:10:28Z

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)

Foritus · 2025-03-05T19:08:32Z

Yay! Thanks friends 💓

alextran1502 assigned zackpollard Feb 26, 2025

This comment has been minimized.

Sign in to view

zackpollard mentioned this issue Mar 5, 2025

fix: storage template failure after re-upload and previous fail #16611

Merged

zackpollard closed this as completed in #16611 Mar 5, 2025

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

duplicate key value violates unique constraint "UQ_newPath" #16354

duplicate key value violates unique constraint "UQ_newPath" #16354

Foritus commented Feb 26, 2025

Morteza-Rastgoo commented Feb 26, 2025

alextran1502 commented Feb 26, 2025

This comment has been minimized.

bo0tzz commented Feb 28, 2025

Morteza-Rastgoo commented Feb 28, 2025

bo0tzz commented Mar 1, 2025

Foritus commented Mar 1, 2025

Foritus commented Mar 5, 2025

duplicate key value violates unique constraint "UQ_newPath" #16354

duplicate key value violates unique constraint "UQ_newPath" #16354

Comments

Foritus commented Feb 26, 2025

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

Morteza-Rastgoo commented Feb 26, 2025

alextran1502 commented Feb 26, 2025

This comment has been minimized.

bo0tzz commented Feb 28, 2025

Morteza-Rastgoo commented Feb 28, 2025

bo0tzz commented Mar 1, 2025

Foritus commented Mar 1, 2025

Foritus commented Mar 5, 2025