immich-app/immich
1
0
Fork 0
mirror of https://github.com/immich-app/immich.git synced 2026-02-05 00:30:57 +03:00
Code Issues 11 Packages Projects Releases 10 Wiki Activity

[BUG] invalid input syntax for type integer: "{"XYZ"}" where XYZ is an integer #1380

New Issue
Closed
opened 2026-02-05 01:33:49 +03:00 by OVERLORD · 0 comments
OVERLORD commented 2026-02-05 01:33:49 +03:00
Owner
Copy Link

Originally created by @davidecavestro on GitHub (Sep 26, 2023).

The bug

As per https://github.com/immich-app/immich/issues/4166#issuecomment-1736178337

I get a lot of errors while extracting metadata from XMP sidecar files, mostly regarding the iso field:

immich-database-1        | 2023-09-25 14:28:15.470 UTC [456] ERROR:  invalid input syntax for type integer: "{"500"}"
immich-database-1        | 2023-09-25 14:28:15.470 UTC [456] CONTEXT:  unnamed portal parameter $21 = '...'
immich-database-1        | 2023-09-25 14:28:15.470 UTC [456] STATEMENT:  INSERT INTO "exif"("assetId", "description", "exifImageWidth", "exifImageHeight", "fileSizeInByte", "orientation", "dateTimeOriginal", "modifyDate", "timeZone", "latitude", "longitude", "projectionType", "city", "livePhotoCID", "state", "country", "make", "model", "lensModel", "fNumber", "focalLength", "iso", "exposureTime", "fps", "exifTextSearchableColumn") VALUES ($1, DEFAULT, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, DEFAULT) ON CONFLICT ( "assetId" ) DO UPDATE SET "assetId" = EXCLUDED."assetId", "exifImageWidth" = EXCLUDED."exifImageWidth", "exifImageHeight" = EXCLUDED."exifImageHeight", "fileSizeInByte" = EXCLUDED."fileSizeInByte", "orientation" = EXCLUDED."orientation", "dateTimeOriginal" = EXCLUDED."dateTimeOriginal", "modifyDate" = EXCLUDED."modifyDate", "timeZone" = EXCLUDED."timeZone", "latitude" = EXCLUDED."latitude", "longitude" = EXCLUDED."longitude", "projectionType" = EXCLUDED."projectionType", "city" = EXCLUDED."city", "livePhotoCID" = EXCLUDED."livePhotoCID", "state" = EXCLUDED."state", "country" = EXCLUDED."country", "make" = EXCLUDED."make", "model" = EXCLUDED."model", "lensModel" = EXCLUDED."lensModel", "fNumber" = EXCLUDED."fNumber", "focalLength" = EXCLUDED."focalLength", "iso" = EXCLUDED."iso", "exposureTime" = EXCLUDED."exposureTime", "fps" = EXCLUDED."fps"  RETURNING "description"
immich_microservices     | [Nest] 7  - 09/25/2023, 2:28:15 PM   ERROR [JobService] Unable to run job handler (metadataExtraction/metadata-extraction): QueryFailedError: invalid input syntax for type integer: "{"500"}"
immich_microservices     | [Nest] 7  - 09/25/2023, 2:28:15 PM   ERROR [JobService] QueryFailedError: invalid input syntax for type integer: "{"500"}"
immich_microservices     |     at PostgresQueryRunner.query (/usr/src/app/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:211:19)
immich_microservices     |     at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
immich_microservices     |     at async InsertQueryBuilder.execute (/usr/src/app/node_modules/typeorm/query-builder/InsertQueryBuilder.js:106:33)
immich_microservices     |     at async AssetRepository.upsertExif (/usr/src/app/dist/infra/repositories/asset.repository.js:37:9)
immich_microservices     |     at async MetadataExtractionProcessor.handleMetadataExtraction (/usr/src/app/dist/microservices/processors/metadata-extraction.processor.js:154:9)
immich_microservices     |     at async /usr/src/app/dist/domain/job/job.service.js:107:37
immich_microservices     |     at async Worker.processJob (/usr/src/app/node_modules/bullmq/dist/cjs/classes/worker.js:346:28)
immich_microservices     |     at async Worker.retryIfFailed (/usr/src/app/node_modules/bullmq/dist/cjs/classes/worker.js:531:24)
immich_microservices     | [Nest] 7  - 09/25/2023, 2:28:15 PM   ERROR [JobService] Object:
immich_microservices     | {
immich_microservices     |   "id": "de31b52b-8069-4a5d-a98a-d6bc6c4f12c4",
immich_microservices     |   "source": "upload"
immich_microservices     | }

