Error after v.112.1 upgrade - Could not retrieve CLIP dimension size

alceasan commented 4 weeks ago

The bug

After upgrade to v.112.1 the server is restarting, I can't access.

The OS that Immich Server is running on

Debian

Version of Immich Server

v.112.1

Version of Immich Mobile App

v1.112.1

Platform with the issue

[X] 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:release
    volumes:
      - /mnt/immich:/usr/src/app/upload
      - /etc/localtime:/etc/localtime:ro
    environment:
      DB_HOSTNAME: ${DB_HOSTNAME}
      DB_PASSWORD: ${DB_PASSWORD}
      DB_USERNAME: ${DB_USERNAME}
      DB_DATABASE_NAME: ${DB_DATABASE_NAME}
      REDIS_HOSTNAME: ${REDIS_HOSTNAME}
    ports:
      - 2283:3001
    restart: unless-stopped
    network_mode: bridge

  immich-machine-learning:
    container_name: immich_machine_learning
    image: ghcr.io/immich-app/immich-machine-learning:release
    environment:
      MACHINE_LEARNING_REQUEST_THREADS: 4
    ports:
      - 3003:3003
    volumes:
      - /data/immich/model-cache:/cache
    restart: unless-stopped
    network_mode: bridge

Your .env content

N/A

Reproduction steps

1.Upgrade to v.112.1

Relevant log output

microservices worker exited with code 1
Error: Could not retrieve CLIP dimension size
    at SearchRepository.getDimensionSize (/usr/src/app/dist/repositories/search.repository.js:216:19)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async /usr/src/app/dist/repositories/database.repository.js:186:23
    at async /usr/src/app/dist/services/smart-info.service.js:70:31

Additional information

I already tried to update pgvecto.rs to 0.3.0, but the same error prevails. My current CLIP model is XLM-Roberta-Large-Vit-B-16Plus

alextran1502 commented 4 weeks ago

cc @mertalev

blackhack commented 4 weeks ago

Can confirm, I just upgrade from v1.111.0 to v1.112.1, and immich_server get stuck in this bootloop.

Nothing else was done, just a sudo docker compose pull

Edit: Just update my docker-compose.yml to the current version just in case, but the only difference was the redis version, and the problem is still here.

This is the immich_server log:

Detected CPU Cores: 4
Starting api worker
Starting microservices worker
[Nest] 7  - 08/15/2024, 9:14:01 PM     LOG [Microservices:EventRepository] Initialized websocket server
[Nest] 7  - 08/15/2024, 9:14:01 PM     LOG [Microservices:SystemConfigService] LogLevel=log (set via system config)
[Nest] 7  - 08/15/2024, 9:14:01 PM     LOG [Microservices:MapRepository] Initializing metadata repository
[Nest] 7  - 08/15/2024, 9:14:01 PM     LOG [Microservices:MetadataService] Initialized local reverse geocoder
[Nest] 7  - 08/15/2024, 9:14:01 PM     LOG [Microservices:ServerService] Feature Flags: {
  "smartSearch": true,
  "facialRecognition": true,
  "duplicateDetection": true,
  "map": true,
  "reverseGeocoding": true,
  "sidecar": true,
  "search": true,
  "trash": true,
  "oauth": false,
  "oauthAutoLaunch": false,
  "passwordLogin": true,
  "configFile": false,
  "email": false
}
[Nest] 17  - 08/15/2024, 9:14:01 PM     LOG [Api:EventRepository] Initialized websocket server
[Nest] 17  - 08/15/2024, 9:14:01 PM     LOG [Api:SystemConfigService] LogLevel=log (set via system config)
[Nest] 17  - 08/15/2024, 9:14:01 PM     LOG [Api:ServerService] Feature Flags: {
  "smartSearch": true,
  "facialRecognition": true,
  "duplicateDetection": true,
  "map": true,
  "reverseGeocoding": true,
  "sidecar": true,
  "search": true,
  "trash": true,
  "oauth": false,
  "oauthAutoLaunch": false,
  "passwordLogin": true,
  "configFile": false,
  "email": false
}
[Nest] 7  - 08/15/2024, 9:14:02 PM     LOG [Microservices:SmartInfoService] Dimension size of model XLM-Roberta-Large-Vit-B-16Plus is 640, but database expects 512.
[Nest] 7  - 08/15/2024, 9:14:02 PM     LOG [Microservices:SmartInfoService] Updating database CLIP dimension size to 640.
QueryFailedError: relation "clip_index" does not exist
    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 DataSource.query (/usr/src/app/node_modules/typeorm/data-source/DataSource.js:350:20)
    at async /usr/src/app/dist/repositories/search.repository.js:227:13
    at async EntityManager.transaction (/usr/src/app/node_modules/typeorm/entity-manager/EntityManager.js:73:28)
    at async /usr/src/app/dist/services/smart-info.service.js:85:17
    at async /usr/src/app/dist/repositories/database.repository.js:186:23 {
  query: 'REINDEX INDEX clip_index',
  parameters: undefined,
  driverError: error: relation "clip_index" does not exist
      at /usr/src/app/node_modules/pg/lib/client.js:526:17
      at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
      at async PostgresQueryRunner.query (/usr/src/app/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:184:25)
      at async DataSource.query (/usr/src/app/node_modules/typeorm/data-source/DataSource.js:350:20)
      at async /usr/src/app/dist/repositories/search.repository.js:227:13
      at async EntityManager.transaction (/usr/src/app/node_modules/typeorm/entity-manager/EntityManager.js:73:28)
      at async /usr/src/app/dist/services/smart-info.service.js:85:17
      at async /usr/src/app/dist/repositories/database.repository.js:186:23 {
    length: 108,
    severity: 'ERROR',
    code: '42P01',
    detail: undefined,
    hint: undefined,
    position: undefined,
    internalPosition: undefined,
    internalQuery: undefined,
    where: undefined,
    schema: undefined,
    table: undefined,
    column: undefined,
    dataType: undefined,
    constraint: undefined,
    file: 'namespace.c',
    line: '433',
    routine: 'RangeVarGetRelidExtended'
  },
  length: 108,
  severity: 'ERROR',
  code: '42P01',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'namespace.c',
  line: '433',
  routine: 'RangeVarGetRelidExtended'
}
microservices worker exited with code 1

