After Update, Memories Is No Longer Displayed -> Manually Run = PostgresError: Date Field Value Out Of Range

by ADMIN 109 views

After Update, Memories are no longer displayed -> Manually run = PostgresError: date field value out of range

Introduction

In this article, we will be discussing an issue that has been reported by a user after updating to version 1.127 of the Immich Server. The user has noticed that memories are no longer being displayed, and when they try to run the job manually, they receive a PostgresError: date field value out of range. We will go through the steps to reproduce the issue, the relevant log output, and the additional information provided by the user.

The Bug

The user has reported that since the update to version 1.127, memories are no longer being displayed. After some research, they realized that they can start the job manually, but this results in a Postgres error. The user has also tried running the cleanup beforehand, but this also resulted in the same error. To further troubleshoot the issue, the user deleted the entire stack and developed it again, but the Postgres problem still persists.

The OS that Immich Server is running on

The Immich Server is running on a Docker Container.

Version of Immich Server

The version of the Immich Server is v1.129.0.

Version of Immich Mobile App

The version of the Immich Mobile App is v1.129.0.

Platform with the issue

The issue is reported on the Server platform.

Docker-compose.yml content

The user has provided the Docker-compose.yml content, which is as follows:

services:

  immich-server:
    container_name: Immich-SERVER
    image: ghcr.io/immich-app/immich-server:${IMMICH_VERSION:-release}
    hostname: immich-server
    user: 1026:100
    security_opt:
      - no-new-privileges:true
    env_file:
      - stack.env
    volumes:
      - /volume2/docker/immich/upload:/usr/src/app/upload:rw
    restart: on-failure:5
    ports:
      - 2283:2283
    depends_on:
      immich-redis:
        condition: service_healthy
      immich-db:
        condition: service_started

  immich-machine-learning:
    image: ghcr.io/immich-app/immich-machine-learning:${IMMICH_VERSION:-release}
    container_name: Immich-LEARNING
    hostname: immich-machine-learning
    user: 1026:100
    security_opt:
      - no-new-privileges:true
    env_file:
      - stack.env
    volumes:
      - /volume2/docker/immich/upload:/usr/src/app/upload:rw
      - /volume2/docker/immich/cache:/cache:rw
      - /volume2/docker/immich/matplotlib:/matplotlib:rw
    environment:
      - MPLCONFIGDIR=/matplotlib
    restart: on-failure:5
    depends_on:
      immich-db:
        condition: service_started

  immich-redis:
    image: redis
    container_name: Immich-REDIS
    hostname: immich-redis
    security_opt:
      - no-new-privileges:true
    healthcheck:
      test: ["CMD-SHELL", "redis-cli ping || exit 1"]
    user: 1026:100
    environment:
      - TZ=Europe/Berlin
    volumes:
      - /volume2/docker/immich/redis:/data:rw
    restart: on-failure:5

  immich-db:
    image: tensorchord/pgvecto-rs:pg15-v0.2.0
    container_name: Immich-DB
    hostname: immich-db
    security_opt:
      - no-new-privileges:true
    healthcheck:
      test: ["CMD", "pg_isready", "-q", "-d", "immich", "-U", "immichuser"]
      interval: 10s
      timeout: 5s
      retries: 5
    user: 1026:100
    volumes:
      - /volume2/docker/immich/db:/var/lib/postgresql/data
    environment:
      - TZ=Europe/Berlin
      - POSTGRES_DB=immich
      - POSTGRES_USER=immichuser
      - POSTGRES_PASSWORD=immichpw
    restart: on-failure:5

.env content

The user has also provided the .env content, which is as follows:

NODE_ENV=production
TZ=Europe/Berlin
DB_HOSTNAME=immich-db
DB_USERNAME=immichuser
DB_PASSWORD=immichpw
DB_DATABASE_NAME=immich
REDIS_HOSTNAME=immich-redis
LOG_LEVEL=log
JWT_SECRET=****************************************************************************************************************
DISABLE_REVERSE_GEOCODING=false
REVERSE_GEOCODING_PRECISION=3
PUBLIC_LOGIN_PAGE_MESSAGE=
IMMICH_WEB_URL=http://immich-web:3000
IMMICH_SERVER_URL=http://immich-server:3001
IMMICH_MACHINE_LEARNING_URL=http://immich-machine-learning:3003

Reproduction steps

To reproduce the issue, the user has followed these steps:

  1. Login as Admin
  2. Jobs > Create Job > Memory Generation
  3. I also did the memory cleanup before

Relevant log output

The user has provided the relevant log output, which is as follows:

