Foreign Key Constraint violation on delete?
I'm running immich v1.132.3 on docker. I wanted to delete a bunch of files that I had uploaded into Immich back in April under the wrong user - they were my wife's photos but I uploaded them as me. I wrote some python code to verify and then delete the files: I query the assets table in postgres to get the id of the assets within a specific createdAt date and then I issue a bunch of deletes via the /api/assets/ api.
It was going well, though slowly as one might imagine on a 16Gb ras pi 5 but now I'm following the docker logs and I am seeing a bunch of errors from postgres like this:
immich_server | [Nest] 8 - 05/15/2025, 11:46:51 PM ERROR [Microservices:{"id":"89ac19fa-7de5-408f-a5af-25a140a7c9ad","deleteOnDisk":true}] Unable to run job handler (backgroundTask/asset-deletion): PostgresError: update or delete on table "assets" violates foreign key constraint "FK_91704e101438fd0653f582426dc" on table "asset_stack"
immich_server | 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)
immich_server | [Nest] 8 - 05/15/2025, 11:46:53 PM LOG [Microservices:TrashService] Queued 3949 asset(s) for deletion from the trash
to be continued in next reply...
33 Replies
:wave: Hey @frankf,
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. :blue_square: verified I'm on the latest release(note that mobile app releases may take some time).
2. :blue_square: read applicable release notes.
3. :blue_square: reviewed the FAQs for known issues.
4. :blue_square: reviewed Github for known issues.
5. :blue_square: tried accessing Immich via local ip (without a custom reverse proxy).
6. :blue_square: uploaded the relevant information (see below).
7. :blue_square: 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.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.
oof. i was tinkering around in the DB for persons a few days ago and managed to (I believe) not hose my system. I have been watching and have not seen any errors like that EVER in the past few weeks since I have been using it. adding/removing/images etc to get everything dialed in. So i would say that is NOT normal.
Also, I have only used the API to download and create albums. All my deletes have been in the GUI.
They should switch to domestic key constraints instead.
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.
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!
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.
I'm quite certain this is a bug related to image stacks, if you are comfortable going into the database you can "fix" it https://discord.com/channels/979116623879368755/1371145286893965442
Personally I am just going to avoid using stacks for now
I missed the part about live photos, in my case these were all stacks I created
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?
Not sure but I don't blame you. I have backups so after poking around the database for a bit I was confident enough to yolo it.
I am going to try and replicate the bug today and open an issue on GitHub and maybe a developer who knows more can chime in
Thanks!
I'm happy to report it if you gave me some pointers as to where?
#18223 covers this
[Issue] Stacks not saved (PostgresError: update or delete on table "assets" violates foreign key ) (immich-app/immich#18223)
Actually #16151
So when you "fixed" your issue, did you just delete the asset_stack entry where the asset.id=asset_stack.primaryAssetId?
Open for 3 months 😬
We don’t have any set timeline for issues. If you see a possible patch please feel free to help out 🙂
Is there a published data dictionary for Immich?
I don’t even know what that is haha
We only have the API explorer
I’m not a dev
After I figured out what the problem was I wanted to just unstack everything. So I removed the stack ID (I can't remember the exact column name) for every row and deleted every row in the asset_stack table.
I would love to but unfortunately I know just enough about software to locate problems, not fix them
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.
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.
pgAdmin shows an ER diagram and as far as I could tell it was safe but I am far from a database or Immich expert
So take what I say with the appropriate quantity of salt
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.
Lol yes I spent a long time trying to figure out why my queries weren't working
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)
Try
and 1=1; drop table assets; -- :p
Oh! Let me run and go do that right now. It should fix ALLL my problems!
OK Anyone else, don't do that.
I kid, I kid. But on the first day of on one of my previous jobs I accidentally ran an update without selecting the where clause.
Hope you had a backup.
They did.
I created the missing index in mine, let me see if I can find it