where its value on XMP sidecar file is

  <exif:ISOSpeedRatings>
   <rdf:Seq>
    <rdf:li>500</rdf:li>
   </rdf:Seq>
  </exif:ISOSpeedRatings>

Since I've manually generated the XMP based on related contents from Takeout I am going to strip the ISO field, though I wonder whether this is related to having a single value on a list datatype.

The OS that Immich Server is running on

TOS 5 (from Terramaster NAS)

Version of Immich Server

v1.79.1

Version of Immich Mobile App

n.a.

Platform with the issue

  • Server
  • Web
  • Mobile

Your docker-compose.yml content

version: "3.8"

services:
  immich-server:
    container_name: immich_server
    image: ghcr.io/immich-app/immich-server:${IMMICH_VERSION:-release}
    command: [ "start.sh", "immich" ]
    volumes:
      - ${UPLOAD_LOCATION}:/usr/src/app/upload
      - /Volume1/Photos/data_gphotos/:/Volume1/Photos/data_gphotos/:ro
      - ./photos:/photos
    env_file:
      - .env
    depends_on:
      - redis
      - database
      - typesense
    restart: always
    deploy:
      resources:        
        limits:
          cpus: '1'
          memory: '1024M'

  immich-microservices:
    container_name: immich_microservices
    image: ghcr.io/immich-app/immich-server:${IMMICH_VERSION:-release}
    extends:
      file: hwaccel.yml
      service: hwaccel
    command: [ "start.sh", "microservices" ]
    volumes:
      - /Volume1/Photos/data_gphotos/:/Volume1/Photos/data_gphotos/:ro
      - ./photos:/photos
    env_file:
      - .env
    depends_on:
      - redis
      - database
      - typesense
    restart: always
    deploy:
      resources:        
        limits:
          cpus: '0.6'
          memory: '768M'

  immich-machine-learning:
    container_name: immich_machine_learning
    image: ghcr.io/immich-app/immich-machine-learning:${IMMICH_VERSION:-release}
    volumes:
      - model-cache:/cache
    env_file:
      - .env
    restart: always
    deploy:
      resources:
        limits:
          cpus: '1.2'
          memory: '1024M'

  immich-web:
    container_name: immich_web
    image: ghcr.io/immich-app/immich-web:${IMMICH_VERSION:-release}
    env_file:
      - .env
    restart: always

  typesense:
    container_name: immich_typesense
    image: typesense/typesense:0.24.1@sha256:9bcff2b829f12074426ca044b56160ca9d777a0c488303469143dd9f8259d4dd
    environment:
      - TYPESENSE_API_KEY=${TYPESENSE_API_KEY}
      - TYPESENSE_DATA_DIR=/data
      # remove this to get debug messages
      - GLOG_minloglevel=1
    volumes:
      - tsdata:/data
    env_file:
      - .env
    restart: always
    deploy:
      resources:
        limits:  
          cpus: '0.4'
          memory: '512M'

  redis:
    container_name: immich_redis
    image: redis:6.2-alpine@sha256:70a7a5b641117670beae0d80658430853896b5ef269ccf00d1827427e3263fa3
    restart: always


  immich-proxy:
    container_name: immich_proxy
    image: ghcr.io/immich-app/immich-proxy:${IMMICH_VERSION:-release}
    environment:
      # Make sure these values get passed through from the env file
      - IMMICH_SERVER_URL
      - IMMICH_WEB_URL
    ports:
      - 8090:8080
    depends_on:
      - immich-server
      - immich-web
    restart: always

  database:
    image: postgres:14-alpine
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: ***
      POSTGRES_DB: immich
    volumes:
      - postgres_data:/var/lib/postgresql/data
    restart: unless-stopped


volumes:
  postgres_data:
    driver: local
  model-cache:
  tsdata:
  immich_appdata:
    driver: local

Your .env content

UPLOAD_LOCATION=/Volume1/docker/immich/orig/upload
IMMICH_VERSION=release
TYPESENSE_API_KEY=***
IMMICH_WEB_URL=http://immich-web:3000
IMMICH_SERVER_URL=http://immich-server:3001
DB_HOSTNAME=database
DB_USERNAME=postgres
DB_PASSWORD=***
DB_DATABASE_NAME=immich
REDIS_HOSTNAME=immich_redis
IMMICH_MEDIA_LOCATION=/photos