alextran1502 commented 4 weeks ago

@blackhack if you roll back to v1.111.0 does it work?

blackhack commented 4 weeks ago

@blackhack if you roll back to v1.111.0 does it work?

Yes, it works fine, nothing wrong in the logs and the smart search works.

alextran1502 commented 4 weeks ago

@blackhack can you post your docker compose and env file?

blackhack commented 4 weeks ago

@blackhack can you post your docker compose and env file?

No problem.

[jdavid@server immich-app]$ cat .env
# You can find documentation for all the supported env variables at https://immich.app/docs/install/environment-variables

# The location where your uploaded files are stored
UPLOAD_LOCATION=/media/Backup/immich_photos
# The location where your database files are stored
DB_DATA_LOCATION=./postgres

# To set a timezone, uncomment the next line and change Etc/UTC to a TZ identifier from this list: https://en.wikipedia.org/wiki/List_of_tz_database_time_zones#List
# TZ=America/Bogota

# The Immich version to use. You can pin this to a specific version like "v1.71.0"
IMMICH_VERSION=v1.111.0

# Connection secret for postgres. You should change it to a random password
DB_PASSWORD=**************

# Using the same naming convention in the example docker-compose.yml. Change this if you are using your own Redis instance
REDIS_HOSTNAME=immich_redis

# The values below this line do not need to be changed
###################################################################################
DB_USERNAME=postgres
DB_DATABASE_NAME=immich

[jdavid@server immich-app]$ cat docker-compose.yml
#
# WARNING: Make sure to use the docker-compose.yml of the current release:
#
# https://github.com/immich-app/immich/releases/latest/download/docker-compose.yml
#
# The compose file on main may not be compatible with the latest release.
#

name: immich

