frankf Comments - Answer Overflow

After looking at the ERD in pgAdmin 4 and backing up my database and a test restore to another server, I went ahead and did this: #Verify the problem children - this returned originally 7985 rows now returns 29 rows select * from assets where "createdAt" between '2025-4-15 12:00:00' and '2025-4-15 23:59:59'; #create a temp table to hold the ids of the asset stacks I want to delete create table temp_asset_stack as select b."id", b."primaryAssetId" from assets a inner join asset_stack b on a."stackId"=b."id" where a."createdAt" between '2025-4-15 12:00:00' and '2025-4-15 23:59:59'; #Now actually delete the asset_stack rows delete from asset_stack where "id" in (select "id" from temp_asset_stack); Finally I went to one of the asset ids of the 29 errant rows left behind from my original python script to delete via the api, by going to the web and going to http://localhost:2283/photos/89ac19fa-7de5-408f-a5af-25a140a7c9ad and then clicking the delete button on the immich web ui. This kicks off the queued jobs again and it removed all 29 remaining photos. So it appears that to fix the problem in the api, before the call to delete the asset, they just need to delete the asset_stack as where "id"=the assets.stackId and "primaryAssetId"=assets.id. So that it removes the single entry that is related to the asset in the assets table if the stackId in the assets table is not null. This should also solve the problem for Zeus (immich-app/immich#16151)

47 replies

IImmich

•Created by frankf on 5/16/2025 in #help-desk-support

Foreign Key Constraint violation on delete?

Duh, I guess I should have looked in pgadmin explorer first. I'm used to MSSQL, MySQL and Oracle but new to Postgres. Took me forever to figure out that column names have to be delineated with "s.

47 replies

IImmich

•Created by frankf on 5/16/2025 in #help-desk-support

Foreign Key Constraint violation on delete?

The data dictionary just documents the relationships between the tables. I'm concerned that if I go into the db and remove the asset_stack entry, it will break something else that I don't know about and creating another instance at this point is fairly painful since my immich instances is about 1.8Tb.

47 replies

IImmich

•Created by frankf on 5/16/2025 in #help-desk-support

Foreign Key Constraint violation on delete?

OK. My situation appears to be exactly the same as the problem you referenced earlier at https://discord.com/channels/979116623879368755/1371145286893965442 . The MOV part of the stack has already been deleted. I suspect that the code allows one part of the stack to be removed and leaves a dangling asset_stack entry.

47 replies

IImmich

•Created by frankf on 5/16/2025 in #help-desk-support

Foreign Key Constraint violation on delete?

Is there a published data dictionary for Immich?

47 replies

IImmich

•Created by frankf on 5/16/2025 in #help-desk-support

Foreign Key Constraint violation on delete?

So when you "fixed" your issue, did you just delete the asset_stack entry where the asset.id=asset_stack.primaryAssetId?

47 replies

IImmich

•Created by frankf on 5/16/2025 in #help-desk-support

Foreign Key Constraint violation on delete?

I'm happy to report it if you gave me some pointers as to where?

47 replies

IImmich

•Created by frankf on 5/16/2025 in #help-desk-support

Foreign Key Constraint violation on delete?

Thanks!

47 replies

IImmich

•Created by frankf on 5/16/2025 in #help-desk-support

Foreign Key Constraint violation on delete?

I'm a little scared to do that directly in the db as it might lead to other consequences. If I had access to the data dictionary, I googled it but didn't find anything, I'd be a lot more confident that I wasn't breaking anything else. Is there a published DD for Immich?

47 replies

IImmich

•Created by frankf on 5/16/2025 in #help-desk-support

Foreign Key Constraint violation on delete?

I just downed the immich docker service waited for a bit, twirled twice in a circle and then brought immich back up and sadly, or maybe not sadly since immich recovers and just returns to what it was doing before...chruning the 29 deletes over and over again.

47 replies

IImmich

•Created by frankf on 5/16/2025 in #help-desk-support

Foreign Key Constraint violation on delete?

Ya so my immich instance is doing weird stuff. Tailing my docker service logs, I see that it is still trying continuously to delete those 29 records. In the Web UI, when I search for the photos that were deleted but not really, the search doesn't find them. eg When I look for one of the photos that can't be deleted by date. it does not find it. So it's like it's deleted. However, if I go to http://localhost:2283/photos/89ac19fa-7de5-408f-a5af-25a140a7c9ad, it shows up still. I looked in my Trash and that's empty. I clicked the button to delete it and it says that the "Permanently deleted the asset". However, when I clear my cache and try to open the photo again using its id, it still comes up. I can't see what's happening when I do this in real time while tailing the log because it's still churning through the delete queue. Another observation is that all of these are stacks auto detected by immich because it consists of an iPhone jpg or HEIC and an accompanying .MOV file of the same name from Apple's Live Photo thing. Wonder if a dev can give me some pointers to stop my db from continually churning and killing my SSD write cycles in my NAS from the continual logging of the problem. Thanks!

47 replies

IImmich

•Created by frankf on 5/16/2025 in #help-desk-support

Foreign Key Constraint violation on delete?

I looked after the delete queue completed today. I looks like out of my original 7985 delete requests, only 29 assets remain. So it's progress I guess. Now I'll try to delete those 29 manually via the web gui and see what happens... Will report back in a bit.

47 replies

IImmich

•Created by frankf on 5/16/2025 in #help-desk-support

Foreign Key Constraint violation on delete?

continuing... immich_server | Query failed : { immich_server | durationMs: 4.970461999997497, immich_server | error: PostgresError: update or delete on table "assets" violates foreign key constraint "FK_91704e101438fd0653f582426dc" on table "asset_stack" immich_server | at ErrorResponse (/usr/src/app/node_modules/postgres/cjs/src/connection.js:790:26) immich_server | at handle (/usr/src/app/node_modules/postgres/cjs/src/connection.js:476:6) immich_server | at Socket.data (/usr/src/app/node_modules/postgres/cjs/src/connection.js:315:9) immich_server | at Socket.emit (node:events:518:28) immich_server | at addChunk (node:internal/streams/readable:561:12) immich_server | at readableAddChunkPushByteMode (node:internal/streams/readable:512:3) immich_server | at Readable.push (node:internal/streams/readable:392:5) immich_server | at TCP.onStreamRead (node:internal/stream_base_commons:189:23) { I guess my question is. Is this normal? Shouldn't immich's api be deleting the files in an order that prevents the FKs from being violated? Does the asset in question actually get removed at some point or do I need to remove them from another api class before calling /api/assets again? Please and thank you in advance. Frank.

47 replies

Gaming

Programming