Skip to content

duplicate key value violates unique constraint "UQ_newPath" #16354

Closed
@Foritus

Description

@Foritus

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

  1. Go to /admin/jobs-status
  2. Start the "Migration" task
  3. 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

Metadata

Metadata

Assignees

Labels

No labels
No labels

Type

No type

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions