duplicate key value violates unique constraint

[pyccl]

The bug

When updating metadata,，live photo all prompts QueryFailedError: duplicate key value violates unique constraint "UQ_assets_owner_library_checksum"，Other normal。

The OS that Immich Server is running on

Windows 11 Docker Desktop V4.34.3

Version of Immich Server

V1.118.1

Version of Immich Mobile App

V1.118.1

Platform with the issue

• [X] Server
• [X] Web
• [ ] Mobile

Your docker-compose.yml content

---

Your .env content

---

Reproduction steps

1. 2. 3. ...

Relevant log output

No response

Additional information

No response

[alextran1502]

Hello, did you take any actions that could potentially lead to this message i.e modify the database, or file system …etc?

[pyccl]

Hello, did you take any actions that could potentially lead to this message i.e modify the database, or file system …etc?

No, I haven't made any changes to the database. I just mapped the 5432 port of the database to outside Docker and connected it with database software to check. This issue existed several versions ago. At first, I didn't store live photos, but later I started storing a few. Sometimes I modify exif and add GPS information for photos. Then it will retrieve the metadata again. Then all live photos prompt this error.

[pyccl]

Hello, did you take any actions that could potentially lead to this message i.e modify the database, or file system …etc?

I use external libraries. I tried backing up the live photos first, then deleting them, and finally rescanning the external library. After that, I deleted the recycle bin. Restore the original backup and then perform the scan. At this point, there will be no errors when extracting metadata from the task. But after executing once and then executing a second time, an error will occur again.

[alextran1502]

Hello, how did you modify gps information of the file?

[pyccl]

Hello, how did you modify gps information of the file?

I use MagicEXIF software to modify all the old photos. The photos before 2017 may not have GPS information, especially those before 2015. After 2018, the basics will be there. I add gps information to add old photos. But live photo was taken in 2024 and has gps information. I haven't modified the live photo source file.

[alextran1502]

When modified the file, were they already uploaded to Immich and modified the files on disk?

[pyccl]

When modified the file, were they already uploaded to Immich and modified the files on disk?

I'm an external library, because uploading can't customize the folder, so I'm an external library. The library is the E:/my photo of my computer, and the old photos have always been in the computer. After using immich, we started to add gps, took time to add it, and after adding it, we got the metadata again.

[alextran1502]

Can you help with reproducible steps, so we can create the bug on our ends to fix the issue?

[pyccl]

Can you help with reproducible steps, so we can create the bug on our ends to fix the issue?

of course, what can i do?

[Micha10]

I have the same issue. I would like to help but it's hard to create all steps again to reproduce the error because I don't know what exactly triggers the issue. I haven't uploaded a file and just uses an external library with ~30 pathes.

I'm using an external library. All pictures have been scanned. Then I updated some meta data of a bunch of files and rescanned the library. In the job-screen I can see "5 active / 73235 waiting", The NAS (external library) is working hard, but the number of files do not change. Sometimes the number is 73230 and then suddenly again 73235. After hours the number do not decrease.

I stopped the task and restarted it. The job counts ~140000 images, is working on them and the number is decreasing until the error is there again at about ~73000 images left.

my log: [Nest] 7 - 10/28/2024, 3:56:00 PM ERROR [Microservices:MetadataService] Failed to extract live photo /mnt/nas/Bild10.jpg: QueryFailedError: duplicate key value violates unique constraint "UQ_assets_owner_library_checksum" QueryFailedError: duplicate key value violates unique constraint "UQ_assets_owner_library_checksum" 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.j 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:14 at async MetadataService.applyMotionPhotos (/usr/src/app/dist/services/metadata.service.js:349:31) at async MetadataService.handleMetadataExtraction (/usr/src/app/dist/services/metadata.service.js:183:9) at async /usr/src/app/dist/services/job.service.js:163:36 at async Worker.processJob (/usr/src/app/node_modules/bullmq/dist/cjs/classes/worker.js:394:28) [Nest] 7 - 10/28/2024, 3:56:03 PM ERROR [Microservices:MetadataService] Failed to extract live photo /mnt/nas/Bild21.jpg: QueryFailedError: duplicate key value violates unique constraint "UQ_assets_owner_library_checksum" QueryFailedError: duplicate key value violates unique constraint "UQ_assets_owner_library_checksum" 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.j 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:14 at async MetadataService.applyMotionPhotos (/usr/src/app/dist/services/metadata.service.js:349:31) at async MetadataService.handleMetadataExtraction (/usr/src/app/dist/services/metadata.service.js:183:9) at async /usr/src/app/dist/services/job.service.js:163:36 at async Worker.processJob (/usr/src/app/node_modules/bullmq/dist/cjs/classes/worker.js:394:28) [Nest] 7 - 10/28/2024, 3:56:12 PM ERROR [Microservices:MetadataService] Failed to extract live photo /mnt/nas/Bild57.jpg: QueryFailedError: duplicate key value violates unique constraint "UQ_assets_owner_library_checksum" QueryFailedError: duplicate key value violates unique constraint "UQ_assets_owner_library_checksum" 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.j 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:14 at async MetadataService.applyMotionPhotos (/usr/src/app/dist/services/metadata.service.js:349:31) at async MetadataService.handleMetadataExtraction (/usr/src/app/dist/services/metadata.service.js:183:9) at async /usr/src/app/dist/services/job.service.js:163:36 at async Worker.processJob (/usr/src/app/node_modules/bullmq/dist/cjs/classes/worker.js:394:28)

