Immich•2w ago

Api:ErrorInterceptor~tza2l33l] Unknown error: PostgresError: there is no unique or exclusion constra

Just updated to v143 and switched to the new timeline. My device appears to be slow updating and I checked the immich logs to reveal…

350 Replies

Immich•2w ago

:wave: Hey @praying for unix, Thanks for reaching out to us. Please carefully read this message and follow the recommended actions. This will help us be more effective in our support effort and leave more time for building Immich :immich:. References - Container Logs: docker compose logs docs - Container Status: docker ps -a docs - Reverse Proxy: https://immich.app/docs/administration/reverse-proxy - Code Formatting https://support.discord.com/hc/en-us/articles/210298617-Markdown-Text-101-Chat-Formatting-Bold-Italic-Underline#h_01GY0DAKGXDEHE263BCAYEGFJA Checklist I have... 1. :ballot_box_with_check: verified I'm on the latest release(note that mobile app releases may take some time). 2. :ballot_box_with_check: read applicable release notes. 3. :ballot_box_with_check: reviewed the FAQs for known issues. 4. :ballot_box_with_check: reviewed Github for known issues. 5. :ballot_box_with_check: tried accessing Immich via local ip (without a custom reverse proxy). 6. :ballot_box_with_check: uploaded the relevant information (see below). 7. :ballot_box_with_check: tried an incognito window, disabled extensions, cleared mobile app cache, logged out and back in, different browsers, etc. as applicable (an item can be marked as "complete" by reacting with the appropriate number) Information In order to be able to effectively help you, we need you to provide clear information to show what the problem is. The exact details needed vary per case, but here is a list of things to consider: - Your docker-compose.yml and .env files. - Logs from all the containers and their status (see above). - All the troubleshooting steps you've tried so far. - Any recent changes you've made to Immich or your system. - Details about your system (both software/OS and hardware). - Details about your storage (filesystems, type of disks, output of commands like fdisk -l and df -h). - The version of the Immich server, mobile app, and other relevant pieces. - Any other information that you think might be relevant. Please paste files and logs with proper code formatting, and especially avoid blurry screenshots. Without the right information we can't work out what the problem is. Help us help you ;) If this ticket can be closed you can use the /close command, and re-open it later if needed.

glueeaterOP•2w ago

sept23_immich_server...

glueeaterOP•2w ago

docker-compose_clean...

glueeaterOP•2w ago

sept23_immich_postgr...

bo0tzz•2w ago

Can you connect to postgres and run \d asset_exif?

glueeaterOP•2w ago

appears to break uploading - i signed out and back in how do i connect again i forget haha

bo0tzz•2w ago

https://immich.app/docs/guides/database-queries

glueeaterOP•2w ago

attach

message.txt

bo0tzz•2w ago

That's not the full output

glueeaterOP•2w ago

Oops I’ll try again in a few gotta put kids down

glueeaterOP•2w ago

try 2 @bo0tzz

message.txt

glueeaterOP•2w ago

glueeaterOP•2w ago

any other outputs ya'll need

glueeaterOP•2w ago

So the logs output this any time im uploading

message.txt

glueeaterOP•2w ago

switching to old timeline shows 16 failed uploads Error persists in v1.143.1 Hi @bo0tzz any chance folks had a min to look at these? happy to help how i can

bo0tzz•2w ago

Seems like there's some constraints etc missing from your database 🤔 Can you post the logs the server outputs when starting up? And your .env file for completeness

jrasm91•2w ago

Might be worth looking through these discussions as well (#20328, #20530). Can you run this on the server and post the output here?

docker exec immich_server sh -c 'DB_URL=postgres://$DB_USERNAME:$DB_PASSWORD@database:5432/$DB_DATABASE_NAME npm run migrations:debug && cat migrations.sql'

docker exec immich_server sh -c 'DB_URL=postgres://$DB_USERNAME:$DB_PASSWORD@database:5432/$DB_DATABASE_NAME npm run migrations:debug && cat migrations.sql'

Immich•2w ago

[Discussion] Upgrade to 1.136.0 fails - missing triggers (immich-app/immich#20328) [Discussion] PostgresError: constraint "FK_c0117fdbc50b917ef9067740c44" for table "asset_exif" does not exist (immich-app/immich#20530)

glueeaterOP•2w ago

do i need to shut down server first?

npm error code ENOENT
npm error syscall open
npm error path /usr/src/app/package.json
npm error errno -2
npm error enoent Could not read package.json: Error: ENOENT: no such file or directory, open '/usr/src/app/package.json'
npm error enoent This is related to npm not being able to find a file.
npm error enoent
npm error A complete log of this run can be found in: /root/.npm/_logs/2025-09-25T00_46_32_331Z-debug-0.log

npm error code ENOENT
npm error syscall open
npm error path /usr/src/app/package.json
npm error errno -2
npm error enoent Could not read package.json: Error: ENOENT: no such file or directory, open '/usr/src/app/package.json'
npm error enoent This is related to npm not being able to find a file.
npm error enoent
npm error A complete log of this run can be found in: /root/.npm/_logs/2025-09-25T00_46_32_331Z-debug-0.log

jrasm91•2w ago

try adding --prefix server So npm run --prefix server ...

glueeaterOP•2w ago

NAME npm run --prefix server  migrations:debug && cat migrations.sql'

> immich@1.143.1 migrations:debug
> node ./dist/bin/migrations.js debug


Wrote migrations.sql
cat: migrations.sql: No such file or directory

NAME npm run --prefix server  migrations:debug && cat migrations.sql'

> immich@1.143.1 migrations:debug
> node ./dist/bin/migrations.js debug


Wrote migrations.sql
cat: migrations.sql: No such file or directory

jrasm91•2w ago

Now it's cat server/migrations.sql

glueeaterOP•2w ago

attached

message.txt

jrasm91•2w ago

Have you done a server dump and restore or what is your server history?

glueeaterOP•2w ago

i restored right after installing v1.143.0 bc it was having issues starting restored from the most recent nightly backup

jrasm91•2w ago

You are basically missing all the indexes and foreign constraints.

glueeaterOP•2w ago

o i had anotehr issue that skatbo helped me with on Github

glueeaterOP•2w ago

still receiving this error in logs

message.txt

jrasm91•2w ago

So you basically need to manually fix this database drift. I still have no idea what/how it is caused. I can only guess a database dump and restore that someone doesn't create constraints or indexes. The command you ran just detects database drift - the difference between your database and what it should do. Basically nothing will work as long as it is so far away from what it is supposed to be.

glueeaterOP•2w ago

oh yeah this is me posting in this thread

jrasm91•2w ago

So you need to go through and run the SQL in the file And then run the command again and eventually you should get down to zero differences

glueeaterOP•2w ago

how do i do this?

jrasm91•2w ago

Do you know how to connect to the database container to run SQL?

glueeaterOP•2w ago

docker exec -it immich_postgres psql --dbname=<DB_DATABASE_NAME> --username=<DB_USERNAME> and then… after that no

jrasm91•2w ago

That should get you to a terminal where you can type in SQL I think Can you do like select * from user;?

glueeaterOP•2w ago

yeah after running that command i can interact with the db

jrasm91•2w ago

So now take the first line from the migrations.sql file and try to run it

glueeaterOP•2w ago

ALTER TABLE "asset" ADD CONSTRAINT "asset_pkey" PRIMARY KEY ("id") that?

jrasm91•2w ago

Yes

glueeaterOP•2w ago

jrasm91•2w ago

Great Now run the command on the immich_server container again Is the file 2 lines shorter / is that first line now gone?

glueeaterOP•2w ago

thats a negative

jrasm91•2w ago

Maybe you need to type a ; still

glueeaterOP•2w ago

jrasm91•2w ago

Try again

glueeaterOP•2w ago

ah it worked

jrasm91•2w ago

It didn't send anything until it received a semi, so it sent the command two times

glueeaterOP•2w ago

it reduced

jrasm91•2w ago

Great

glueeaterOP•2w ago

i ran the command you suggested again and see it reduced

jrasm91•2w ago

Basically I would just work your way through the file

glueeaterOP•2w ago

is there an automation for this

jrasm91•2w ago

Lol The automation is to not lose them in the first place

glueeaterOP•2w ago

i didnt do anything int he db prior to this

jrasm91•2w ago

You can copy/paste everything at once if you want

glueeaterOP•2w ago

so now im sad what about the --missing in target?

jrasm91•2w ago

Don't do the // down part though -- is a SQL comment so it's fine

glueeaterOP•2w ago

? whats that

ERROR:  could not create unique index "UQ_assets_owner_checksum"
DETAIL:  Key ("ownerId", checksum)=(54bb8e76-396e-433e-a188-6a2d1a029c2e, \xe074d7267f0750bf69944ef07a8877bad9d3faee) is duplicated.
CONTEXT:  parallel worker

ERROR:  could not create unique index "UQ_assets_owner_checksum"
DETAIL:  Key ("ownerId", checksum)=(54bb8e76-396e-433e-a188-6a2d1a029c2e, \xe074d7267f0750bf69944ef07a8877bad9d3faee) is duplicated.
CONTEXT:  parallel worker

got this a few times

ERROR:  insert or update on table "memory_asset" violates foreign key constraint "memory_asset_memoriesId_fkey"
DETAIL:  Key (memoriesId)=(163bbb1e-b999-4bb9-9de9-56a811a2eae9) is not present in table "memory".
ALTER TABLE
ALTER TABLE
ALTER TABLE
ERROR:  insert or update on table "smart_search" violates foreign key constraint "smart_search_assetId_fkey"
DETAIL:  Key (assetId)=(63c60160-d037-4104-9ddd-4bb6991e4e2d) is not present in table "asset".

ERROR:  insert or update on table "memory_asset" violates foreign key constraint "memory_asset_memoriesId_fkey"
DETAIL:  Key (memoriesId)=(163bbb1e-b999-4bb9-9de9-56a811a2eae9) is not present in table "memory".
ALTER TABLE
ALTER TABLE
ALTER TABLE
ERROR:  insert or update on table "smart_search" violates foreign key constraint "smart_search_assetId_fkey"
DETAIL:  Key (assetId)=(63c60160-d037-4104-9ddd-4bb6991e4e2d) is not present in table "asset".

jrasm91•2w ago

Oh nevermind. It used to output SQL to undo the "up" but it doesn't anymore.

glueeaterOP•2w ago

these all cause issues

jrasm91•2w ago

This one means you have duplicate assets in the table now, so it can't add the constraint.

glueeaterOP•2w ago

do i ignore?

jrasm91•2w ago

No.... Immich prevents duplicates by requiring assets have a unique checksum You have at least two assets in the asset table with the same checksum.

glueeaterOP•2w ago

ALTER TABLE "asset_exif" ADD CONSTRAINT "asset_exif_pkey" PRIMARY KEY ("assetId"); -- missing in target
ALTER TABLE "asset_job_status" ADD CONSTRAINT "asset_job_status_pkey" PRIMARY KEY ("assetId"); -- missing in target
ALTER TABLE "memory_asset" ADD CONSTRAINT "memory_asset_pkey" PRIMARY KEY ("memoriesId", "assetsId"); -- missing in target
ALTER TABLE "album_asset" ADD CONSTRAINT "album_asset_assetsId_fkey" FOREIGN KEY ("assetsId") REFERENCES "asset" ("id") ON UPDATE CASCADE ON DELETE CASCADE; -- missing in target
ALTER TABLE "asset_exif" ADD CONSTRAINT "asset_exif_assetId_fkey" FOREIGN KEY ("assetId") REFERENCES "asset" ("id") ON UPDATE NO ACTION ON DELETE CASCADE; -- missing in target
ALTER TABLE "asset_face" ADD CONSTRAINT "asset_face_assetId_fkey" FOREIGN KEY ("assetId") REFERENCES "asset" ("id") ON UPDATE CASCADE ON DELETE CASCADE; -- missing in target
ALTER TABLE "asset_file" ADD CONSTRAINT "asset_file_assetId_fkey" FOREIGN KEY ("assetId") REFERENCES "asset" ("id") ON UPDATE CASCADE ON DELETE CASCADE; -- missing in target
ALTER TABLE "asset_job_status" ADD CONSTRAINT "asset_job_status_assetId_fkey" FOREIGN KEY ("assetId") REFERENCES "asset" ("id") ON UPDATE CASCADE ON DELETE CASCADE; -- missing in target
ALTER TABLE "memory_asset" ADD CONSTRAINT "memory_asset_memoriesId_fkey" FOREIGN KEY ("memoriesId") REFERENCES "memory" ("id") ON UPDATE CASCADE ON DELETE CASCADE; -- missing in target
ALTER TABLE "smart_search" ADD CONSTRAINT "smart_search_assetId_fkey" FOREIGN KEY ("assetId") REFERENCES "asset" ("id") ON UPDATE NO ACTION ON DELETE CASCADE; -- missing in target
CREATE UNIQUE INDEX "UQ_assets_owner_checksum" ON "asset" ("ownerId", "checksum") WHERE ("libraryId" IS NULL); -- missing in target

ALTER TABLE "asset_exif" ADD CONSTRAINT "asset_exif_pkey" PRIMARY KEY ("assetId"); -- missing in target
ALTER TABLE "asset_job_status" ADD CONSTRAINT "asset_job_status_pkey" PRIMARY KEY ("assetId"); -- missing in target
ALTER TABLE "memory_asset" ADD CONSTRAINT "memory_asset_pkey" PRIMARY KEY ("memoriesId", "assetsId"); -- missing in target
ALTER TABLE "album_asset" ADD CONSTRAINT "album_asset_assetsId_fkey" FOREIGN KEY ("assetsId") REFERENCES "asset" ("id") ON UPDATE CASCADE ON DELETE CASCADE; -- missing in target
ALTER TABLE "asset_exif" ADD CONSTRAINT "asset_exif_assetId_fkey" FOREIGN KEY ("assetId") REFERENCES "asset" ("id") ON UPDATE NO ACTION ON DELETE CASCADE; -- missing in target
ALTER TABLE "asset_face" ADD CONSTRAINT "asset_face_assetId_fkey" FOREIGN KEY ("assetId") REFERENCES "asset" ("id") ON UPDATE CASCADE ON DELETE CASCADE; -- missing in target
ALTER TABLE "asset_file" ADD CONSTRAINT "asset_file_assetId_fkey" FOREIGN KEY ("assetId") REFERENCES "asset" ("id") ON UPDATE CASCADE ON DELETE CASCADE; -- missing in target
ALTER TABLE "asset_job_status" ADD CONSTRAINT "asset_job_status_assetId_fkey" FOREIGN KEY ("assetId") REFERENCES "asset" ("id") ON UPDATE CASCADE ON DELETE CASCADE; -- missing in target
ALTER TABLE "memory_asset" ADD CONSTRAINT "memory_asset_memoriesId_fkey" FOREIGN KEY ("memoriesId") REFERENCES "memory" ("id") ON UPDATE CASCADE ON DELETE CASCADE; -- missing in target
ALTER TABLE "smart_search" ADD CONSTRAINT "smart_search_assetId_fkey" FOREIGN KEY ("assetId") REFERENCES "asset" ("id") ON UPDATE NO ACTION ON DELETE CASCADE; -- missing in target
CREATE UNIQUE INDEX "UQ_assets_owner_checksum" ON "asset" ("ownerId", "checksum") WHERE ("libraryId" IS NULL); -- missing in target

this is the remaining issues

jrasm91•2w ago

Because the constraint got dropped it didn't prevent it from being added. This is all that's left of the output of the database diff?

glueeaterOP•2w ago

yep i ran the rest

jrasm91•2w ago

That's good at least. We need to delete the invalid data and then try to apply it again, for each one. You need to do something like select from asset where "ownerId" = is AND "checksum" = checksum; Using the values from the error

glueeaterOP•2w ago

select from asset where "ownerId" = 54bb8e76-396e-433e-a188-6a2d1a029c2e AND "checksum" = \x8fd9a48e5387118cbdb3ee7ca5f58b641d67dc50;

jrasm91•2w ago

sure does it return anything?

select * from asset where "ownerId" = '54bb8e76-396e-433e-a188-6a2d1a029c2e' AND "checksum" = '\x8fd9a48e5387118cbdb3ee7ca5f58b641d67dc50';

select * from asset where "ownerId" = '54bb8e76-396e-433e-a188-6a2d1a029c2e' AND "checksum" = '\x8fd9a48e5387118cbdb3ee7ca5f58b641d67dc50';

\ the id needs to be in single quotes at least

glueeaterOP•2w ago

jrasm91•2w ago

do select * from ... How many assets/users do you have in you server?

glueeaterOP•2w ago

1 user, 30k ish

glueeaterOP•2w ago

attached output

message.txt

jrasm91•2w ago

Yeah. The options are to go through this tedious process of of removing invalid data and re-applying the missing schema changes or make a brand new install and re-upload/process everything from scratch. The data cleanup isn't too bad once you get the hang of it.

glueeaterOP•2w ago

i already deleted some of the data, so i think i have to go thru and manually fix

jrasm91•2w ago

glueeaterOP•2w ago

re-uploading is somewhat out of the mix

jrasm91•2w ago

if you want, you can change the sql to select "id", "createdAt", "originalFileName" from "asset" ... You can see that the top record is probably the "original" and then you have 4 "bad" copies yesterday that were duplicates.

glueeaterOP•2w ago

so strange so what do I do?

jrasm91•2w ago

So now we want to run this

DELETE FROM "asset" where "ownerId" = '54bb8e76-396e-433e-a188-6a2d1a029c2e' AND "checksum" = '\x8fd9a48e5387118cbdb3ee7ca5f58b641d67dc50' and "id" <> '2ae3aad5-4e85-403f-8338-ae79907a2558';

DELETE FROM "asset" where "ownerId" = '54bb8e76-396e-433e-a188-6a2d1a029c2e' AND "checksum" = '\x8fd9a48e5387118cbdb3ee7ca5f58b641d67dc50' and "id" <> '2ae3aad5-4e85-403f-8338-ae79907a2558';

this should delete the 4 duplicate records and then you should be able to create the unique constraint

CREATE UNIQUE INDEX "UQ_assets_owner_checksum" ON "asset" ("ownerId", "checksum") WHERE ("libraryId" IS NULL); -- missing in target

CREATE UNIQUE INDEX "UQ_assets_owner_checksum" ON "asset" ("ownerId", "checksum") WHERE ("libraryId" IS NULL); -- missing in target

glueeaterOP•2w ago

syntax error

jrasm91•2w ago

Did you also take a recent database backup in case something goes wrong and you need to restore?

glueeaterOP•2w ago

yep i have nightlys (14 or w/e the defualt is)

jrasm91•2w ago

oh it's <> instead of IS NOT I updated the sql

glueeaterOP•2w ago

lol so i jsut run it back right

jrasm91•2w ago

hold on I wonder if you phone has uploaded like an insane amount of duplicates

glueeaterOP•2w ago

it feels possible iCloud or something glitched out a while ago

jrasm91•2w ago

SELECT COUNT(*), "checksum" from "asset" group by "checksum";

SELECT COUNT(*), "checksum" from "asset" group by "checksum";

glueeaterOP•2w ago

i cant even copy paste the output is so long i dont think its worth copying, but its an endless scroll basically

glueeaterOP•2w ago

this forever

jrasm91•2w ago

SELECT "checksum", COUNT(*) AS count FROM "asset" GROUP BY "checksum" HAVING COUNT(*) > 1;

SELECT "checksum", COUNT(*) AS count FROM "asset" GROUP BY "checksum" HAVING COUNT(*) > 1;

this should tell you how many assets have bad duplicates

glueeaterOP•2w ago

140 rows

jrasm91•2w ago

that's not too bad at least

glueeaterOP•2w ago

i cant figure out how to copy paste but yeah its 140 rows

glueeaterOP•2w ago

like this

message.txt

jrasm91•2w ago

just need to figure out if there is an easy way to delete all of them at once minus the original

SELECT "id", "checksum", "createdAt", "originalFileName" FROM "asset" WHERE "checksum" IN ( SELECT "checksum" FROM "asset" GROUP BY "checksum" HAVING COUNT(*) > 1);

SELECT "id", "checksum", "createdAt", "originalFileName" FROM "asset" WHERE "checksum" IN ( SELECT "checksum" FROM "asset" GROUP BY "checksum" HAVING COUNT(*) > 1);

you know what maybe we should do this part last. Let's fix the other issues first and do this one last. the other issues are easier to fix as well but yeah, for this, we need to essentially go through one checksum at a a time and delete the rows that are the oldest

glueeaterOP•2w ago

incredible

immich=# ALTER TABLE "asset_exif" ADD CONSTRAINT "asset_exif_pkey" PRIMARY KEY ("assetId"); -- missing in target
ERROR:  could not create unique index "asset_exif_pkey"
DETAIL:  Key ("assetId")=(301b1aa1-cf1a-4e1b-9e09-a75718ef16d1) is duplicated.
CONTEXT:  parallel worker

immich=# ALTER TABLE "asset_exif" ADD CONSTRAINT "asset_exif_pkey" PRIMARY KEY ("assetId"); -- missing in target
ERROR:  could not create unique index "asset_exif_pkey"
DETAIL:  Key ("assetId")=(301b1aa1-cf1a-4e1b-9e09-a75718ef16d1) is duplicated.
CONTEXT:  parallel worker

so if i start here instead

jrasm91•2w ago

bruh it's the same thing

glueeaterOP•2w ago

oh. pain.

jrasm91•2w ago

chat-gpt came up with this though:

DELETE FROM "asset"
WHERE "id" IN (
  SELECT "id"
  FROM (
    SELECT 
      "id",
      ROW_NUMBER() OVER (
        PARTITION BY "checksum"
        ORDER BY "createdAt" ASC
      ) AS rn
    FROM "asset"
  ) sub
  WHERE rn > 1
);

DELETE FROM "asset"
WHERE "id" IN (
  SELECT "id"
  FROM (
    SELECT 
      "id",
      ROW_NUMBER() OVER (
        PARTITION BY "checksum"
        ORDER BY "createdAt" ASC
      ) AS rn
    FROM "asset"
  ) sub
  WHERE rn > 1
);

you can test it with this though

SELECT "id", "checksum", "createdAt", "originalFileName"
FROM (
  SELECT 
    "id",
    "checksum",
    "createdAt",
    "originalFileName",
    ROW_NUMBER() OVER (
      PARTITION BY "checksum"
      ORDER BY "createdAt" ASC
    ) AS rn
  FROM "asset"
) sub
WHERE rn > 1;

SELECT "id", "checksum", "createdAt", "originalFileName"
FROM (
  SELECT 
    "id",
    "checksum",
    "createdAt",
    "originalFileName",
    ROW_NUMBER() OVER (
      PARTITION BY "checksum"
      ORDER BY "createdAt" ASC
    ) AS rn
  FROM "asset"
) sub
WHERE rn > 1;

I think if this works we can probably use it for each table with problems

glueeaterOP•2w ago

that damn llm this produced 507 rows everything was created on 9/23

jrasm91•2w ago

that seems promising then, I'd run the delete hold on

glueeaterOP•2w ago

oh i ran it concerning?

jrasm91•2w ago

it's fine no worries

glueeaterOP•2w ago

now what

jrasm91•2w ago

I was wondering if we wanted those ids to delete from asset_exif as well and the like, but it's not a big deal try to make the UQ now

CREATE UNIQUE INDEX "UQ_assets_owner_checksum" ON "asset" ("ownerId", "checksum") WHERE ("libraryId" IS NULL); -- missing in target

CREATE UNIQUE INDEX "UQ_assets_owner_checksum" ON "asset" ("ownerId", "checksum") WHERE ("libraryId" IS NULL); -- missing in target

glueeaterOP•2w ago

it worked

jrasm91•2w ago

thank god

glueeaterOP•2w ago

wallahi yeah i ran that migrations.sql thing you asked before nad that line is gone now

jrasm91•2w ago

great pick the next one and run it and show me the error for it

glueeaterOP•2w ago

immich=# ALTER TABLE "smart_search" ADD CONSTRAINT "smart_search_assetId_fkey" FOREIGN KEY ("assetId") REFERENCES "asset" ("id") ON UPDATE NO ACTION ON DELETE CASCADE; -- missing in target
ERROR:  insert or update on table "smart_search" violates foreign key constraint "smart_search_assetId_fkey"
DETAIL:  Key (assetId)=(63c60160-d037-4104-9ddd-4bb6991e4e2d) is not present in table "asset".
immich=#

immich=# ALTER TABLE "smart_search" ADD CONSTRAINT "smart_search_assetId_fkey" FOREIGN KEY ("assetId") REFERENCES "asset" ("id") ON UPDATE NO ACTION ON DELETE CASCADE; -- missing in target
ERROR:  insert or update on table "smart_search" violates foreign key constraint "smart_search_assetId_fkey"
DETAIL:  Key (assetId)=(63c60160-d037-4104-9ddd-4bb6991e4e2d) is not present in table "asset".
immich=#

jrasm91•2w ago

so it is saying that records exist in the smart_search table that are not in the asset table So we just deleted from bad records from the asset table and it's saying those (or other old data) may have been removed, but orphaned rows still exist in that table. This should be easy to fix

glueeaterOP•2w ago

Go on.

jrasm91•2w ago

sec lol

DELETE FROM "smart_search" ss WHERE NOT EXISTS ( SELECT 1 FROM "asset" a WHERE a."id" = ss."assetId" );

DELETE FROM "smart_search" ss WHERE NOT EXISTS ( SELECT 1 FROM "asset" a WHERE a."id" = ss."assetId" );

and then this again

ALTER TABLE "smart_search" ADD CONSTRAINT "smart_search_assetId_fkey" FOREIGN KEY ("assetId") REFERENCES "asset" ("id") ON UPDATE NO ACTION ON DELETE CASCADE;

ALTER TABLE "smart_search" ADD CONSTRAINT "smart_search_assetId_fkey" FOREIGN KEY ("assetId") REFERENCES "asset" ("id") ON UPDATE NO ACTION ON DELETE CASCADE;

glueeaterOP•2w ago

worked linegone

immich=# ALTER TABLE "memory_asset" ADD CONSTRAINT "memory_asset_memoriesId_fkey" FOREIGN KEY ("memoriesId") REFERENCES "memory" ("id") ON UPDATE CASCADE ON DELETE CASCADE; -- missing in target
ERROR:  insert or update on table "memory_asset" violates foreign key constraint "memory_asset_memoriesId_fkey"
DETAIL:  Key (memoriesId)=(163bbb1e-b999-4bb9-9de9-56a811a2eae9) is not present in table "memory".
immich=#

immich=# ALTER TABLE "memory_asset" ADD CONSTRAINT "memory_asset_memoriesId_fkey" FOREIGN KEY ("memoriesId") REFERENCES "memory" ("id") ON UPDATE CASCADE ON DELETE CASCADE; -- missing in target
ERROR:  insert or update on table "memory_asset" violates foreign key constraint "memory_asset_memoriesId_fkey"
DETAIL:  Key (memoriesId)=(163bbb1e-b999-4bb9-9de9-56a811a2eae9) is not present in table "memory".
immich=#

same method?

jrasm91•2w ago

DELETE FROM "memory_asset" b WHERE NOT EXISTS ( SELECT 1 FROM "memory" a WHERE a."id" = b."memoriesId" );

DELETE FROM "memory_asset" b WHERE NOT EXISTS ( SELECT 1 FROM "memory" a WHERE a."id" = b."memoriesId" );

glueeaterOP•2w ago

deleted 536 i ran the command and it removed the line (alter table) how do you come up with this?

immich=# ALTER TABLE "asset_job_status" ADD CONSTRAINT "asset_job_status_assetId_fkey" FOREIGN KEY ("assetId") REFERENCES "asset" ("id") ON UPDATE CASCADE ON DELETE CASCADE; -- missing in target
ERROR:  insert or update on table "asset_job_status" violates foreign key constraint "asset_job_status_assetId_fkey"
DETAIL:  Key (assetId)=(63c60160-d037-4104-9ddd-4bb6991e4e2d) is not present in table "asset".
immich=#

immich=# ALTER TABLE "asset_job_status" ADD CONSTRAINT "asset_job_status_assetId_fkey" FOREIGN KEY ("assetId") REFERENCES "asset" ("id") ON UPDATE CASCADE ON DELETE CASCADE; -- missing in target
ERROR:  insert or update on table "asset_job_status" violates foreign key constraint "asset_job_status_assetId_fkey"
DETAIL:  Key (assetId)=(63c60160-d037-4104-9ddd-4bb6991e4e2d) is not present in table "asset".
immich=#

jrasm91•2w ago

the FK you are trying to add is linking the two tables together, so I am just putting in the right table a, table b, table a column and table b column

DELETE FROM "asset_job_status" b WHERE NOT EXISTS ( SELECT 1 FROM "asset" a WHERE a."id" = b."assetId" );

DELETE FROM "asset_job_status" b WHERE NOT EXISTS ( SELECT 1 FROM "asset" a WHERE a."id" = b."assetId" );

DELETE FROM "asset_file" b WHERE NOT EXISTS ( SELECT 1 FROM "asset" a WHERE a."id" = b."assetId" );

DELETE FROM "asset_file" b WHERE NOT EXISTS ( SELECT 1 FROM "asset" a WHERE a."id" = b."assetId" );

glueeaterOP•2w ago

oh i just replace the word

jrasm91•2w ago

DELETE FROM "asset_exif" b WHERE NOT EXISTS ( SELECT 1 FROM "asset" a WHERE a."id" = b."assetId" );

DELETE FROM "asset_exif" b WHERE NOT EXISTS ( SELECT 1 FROM "asset" a WHERE a."id" = b."assetId" );

DELETE FROM "asset_face" b WHERE NOT EXISTS ( SELECT 1 FROM "asset" a WHERE a."id" = b."assetId" );

DELETE FROM "asset_face" b WHERE NOT EXISTS ( SELECT 1 FROM "asset" a WHERE a."id" = b."assetId" );

glueeaterOP•2w ago

ERROR:  insert or update on table "asset_face" violates foreign key constraint "asset_face_assetId_fkey"
DETAIL:  Key (assetId)=(63c60160-d037-4104-9ddd-4bb6991e4e2d) is not present in table "asset".

ERROR:  insert or update on table "asset_face" violates foreign key constraint "asset_face_assetId_fkey"
DETAIL:  Key (assetId)=(63c60160-d037-4104-9ddd-4bb6991e4e2d) is not present in table "asset".

nvm got it that time

jrasm91•2w ago

yeah I changed the table once do asset_exif and asset_face both

glueeaterOP•2w ago

immich=# ALTER TABLE "album_asset" ADD CONSTRAINT "album_asset_assetsId_fkey" FOREIGN KEY ("assetsId") REFERENCES "asset" ("id") ON UPDATE CASCADE ON DELETE CASCADE; -- missing in target
ERROR:  insert or update on table "album_asset" violates foreign key constraint "album_asset_assetsId_fkey"
DETAIL:  Key (assetsId)=(63c60160-d037-4104-9ddd-4bb6991e4e2d) is not present in table "asset".

immich=# ALTER TABLE "album_asset" ADD CONSTRAINT "album_asset_assetsId_fkey" FOREIGN KEY ("assetsId") REFERENCES "asset" ("id") ON UPDATE CASCADE ON DELETE CASCADE; -- missing in target
ERROR:  insert or update on table "album_asset" violates foreign key constraint "album_asset_assetsId_fkey"
DETAIL:  Key (assetsId)=(63c60160-d037-4104-9ddd-4bb6991e4e2d) is not present in table "asset".

these both worked

jrasm91•2w ago

DELETE FROM "album_asset" b WHERE NOT EXISTS ( SELECT 1 FROM "asset" a WHERE a."id" = b."assetsId" );

DELETE FROM "album_asset" b WHERE NOT EXISTS ( SELECT 1 FROM "asset" a WHERE a."id" = b."assetsId" );

this one is assetsId not assetId btw which ones are left now?

glueeaterOP•2w ago

immich=# ALTER TABLE "memory_asset" ADD CONSTRAINT "memory_asset_pkey" PRIMARY KEY ("memoriesId", "assetsId"); -- missing in target
ERROR:  could not create unique index "memory_asset_pkey"
DETAIL:  Key ("memoriesId", "assetsId")=(3c574b09-357f-4344-8655-acee13409ca2, 0d35d9ff-d150-467f-b622-ae5de4690ac9) is duplicated.

immich=# ALTER TABLE "memory_asset" ADD CONSTRAINT "memory_asset_pkey" PRIMARY KEY ("memoriesId", "assetsId"); -- missing in target
ERROR:  could not create unique index "memory_asset_pkey"
DETAIL:  Key ("memoriesId", "assetsId")=(3c574b09-357f-4344-8655-acee13409ca2, 0d35d9ff-d150-467f-b622-ae5de4690ac9) is duplicated.

ALTER TABLE "asset_exif" ADD CONSTRAINT "asset_exif_pkey" PRIMARY KEY ("assetId"); -- missing in target
ALTER TABLE "asset_job_status" ADD CONSTRAINT "asset_job_status_pkey" PRIMARY KEY ("assetId"); -- missing in target
ALTER TABLE "memory_asset" ADD CONSTRAINT "memory_asset_pkey" PRIMARY KEY ("memoriesId", "assetsId"); -- missing in target

ALTER TABLE "asset_exif" ADD CONSTRAINT "asset_exif_pkey" PRIMARY KEY ("assetId"); -- missing in target
ALTER TABLE "asset_job_status" ADD CONSTRAINT "asset_job_status_pkey" PRIMARY KEY ("assetId"); -- missing in target
ALTER TABLE "memory_asset" ADD CONSTRAINT "memory_asset_pkey" PRIMARY KEY ("memoriesId", "assetsId"); -- missing in target

jrasm91•2w ago

only 3? nice

glueeaterOP•2w ago

yeah magic

jrasm91•2w ago

hmmk actually do you even care about memories? alright, the same memory_asset record exists multiple times, similar to what we did with assets

glueeaterOP•2w ago

they're kinda cool i actually use them these days

jrasm91•2w ago

ok we can fix it

glueeaterOP•2w ago

i went from disliking them to liking them after seeing my kids

jrasm91•2w ago

haha

DELETE FROM "memory_asset"
WHERE ("memoriesId", "assetsId") IN (
  SELECT "memoriesId", "assetsId"
  FROM (
    SELECT 
      "memoriesId", "assetsId",
      ROW_NUMBER() OVER (
        PARTITION BY "memoriesId", "assetsId"
        ORDER BY "createdAt" ASC
      ) AS rn
    FROM "memory_asset"
  ) sub
  WHERE rn > 1
);

DELETE FROM "memory_asset"
WHERE ("memoriesId", "assetsId") IN (
  SELECT "memoriesId", "assetsId"
  FROM (
    SELECT 
      "memoriesId", "assetsId",
      ROW_NUMBER() OVER (
        PARTITION BY "memoriesId", "assetsId"
        ORDER BY "createdAt" ASC
      ) AS rn
    FROM "memory_asset"
  ) sub
  WHERE rn > 1
);

glueeaterOP•2w ago

delete 5734 lol it worked

jrasm91•2w ago

SELECT count(*) FROM "memory_asset";

SELECT count(*) FROM "memory_asset";

glueeaterOP•2w ago

Count 222

jrasm91•2w ago

ok so you still have memories at least lol 222 sounds more realistic I think lol

glueeaterOP•2w ago

immich=# ALTER TABLE "asset_job_status" ADD CONSTRAINT "asset_job_status_pkey" PRIMARY KEY ("assetId"); -- missing in target
ERROR:  could not create unique index "asset_job_status_pkey"
DETAIL:  Key ("assetId")=(9b34437b-03b5-4742-8792-99549065b34b) is duplicated.

immich=# ALTER TABLE "asset_job_status" ADD CONSTRAINT "asset_job_status_pkey" PRIMARY KEY ("assetId"); -- missing in target
ERROR:  could not create unique index "asset_job_status_pkey"
DETAIL:  Key ("assetId")=(9b34437b-03b5-4742-8792-99549065b34b) is duplicated.

jrasm91•2w ago

ok, same issue with duplicate keys I guess

glueeaterOP•2w ago

immich=# ALTER TABLE "asset_exif" ADD CONSTRAINT "asset_exif_pkey" PRIMARY KEY ("assetId"); -- missing in target
ERROR:  could not create unique index "asset_exif_pkey"
DETAIL:  Key ("assetId")=(9200112b-c346-4184-8a0b-fcbbbe594bbe) is duplicated.
CONTEXT:  parallel worker

immich=# ALTER TABLE "asset_exif" ADD CONSTRAINT "asset_exif_pkey" PRIMARY KEY ("assetId"); -- missing in target
ERROR:  could not create unique index "asset_exif_pkey"
DETAIL:  Key ("assetId")=(9200112b-c346-4184-8a0b-fcbbbe594bbe) is duplicated.
CONTEXT:  parallel worker

Same for this last one?

jrasm91•2w ago

well the asset_job_status doesn't have a createdAt column it has 5 date columns for each job basically It doesn't really matter. maybe let's verify each one has metadataCreatedAt though

glueeaterOP•2w ago

hokay, gotta take a break to feed small human and i can come back

jrasm91•2w ago

no problem!

glueeaterOP•2w ago

thx for all the help sofar! hope it fixes it

jrasm91•2w ago

No problem. When you get back, try to run this and let me know if metadataExtractedAt is null for any of the records.

SELECT "assetId", "metadataExtractedAt" FROM "asset_job_status" WHERE "assetId" IN (SELECT "assetId" FROM "asset_job_status" GROUP BY "assetId" HAVING COUNT(1) > 1) ORDER BY "assetId", "metadataExtractedAt";

SELECT "assetId", "metadataExtractedAt" FROM "asset_job_status" WHERE "assetId" IN (SELECT "assetId" FROM "asset_job_status" GROUP BY "assetId" HAVING COUNT(1) > 1) ORDER BY "assetId", "metadataExtractedAt";

glueeaterOP•2w ago

no null

glueeaterOP•2w ago

here

message.txt

jrasm91•2w ago

the ones without a date in the second column are null we probably only want to keep the ones with dates tbh

glueeaterOP•2w ago

Oh.

jrasm91•2w ago

is it actually null or is it an empty string...

glueeaterOP•2w ago

It’s more rows than I could copy bc idk how to use termius Uhhh I copy pasted

jrasm91•2w ago

SELECT "assetId", "metadataExtractedAt" FROM "asset_job_status" WHERE "assetId" IN (SELECT COUNT(1), "assetId" FROM "asset_job_status" GROUP BY "assetId" HAVING COUNT(1) > 1) AND "metadataExtractedAt" IS NULL;

SELECT "assetId", "metadataExtractedAt" FROM "asset_job_status" WHERE "assetId" IN (SELECT COUNT(1), "assetId" FROM "asset_job_status" GROUP BY "assetId" HAVING COUNT(1) > 1) AND "metadataExtractedAt" IS NULL;

woops sec

SELECT "assetId", "metadataExtractedAt" FROM "asset_job_status" WHERE "assetId" IN (SELECT "assetId" FROM "asset_job_status" GROUP BY "assetId" HAVING COUNT(1) > 1) AND "metadataExtractedAt" IS NULL;

SELECT "assetId", "metadataExtractedAt" FROM "asset_job_status" WHERE "assetId" IN (SELECT "assetId" FROM "asset_job_status" GROUP BY "assetId" HAVING COUNT(1) > 1) AND "metadataExtractedAt" IS NULL;

Assuming that still returns rows, you can run this:

DELETE FROM "asset_job_status" WHERE "assetId" IN (SELECT "assetId" FROM "asset_job_status" GROUP BY "assetId" HAVING COUNT(1) > 1) AND "metadataExtractedAt" IS NULL;

DELETE FROM "asset_job_status" WHERE "assetId" IN (SELECT "assetId" FROM "asset_job_status" GROUP BY "assetId" HAVING COUNT(1) > 1) AND "metadataExtractedAt" IS NULL;

glueeaterOP•2w ago

returned 123

jrasm91•2w ago

yeah, run the delete

glueeaterOP•2w ago

Final boss

immich=# ALTER TABLE "asset_exif" ADD CONSTRAINT "asset_exif_pkey" PRIMARY KEY ("assetId"); -- missing in target
ERROR:  could not create unique index "asset_exif_pkey"
DETAIL:  Key ("assetId")=(ea43db3c-4fb9-46fe-a14f-b833e9e4c4cb) is duplicated.

immich=# ALTER TABLE "asset_exif" ADD CONSTRAINT "asset_exif_pkey" PRIMARY KEY ("assetId"); -- missing in target
ERROR:  could not create unique index "asset_exif_pkey"
DETAIL:  Key ("assetId")=(ea43db3c-4fb9-46fe-a14f-b833e9e4c4cb) is duplicated.

jrasm91•2w ago

last one?

glueeaterOP•2w ago

yeep 🫂

jrasm91•2w ago

I think we just delete the second row, since there's no date column on the table and the data should be identical anyways

DELETE FROM "asset_exif"
WHERE "assetId" IN (
  SELECT "assetId"
  FROM (
    SELECT 
      "assetId",
      ROW_NUMBER() OVER (
        PARTITION BY "assetId"
      ) AS rn
    FROM "asset_exif"
  ) sub
  WHERE rn > 1
);

DELETE FROM "asset_exif"
WHERE "assetId" IN (
  SELECT "assetId"
  FROM (
    SELECT 
      "assetId",
      ROW_NUMBER() OVER (
        PARTITION BY "assetId"
      ) AS rn
    FROM "asset_exif"
  ) sub
  WHERE rn > 1
);

glueeaterOP•2w ago

Small fluffy animal needs bathroom now How did this all happen btw?

jrasm91•2w ago

I have no idea, but it is quite concerning, especially if this has happened to you multiple ties now. Definitely take a database backup immediately after you get this issue resolved

glueeaterOP•2w ago

delete 120 migrations sql empty doing a backup now i have a feeling its related to "new timeline" btw bc thats something i did attempt to do on 9/23 i tried enabling new timeline hmm…its missing a bunch of images that are backed up

jrasm91•2w ago

hmm It came up at least though?

glueeaterOP•2w ago

so its really odd - my desktop (no local photos) only showing immich images from last thursday my mobile device (where the photos are from) is showing all recent images w/ a check mark sorry not all, but a bunch i've got a lot of ! images too hmm i pressed start backup (on old timeline)

jrasm91•2w ago

Ah, I think I see the problem

glueeaterOP•2w ago

the immich_server logs are clean (before they threw errors)

jrasm91•2w ago

how many are missing thumbnails? over 100? I'm guessing just the most recent assets are missing them. Is that correct?

glueeaterOP•2w ago

Prob less than 50 (they are most recent)

jrasm91•2w ago

yeah, there was a slight issue with sql to clean up duplciate ids, which we ran on memory_asset and asset_exif that deleted both records, instead of just one (I don't even know how to fix this problem when the primary key itself is duplicated) Since it's just asset_exif (which I'm pretty sure is affecting the query for the thumbnail), we can regenerate metadata for those

glueeaterOP•2w ago

But that doesn’t solve why it’s not showing any assets after last Thursday tho? On the web client

jrasm91•2w ago

Just select the most recent picture on the web, scroll down until you are past all the broken thumbnails and shift select another one, then use the bulk actions to run "Refresh Metadata"

glueeaterOP•2w ago

I just run this in admin jobs The most recent web pic doesn’t include the no thumbnail on mobile

jrasm91•2w ago

oh I see let me check something dang, yeah. the timeline on the web won't show anything without exif records and we accidentally deleted like 100 records or so if you run metadata extraction on everything that will fix itself

glueeaterOP•2w ago

hmm

glueeaterOP•2w ago

thats a bigger number

jrasm91•2w ago

100k assets? I thought you only had like 30k lol

glueeaterOP•2w ago

look man maybe i lied

glueeaterOP•2w ago

jrasm91•2w ago

I hope it's not 30k duplicated

glueeaterOP•2w ago

the disk usage is close to being correct which is whats confusing to me i assumed if it duplicated, it'd be double the space its off by maybe…30-50gb max

jrasm91•2w ago

we deleted about 500 images that were uploaded again those records in the database were removed, but the original is still going to be there on disk, there's not a super easy way to fix that atm we're hoping to build out some features in the future which will make it easy to detect orphan files and clean them up though

glueeaterOP•2w ago

its making my cpu happy

jrasm91•2w ago

lol I have no idea if it starts at the beginning or the end

glueeaterOP•2w ago

its ok i might need to sleep on it while the job runs should i try to enable new timeline or should i wait

jrasm91•2w ago

I'd leave the mobile app alone for a bit

glueeaterOP•2w ago

ok i'll let this server cook

jrasm91•2w ago

Let the jobs finish, verify everything is fixed, take a dump, and then turn it on Is your database server on a drive on the host or a network share?

glueeaterOP•2w ago

host

jrasm91•2w ago

SSD?

glueeaterOP•2w ago

nvme yep

jrasm91•2w ago

This has happened twice already?

glueeaterOP•2w ago

uh the other time was way easier to fix in that i did whatever is in that github issues page and it was resolved

jrasm91•2w ago

did you run the script to generate the diff or just fix the error to get it to start?

glueeaterOP•2w ago

uh i just fixed the error i think if you follow the chronology in that thread, i just did literally whatever was posted first and then whenever it started working i moved on with life and here we are

jrasm91•2w ago

yeah makes sense

glueeaterOP•2w ago

so i probably put a bandaid on this bigger problem

jrasm91•2w ago

so those foreign keys and indexes might have always been missing ever after you fixed the one that was causing a problem originally that'd be less concerning tbh which thread was it again? I want to see if the same constraint came up again or not

glueeaterOP•2w ago

https://github.com/immich-app/immich/discussions/20530

Immich•2w ago

[Discussion] PostgresError: constraint "FK_c0117fdbc50b917ef9067740c44" for table "asset_exif" does not exist (immich-app/immich#20530)

jrasm91•2w ago

FK_c0117fdbc50b917ef9067740c44 was missing? that was renamed to asset_exif_assetId_fkey which you had a problem with today

glueeaterOP•2w ago

https://github.com/immich-app/immich/discussions/20530#discussioncomment-13959523

Immich•2w ago

[Discussion] PostgresError: constraint "FK_c0117fdbc50b917ef9067740c44" for table "asset_exif" does not exist (immich-app/immich#20530)

glueeaterOP•2w ago

this has the schema (the original post is not mine i just piled in bc similar)

jrasm91•2w ago

https://github.com/immich-app/immich/discussions/20530#discussioncomment-13959525

glueeaterOP•2w ago

ok its not fast, gonna need 2 sleep and i'll report back

Immich•2w ago

[Discussion] PostgresError: constraint "FK_c0117fdbc50b917ef9067740c44" for table "asset_exif" does not exist (immich-app/immich#20530)

jrasm91•2w ago

yeah, looks like you fixed these 3

glueeaterOP•2w ago

and there was just many more ?

jrasm91•2w ago

That particular migration made some database changes and the changes failed without the constraints/indexes being there Basically we ran SQL that uncovered specific problems

glueeaterOP•2w ago

I see - this is very fun, i hope it helps someone else.

jrasm91•2w ago

But those problems were different than the ones today, so it still could have been a single event that happened sometime before that Anyways, I would probably recommend running that diff command periodically to make sure stuff is still there Let me know tomorrow if you are still having issues! Have a good night!

glueeaterOP•2w ago

from overnight logs

message.txt

glueeaterOP•2w ago

Pretty much all new assets (not in immich) are showing no thumbnail in the mobile app now Mobile app is now being excruiciatingly slow (even after pressing start backup, nothing is happening)

glueeaterOP•2w ago

took out lines w/ albums

message.txt

glueeaterOP•2w ago

so the mobile app is still showing broken thumbnails for many assets, but at least web client is now showing up to Saturday. Missing Sunday to Wednesday now… Again, the strnage thing is mobile app shows the asset is backed up (check mark) but the assets dont appear in web

jrasm91•2w ago

I'd try the beta timeline now

glueeaterOP•2w ago

Checked the logs bc the thumbnails were still broken #0 _futurize (dart:ui/painting.dart:7991) #1 ImageDescriptor.encoded (dart:ui/painting.dart:7785) #2 instantiateImageCodecWithSize (dart:ui/painting.dart:2558) #3 PaintingBinding.instantiateImageCodecWithSize (package:flutter/src/painting/binding.dart:147) #4 RemoteImageRequest._decodeBuffer (package:immich_mobile/infrastructure/loaders/remote_image_request.dart:164) #5 RemoteImageRequest.load (package:immich_mobile/infrastructure/loaders/remote_image_request.dart:32) <asynchronous suspension> #6 CancellableImageProviderMixin.loadRequest (package:immich_mobile/presentation/widgets/images/image_provider.dart:59) <asynchronous suspension> #7 ImageStreamCompleter.setImage (package:flutter/src/painting/image_stream.dart:724) <asynchronous suspension> oh okay uploading is now working in new timeline the logs are relatively clean

glueeaterOP•2w ago

It’s just stuck here

jrasm91•2w ago

can you click view details?

glueeaterOP•2w ago

No uploads in progress

jrasm91•2w ago

is your mobile app on 1.143.1? It might be backing up still and the ui is just not updating, or, it might be frozen and force-closing and reopening the app might fix it.

glueeaterOP•2w ago

just got update same behavior assets in view details are backed up tho Ok noticing a pattern on a few. They’re videos that are short but the app is showing them as .HEIC Also the web client seems to be not showing video backups between Sunday and Wednesday

jrasm91•2w ago

videos might be done last they also might be live photos just wait until everything is backed up and then see if there is still an issue between mobile and web

glueeaterOP•2w ago

but the app is showing check marks on stuff, doesnt that mean backed up? I have way fewer broken thumbnails now, but the web client just isn't showing some stuff OK weird behavior… I saved 3 videos from a different device (my wife took them and sent them to me) - those uploaded fine I took a new vidoe now - it's stuck in the "Remainder" count - now at 136 instead of 135. And my video is definitely NOT backed up, but the one's i saved from my wife are Her videos have a check mark, my video has a crossed out cloud

glueeaterOP•2w ago

logs attached

message.txt

glueeaterOP•2w ago

for completeness

glueeaterOP•2w ago

spotted in my logs just now

message.txt

glueeaterOP•2w ago

OK - narrowing in on its the New Timeline… I just switched back (disabled New Timeline) and the new items uploaded (no idea about those other 135) Old Timeline still has a bunch of broken Thumbnails (that were fixed in New Timeline) hope this helps w/e going on w/ new timeline i just want uploads to work (lol) - so its okay if i dont have New Timeline for now No database drift by switching between (just wanted to confirm)

glueeaterOP•2w ago

Btw on old timeline shows 30k duplicate assets (idk why)

glueeaterOP•2w ago

sept25_immich_postgr...

glueeaterOP•2w ago

Adding to the weird behavior - I now have many recents folders with various dates

jrasm91•2w ago

So some of the problems might have been caused by the broken constraints, which let you upload duplicate images and we've deleted some of those It might be worth logging out and back in

glueeaterOP•2w ago

already tried, took those logs/screenshots after wards

jrasm91•2w ago

54bb8e76-396e-433e-a188-6a2d1a029c2e/54/92/54920d1d-f374-4165-aecb-63eb4071af7c.MOV It might be worth navigating to /photos/54920d1d-f374-4165-aecb-63eb4071af7c on the web You seem to have 2 different issues: 1. Files not uploading from phone 2. Broken thumbnails corresponding to files uploaded to the server that aren't actually there

glueeaterOP•2w ago

Not found or no asset.read access (400)
Stacktrace

ct@http://192.168.50.119:2283/_app/immutable/chunks/9kRnNcW7.js:1:5505
rt@http://192.168.50.119:2283/_app/immutable/chunks/9kRnNcW7.js:1:5409

Not found or no asset.read access (400)
Stacktrace

ct@http://192.168.50.119:2283/_app/immutable/chunks/9kRnNcW7.js:1:5505
rt@http://192.168.50.119:2283/_app/immutable/chunks/9kRnNcW7.js:1:5409

Files upload from phone on Old Timeline, but do not upload from phone on New Timeline Also, Thumbnails are broken on New Timeline, but functional on Old Timeline

jrasm91•2w ago

This just may be open issues with the new timeline then Is anything still broken on the web?

glueeaterOP•2w ago

web client still is not showing any images from Sunday to Wednesday, but is showing all new images/videos

jrasm91•2w ago

Sounds like the old timeline is also broken in a different way then as it's not backing up those pictures Are those the pictures the new timeline refuses to upload?

glueeaterOP•2w ago

it doesnt appear to be related to that. New Timeline shows those 135 assets (mix of recent, old and very old) that won't upload. Then any new assets (after the 135) won't upload at all. New Timeline makes all the broken thumbnails appear as normal

jrasm91•2w ago

So they are failing to backup on both timelines?

glueeaterOP•2w ago

No Old Timeline succeeds at backing up the newest assets (the 135 are still a mystery to me, they're backed up - they've been backed up) but they show in new timeline as in queue

jrasm91•2w ago

It sounds like they are not actually backed up, especially if you cannot find them on the web

glueeaterOP•2w ago

No no - the assets not showing on web are specifically just assets uploaded on the date of this past sunday to wednesday the mysterious 135 assets do show up in web

jrasm91•2w ago

Ok I don't really care about the old timeline at all There are 135 assets that are not showing up on the web And a different set of assets not having thumbnails on the timeline?

glueeaterOP•2w ago

the 135 assets (that new timeline has in queue) don't seem to have an issue; i'm going to switch one more time and see if i can triple check this

jrasm91•2w ago

It would be good to figure out why they are not progressing

glueeaterOP•2w ago

what steps can i take to figure out why i tried a bunch of intuitive things like signing out lol

jrasm91•2w ago

Just the logs honestly

glueeaterOP•2w ago

ok so i checked multiple images - they common issue with many of the remainder items is that they're 2ish second videos uploaded as *.MOV BUT the Remainder logs shows *.HEIC the really common piece is video length 0:02

jrasm91•2w ago

sounds like live photos do you have an iphone?

glueeaterOP•2w ago

they are actual videos ya its an iphone but i know for sure, these are short videos

jrasm91•2w ago

do you take live photos?

glueeaterOP•2w ago

i take intentionally short videos (and live photos)

jrasm91•2w ago

and these are definitely not live photos?

glueeaterOP•2w ago

yep they're definitely actual videos (the files in the server are MOV) but the "Remainder" log in mobile app for some reason is showing HEIC

jrasm91•2w ago

can you send a screenshot?

glueeaterOP•2w ago

all are 0:02

glueeaterOP•2w ago

jrasm91•2w ago

Looks good. And there is nothing recent in the logs that would indicate why videos are not being backed up?

glueeaterOP•2w ago

message.txt

shenlong-tanwen•2w ago

2025-09-25 13:53:43.405863 | warning  | StorageRepository    | Error getting motion file for asset 7BC5C3A5-CC74-4771-BBC5-2234E79DED2A/L0/001, name: IMG_1449.JPG, created on: 2024-12-21 14:13:13.000 | PlatformException(PHPhotosErrorDomain (3169), The operation couldn’t be completed. (PHPhotosErrorDomain error 3169.), No failure reason provided, null)

3169 is network error, presumably, the asset is on iCloud and the app is struggling to download it. From the error, this also looks like a live photo. Can you find the asset on the photos app using the metadata from the above log and check if you can share it from there?

glueeaterOP•2w ago

is there any way to search immich for file name like this

shenlong-tanwen•2w ago

Oh you've to search the file on your iOS Photos app, not within Immich You can use the date to narrow it down

glueeaterOP•2w ago

i cannot even find this file name ot save my life

shenlong-tanwen•2w ago

Can you locate the asset inside Immich using the "View in timeline" button from the Remainder list?

glueeaterOP•2w ago

oh i see now in the remainder list, it's in a "Shared Icloud" album

shenlong-tanwen•2w ago

You can enable Advanced Troubleshooting in the app settings, and once you have the asset, long press it, tap the "Advanced Info" button in the bottom sheet that opens. Should give you more metadata about the asset and the album that it is a part of

glueeaterOP•2w ago

aka one i dont own directly, but i have access to

shenlong-tanwen•2w ago

From the Photos app, Can you see if you can share / access the asset?

glueeaterOP•2w ago

Yeah i can see it

shenlong-tanwen•2w ago

Can you play the live video as well? Try sharing it from the Photos app and see if the "Preparing" dialog ever finishes

glueeaterOP•2w ago

It plays!

shenlong-tanwen•2w ago

If it plays now, Can you try going into the Immich app, and toggle the backup button and see if the upload queues gets cleared

glueeaterOP•2w ago

still stuck

shenlong-tanwen•2w ago

Would you mind sharing the app logs again? Just want to confirm if it is the same error

glueeaterOP•2w ago

message.txt

shenlong-tanwen•2w ago

Hmm, odd. The same errors. Also, the reason that this isn't showing up on the old timeline could be due to the fact that the old timeline logs these errors and moves on without processing them

glueeaterOP•2w ago

lmk i had to step away for a while today in zoomland im considering going thru each 135 asset and deleting them lmao its working to decrease the remainer by eliminating the asset. the strange part is many of these assets have a duplicate that is uploaded OK so i removed "Shared iCloud Albums" and upload started workign again im gonna try adding them back OK i found the issue the Shared iCloud assets are already uploaded or look to be but if I open the asset (and play video) i can "click Upload" and force the upload and the asset moves on its almost like you can just force it to try again and it works Okay I just removed the iCloud shared album and uploads are fine from local recents

jrasm91•2w ago

Nice!

glueeaterOP•2w ago

id like shared icloud to work; only one album is having issues would be nice to force continue I think we actually deleted memories I probably had thousands bc I have an image or video or more everyday for the last 13 years

jrasm91•2w ago

We can regenerate them easily enough if you want. In any case we only generate memories 3 days in advance, so they will fix themselves probably day after tomorrow https://github.com/immich-app/immich/issues/16484#issuecomment-2692718993

Immich•2w ago

[Issue] Multiple Memories at top (immich-app/immich#16484)

jrasm91•2w ago

Just replace memories with memory and it should work

shenlong-tanwen•2w ago

Once the upload goes through all the assets from the Recents album, you can try adding the shared album as well

glueeaterOP•2w ago

I tried it’s such on 8 assets now that I can’t force upload No log errors either Just literally won’t upload

shenlong-tanwen•2w ago

Are those the ones you’ve received from a different iCloud users or are they your own assets but on a shared album?

glueeaterOP•2w ago

They're a shared album owned by someone else that i joined (that i contribute to) but the specific assets are owned by someone else and uploaded by someone else The trickier thing might also be some items I took the picture (thus are in my recents) but sent to someone else who then uploaded to said album

shenlong-tanwen•2w ago

I see, Thanks for the info! Can you download the problematic assets from the shared album to your device and try uploading them from there?

glueeaterOP•2w ago

1) I take picture 2) I send to waifu 3) Waifu uploads to shared album 4) Immich confused

shenlong-tanwen•2w ago

Or through the web, as long as it is the same asset and the checksums match, the mobile app will skip them even if they are uploaded through other means

glueeaterOP•2w ago

ok i can…attempt…its really weird bc i can open the asset on Immich mobile but for these specific ones when i press upload literally nothing happens

glueeaterOP•2w ago

https://tenor.com/view/nothing-ever-happens-chud-chudjak-soyjak-90-seconds-to-nothing-gif-9277709574191520604

Tenor

shenlong-tanwen•2w ago

All of them seems to be a live photo. When you open them, you're viewing the photo part of it. The way the live photo upload works is to first send the video part of the asset followed by the photo part. And since we have trouble accessing the video part, it sits there without doing anything

glueeaterOP•2w ago

YO I airdropped from imich mobile app and uploaded via web and we have moved on.

shenlong-tanwen•2w ago

:ppParty:

glueeaterOP•2w ago

its so fucking hard to find the photo tho - the little icons are like a scavenger hunt wallahi my brothers we have fixed it

glueeaterOP•2w ago

a support team of devs hates to see it

shenlong-tanwen•2w ago

No problem lol! Glad we sorted things out 😃

jrasm91•2w ago

wait, so what was the issue then? cannot access motion parts of a live photo from the iCloud shared album? we need to fix this though, that should not block/prevent other uploads from progressing. do we at least skip them now? maybe we should at least add something to the logs when this happens so the user is aware of what is going on

shenlong-tanwen•2w ago

We do have the logs. But we might not be skipping them Yep, the actual error is thrown from iOS side, so there's not really much we can do other than asking the user to upload it through the web We'll should definitely address the blocking issue though!

jrasm91•2w ago

yeah, the blocking issue, and also feedback as what's happening or why it's skipped

glueeaterOP•2w ago

Oh I see I completely missed this in the excitement of fixing it

glueeaterOP•2w ago

https://tenor.com/view/im-doing-my-part-serious-stare-gif-11979677

Tenor

glueeaterOP•2w ago

tbf i'd accept a toggle that says backing up live photos may result in only still images in edge cases idc that much, i do love a good live photo

shenlong-tanwen•2w ago

The airdropped one did retain the motion part of the image as well, right?

glueeaterOP•2w ago

Yep cuz they’re heic I’m pretty sure I deleted them and they were already backed up so idk Don’t make me look again I have the memory of a goldfish

shenlong-tanwen•2w ago

lol alright

glueeaterOP•2w ago

It doesn’t explain the other weird MOV ones Where remainder claimed the file was HEIC But the file was indeed a 0:02 video That was a bizarre case I swear to you I never change Live Photo to video

shenlong-tanwen•2w ago

You still have those in the remainder? Or do they come back if you add the shared album to the backup list?

glueeaterOP•2w ago

I removed them from device

shenlong-tanwen•2w ago

Ah, I see :monkaHmm:

glueeaterOP•2w ago

They were local files In recents I have a feeling however you’re looking for heic/videos it’s looking for a specified length and I just happen to have that dog in me to record the exact length of a Live Photo

shenlong-tanwen•2w ago

Were you able to play the videos through the app though?

glueeaterOP•2w ago

Yup they play Remainder just kept saying hey there’s an heic file I can’t upload And I was like It’s MOV and here we are This error specifically I just saw the resolution And now I’m not even confident they were videos Wtf I hate it here Anyway my solution for local files was just to delete the file So I can’t even tell you now But what you said wrt Live Photo motion makes sense

shenlong-tanwen•2w ago

Feel free to ping me if you have anything weird with the mobile app

glueeaterOP•2w ago

It just randomly started backing up 419 assets that were already backed up

glueeaterOP•2w ago

Doesn’t look like it’s getting stuck OhNOOOOOO it’s telling me the same assets as last time haven’t uploaded again

shenlong-tanwen•2w ago

Are these the same assets? Can you close and reopen the app once and share a screenshot of the backup page

glueeaterOP•2w ago

Yeah they look like the assets I “fixed” before

glueeaterOP•2w ago

Okay another complexity. An asset exists in two different shared albums And it seems that’s tripping it up 1) I take video 2) send to Waifu 3) Waifu uploads to Album A 4) Waifu also uploads to album B 5) I attempt to backup both albums 6) Immich sad I can’t tell why (after doing nothing) it’s asking these be uploaded again? Just to throw it out - some assets are JPGs (not Live Photos) that are getting suck Inside Recents (so local file) but probably sitting in iCloud bc of “optimized” storage

shenlong-tanwen•2w ago

Wait, Can you update your app to the latest version?

glueeaterOP•2w ago

143.1?

shenlong-tanwen•2w ago

The latest one has a new UI that shows the number of assets that are being processed and those that have been processed Yep

glueeaterOP•2w ago

i am on oh yeah i saw that - they are on the right side of the UI already instead of "preparing"

shenlong-tanwen•2w ago

I think it is displayed only when hashing is in progress, so if it is not being rendered, then we can assume that everything is hashed Let's try this then, do you have the advanced troubleshooting enabled?

glueeaterOP•2w ago

i can its enabled

shenlong-tanwen•2w ago

Before checking this, does the timeline display two different thumbnails for the same asset? Can you find any duplicates there?

glueeaterOP•2w ago

yeah i can all the ones that are "stuck" in remainder are duplicates in that the file is already uploaded or a version i guess

shenlong-tanwen•2w ago

Then can you share the screenshot of the advanced info page of the one that is being tried to be uploaded and the one that is already on the server?

glueeaterOP•2w ago

oh no some voodoo just happened and everything just uploaded

shenlong-tanwen•2w ago

🪄

glueeaterOP•2w ago

it went from 31 to 0

shenlong-tanwen•2w ago

but you should still be having duplicates on the timeline, right?

glueeaterOP•2w ago

no once i upload or delete the problematic asset it stops being a duplicate

shenlong-tanwen•2w ago

It doesn't make sense lol, for it to stop being a duplicate, it has to have the same checksum, and if it has the same checksum as the one on the server, it should not be in the upload list at all, since the server already has it

glueeaterOP•2w ago

you're telling me boss

shenlong-tanwen•2w ago

If at all this happens the next time, Grab a screenshot of the Remainders page, as well as the advanced info page of both the assets

glueeaterOP•2w ago

shenlong-tanwen•2w ago

It might help us troubleshoot what's happening with your device. For completeness sake, Your server is also on 143.1, right?

glueeaterOP•2w ago

yup

glueeaterOP•2w ago

Same asset I ain’t time traveling 7BC5C3A5-CC74-4771-BBC5-2234E79DED2A_L0_001_1758825782.100549_o_IMG_4289 full file name after airdrop okay its definitely duplicated in my library now tremendous i love it. OH I remember why it might have done the ask for the assets that got blocked. I used deduplicate on the web app So I bet I removed the problem assets Yeah it was definitely this deduping I just checked duplicate tool - and its showing 300 duplicates, i bet if i delete these - i'll have 31 blocked assets agai Took me a whileto realize - you should somehow account for the checksum of a deduped file then tell mobile app to pls not attempt reupload if its been deduped

shenlong-tanwen•2w ago

Oh yeah, we still have this behaviour where the app re-uploads stuffs after being deleted from the web. We’d have better handling in the future, but the way to workaround this is to also remove the assets from the device after deleting from the web app

glueeaterOP•2w ago

But impossible with shared iCloud bc I don’t own the asset

shenlong-tanwen•2w ago

That is true. Probably the best way to go about this is to let the app upload all the assets from the shared album, then remove it from the backups so they won't be retried. But then when you want to upload new assets from the album, you can go to the Library tab -> On this device -> Shared album, select the new assets manually and do a manual upload of them My workaround would work on Android or your own assets, but sadly, might not be a solution for assets from the shared albums

glueeaterOP•2w ago

Wow guys new timeline is very good when it works for me now

Gaming

Programming

Api:ErrorInterceptor~tza2l33l] Unknown error: PostgresError: there is no unique or exclusion constra

Did you find this page helpful?