services:
  immich-server:
    container_name: immich_server
    image: ghcr.io/immich-app/immich-server:${IMMICH_VERSION:-release}
    # extends:
    #   file: hwaccel.transcoding.yml
    #   service: cpu # set to one of [nvenc, quicksync, rkmpp, vaapi, vaapi-wsl] for accelerated transcoding
    volumes:
      # Do not edit the next line. If you want to change the media storage location on your system, edit the value of UPLOAD_LOCATION in the .env file
      - ${UPLOAD_LOCATION}:/usr/src/app/upload
      - /etc/localtime:/etc/localtime:ro
    env_file:
      - .env
    ports:
      - 2283:3001
    depends_on:
      - redis
      - database
    restart: always
    healthcheck:
      disable: false

  immich-machine-learning:
    container_name: immich_machine_learning
    # For hardware acceleration, add one of -[armnn, cuda, openvino] to the image tag.
    # Example tag: ${IMMICH_VERSION:-release}-cuda
    image: ghcr.io/immich-app/immich-machine-learning:${IMMICH_VERSION:-release}
    # extends: # uncomment this section for hardware acceleration - see https://immich.app/docs/features/ml-hardware-acceleration
    #   file: hwaccel.ml.yml
    #   service: cpu # set to one of [armnn, cuda, openvino, openvino-wsl] for accelerated inference - use the `-wsl` version for WSL2 where applicable
    volumes:
      - model-cache:/cache
    env_file:
      - .env
    restart: always
    healthcheck:
      disable: false

  redis:
    container_name: immich_redis
    image: docker.io/redis:6.2-alpine@sha256:e3b17ba9479deec4b7d1eeec1548a253acc5374d68d3b27937fcfe4df8d18c7e
    healthcheck:
      test: redis-cli ping || exit 1
    restart: always

  database:
    container_name: immich_postgres
    image: docker.io/tensorchord/pgvecto-rs:pg14-v0.2.0@sha256:90724186f0a3517cf6914295b5ab410db9ce23190a2d9d0b9dd6463e3fa298f0
    environment:
      POSTGRES_PASSWORD: ${DB_PASSWORD}
      POSTGRES_USER: ${DB_USERNAME}
      POSTGRES_DB: ${DB_DATABASE_NAME}
      POSTGRES_INITDB_ARGS: '--data-checksums'
    volumes:
      # Do not edit the next line. If you want to change the database storage location on your system, edit the value of DB_DATA_LOCATION in the .env file
      - ${DB_DATA_LOCATION}:/var/lib/postgresql/data
    healthcheck:
      test: pg_isready --dbname='${DB_DATABASE_NAME}' --username='${DB_USERNAME}' || exit 1; Chksum="$$(psql --dbname='${DB_DATABASE_NAME}' --username='${DB_USERNAME}' --tuples-only --no-align --command='SELECT COALESCE(SUM(checksum_failures), 0) FROM pg_stat_database')"; echo "checksum failure count is $$Chksum"; [ "$$Chksum" = '0' ] || exit 1
      interval: 5m
      start_interval: 30s
      start_period: 5m
    command: ["postgres", "-c", "shared_preload_libraries=vectors.so", "-c", 'search_path="$$user", public, vectors', "-c", "logging_collector=on", "-c", "max_wal_size=2GB", "-c", "shared_buffers=512MB", "-c", "wal_compression=on"]
    restart: always

volumes:
  model-cache:

SuberSeb commented 3 weeks ago

I have the same issue. immich-server container keeps restarting.

Error: Could not retrieve CLIP dimension size at SearchRepository.getDimensionSize (/usr/src/app/dist/repositories/search.repository.js:216:19) at process.processTicksAndRejections (node:internal/process/task_queues:95:5) at async /usr/src/app/dist/services/smart-info.service.js:70:31 at async /usr/src/app/dist/repositories/database.repository.js:186:23

My setup the same as here https://github.com/immich-app/immich/issues/10892

alextran1502 commented 3 weeks ago

@mertalev do you have any input on how to resolve these issues?

mertalev commented 3 weeks ago

It most likely means the embedding column is missing from the table. You can fix it with a few SQL commands:

SET vectors.pgvector_compatibility=on;
ALTER TABLE smart_search ADD COLUMN embedding vector(512);
CREATE INDEX clip_index ON smart_search
  USING hnsw (embedding vector_cosine_ops)
  WITH (ef_construction = 300, m = 16);

If you get an error like The extension is upgraded so all index files are outdated., run SELECT pgvectors_upgrade();, restart and try again.

rekh127 commented 3 weeks ago

Same issue! Will see about manually adding this column when I get a chance.

rekh127 commented 3 weeks ago

Column embedding does exist. Adding clip_index I get

ERROR:  pgvecto.rs: The given vector is invalid for input.
ADVICE: Check if dimensions and scalar type of the vector is matched with the index.

(edited, to current issue after solving a silly one)

here is the smart search table as it exists.

                                         Table "public.smart_search"
  Column   |    Type     | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
-----------+-------------+-----------+----------+---------+----------+-------------+--------------+-------------
 assetId   | uuid        |           | not null |         | plain    |             |              | 
 embedding | vector(512) |           | not null |         | external |             |              | 
Indexes:
    "smart_search_pkey" PRIMARY KEY, btree ("assetId")
Foreign-key constraints:
    "smart_search_assetId_fkey" FOREIGN KEY ("assetId") REFERENCES assets(id) ON DELETE CASCADE
Access method: heap

and the logs, though they look the same as the above to my quick read

