107.2 noml image database query fails

Question

107.2 noml image database query fails

Closed this issue 6 months ago · 15 comments

Upgrading from 106.4-noml to latest 107.2-noml I get this error against a DB where 106.4 was running fine and vectors is enabled.

QueryFailedError: type "vector" does not exist
    at PostgresQueryRunner.query (/app/immich/server/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:219:19)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async AddFaceSearchRelation1718486162779.up (/app/immich/server/dist/migrations/1718486162779-AddFaceSearchRelation.js:12:9)
    at async MigrationExecutor.executePendingMigrations (/app/immich/server/node_modules/typeorm/migration/MigrationExecutor.js:225:17)
    at async DataSource.runMigrations (/app/immich/server/node_modules/typeorm/data-source/DataSource.js:265:35)
    at async DatabaseRepository.runMigrations (/app/immich/server/dist/repositories/database.repository.js:169:9)
    at async /app/immich/server/dist/services/database.service.js:134:17
    at async /app/immich/server/dist/repositories/database.repository.js:177:23 {
  query: '\n' +
    '            CREATE TABLE face_search (\n' +
    '            "faceId"  uuid PRIMARY KEY REFERENCES asset_faces(id) ON DELETE CASCADE,\n' +
    '            embedding  vector(512) NOT NULL )',
  parameters: undefined,
  driverError: error: type "vector" does not exist
      at /app/immich/server/node_modules/pg/lib/client.js:526:17
      at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
      at async PostgresQueryRunner.query (/app/immich/server/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:184:25)
      at async AddFaceSearchRelation1718486162779.up (/app/immich/server/dist/migrations/1718486162779-AddFaceSearchRelation.js:12:9)
      at async MigrationExecutor.executePendingMigrations (/app/immich/server/node_modules/typeorm/migration/MigrationExecutor.js:225:17)
      at async DataSource.runMigrations (/app/immich/server/node_modules/typeorm/data-source/DataSource.js:265:35)
      at async DatabaseRepository.runMigrations (/app/immich/server/dist/repositories/database.repository.js:169:9)
      at async /app/immich/server/dist/services/database.service.js:134:17
      at async /app/immich/server/dist/repositories/database.repository.js:177:23 {
...

Vectors is enabled on my separate psql container using tensorchord/pgvecto-rs:pg14-v0.2.0 docker image for postgres container.

Manually running CREATE TABLE face_search ("faceId" uuid PRIMARY KEY REFERENCES asset_faces(id) ON DELETE CASCADE, embedding vector(512) NOT NULL ); via psql as immich user against configured immich user is successful, noml images still fail to start.

I believe issue is erroneous , at end of query instead of ;.

Answer 1 · 2024-07-03T20:51:31.000Z

Seeing exactly same issue.

Answer 2 · 2024-07-04T02:48:53.000Z

I have the same issue as well. Tried removing and re-added vectors with the same error. Issue is not solved on vectors 0.2.0 or 0.2.1 (0.3.X isnt compatible with immich yet). I also ran the command manually as the same user/database, and still get the same error.

Answer 3 · 2024-07-04T03:24:29.000Z

I will say I did a new install for giggles on 107.2 and it’s working fine.

Answer 4 · 2024-07-04T06:52:38.000Z

Are you guys all running the noml version?

If yes, this is probably an issue with one of the migration script shipped with v1.107.0

Answer 5 · 2024-07-04T08:25:10.000Z

I probably have the same issue with the main version.

QueryFailedError: the symbol _vectors_typmod_in is removed in the extension; please run extension update scripts
    at PostgresQueryRunner.query (/app/immich/server/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:219:19)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async AddFaceSearchRelation1718486162779.up (/app/immich/server/dist/migrations/1718486162779-AddFaceSearchRelation.js:12:9)
    at async MigrationExecutor.executePendingMigrations (/app/immich/server/node_modules/typeorm/migration/MigrationExecutor.js:225:17)
    at async DataSource.runMigrations (/app/immich/server/node_modules/typeorm/data-source/DataSource.js:265:35)
    at async DatabaseRepository.runMigrations (/app/immich/server/dist/repositories/database.repository.js:169:9)
    at async /app/immich/server/dist/services/database.service.js:134:17
    at async /app/immich/server/dist/repositories/database.repository.js:177:23 {
  query: '\n' +
    '            CREATE TABLE face_search (\n' +
    '            "faceId"  uuid PRIMARY KEY REFERENCES asset_faces(id) ON DELETE CASCADE,\n' +
    '            embedding  vector(512) NOT NULL )',
  parameters: undefined,
  driverError: error: the symbol _vectors_typmod_in is removed in the extension; please run extension update scripts
      at /app/immich/server/node_modules/pg/lib/client.js:526:17
      at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
      at async PostgresQueryRunner.query (/app/immich/server/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:184:25)
      at async AddFaceSearchRelation1718486162779.up (/app/immich/server/dist/migrations/1718486162779-AddFaceSearchRelation.js:12:9)
      at async MigrationExecutor.executePendingMigrations (/app/immich/server/node_modules/typeorm/migration/MigrationExecutor.js:225:17)
      at async DataSource.runMigrations (/app/immich/server/node_modules/typeorm/data-source/DataSource.js:265:35)
      at async DatabaseRepository.runMigrations (/app/immich/server/dist/repositories/database.repository.js:169:9)
      at async /app/immich/server/dist/services/database.service.js:134:17
      at async /app/immich/server/dist/repositories/database.repository.js:177:23 {

Answer 6 · 2024-07-04T23:00:14.000Z

I had the same error, in my case it was caused by incorrect permissions in the database. I'm running Immich using a Postgres non-superuser immich. I had to run the following query as a superuser to fix it:

ALTER SCHEMA vectors OWNER TO immich;

This caused another error to show up:

QueryFailedError: permission denied for view pg_vector_index_stat
    at PostgresQueryRunner.query (/app/immich/server/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:219:19)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async DataSource.query (/app/immich/server/node_modules/typeorm/data-source/DataSource.js:350:20)
    at async DatabaseRepository.shouldReindex (/app/immich/server/dist/repositories/database.repository.js:119:25)
    at async /app/immich/server/dist/services/database.service.js:122:21
    at async /app/immich/server/dist/repositories/database.repository.js:177:23 {
  query: '\n' +
    '          SELECT idx_status\n' +
    '          FROM pg_vector_index_stat\n' +
    '          WHERE indexname = $1',
  parameters: [ 'clip_index' ],
  driverError: error: permission denied for view pg_vector_index_stat
      at /app/immich/server/node_modules/pg/lib/client.js:526:17
      at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
      at async PostgresQueryRunner.query (/app/immich/server/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:184:25)
      at async DataSource.query (/app/immich/server/node_modules/typeorm/data-source/DataSource.js:350:20)
      at async DatabaseRepository.shouldReindex (/app/immich/server/dist/repositories/database.repository.js:119:25)
      at async /app/immich/server/dist/services/database.service.js:122:21
      at async /app/immich/server/dist/repositories/database.repository.js:177:23 {

Which I fixed using this query:

ALTER VIEW pg_vector_index_stat OWNER TO immich;

Then everything started working again.

Answer 7 · 2024-07-04T23:57:37.000Z

Had an extra ' in the above command which I thought I had parsed correctly. Running the command mentioned in my initial comment as immich along with the above ALTER commands and 107.2 still does not start correctly.

Now I am getting QueryFailedError: relation "face_search" already exists

Answer 8 · 2024-07-05T05:31:50.000Z

I also ran the two ALTER commands above. Where I was met with the same QueryFailedError error for face_search. Since I had manually run the command to create the table I went ahead and dropped it. Next restart I got this error:
QueryFailedError: column "embedding" of relation "smart_search" does not exist

Answer 9 · 2024-07-05T05:47:18.000Z

Are you guys all running the noml version?

If yes, this is probably an issue with one of the migration script shipped with v1.107.0

I tried using both noml and latest, both ended up with the same error messages. However I do have DISABLE_MACHINE_LEARNING (tried both true and false with each version with no avail) in my docker config.

Answer 10 · 2024-07-05T09:32:00.000Z

In my case the following queries solved the problem:
CREATE TABLE face_search ("faceId" uuid PRIMARY KEY REFERENCES asset_faces(id) ON DELETE CASCADE, embedding vectors.vector(512) NOT NULL );
INSERT INTO "migrations" ("id", "timestamp", "name") VALUES (153, 1718486162779, 'AddFaceSearchRelation1718486162779');

After that Immich v1.107.2 started normally and everything seems to work using tensorchord/pgvecto-rs:pg15-v0.2.1.

Answer 11 · 2024-07-05T17:00:20.000Z

INSERT INTO "migrations" ("id", "timestamp", "name") VALUES (153, 1718486162779, 'AddFaceSearchRelation1718486162779');

That fixed it for me, thanks.

Answer 12 · 2024-07-07T05:45:11.000Z

I'm not using noml version, but upgrading the standard version from 106.4 to 107.2 still fails with the following error

Migration "AddFaceSearchRelation1718486162779" failed, error: internal error: entered unreachable code                                                                                                    
QueryFailedError: internal error: entered unreachable code                                                                                                                                                
    at PostgresQueryRunner.query (/app/immich/server/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:219:19)                                                                                  
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)                                                                                                                         
    at async AddFaceSearchRelation1718486162779.up (/app/immich/server/dist/migrations/1718486162779-AddFaceSearchRelation.js:23:9)                                                                       
    at async MigrationExecutor.executePendingMigrations (/app/immich/server/node_modules/typeorm/migration/MigrationExecutor.js:225:17)                                                                   
    at async DataSource.runMigrations (/app/immich/server/node_modules/typeorm/data-source/DataSource.js:265:35)                                                                                          
    at async DatabaseRepository.runMigrations (/app/immich/server/dist/repositories/database.repository.js:169:9)                                                                                         
    at async /app/immich/server/dist/services/database.service.js:134:17                                                                                                                                  
    at async /app/immich/server/dist/repositories/database.repository.js:177:23 {                                                                                                                         
  query: '\n' +                                                                                                                                                                                           
    '            CREATE INDEX face_index ON face_search\n' +                                                                                                                                              
    '            USING hnsw (embedding vector_cosine_ops)\n' +                                                                                                                                            
    '            WITH (ef_construction = 300, m = 16)',                                                                                                                                                   
  parameters: undefined,

Running the following two postgres commands provided by @oli906 solves the issue for me. It works fine so far.

CREATE TABLE face_search ("faceId" uuid PRIMARY KEY REFERENCES asset_faces(id) ON DELETE CASCADE, embedding  vectors.vector(512) NOT NULL );
INSERT INTO "migrations" ("id", "timestamp", "name") VALUES (153, 1718486162779, 'AddFaceSearchRelation1718486162779');

I'm using tensorchord/pgvecto-rs:pg14-v0.2.0

Answer 13 · 2024-07-08T04:19:34.000Z

Seems like theres enough solutions here - perhaps if any other similar issues arise, open an upstream issue (not an issue with this repo/container)

Answer 14 · 2024-07-08T20:17:12.000Z

Was able to get my installation to 107.2 to upgrade by inputting the postgres authentication into the container which I had never had to do before since first installing around 1.61. The ALTER commands above didn't allow my immich user to see the vectors extension but I guess have a work around for when new table is needed like what was added in 107.0.

Answer 15 · 2024-07-08T20:22:25.000Z

Was able to get my installation to 107.2 to upgrade by inputting the postgres authentication into the container which I had never had to do before since first installing around 1.61. The ALTER commands above didn't allow my immich user to see the vectors extension but I guess have a work around for when new table is needed like what was added in 107.0.

Where did you “input Postgres info” into container at?