immich
immich copied to clipboard
immich-app
Error after v.112.1 upgrade - Could not retrieve CLIP dimension size
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
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
@blackhack if you roll back to
v1.111.0does it work?
Yes, it works fine, nothing wrong in the logs and the smart search works.
@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:
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
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.
Same issue! Will see about manually adding this column when I get a chance.
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 | }
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=#
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).
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
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.
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 😅
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[]
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 existsWhen 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.
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.
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.
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.
clip_indexbeing 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?
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.
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'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.
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.
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.
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: @.***>
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
If you replace all instances of \r\r\n with \n, does it successfully create the index when restoring?