immich_server            | QueryFailedError: relation "clip_index" does not exist
immich_server            |     at PostgresQueryRunner.query (/usr/src/app/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:219:19)
immich_server            |     at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
immich_server            |     at async DataSource.query (/usr/src/app/node_modules/typeorm/data-source/DataSource.js:350:20)
immich_server            |     at async /usr/src/app/dist/repositories/search.repository.js:227:13
immich_server            |     at async EntityManager.transaction (/usr/src/app/node_modules/typeorm/entity-manager/EntityManager.js:73:28)
immich_server            |     at async /usr/src/app/dist/services/smart-info.service.js:85:17
immich_server            |     at async /usr/src/app/dist/repositories/database.repository.js:186:23 {
immich_server            |   query: 'REINDEX INDEX clip_index',
immich_server            |   parameters: undefined,
immich_server            |   driverError: error: relation "clip_index" does not exist
immich_server            |       at /usr/src/app/node_modules/pg/lib/client.js:526:17
immich_server            |       at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
immich_server            |       at async PostgresQueryRunner.query (/usr/src/app/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:184:25)
immich_server            |       at async DataSource.query (/usr/src/app/node_modules/typeorm/data-source/DataSource.js:350:20)
immich_server            |       at async /usr/src/app/dist/repositories/search.repository.js:227:13
immich_server            |       at async EntityManager.transaction (/usr/src/app/node_modules/typeorm/entity-manager/EntityManager.js:73:28)
immich_server            |       at async /usr/src/app/dist/services/smart-info.service.js:85:17
immich_server            |       at async /usr/src/app/dist/repositories/database.repository.js:186:23 {
immich_server            |     length: 108,
immich_server            |     severity: 'ERROR',
immich_server            |     code: '42P01',
immich_server            |     detail: undefined,
immich_server            |     hint: undefined,
immich_server            |     position: undefined,
immich_server            |     internalPosition: undefined,
immich_server            |     internalQuery: undefined,
immich_server            |     where: undefined,
immich_server            |     schema: undefined,
immich_server            |     table: undefined,
immich_server            |     column: undefined,
immich_server            |     dataType: undefined,
immich_server            |     constraint: undefined,
immich_server            |     file: 'namespace.c',
immich_server            |     line: '433',
immich_server            |     routine: 'RangeVarGetRelidExtended'
immich_server            |   },
immich_server            |   length: 108,
immich_server            |   severity: 'ERROR',
immich_server            |   code: '42P01',
immich_server            |   detail: undefined,
immich_server            |   hint: undefined,
immich_server            |   position: undefined,
immich_server            |   internalPosition: undefined,
immich_server            |   internalQuery: undefined,
immich_server            |   where: undefined,
immich_server            |   schema: undefined,
immich_server            |   table: undefined,
immich_server            |   column: undefined,
immich_server            |   dataType: undefined,
immich_server            |   constraint: undefined,
immich_server            |   file: 'namespace.c',
immich_server            |   line: '433',
immich_server            |   routine: 'RangeVarGetRelidExtended'
immich_server            | }

blackhack commented 3 weeks ago

Just like @rekh127, i get the same result when applying the query.

[jdavid@server immich-app]$ sudo docker exec -it immich_postgres psql --dbname=immich --username=postgres
psql (14.10 (Debian 14.10-1.pgdg120+1))
Type "help" for help.

immich=# SET vectors.pgvector_compatibility=on;
ALTER TABLE smart_search ADD COLUMN embedding vector(512);
CREATE INDEX clip_index ON smart_search
  USING hnsw (embedding vector_cosine_ops)
  WITH (ef_construction = 300, m = 16);
SET
ERROR:  column "embedding" of relation "smart_search" already exists
ERROR:  pgvecto.rs: The given vector is invalid for input.
ADVICE: Check if dimensions and scalar type of the vector is matched with the index.
immich=#

mertalev commented 3 weeks ago

What model are you using? You might need to change the 512 value based on the model if it's not default. See here for the right size for each model. You can run ALTER TABLE smart_search ALTER COLUMN embedding SET DATA TYPE vector(<INSERT_SIZE_HERE>);. Then you should be able to run the CREATE INDEX command above (changing 512 as appropriate).

blackhack commented 3 weeks ago

I'm using XLM-Roberta-Large-Vit-B-16Plus so the size is 640

Running the queries I got an error because embedding already exist, but this time it actually did something on CREATE INDEX because it took some seconds to complete.

Good news, immich_server is now running the latest version without warnings in its logs!

The question is: where things went wrong in the first place? This is the first time I've touched the database directly so I don't think it was something I touched or fiddled with.

Thank you for the help!

immich=# ALTER TABLE smart_search ALTER COLUMN embedding SET DATA TYPE vector(640);
ALTER TABLE
immich=# SET vectors.pgvector_compatibility=on;
ALTER TABLE smart_search ADD COLUMN embedding vector(512);
CREATE INDEX clip_index ON smart_search
  USING hnsw (embedding vector_cosine_ops)
  WITH (ef_construction = 300, m = 16);
SET
ERROR:  column "embedding" of relation "smart_search" already exists
CREATE INDEX

rekh127 commented 3 weeks ago

Altering the table and inserting the index got me going again too.

From my logs (though I clipped a bit after I should have) and BlackHacks it looks like thats what immich is trying to update when it boots when this fails. Seems like something should have happened when we switched models initially that didn't? Though not sure why this index would be completely missing.

The line I mean from blackhacks:

[Nest] 7  - 08/15/2024, 9:14:02 PM     LOG [Microservices:SmartInfoService] Updating database CLIP dimension size to 640.

I had the same line item but for the size of the model I switched to.

mertalev commented 3 weeks ago

This release added some missing validation to make config changes more robust. It had a side effect of exposing all the cases where something was wrong 😅

SuberSeb commented 3 weeks ago

When running this:

SET vectors.pgvector_compatibility=on;
ALTER TABLE smart_search ADD COLUMN embedding vector(512);
CREATE INDEX clip_index ON smart_search
  USING hnsw (embedding vector_cosine_ops)
  WITH (ef_construction = 300, m = 16);

I got an error: column "embedding" of relation "smart_search" already exists When just creating an index: operator class "vector_cos_ops" does not accept data type real[]

mertalev commented 3 weeks ago

When running this:
SET vectors.pgvector_compatibility=on;

ALTER TABLE smart_search ADD COLUMN embedding vector(512);

CREATE INDEX clip_index ON smart_search

  USING hnsw (embedding vector_cosine_ops)

  WITH (ef_construction = 300, m = 16);
I got an error: column "embedding" of relation "smart_search" already exists

When just creating an index: operator class "vector_cos_ops" does not accept data type real[]

Run ALTER TABLE smart_search ALTER COLUMN embedding SET DATA TYPE vector(512); (changing 512 if using a non-default model with a different dimension size as mentioned above). Index creation should work after that.

SuberSeb commented 3 weeks ago

The full script:

GRANT USAGE ON SCHEMA vectors TO immichuser;
GRANT SELECT ON TABLE pg_vector_index_stat TO PUBLIC;

ALTER TABLE smart_search ALTER COLUMN embedding SET DATA TYPE vector(512);

SET vectors.pgvector_compatibility=on;

CREATE INDEX clip_index ON smart_search
  USING hnsw (embedding vector_cosine_ops)
  WITH (ef_construction = 300, m = 16);

After this immich was started. Also I had to grand permissions on vectors for immichuser.

rekh127 commented 3 weeks ago

This release added some missing validation to make config changes more robust. It had a side effect of exposing all the cases where something was wrong 😅

Makes sense.

I should note though that I changed the clip model on this release. Up til now it had been the default from when I first installed So the config change seems not robust enough yet.

mertalev commented 3 weeks ago

clip_index being completely missing isn't normal, so it wasn't accounted for. That's something that predates your change to a different model. But it would be nice if it created the index in this case instead of failing.

rekh127 commented 3 weeks ago

clip_index being completely missing isn't normal, so it wasn't accounted for. That's something that predates your change to a different model. But it would be nice if it created the index in this case instead of failing.

Seems based on the number of people running into this, who haven't done anything to mess with their databse it is normal - if not expected.

Or perhaps whats going on is an old clip index being remove but a new one not being created because something didn't properly update the embedded vector size?

mertalev commented 3 weeks ago

Seems based on the number of people running into this, who haven't done anything to mess with their databse it is normal - if not expected.

These kinds of issues typically come from instances that had issues switching to pgvecto.rs or going from 0.1.11 -> 0.2. If the admin ran SQL commands they found to fix it and Immich booted up successfully, the assumption might be that everything was right when the DB was actually in a weird state. This could be because there was something else that needed to be done as well, they ran other things before and the sequence of those commands led to a different outcome, or the command was simply wrong.

It could also possibly happen if something goes wrong when restoring from a backup.

Or perhaps whats going on is an old clip index being remove but a new one not being created because something didn't properly update the embedded vector size?

There's only one place where it tries to drop the index and it's part of a transaction where the next command is to create it again. If any step of the transaction fails, the database aborts the whole transaction like none of the commands happened.

blackhack commented 3 weeks ago

Now that you mention restoring from a backup, I always get this kind of error. Not sure if it's related though. I just restore it to test, and even though I got the error, the server is up and running fine in the latest version.

ERROR:  called `Result::unwrap()` on an `Err` value: Error { inner: Error { inner: TomlError { message: "invalid table header\nexpected newline, `#`", original: Some("[indexing.hnsw]\r\r\nm = 16\r\r\nef_construction = 300"), keys: [], span: Some(15..16) } } }

Full restore log

``` [root@server immich-app]# docker compose down -v # CAUTION! Deletes all Immich data to start from scratch. # rm -rf DB_DATA_LOCATION # CAUTION! Deletes all Immich data to start from scratch. docker compose pull # Update to latest version of Immich (if desired) docker compose create # Create Docker containers for Immich apps without running them. docker start immich_postgres # Start Postgres server sleep 10 # Wait for Postgres server to start up gunzip < "/media/Backup/immich_backup/dump.sql.gz" \ | sed "s/SELECT pg_catalog.set_config('search_path', '', false);/SELECT pg_catalog.set_config('search_path', 'public, pg_catalog', true);/g" \ | docker exec -i immich_postgres psql --username=postgres # Restore Backup docker compose up -d # Start remainder of Immich apps [+] Running 6/6 ✔ Container immich_machine_learning Removed 0.4s ✔ Container immich_server Removed 0.5s ✔ Container immich_redis Removed 0.4s ✔ Container immich_postgres Removed 0.4s ✔ Volume immich_model-cache Removed 0.4s ✔ Network immich_default Removed 0.4s [+] Pulling 4/4 ✔ immich-machine-learning Pulled 1.3s ✔ database Pulled 1.8s ✔ immich-server Pulled 1.4s ✔ redis Pulled 0.8s [+] Creating 6/5 ✔ Network immich_default Created 0.3s ✔ Volume "immich_model-cache" Created 0.0s ✔ Container immich_machine_learning Created 0.3s ✔ Container immich_redis Created 0.3s ✔ Container immich_postgres Created 0.3s ✔ Container immich_server Created 0.1s immich_postgres SET SET SET DROP DATABASE ERROR: current user cannot be dropped ERROR: role "postgres" already exists ALTER ROLE SET SET SET SET SET set_config -------------------- public, pg_catalog (1 row) SET SET SET SET UPDATE 1 DROP DATABASE CREATE DATABASE ALTER DATABASE You are now connected to database "template1" as user "postgres". SET SET SET SET SET set_config -------------------- public, pg_catalog (1 row) SET SET SET SET COMMENT ALTER DATABASE You are now connected to database "template1" as user "postgres". SET SET SET SET SET set_config -------------------- public, pg_catalog (1 row) SET SET SET SET REVOKE GRANT SET SET SET SET SET set_config -------------------- public, pg_catalog (1 row) SET SET SET SET CREATE DATABASE ALTER DATABASE You are now connected to database "immich" as user "postgres". SET SET SET SET SET set_config -------------------- public, pg_catalog (1 row) SET SET SET SET ALTER DATABASE You are now connected to database "immich" as user "postgres". SET SET SET SET SET set_config -------------------- public, pg_catalog (1 row) SET SET SET SET CREATE SCHEMA ALTER SCHEMA CREATE EXTENSION COMMENT CREATE EXTENSION COMMENT CREATE EXTENSION COMMENT CREATE EXTENSION COMMENT CREATE EXTENSION COMMENT CREATE EXTENSION COMMENT CREATE FUNCTION ALTER FUNCTION CREATE FUNCTION ALTER FUNCTION SET SET CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE COMMENT CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE SEQUENCE ALTER TABLE ALTER SEQUENCE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE SEQUENCE ALTER TABLE ALTER SEQUENCE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE SEQUENCE ALTER TABLE ALTER SEQUENCE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE SEQUENCE ALTER TABLE ALTER SEQUENCE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE COMMENT CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE COPY 0 COPY 23 COPY 2510 COPY 16 COPY 5 COPY 26613 COPY 44846 COPY 138 COPY 44846 COPY 3170 COPY 44846 COPY 26613 COPY 206067 COPY 0 COPY 0 COPY 0 COPY 157 COPY 0 COPY 4274 COPY 0 COPY 1668 COPY 13 COPY 3 COPY 4 COPY 0 COPY 44683 COPY 0 COPY 5 COPY 0 COPY 0 COPY 2 COPY 1 COPY 5 setval -------- 3173 (1 row) setval -------- 157 (1 row) setval -------- 42740 (1 row) setval -------- 1 (1 row) ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX ERROR: called `Result::unwrap()` on an `Err` value: Error { inner: Error { inner: TomlError { message: "invalid table header\nexpected newline, `#`", original: Some("[indexing.hnsw]\r\r\nm = 16\r\r\nef_construction = 300"), keys: [], span: Some(15..16) } } } CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE SET SET SET SET SET set_config -------------------- public, pg_catalog (1 row) SET SET SET SET DROP DATABASE CREATE DATABASE ALTER DATABASE You are now connected to database "postgres" as user "postgres". SET SET SET SET SET set_config -------------------- public, pg_catalog (1 row) SET SET SET SET COMMENT [+] Running 4/4 ✔ Container immich_postgres Running 0.0s ✔ Container immich_redis Started 0.5s ✔ Container immich_machine_learning Started 0.5s ✔ Container immich_server Started 0.4s ```

rekh127 commented 3 weeks ago

I'm not sure if this will be helpful for you or not.

But by bisecting my vm backups I can say my clip_index was present on 5/27 running v1.101.0 and was not present on 6/24 running v1.106.4. I wish I had some more granularity back here, but I pruned a lot of my backups in July.

I did not manually run any sql including backup restoration during this period. I did restore immich from backup to transfer to a new VM on 4/19, but thats the only direct execution of sql this database has seen until now. Testing that sql dump I see no errors on restore. I can share that sql dump, or a sql dump from any available VM backup if you want to check to see if there are any other odd database things that might provoke more bugs for people going forward.