Query failed : {

  durationMs: 173.40682699999888,

  error: PostgresError: date field value out of range: 0-03-10

      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: '22008',

    file: 'date.c',

    line: '259',

    routine: 'make_date'

  },

  sql: `with "res" as (with "today" as (select make_date(year::int, $1::int, $2::int) as "date" from generate_series((select date_part('year', min(("localDateTime" at time zone 'UTC')::date))::int from assets), date_part('year', current_date)::int - 1) as "year") select "a".*, to_json("exif") as "exifInfo" from "today" inner join lateral (select "assets".* from "assets" inner join "asset_job_status" on "assets"."id" = "asset_job_status"."assetId" where "asset_job_status"."previewAt" is not null and (assets."localDateTime" at time zone 'UTC')::date = today.date and "assets"."ownerId" = any($3::uuid[]) and "assets"."isVisible" = $4 and "assets"."isArchived" = $5 and exists (select from "asset_files" where "assetId" = "assets"."id" and "asset_files"."type" = $6) and "assets"."deletedAt" is null order by (assets."localDateTime" at time zone 'UTC')::date desc limit $7) as "a" on true inner join "exif" on "a"."id" = "exif"."assetId") select date_part('year', ("localDateTime" at time zone 'UTC')::date)::int as "year", json_agg("res") as "assets" from "res" group by ("localDateTime" at time zone 'UTC')::date order by ("localDateTime" at time zone 'UTC')::date desc limit $8`,

  params: [

    3,

    10,

    '{25a015ff-fb2a-4a40-9497-cd9d2cf1d3e3}',



Conclusion

In this article, we have discussed an issue that has been reported by a user after updating to version 1.127 of the Immich Server. The user has noticed that memories are no longer being displayed, and when they try to run the job manually, they receive a PostgresError: date field value out of range. We have gone through the steps to reproduce the issue, the relevant log output, and the additional information provided by the user. The issue is still under investigation, and we will provide an update as soon as more information becomes available.

Recommendations

Based on the information provided, we recommend that the user try the following:

  1. Check the PostgreSQL database for any errors or issues.
  2. Verify that the date field in the database is not being truncated or rounded.
  3. Check the SQL query being executed to ensure that it is correct and not causing any issues.
  4. Try running the job manually with a different date range to see if the issue persists.

Future Work

We will continue to investigate this issue and provide an update as soon as more information becomes available. In the meantime, we recommend that the user try the recommendations above to see if they can resolve the issue.
Q&A: After Update, Memories are no longer displayed -> Manually run = PostgresError: date field value out of range

Introduction

In our previous article, we discussed an issue that has been reported by a user after updating to version 1.127 of the Immich Server. The user has noticed that memories are no longer being displayed, and when they try to run the job manually, they receive a PostgresError: date field value out of range. In this article, we will provide a Q&A section to help answer some of the common questions related to this issue.

Q: What is the cause of the PostgresError: date field value out of range?

A: The cause of the PostgresError: date field value out of range is not immediately clear. However, based on the log output provided, it appears that the issue may be related to the date field in the database being truncated or rounded. We recommend that the user check the PostgreSQL database for any errors or issues.

Q: How can I troubleshoot the issue?

A: To troubleshoot the issue, we recommend that the user try the following:

  1. Check the PostgreSQL database for any errors or issues.
  2. Verify that the date field in the database is not being truncated or rounded.
  3. Check the SQL query being executed to ensure that it is correct and not causing any issues.
  4. Try running the job manually with a different date range to see if the issue persists.

Q: What are the possible solutions to the issue?

A: Based on the information provided, the possible solutions to the issue are:

  1. Check the PostgreSQL database for any errors or issues.
  2. Verify that the date field in the database is not being truncated or rounded.
  3. Check the SQL query being executed to ensure that it is correct and not causing any issues.
  4. Try running the job manually with a different date range to see if the issue persists.
  5. Update the Immich Server to the latest version.

Q: How can I prevent this issue from occurring in the future?

A: To prevent this issue from occurring in the future, we recommend that the user:

  1. Regularly check the PostgreSQL database for any errors or issues.
  2. Verify that the date field in the database is not being truncated or rounded.
  3. Check the SQL query being executed to ensure that it is correct and not causing any issues.
  4. Update the Immich Server to the latest version.

Q: What is the current status of the issue?

A: The issue is still under investigation, and we will provide an update as soon as more information becomes available.

Q: How can I get help with this issue?

A: If you are experiencing this issue, we recommend that you contact our support team for assistance. We will do our best to help you troubleshoot the issue and provide a solution.

Conclusion

In this article, we have provided a Q&A section to help answer some of the common questions related to the issue of memories not being displayed after updating to version 1.127 of the Immich Server. We hope that this information has been helpful in troubleshooting the issue and providing a solution. If you have any further questions or concerns, please do not hesitate to contact our support team.