1.95.1 QueryFailedError: column "embedding" of relation "asset_faces" does not exist

[518512]

The bug

immich_microservices log

[Nest] 7  - 02/23/2024, 10:17:38 AM   ERROR [JobService] Unable to run job handler (faceDetection/face-detection): QueryFailedError: column "embedding" of relation "asset_faces" does not exist
[Nest] 7  - 02/23/2024, 10:17:38 AM   ERROR [JobService] QueryFailedError: column "embedding" of relation "asset_faces" 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 InsertQueryBuilder.execute (/usr/src/app/node_modules/typeorm/query-builder/InsertQueryBuilder.js:106:33)
    at async PersonRepository.createFaces (/usr/src/app/dist/infra/repositories/person.repository.js:186:21)
    at async PersonService.handleDetectFaces (/usr/src/app/dist/domain/person/person.service.js:263:29)
    at async /usr/src/app/dist/domain/job/job.service.js:137:37
    at async Worker.processJob (/usr/src/app/node_modules/bullmq/dist/cjs/classes/worker.js:394:28)
    at async Worker.retryIfFailed (/usr/src/app/node_modules/bullmq/dist/cjs/classes/worker.js:581:24)
[Nest] 7  - 02/23/2024, 10:17:38 AM   ERROR [JobService] Object:
{
  "id": "cbe7e7dc-f562-4cc0-99f7-d8e3da6a4c4c"
}

immich_server log

    at async SearchRepository.searchSmart (/usr/src/app/dist/infra/repositories/search.repository.js:65:9)] Failed to search
[Nest] 7  - 02/22/2024, 4:43:12 PM   ERROR [QueryFailedError: column search.embedding 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 SelectQueryBuilder.loadRawResults (/usr/src/app/node_modules/typeorm/query-builder/SelectQueryBuilder.js:2192:25)
    at async SelectQueryBuilder.executeEntitiesAndRawResults (/usr/src/app/node_modules/typeorm/query-builder/SelectQueryBuilder.js:2040:26)
    at async SelectQueryBuilder.getRawAndEntities (/usr/src/app/node_modules/typeorm/query-builder/SelectQueryBuilder.js:684:29)
    at async SelectQueryBuilder.getMany (/usr/src/app/node_modules/typeorm/query-builder/SelectQueryBuilder.js:750:25)
    at async paginatedBuilder (/usr/src/app/dist/infra/infra.utils.js:49:19)
    at async /usr/src/app/dist/infra/repositories/search.repository.js:74:23
    at async EntityManager.transaction (/usr/src/app/node_modules/typeorm/entity-manager/EntityManager.js:73:28)
    at async SearchRepository.searchSmart (/usr/src/app/dist/infra/repositories/search.repository.js:65:9)] QueryFailedError: column search.embedding does not exist

Try

#7310 (reply in thread)

DELETE FROM smart_search WHERE true;
DELETE FROM asset_faces WHERE true;
DELETE FROM person WHERE true;

ALTER TABLE smart_search
ADD COLUMN IF NOT EXISTS embedding vector(512); -- need to change the number here if using a non-default CLIP model

ALTER TABLE asset_faces
ADD COLUMN IF NOT EXISTS embedding vector(512);

SET vectors.pgvector_compatibility=on;

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

CREATE INDEX IF NOT EXISTS face_index ON asset_faces
    USING hnsw (embedding vector_cosine_ops)
    WITH (ef_construction = 300, m = 16);

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

Error in query: ERROR: access method "hnsw" does not exist

The OS that Immich Server is running on

Armbian 12

Version of Immich Server

v1.95.1

Version of Immich Mobile App

v1.95.1

Platform with the issue

• Server
• Web
• Mobile

Your docker-compose.yml content

version: "3.8"

#
# 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}
    command: [ "start.sh", "immich" ]
    volumes:
      - ${UPLOAD_LOCATION}:/usr/src/app/upload
      - /etc/localtime:/etc/localtime:ro
    env_file:
      - .env
    ports:
      - 2283:3001
    depends_on:
      - redis
      - database
    restart: always

  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:
      - ${UPLOAD_LOCATION}:/usr/src/app/upload
      - /etc/localtime:/etc/localtime:ro
    env_file:
      - .env
    depends_on:
      - redis
      - database
    restart: always

  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

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

  database:
    container_name: immich_postgres
    #image: tensorchord/pgvecto-rs:pg14-v0.1.11@sha256:0335a1a22f8c5dd1b697f14f079934f5152eaaa216c09b61e293be285491f8ee
    image: tensorchord/pgvecto-rs:pg14-v0.2.0@sha256:90724186f0a3517cf6914295b5ab410db9ce23190a2d9d0b9dd6463e3fa298f0
    volumes:
      - pgdata:/var/lib/postgresql/data
    restart: always

volumes:
  pgdata:
  model-cache:

Your .env content

# 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=/mnt/docker/immich/immich-app/immich-data

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

# Connection secrets for postgres and typesense. You should change these to random passwords
TYPESENSE_API_KEY=some-random-text
#DB_PASSWORD=postgres

# The values below this line do not need to be changed
###################################################################################
DB_HOSTNAME=immich_postgres
DB_USERNAME=immich -- **_Not SuperUser_**
DB_PASSWORD=password
DB_DATABASE_NAME=immich

REDIS_HOSTNAME=immich_redis

Reproduction steps

1.
2.
3.
...

Additional information

No response

[danieldietzler]

You followed the reply in the thread and you're still experiencing these issues?

[518512]

Yes

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

> Error in query: ERROR: access method "hnsw" does not exist

[danieldietzler]

@mertalev (sorry for all the pings regarding this topic, Mert. You're just the most component person regarding all these issues :))

[mertalev]

Did you run SET vectors.pgvector_compatibility=on; in this session?

[518512]

SET vectors.pgvector_compatibility=on;

Query executed OK, 0 rows affected. (0.002 s) Edit

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

Error in query: ERROR: access method "hnsw" does not exist

[518512]

follow #7289 (comment) ，the problem is still exist

I am running SQL commands as a superuser,

[mertalev]

A later comment from that thread suggests alter role immich with superuser; fixes the issue. Hard to say if it'll fix it for your case but worth a shot

[518512]

problem is still exist，Let’s wait and see for the new version.

[518512]

docker comepose pull  #Upgrade 1.97.0 

UPDATE pg_catalog.pg_extension SET extversion = '0.2.0' WHERE extname = 'vectors';

docker compose down

docker compose up -d

The problem seems to be solved. Looking at the logs, everything is ok now.