Thanks for your help getting things running again.

mertalev commented 3 weeks ago

Now that you mention restoring from a backup, I always get this kind of error. Not sure if it's related though. I just restore it to test, and even though I got the error, the server is up and running fine in the latest version.
ERROR:  called `Result::unwrap()` on an `Err` value: Error { inner: Error { inner: TomlError { message: "invalid table header\nexpected newline, `#`", original: Some("[indexing.hnsw]\r\r\nm = 16\r\r\nef_construction = 300"), keys: [], span: Some(15..16) } } }
Full restore log
``` [root@server immich-app]# docker compose down -v # CAUTION! Deletes all Immich data to start from scratch. # rm -rf DB_DATA_LOCATION # CAUTION! Deletes all Immich data to start from scratch. docker compose pull # Update to latest version of Immich (if desired) docker compose create # Create Docker containers for Immich apps without running them. docker start immich_postgres # Start Postgres server sleep 10 # Wait for Postgres server to start up gunzip < "/media/Backup/immich_backup/dump.sql.gz" \ | sed "s/SELECT pg_catalog.set_config('search_path', '', false);/SELECT pg_catalog.set_config('search_path', 'public, pg_catalog', true);/g" \ | docker exec -i immich_postgres psql --username=postgres # Restore Backup docker compose up -d # Start remainder of Immich apps [+] Running 6/6 ✔ Container immich_machine_learning Removed 0.4s ✔ Container immich_server Removed 0.5s ✔ Container immich_redis Removed 0.4s ✔ Container immich_postgres Removed 0.4s ✔ Volume immich_model-cache Removed 0.4s ✔ Network immich_default Removed 0.4s [+] Pulling 4/4 ✔ immich-machine-learning Pulled 1.3s ✔ database Pulled 1.8s ✔ immich-server Pulled 1.4s ✔ redis Pulled 0.8s [+] Creating 6/5 ✔ Network immich_default Created 0.3s ✔ Volume "immich_model-cache" Created 0.0s ✔ Container immich_machine_learning Created 0.3s ✔ Container immich_redis Created 0.3s ✔ Container immich_postgres Created 0.3s ✔ Container immich_server Created 0.1s immich_postgres SET SET SET DROP DATABASE ERROR: current user cannot be dropped ERROR: role "postgres" already exists ALTER ROLE SET SET SET SET SET set_config -------------------- public, pg_catalog (1 row) SET SET SET SET UPDATE 1 DROP DATABASE CREATE DATABASE ALTER DATABASE You are now connected to database "template1" as user "postgres". SET SET SET SET SET set_config -------------------- public, pg_catalog (1 row) SET SET SET SET COMMENT ALTER DATABASE You are now connected to database "template1" as user "postgres". SET SET SET SET SET set_config -------------------- public, pg_catalog (1 row) SET SET SET SET REVOKE GRANT SET SET SET SET SET set_config -------------------- public, pg_catalog (1 row) SET SET SET SET CREATE DATABASE ALTER DATABASE You are now connected to database "immich" as user "postgres". SET SET SET SET SET set_config -------------------- public, pg_catalog (1 row) SET SET SET SET ALTER DATABASE You are now connected to database "immich" as user "postgres". SET SET SET SET SET set_config -------------------- public, pg_catalog (1 row) SET SET SET SET CREATE SCHEMA ALTER SCHEMA CREATE EXTENSION COMMENT CREATE EXTENSION COMMENT CREATE EXTENSION COMMENT CREATE EXTENSION COMMENT CREATE EXTENSION COMMENT CREATE EXTENSION COMMENT CREATE FUNCTION ALTER FUNCTION CREATE FUNCTION ALTER FUNCTION SET SET CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE COMMENT CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE SEQUENCE ALTER TABLE ALTER SEQUENCE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE SEQUENCE ALTER TABLE ALTER SEQUENCE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE SEQUENCE ALTER TABLE ALTER SEQUENCE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE SEQUENCE ALTER TABLE ALTER SEQUENCE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE COMMENT CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE CREATE TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE COPY 0 COPY 23 COPY 2510 COPY 16 COPY 5 COPY 26613 COPY 44846 COPY 138 COPY 44846 COPY 3170 COPY 44846 COPY 26613 COPY 206067 COPY 0 COPY 0 COPY 0 COPY 157 COPY 0 COPY 4274 COPY 0 COPY 1668 COPY 13 COPY 3 COPY 4 COPY 0 COPY 44683 COPY 0 COPY 5 COPY 0 COPY 0 COPY 2 COPY 1 COPY 5 setval -------- 3173 (1 row) setval -------- 157 (1 row) setval -------- 42740 (1 row) setval -------- 1 (1 row) ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX ERROR: called `Result::unwrap()` on an `Err` value: Error { inner: Error { inner: TomlError { message: "invalid table header\nexpected newline, `#`", original: Some("[indexing.hnsw]\r\r\nm = 16\r\r\nef_construction = 300"), keys: [], span: Some(15..16) } } } CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX CREATE INDEX ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE SET SET SET SET SET set_config -------------------- public, pg_catalog (1 row) SET SET SET SET DROP DATABASE CREATE DATABASE ALTER DATABASE You are now connected to database "postgres" as user "postgres". SET SET SET SET SET set_config -------------------- public, pg_catalog (1 row) SET SET SET SET COMMENT [+] Running 4/4 ✔ Container immich_postgres Running 0.0s ✔ Container immich_redis Started 0.5s ✔ Container immich_machine_learning Started 0.5s ✔ Container immich_server Started 0.4s ```