Edit: I don't know if this has something to do with that issue but in the log of every new "extract metadata"-job there are a lot of these lines:

[Nest] 7 - 10/28/2024, 6:14:54 PM WARN [Microservices:MetadataService] Asset 2aea93bd-3c9d-475c-aa42-9dd0f56d2423 has no time zone information [Nest] 7 - 10/28/2024, 6:14:55 PM WARN [Microservices:MetadataService] Asset e0c8545f-c53c-4c70-b032-9d49aa16d28a has no time zone information [Nest] 7 - 10/28/2024, 6:14:56 PM WARN [Microservices:MetadataService] Asset 8f9b3bda-df7f-4019-a98c-1c85d106bbfb has no valid date, falling back to asset.fileCreatedAt [Nest] 7 - 10/28/2024, 6:14:56 PM WARN [Microservices:MetadataService] Asset a74207a4-db7c-44aa-bd45-a961fdd37b5b has no valid date, falling back to asset.fileCreatedAt That job does not continue, too

[pyccl]

I have the same issue. I would like to help but it's hard to create all steps again to reproduce the error because I don't know what exactly triggers the issue. I haven't uploaded a file and just uses an external library with ~30 pathes.

I'm using an external library. All pictures have been scanned. Then I updated some meta data of a bunch of files and rescanned the library. In the job-screen I can see "5 active / 73235 waiting", The NAS (external library) is working hard, but the number of files do not change. Sometimes the number is 73230 and then suddenly again 73235. After hours the number do not decrease.

I stopped the task and restarted it. The job counts ~140000 images, is working on them and the number is decreasing until the error is there again at about ~73000 images left.

my log: [Nest] 7 - 10/28/2024, 3:56:00 PM ERROR [Microservices:MetadataService] Failed to extract live photo /mnt/nas/Bild10.jpg: QueryFailedError: duplicate key value violates unique constraint "UQ_assets_owner_library_checksum" QueryFailedError: duplicate key value violates unique constraint "UQ_assets_owner_library_checksum" 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.j 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:14 at async MetadataService.applyMotionPhotos (/usr/src/app/dist/services/metadata.service.js:349:31) at async MetadataService.handleMetadataExtraction (/usr/src/app/dist/services/metadata.service.js:183:9) at async /usr/src/app/dist/services/job.service.js:163:36 at async Worker.processJob (/usr/src/app/node_modules/bullmq/dist/cjs/classes/worker.js:394:28) [Nest] 7 - 10/28/2024, 3:56:03 PM ERROR [Microservices:MetadataService] Failed to extract live photo /mnt/nas/Bild21.jpg: QueryFailedError: duplicate key value violates unique constraint "UQ_assets_owner_library_checksum" QueryFailedError: duplicate key value violates unique constraint "UQ_assets_owner_library_checksum" 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.j 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:14 at async MetadataService.applyMotionPhotos (/usr/src/app/dist/services/metadata.service.js:349:31) at async MetadataService.handleMetadataExtraction (/usr/src/app/dist/services/metadata.service.js:183:9) at async /usr/src/app/dist/services/job.service.js:163:36 at async Worker.processJob (/usr/src/app/node_modules/bullmq/dist/cjs/classes/worker.js:394:28) [Nest] 7 - 10/28/2024, 3:56:12 PM ERROR [Microservices:MetadataService] Failed to extract live photo /mnt/nas/Bild57.jpg: QueryFailedError: duplicate key value violates unique constraint "UQ_assets_owner_library_checksum" QueryFailedError: duplicate key value violates unique constraint "UQ_assets_owner_library_checksum" 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.j 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:14 at async MetadataService.applyMotionPhotos (/usr/src/app/dist/services/metadata.service.js:349:31) at async MetadataService.handleMetadataExtraction (/usr/src/app/dist/services/metadata.service.js:183:9) at async /usr/src/app/dist/services/job.service.js:163:36 at async Worker.processJob (/usr/src/app/node_modules/bullmq/dist/cjs/classes/worker.js:394:28)

Edit: I don't know if this has something to do with that issue but in the log of every new "extract metadata"-job there are a lot of these lines:

[Nest] 7 - 10/28/2024, 6:14:54 PM WARN [Microservices:MetadataService] Asset 2aea93bd-3c9d-475c-aa42-9dd0f56d2423 has no time zone information [Nest] 7 - 10/28/2024, 6:14:55 PM WARN [Microservices:MetadataService] Asset e0c8545f-c53c-4c70-b032-9d49aa16d28a has no time zone information [Nest] 7 - 10/28/2024, 6:14:56 PM WARN [Microservices:MetadataService] Asset 8f9b3bda-df7f-4019-a98c-1c85d106bbfb has no valid date, falling back to asset.fileCreatedAt [Nest] 7 - 10/28/2024, 6:14:56 PM WARN [Microservices:MetadataService] Asset a74207a4-db7c-44aa-bd45-a961fdd37b5b has no valid date, falling back to asset.fileCreatedAt That job does not continue, too

I can solve this problem at the bottom:

1, Asset e0c8545f-c53c-4c70-b032-9d49aa16d28a has no time zone information:this means that this photo has the shooting date in exif data, but there is no time zone information (solution: add the utc time and date of gps to solve it. )

2, Asset a74207a4-db7c-44aa-bd45-a961fdd37b5b has no valid date, falling back to asset.fileCreatedAt:this means that there is no shooting time in the exif of the photo, and the date and time of file creation of the photo will be adopted. (Solution: Add the shooting time in exif, and add the utc time and date of gps to solve it.)