Reproduction steps

1. Generate a XMP file having 
  <exif:ISOSpeedRatings>
   <rdf:Seq>
    <rdf:li>500</rdf:li>
   </rdf:Seq>
  </exif:ISOSpeedRatings>
  1. sync it


### Additional information

_No response_
Originally created by @davidecavestro on GitHub (Sep 26, 2023). ### The bug As per https://github.com/immich-app/immich/issues/4166#issuecomment-1736178337 I get a lot of errors while extracting metadata from XMP sidecar files, mostly regarding the _iso_ field: ``` immich-database-1 | 2023-09-25 14:28:15.470 UTC [456] ERROR: invalid input syntax for type integer: "{"500"}" immich-database-1 | 2023-09-25 14:28:15.470 UTC [456] CONTEXT: unnamed portal parameter $21 = '...' immich-database-1 | 2023-09-25 14:28:15.470 UTC [456] STATEMENT: INSERT INTO "exif"("assetId", "description", "exifImageWidth", "exifImageHeight", "fileSizeInByte", "orientation", "dateTimeOriginal", "modifyDate", "timeZone", "latitude", "longitude", "projectionType", "city", "livePhotoCID", "state", "country", "make", "model", "lensModel", "fNumber", "focalLength", "iso", "exposureTime", "fps", "exifTextSearchableColumn") VALUES ($1, DEFAULT, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, DEFAULT) ON CONFLICT ( "assetId" ) DO UPDATE SET "assetId" = EXCLUDED."assetId", "exifImageWidth" = EXCLUDED."exifImageWidth", "exifImageHeight" = EXCLUDED."exifImageHeight", "fileSizeInByte" = EXCLUDED."fileSizeInByte", "orientation" = EXCLUDED."orientation", "dateTimeOriginal" = EXCLUDED."dateTimeOriginal", "modifyDate" = EXCLUDED."modifyDate", "timeZone" = EXCLUDED."timeZone", "latitude" = EXCLUDED."latitude", "longitude" = EXCLUDED."longitude", "projectionType" = EXCLUDED."projectionType", "city" = EXCLUDED."city", "livePhotoCID" = EXCLUDED."livePhotoCID", "state" = EXCLUDED."state", "country" = EXCLUDED."country", "make" = EXCLUDED."make", "model" = EXCLUDED."model", "lensModel" = EXCLUDED."lensModel", "fNumber" = EXCLUDED."fNumber", "focalLength" = EXCLUDED."focalLength", "iso" = EXCLUDED."iso", "exposureTime" = EXCLUDED."exposureTime", "fps" = EXCLUDED."fps" RETURNING "description" immich_microservices | [Nest] 7 - 09/25/2023, 2:28:15 PM ERROR [JobService] Unable to run job handler (metadataExtraction/metadata-extraction): QueryFailedError: invalid input syntax for type integer: "{"500"}" immich_microservices | [Nest] 7 - 09/25/2023, 2:28:15 PM ERROR [JobService] QueryFailedError: invalid input syntax for type integer: "{"500"}" immich_microservices | at PostgresQueryRunner.query (/usr/src/app/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:211:19) immich_microservices | at process.processTicksAndRejections (node:internal/process/task_queues:95:5) immich_microservices | at async InsertQueryBuilder.execute (/usr/src/app/node_modules/typeorm/query-builder/InsertQueryBuilder.js:106:33) immich_microservices | at async AssetRepository.upsertExif (/usr/src/app/dist/infra/repositories/asset.repository.js:37:9) immich_microservices | at async MetadataExtractionProcessor.handleMetadataExtraction (/usr/src/app/dist/microservices/processors/metadata-extraction.processor.js:154:9) immich_microservices | at async /usr/src/app/dist/domain/job/job.service.js:107:37 immich_microservices | at async Worker.processJob (/usr/src/app/node_modules/bullmq/dist/cjs/classes/worker.js:346:28) immich_microservices | at async Worker.retryIfFailed (/usr/src/app/node_modules/bullmq/dist/cjs/classes/worker.js:531:24) immich_microservices | [Nest] 7 - 09/25/2023, 2:28:15 PM ERROR [JobService] Object: immich_microservices | { immich_microservices | "id": "de31b52b-8069-4a5d-a98a-d6bc6c4f12c4", immich_microservices | "source": "upload" immich_microservices | } ``` where its value on XMP sidecar file is ``` <exif:ISOSpeedRatings> <rdf:Seq> <rdf:li>500</rdf:li> </rdf:Seq> </exif:ISOSpeedRatings> ``` Since I've manually generated the XMP based on related contents from Takeout I am going to strip the ISO field, though I wonder whether this is related to having a single value on a list datatype. ### The OS that Immich Server is running on TOS 5 (from Terramaster NAS) ### Version of Immich Server v1.79.1 ### Version of Immich Mobile App n.a. ### Platform with the issue - [X] Server - [ ] Web - [ ] Mobile ### Your docker-compose.yml content ```YAML version: "3.8" services: immich-server: container_name: immich_server image: ghcr.io/immich-app/immich-server:${IMMICH_VERSION:-release} command: [ "start.sh", "immich" ] volumes: - ${UPLOAD_LOCATION}:/usr/src/app/upload - /Volume1/Photos/data_gphotos/:/Volume1/Photos/data_gphotos/:ro - ./photos:/photos env_file: - .env depends_on: - redis - database - typesense restart: always deploy: resources: limits: cpus: '1' memory: '1024M' immich-microservices: container_name: immich_microservices image: ghcr.io/immich-app/immich-server:${IMMICH_VERSION:-release} extends: file: hwaccel.yml service: hwaccel command: [ "start.sh", "microservices" ] volumes: - /Volume1/Photos/data_gphotos/:/Volume1/Photos/data_gphotos/:ro - ./photos:/photos env_file: - .env depends_on: - redis - database - typesense restart: always deploy: resources: limits: cpus: '0.6' memory: '768M' immich-machine-learning: container_name: immich_machine_learning image: ghcr.io/immich-app/immich-machine-learning:${IMMICH_VERSION:-release} volumes: - model-cache:/cache env_file: - .env restart: always deploy: resources: limits: cpus: '1.2' memory: '1024M' immich-web: container_name: immich_web image: ghcr.io/immich-app/immich-web:${IMMICH_VERSION:-release} env_file: - .env restart: always typesense: container_name: immich_typesense image: typesense/typesense:0.24.1@sha256:9bcff2b829f12074426ca044b56160ca9d777a0c488303469143dd9f8259d4dd environment: - TYPESENSE_API_KEY=${TYPESENSE_API_KEY} - TYPESENSE_DATA_DIR=/data # remove this to get debug messages - GLOG_minloglevel=1 volumes: - tsdata:/data env_file: - .env restart: always deploy: resources: limits: cpus: '0.4' memory: '512M' redis: container_name: immich_redis image: redis:6.2-alpine@sha256:70a7a5b641117670beae0d80658430853896b5ef269ccf00d1827427e3263fa3 restart: always immich-proxy: container_name: immich_proxy image: ghcr.io/immich-app/immich-proxy:${IMMICH_VERSION:-release} environment: # Make sure these values get passed through from the env file - IMMICH_SERVER_URL - IMMICH_WEB_URL ports: - 8090:8080 depends_on: - immich-server - immich-web restart: always database: image: postgres:14-alpine environment: POSTGRES_USER: postgres POSTGRES_PASSWORD: *** POSTGRES_DB: immich volumes: - postgres_data:/var/lib/postgresql/data restart: unless-stopped volumes: postgres_data: driver: local model-cache: tsdata: immich_appdata: driver: local ``` ### Your .env content ```Shell UPLOAD_LOCATION=/Volume1/docker/immich/orig/upload IMMICH_VERSION=release TYPESENSE_API_KEY=*** IMMICH_WEB_URL=http://immich-web:3000 IMMICH_SERVER_URL=http://immich-server:3001 DB_HOSTNAME=database DB_USERNAME=postgres DB_PASSWORD=*** DB_DATABASE_NAME=immich REDIS_HOSTNAME=immich_redis IMMICH_MEDIA_LOCATION=/photos ``` ### Reproduction steps ```bash 1. Generate a XMP file having <exif:ISOSpeedRatings> <rdf:Seq> <rdf:li>500</rdf:li> </rdf:Seq> </exif:ISOSpeedRatings> ``` 2. sync it ``` ### Additional information _No response_
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
accessibility
changelog:enhancement
changelog:security
changelog:translation
cli
date-time
documentation
external-library
format
good first issue
needs-answer
nice to have
sharing
tech-debt
📱mobile
🖥️web
🗄️server
🧠machine-learning
No Label
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
OVERLORD
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: immich-app/immich#1380
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?