I think the issue there is the \r\r\n whitespace. This is just the right level of silliness for it to be the underlying issue.

mertalev commented 3 weeks ago

I'm not sure if this will be helpful for you or not.

But by bisecting my vm backups I can say my clip_index was present on 5/27 running v1.101.0 and was not present on 6/24 running v1.106.4. I wish I had some more granularity back here, but I pruned a lot of my backups in July.

I did not manually run any sql including backup restoration during this period. I did restore immich from backup to transfer to a new VM on 4/19, but thats the only direct execution of sql this database has seen until now. Testing that sql dump I see no errors on restore. I can share that sql dump, or a sql dump from any available VM backup if you want to check to see if there are any other odd database things that might provoke more bugs for people going forward.

Thanks for your help getting things running again.

Thanks, this is helpful. I can take a look at what changes were made between those dates if there's anything relevant.

rekh127 commented 3 weeks ago

No problem. I'm not always the fastest at updating immich but the version numbers should at least clarify there

On Mon, Aug 19, 2024, 11:22 AM Mert @.***> wrote:

I'm not sure if this will be helpful for you or not.

But by bisecting my vm backups I can say my clip_index was present on 5/27 running v1.101.0 and was not present on 6/24 running v1.106.4. I wish I had some more granularity back here, but I pruned a lot of my backups in July.

I did not manually run any sql including backup restoration during this period. I did restore immich from backup to transfer to a new VM on 4/19, but thats the only direct execution of sql this database has seen until now. Testing that sql dump I see no errors on restore. I can share that sql dump, or a sql dump from any available VM backup if you want to check to see if there are any other odd database things that might provoke more bugs for people going forward.

Thanks for your help getting things running again.

Thanks, this is helpful. I can take a look at what changes were made between those dates if there's anything relevant.

— Reply to this email directly, view it on GitHub https://github.com/immich-app/immich/issues/11801#issuecomment-2297169349, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAJB2JQLZMDZ5UGAQPTL3CTZSIZUPAVCNFSM6AAAAABMR2IVXGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDEOJXGE3DSMZUHE . You are receiving this because you were mentioned.Message ID: @.***>

rekh127 commented 3 weeks ago

Arg! I have led you astray. I was looking back at release notes and saw the warning about v1.102.0 's databse changes.

I did backup and restore to migrate my database for 102. I don't know why I didn't note that in my maintenance journal here.

That backup file does fail to recreate the index. And looks like the same issue as @blackhack

CREATE INDEX
ERROR:  called `Result::unwrap()` on an `Err` value: Error { inner: Error { inner: TomlError { message: "invalid table header\nexpected newline, `#`", original: Some("[indexing.hnsw]\r\r\nm = 16\r\r\nef_construction = 300"), keys: [], span: Some(15..16) } } }
CREATE INDEX
ERROR:  called `Result::unwrap()` on an `Err` value: Error { inner: Error { inner: TomlError { message: "invalid table header\nexpected newline, `#`", original: Some("[indexing.hnsw]\r\r\nm = 16\r\r\nef_construction = 300"), keys: [], span: Some(15..16) } } }
CREATE INDEX

mertalev commented 3 weeks ago

If you replace all instances of \r\r\n with \n, does it successfully create the index when restoring?

rekh127 commented 3 weeks ago

Yep, it does!

I don't love that this is a new line delimited string haha!

lakemike commented 2 weeks ago

Just to mention that I had the same problem. After changing to one of the new models with 768 as dimension, this error cropped up. The above database magic seems to have helped.. I'm now recalculating the data for the smart search. Will know more after restart of docker container. => Potentially this bug applies to everyone who changed the postgres image ("pgvecto..")?

bhugh commented 4 days ago

FYI I had the same issue as described above. The database updates from this message above fixed the problem (though I didn't need to use the two grant lines). It has now updated to 1.114.0 without any problems.

immich-app / immich