alextran1502
commented
10 months ago If you run 1.84 again, do you still that message?
Closed vadimzharov closed 10 months ago
alextran1502
commented
10 months ago If you run 1.84 again, do you still that message?
vadimzharov
commented
10 months ago Yes. The issue is consistent - I changed the tag couple times - and containers are failing with this error once I switch to tag v1.84.0 and working with no issues once I revert it to v1.83.0
alextran1502
commented
10 months ago Are the two instances using the same database?
Saw the info above, they are using separate database
vadimzharov
commented
10 months ago I queried select * from albums; from PSQL,
for working immich instance:
immich=> select * from albums;
id | ownerId | albumName | createdAt | albumThumbnailAssetId | updatedAt | description | deletedA
t
--------------------------------------+--------------------------------------+-----------------------+-------------------------------+--------------------------------------+-------------------------------+-------------+---------
--
b34f7f64-ab7e-4b3b-b5ed-b1f4bae24099 | f3298bc8-358b-478d-9e71-f7ad75a50667 | Test data | 2023-09-10 22:27:18.418453+00 | e839e726-849d-42cb-8cfe-1b811a70cd30 | 2023-09-20 21:54:38.716+00 | |
200e7146-bf62-4f0e-ba55-c69350910aea | f3298bc8-358b-478d-9e71-f7ad75a50667 | XXXX birthday | 2023-09-22 01:44:58.961231+00 | 62b3f413-cf56-4431-bdfb-005ab0fec718 | 2023-09-22 01:45:40.57+00 | |
b1ceae77-bc9e-4698-bb1e-7c48d3230871 | f3298bc8-358b-478d-9e71-f7ad75a50667 | XXXX parkour | 2023-09-26 01:54:51.647683+00 | 9a36048e-e71c-463f-9793-ae942992d6c5 | 2023-09-26 02:49:42.035+00 | |
3f62adcc-2cfc-4ea4-abd4-e1db22026210 | f3298bc8-358b-478d-9e71-f7ad75a50667 | Camp 2023 July 23 | 2023-09-26 02:55:27.931672+00 | 2bbc185d-52d4-44cc-aa3a-dee51bf09af7 | 2023-09-26 02:56:47.692+00 | |
8edc4b80-d55e-499d-8aaa-8ab5dc7b3d28 | f3298bc8-358b-478d-9e71-f7ad75a50667 | Independence day 2023 | 2023-09-26 16:28:28.522773+00 | 3e851537-1545-492f-a5b6-b9cb2819afda | 2023-09-26 16:28:45.007931+00 | |
897219b5-87be-4693-ac81-fa77676e579c | f3298bc8-358b-478d-9e71-f7ad75a50667 | Test data 1 | 2023-09-21 22:35:12.37014+00 | 8437e4af-e110-4fa4-8d99-021c32f07af3 | 2023-09-27 02:14:47.74+00 | |
af72475b-e9b8-44ed-b283-bcdcd0919335 | f3298bc8-358b-478d-9e71-f7ad75a50667 | XXXX 2022 | 2023-09-27 03:13:52.650014+00 | 722d34cc-7db2-4169-a7a6-171922bbc222 | 2023-09-27 03:14:52.746+00 | |
8d2068e5-4aa4-4b4c-8550-7f0882ae6b66 | f3298bc8-358b-478d-9e71-f7ad75a50667 | XXXX 2023 | 2023-09-28 02:03:47.339795+00 | 77d1f15e-cb76-424a-a52b-96a7e2356fc2 | 2023-09-28 02:03:47.339795+00 | |
8a41821b-06a4-46f7-ba53-552619859138 | f3298bc8-358b-478d-9e71-f7ad75a50667 | Kids 2023 | 2023-09-29 01:21:30.365868+00 | b507599f-313b-4b96-9924-d66be2032606 | 2023-10-20 04:28:42.194+00 | |
(9 rows)
immich=> For instance where immich containers are failing:
immich=> select * from albums;
id | ownerId | albumName | createdAt | albumThumbnailAssetId | updatedAt | description |
deletedAt
--------------------------------------+--------------------------------------+---------------------------------+-------------------------------+--------------------------------------+------------------------------+-------------+
-----------
034b16e7-fcb8-4f5d-9564-e9991fcd7fc6 | 725b3c2f-104a-43d3-b545-f8e7c615d5d5 | Camp 2023 Sept 09 | 2023-09-26 03:49:54.33698+00 | 22052b27-4e85-487f-9d15-dcddf75579bf | 2023-09-26 03:50:35.631+00 | |
6674f218-ac51-4547-bb88-02ddc23c875e | 725b3c2f-104a-43d3-b545-f8e7c615d5d5 | Trip to the XXXX - July 16 2023 | 2023-09-26 14:48:59.622665+00 | e113b131-633f-429c-ad01-542fbdda9cf6 | 2023-09-26 14:50:08.576+00 | |
04aa8db0-9f7e-4602-b0de-1c1b813ae357 | 725b3c2f-104a-43d3-b545-f8e7c615d5d5 | XXXX 2023 | 2023-09-27 04:09:44.53863+00 | f6951948-34c8-4d26-9e06-7270d2778377 | 2023-09-27 04:09:44.53863+00 | |
034b16e7-fcb8-4f5d-9564-e9991fcd7fc6 | 725b3c2f-104a-43d3-b545-f8e7c615d5d5 | XXX 2023 Sept 09 | 2023-09-26 03:49:54.33698+00 | 22052b27-4e85-487f-9d15-dcddf75579bf | 2023-09-26 03:50:35.631+00 | |
6674f218-ac51-4547-bb88-02ddc23c875e | 725b3c2f-104a-43d3-b545-f8e7c615d5d5 | Trip to the XXXX - July 16 2023 | 2023-09-26 14:48:59.622665+00 | e113b131-633f-429c-ad01-542fbdda9cf6 | 2023-09-26 14:50:08.576+00 | |
04aa8db0-9f7e-4602-b0de-1c1b813ae357 | 725b3c2f-104a-43d3-b545-f8e7c615d5d5 | XXXX 2023 | 2023-09-27 04:09:44.53863+00 | f6951948-34c8-4d26-9e06-7270d2778377 | 2023-09-27 04:09:44.53863+00 | |
63be1527-d82c-4ea4-b1b4-79e9a9c4e78a | 725b3c2f-104a-43d3-b545-f8e7c615d5d5 | Kids 2023 | 2023-10-19 23:02:33.73551+00 | ab1e6634-65c5-4df3-9c6d-fad7bf49efbf | 2023-10-19 23:04:18.472+00 | |
(7 rows)
immich=> I noticed - database for failing immich instance has some albums doubled (same ID and name). Can this be the root cause of the issue? Can I delete doubled records from the table?
alextran1502
commented
10 months ago Hmm, interesting. Yes, you can remove the album
jrasm91
commented
10 months ago Can you show the output of this psql command (describe table) \d+ "albums". It should be something like this:
Table "public.albums"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-----------------------+--------------------------+-----------+----------+-------------------------------------+----------+-------------+--------------+----------------------------------
id | uuid | | not null | uuid_generate_v4() | plain | | |
ownerId | uuid | | not null | | plain | | |
albumName | character varying | | not null | 'Untitled Album'::character varying | extended | | |
createdAt | timestamp with time zone | | not null | now() | plain | | |
albumThumbnailAssetId | uuid | | | | plain | | | Asset ID to be used as thumbnail
updatedAt | timestamp with time zone | | not null | now() | plain | | |
description | text | | not null | ''::text | extended | | |
deletedAt | timestamp with time zone | | | | plain | | |
Indexes:
"PK_7f71c7b5bc7c87b8f94c9a93a00" PRIMARY KEY, btree (id)
Foreign-key constraints:
"FK_05895aa505a670300d4816debce" FOREIGN KEY ("albumThumbnailAssetId") REFERENCES assets(id) ON UPDATE CASCADE ON DELETE SET NULL
"FK_b22c53f35ef20c28c21637c85f4" FOREIGN KEY ("ownerId") REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE
Referenced by:
TABLE "shared_links" CONSTRAINT "FK_0c6ce9058c29f07cdf7014eac66" FOREIGN KEY ("albumId") REFERENCES albums(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "activity" CONSTRAINT "FK_1af8519996fbfb3684b58df280b" FOREIGN KEY ("albumId") REFERENCES albums(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "albums_shared_users_users" CONSTRAINT "FK_427c350ad49bd3935a50baab737" FOREIGN KEY ("albumsId") REFERENCES albums(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "albums_assets_assets" CONSTRAINT "FK_e590fa396c6898fcd4a50e40927" FOREIGN KEY ("albumsId") REFERENCES albums(id) ON UPDATE CASCADE ON DELETE CASCADEIt sounds like your PK might not be unique somehow.
vadimzharov
commented
10 months ago Sorry, I missed last comment before I did this: I deleted albums that are duplicated, and now I have:
immich=> select * from albums;
id | ownerId | albumName | createdAt | albumThumbnailAssetId | updatedAt | description | deletedAt
--------------------------------------+--------------------------------------+-----------+------------------------------+--------------------------------------+----------------------------+-------------+-----------
63be1527-d82c-4ea4-b1b4-79e9a9c4e78a | 725b3c2f-104a-43d3-b545-f8e7c615d5d5 | Kids 2023 | 2023-10-19 23:02:33.73551+00 | ab1e6634-65c5-4df3-9c6d-fad7bf49efbf | 2023-10-19 23:04:18.472+00 | |
(1 row)But containers are still crushing with the same error.
Here is the output from \dt+ "albums"
immich=> \d+ "albums"
Table "public.albums"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-----------------------+--------------------------+-----------+----------+-------------------------------------+----------+-------------+--------------+----------------------------------
id | uuid | | not null | uuid_generate_v4() | plain | | |
ownerId | uuid | | not null | | plain | | |
albumName | character varying | | not null | 'Untitled Album'::character varying | extended | | |
createdAt | timestamp with time zone | | not null | now() | plain | | |
albumThumbnailAssetId | uuid | | | | plain | | | Asset ID to be used as thumbnail
updatedAt | timestamp with time zone | | not null | now() | plain | | |
description | text | | not null | ''::text | extended | | |
deletedAt | timestamp with time zone | | | | plain | | |
Foreign-key constraints:
"FK_05895aa505a670300d4816debce" FOREIGN KEY ("albumThumbnailAssetId") REFERENCES assets(id) ON UPDATE CASCADE ON DELETE SET NULL
"FK_b22c53f35ef20c28c21637c85f4" FOREIGN KEY ("ownerId") REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE
Access method: heap
immich=> Also, I noticed PSQL does not have "activity" table:
immich=> \dt;
List of relations
Schema | Name | Type | Owner
--------+---------------------------+-------+--------
public | albums | table | immich
public | albums_assets_assets | table | immich
public | albums_shared_users_users | table | immich
public | api_keys | table | immich
public | asset_faces | table | immich
public | assets | table | immich
public | audit | table | immich
public | exif | table | immich
public | libraries | table | immich
public | migrations | table | immich
public | move_history | table | immich
public | partners | table | immich
public | person | table | immich
public | shared_link__asset | table | immich
public | shared_links | table | immich
public | smart_info | table | immich
public | system_config | table | immich
public | tag_asset | table | immich
public | tags | table | immich
public | typeorm_metadata | table | immich
public | user_token | table | immich
public | users | table | immich
(22 rows)
immich=> I pulled my PSQL backups before I triggered the upgrade for both immich instances - looks like the activity table was introduced in the recent version (1.84 or 1.83) - old psql databases (for both instances) don't have it, but now my first (working) immich instance have it, and the second (where containers are crushing) - doesn't.
jrasm91
commented
10 months ago You are missing both foreign keys and also the primary key on the table, which is bad.
The activity table being missing is expected. The environment is trying to run the migration to upgrade the schema to the latest version but it is failing since a primary key on the album table is required by one of the steps.
Do you have any idea why/how you are missing these constraints? This seems very concerning. Can you do a more widespread search? Do any tables have PK and FK? I suppose you could try manually adding the missing ones but who knows what state the data is in at this point.
vadimzharov
commented
10 months ago Thanks for the response!
I don't know why I'm missing the keys, but I noticed I'm missing not only for albums table, but also for other tables (i.e. api_keys). I'm missing these keys in DB dump from the date before I executed the upgrade.
Is there a way to restore somehow the data? Since everything is working in 1.83.0 version, can I somehow migrate all these data (I mean some data from DB and pictures) to a new immich instance? Like create a new immich instance, mount my pictures but restore PSQL not from the whole dump, but only tables with pictures data?
jrasm91
commented
10 months ago Yeah, this is a really interesting problem. Can you connect to the immich-server container and run the following?
npm run typeorm:migrations:generate DiffIf should create a new file, which will include a set of sql to run to "sync" the database. I would be interested to see what it comes up with.
It might be worthwhile to get your database to at least be able to upgrade to the latest version and then look into some sort of backup and restore that can utilize a fresh schema.
vadimzharov
commented
10 months ago I executed the command:
root@immich-server-debug:/usr/src/app# npm run typeorm:migrations:generate Diff
> immich@1.84.0 typeorm:migrations:generate
> typeorm migration:generate -d ./dist/infra/database.config.js Diff
Migration /usr/src/app/1699033809782-Diff.ts has been generated successfully.
root@immich-server-debug:/usr/src/app# cat /usr/src/app/1699033809782-Diff.ts
import { MigrationInterface, QueryRunner } from "typeorm";
export class Diff1699033809782 implements MigrationInterface {
name = 'Diff1699033809782'
public async up(queryRunner: QueryRunner): Promise<void> {
await queryRunner.query(`CREATE TABLE "activity" ("id" uuid NOT NULL DEFAULT uuid_generate_v4(), "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(), "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(), "albumId" uuid NOT NULL, "userId" uuid NOT NULL, "assetId" uuid, "comment" text, "isLiked" boolean NOT NULL DEFAULT false, CONSTRAINT "CHK_2ab1e70f113f450eb40c1e3ec8" CHECK (("comment" IS NULL AND "isLiked" = true) OR ("comment" IS NOT NULL AND "isLiked" = false)), CONSTRAINT "PK_24625a1d6b1b089c8ae206fe467" PRIMARY KEY ("id"))`);
await queryRunner.query(`CREATE UNIQUE INDEX "IDX_activity_like" ON "activity" ("assetId", "userId", "albumId") WHERE ("isLiked" = true)`);
await queryRunner.query(`ALTER TABLE "shared_links" ADD "password" character varying`);
await queryRunner.query(`ALTER TABLE "albums_assets_assets" ADD CONSTRAINT "PK_c5cb72125ec6faed06552ba498a" PRIMARY KEY ("albumsId", "assetsId")`);
await queryRunner.query(`ALTER TABLE "albums" ADD CONSTRAINT "PK_838ebae24d2e12082670ffc95d7" PRIMARY KEY ("id")`);
await queryRunner.query(`ALTER TABLE "api_keys" ADD CONSTRAINT "PK_5c8a79801b44bd27b79228e1dad" PRIMARY KEY ("id")`);
await queryRunner.query(`ALTER TABLE "shared_links" ADD CONSTRAINT "FK_0c6ce9058c29f07cdf7014eac66" FOREIGN KEY ("albumId") REFERENCES "albums"("id") ON DELETE CASCADE ON UPDATE CASCADE`);
await queryRunner.query(`ALTER TABLE "activity" ADD CONSTRAINT "FK_8091ea76b12338cb4428d33d782" FOREIGN KEY ("assetId") REFERENCES "assets"("id") ON DELETE CASCADE ON UPDATE CASCADE`);
await queryRunner.query(`ALTER TABLE "activity" ADD CONSTRAINT "FK_3571467bcbe021f66e2bdce96ea" FOREIGN KEY ("userId") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE`);
await queryRunner.query(`ALTER TABLE "activity" ADD CONSTRAINT "FK_1af8519996fbfb3684b58df280b" FOREIGN KEY ("albumId") REFERENCES "albums"("id") ON DELETE CASCADE ON UPDATE CASCADE`);
await queryRunner.query(`ALTER TABLE "albums_shared_users_users" ADD CONSTRAINT "FK_427c350ad49bd3935a50baab737" FOREIGN KEY ("albumsId") REFERENCES "albums"("id") ON DELETE CASCADE ON UPDATE CASCADE`);
await queryRunner.query(`ALTER TABLE "albums_assets_assets" ADD CONSTRAINT "FK_e590fa396c6898fcd4a50e40927" FOREIGN KEY ("albumsId") REFERENCES "albums"("id") ON DELETE CASCADE ON UPDATE CASCADE`);
}
public async down(queryRunner: QueryRunner): Promise<void> {
await queryRunner.query(`ALTER TABLE "albums_assets_assets" DROP CONSTRAINT "FK_e590fa396c6898fcd4a50e40927"`);
await queryRunner.query(`ALTER TABLE "albums_shared_users_users" DROP CONSTRAINT "FK_427c350ad49bd3935a50baab737"`);
await queryRunner.query(`ALTER TABLE "activity" DROP CONSTRAINT "FK_1af8519996fbfb3684b58df280b"`);
await queryRunner.query(`ALTER TABLE "activity" DROP CONSTRAINT "FK_3571467bcbe021f66e2bdce96ea"`);
await queryRunner.query(`ALTER TABLE "activity" DROP CONSTRAINT "FK_8091ea76b12338cb4428d33d782"`);
await queryRunner.query(`ALTER TABLE "shared_links" DROP CONSTRAINT "FK_0c6ce9058c29f07cdf7014eac66"`);
await queryRunner.query(`ALTER TABLE "api_keys" DROP CONSTRAINT "PK_5c8a79801b44bd27b79228e1dad"`);
await queryRunner.query(`ALTER TABLE "albums" DROP CONSTRAINT "PK_838ebae24d2e12082670ffc95d7"`);
await queryRunner.query(`ALTER TABLE "albums_assets_assets" DROP CONSTRAINT "PK_c5cb72125ec6faed06552ba498a"`);
await queryRunner.query(`ALTER TABLE "shared_links" DROP COLUMN "password"`);
await queryRunner.query(`DROP INDEX "public"."IDX_activity_like"`);
await queryRunner.query(`DROP TABLE "activity"`);
}
}
root@immich-server-debug:/usr/src/app# Yeah maybe try manually running these and then see if the upgrade works.
ALTER TABLE "albums_assets_assets" ADD CONSTRAINT "PK_c5cb72125ec6faed06552ba498a" PRIMARY KEY("albumsId", "assetsId");
ALTER TABLE "albums" ADD CONSTRAINT "PK_838ebae24d2e12082670ffc95d7" PRIMARY KEY ("id");
ALTER TABLE "api_keys" ADD CONSTRAINT "PK_5c8a79801b44bd27b79228e1dad" PRIMARY KEY("id");
ALTER TABLE "shared_links" ADD CONSTRAINT "FK_0c6ce9058c29f07cdf7014eac66" FOREIGN KEY ("albumId") REFERENCES "albums"("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "albums_shared_users_users" ADD CONSTRAINT "FK_427c350ad49bd3935a50baab737" FOREIGN KEY ("albumsId") REFERENCES "albums"("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "albums_assets_assets" ADD CONSTRAINT "FK_e590fa396c6898fcd4a50e40927" FOREIGN KEY ("albumsId") REFERENCES "albums"("id") ON DELETE CASCADE ON UPDATE CASCADE;Then run the diff again afterwards and it should report no changes.
jrasm91
commented
10 months ago Did you ever do a database restore to this system? Or was it a new install and everything was added via web/cli/libraries?
vadimzharov
commented
10 months ago Yes, I restored DB for both of my instances a few times.
jrasm91
commented
10 months ago I'm guessing something went wrong but the logs/output was overlooked. I don't see any other way a PK could get dropped.
The bug
I have two identical immich environments, deployed in k8s cluster in two separate namespaces. Both are deployed using immich helm charts and use the same values file, example (some values are specific to ArgoCD which I'm using to deploy immich via helm):
Both instances is using PQSQL database (each instance is using it's own database) running in container
docker.io/bitnami/postgresql:15.4.0-debian-11-r0.The first immich instance was installed a while ago and seamlessly upgraded from v1.72.2 to v1.84.0 without any issues (this instance is serving around 4000 photos). Last upgrade was by changing tag from 1.82.1 to 1.84.0 and all containers are running and the instance is working (all containers are up, no errors).
But I cannot upgrade the second immich instance. It was initially installed as 1.74.0 and upgraded to 1.82.1 - again without any issues (this instance is service around 65000 photos). Recently I changed immich images tag from 1.82.1 to 1.84.0 - and both immich-microservices and immich-service containers are failing with the same error:
I didn't touch the database at all (it is still running without any restarts) for both immich instances.c
If I change tag to 1.83.0 - immich (all containers including immich-server and immich-microservices) started to work without any issues - means can connect to the database and I can access immich UI.
So the issue I have - immich-server and immich-microservices containers are failing with error:
after I upgrade immich from 1.83.0 to 1.84.0, but can work without any issues/errors if I revert immich to use version 1.83.0
I guess it might be some issue with PSQL database - something changed in 1.84.0 and preventing immich to work with database properly.
The OS that Immich Server is running on
K8S cluster with Ubuntu nodes.
Version of Immich Server
v1.84.0
Version of Immich Mobile App
1.84.0
Platform with the issue
Your docker-compose.yml content
Your .env content
Reproduction steps
Additional information
No response