# Uploads not getting orphaned and purged
**URL:** https://meta.discourse.org/t/uploads-not-getting-orphaned-and-purged/259036
**Category:** Support
**Created:** [March 22, 2023, 1:47pm UTC](https://meta.discourse.org/t/uploads-not-getting-orphaned-and-purged/259036 "2023-03-22T13:47:52Z")
**Posts on this page:** 10
**Page:** 1
### Author:  [@Uphill4721](https://meta.discourse.org/u/Uphill4721)
#### Post date: [March 22, 2023, 1:47pm UTC](https://meta.discourse.org/t/uploads-not-getting-orphaned-and-purged/259036/1 "2023-03-22T13:47:53Z")
Hi,
Recently I ended up being the last remaining admin and maintainer of a basic Discourse Docker image instance that was originally installed on our server in 2021 (I think) and mostly updated by someone else. For some time now, possibly right from the start we’ve been having a problem with uploads from soft-deleted posts not getting orphaned and purged and I’ve been trying to troubleshoot this issue again for a few days as the obsolete files keep piling up and wasting storage space. We are not using S3 and there’s plenty enough storage for the uploads that we actually want to keep available.
I’ve migrated the full Discourse backup file including uploads to a separate staging server for testing by rebuilding with our app.yml following the official Discourse Docker installation guides and after that restoring the backup from commandline. Both installations seem to be running identically fine with no other obvious problems, but the upload issue remains.
I can’t seem to find any relevant errors from any logs and Sidekiq is running the cleanup jobs as scheduled. I have run `rake db:migrate` on the staging version and rebuilt many times, tried destroying posts permanently and checking settings. After permanently deleting some posts straight from the rails console and trying to run the cleanup job manually, I noticed that the tombstone directory had grown in size just a little at some point and there was some files to begin with anyways so the mechanism must’ve been working in some situations, right? Judging from the small size increase, almost all of the obsolete files are still not detected as orphans.
Current relevant admin panel settings listed below. Can I set the last ones to 0 to effectively skip the grace periods during testing?
> clean up uploads = true
> clean orphan uploads grace period hours = 1
> purge deleted uploads grace period days = 1
**How can I troubleshoot this efficiently?** I’m comfortable with the commandline but my database skills are rudimentary, so I’d really appreciate some tips to avoid going through every possible server setup detail without a clue about what I’m looking for at this point.
I’ve been desparately searching and reading this forum for similiar cases, but there’s only a few and those threads appear to stop either to a dead end or manual solutions for single files, so not directly suitable for this usecase.
Please ask me for more details if needed, I’m doing my best to solve this for good.
---
### Author:  [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [March 22, 2023, 1:55pm UTC](https://meta.discourse.org/t/uploads-not-getting-orphaned-and-purged/259036/2 "2023-03-22T13:55:49Z")
Hello and welcome @Uphill4721 🙂
I think there’s some relevant info in these topics, if I remember rightly:
> [@How to make an image orphaned so that it gets removed?](https://meta.discourse.org/t/how-to-make-an-image-orphaned-so-that-it-gets-removed/79231):
>
> Because message revisions are kept, and older removed messages are also kept, and even removed topics are still there in database, how do I make an image orphaned so it gets removed automatically by a sidekiq job?
> [@Delete deleted-posts permanently in bulk?](https://meta.discourse.org/t/delete-deleted-posts-permanently-in-bulk/203289/14):
>
> I think the attachments will be cleaned up by one of the daily jobs, as long as they’re not part of the latest version of the post. So changing the deleted post to ‘(deleted by admin)’ or something would make its attachment part of the edit history and would then orphan it (thus scooping it up in the ‘clear orphan’ clean-up). I am only theorising though, so this may be wrong. slightly_smiling_face Source:
---
### Author:  [@Uphill4721](https://meta.discourse.org/u/Uphill4721)
#### Post date: [March 22, 2023, 4:41pm UTC](https://meta.discourse.org/t/uploads-not-getting-orphaned-and-purged/259036/3 "2023-03-22T16:41:08Z")
Thanks for the fast response!
Those topics and a few more linked in them have become quite familiar to me while trying to solve this issue but unfortunately they haven’t provided any definitive solution to this problem.
Yesterday on the staging server I ran these commands modified for topics and posts deleted more than 9 days ago:
> [@Delete deleted-posts permanently in bulk?](https://meta.discourse.org/t/delete-deleted-posts-permanently-in-bulk/203289/57):
>
> Excellent, glad to hear that did the job for you. So for the sake of combining the answers, one can use the following in the Rails console to destroy all topics that were deleted more than 90 days ago, repeating as many times as necessary if there are more than 1000 topics: Topic.with\_deleted.where(deleted\_at: ...90.days.ago).limit(1000).destroy\_all After that has completed, the following can be used to destroy all posts that have been orphaned from destroyed topics: Post.find\_by\_sql("select …
After this I noticed a slight increase in tombstone directory content size and I’m still monitoring the situation due to the grace period, still wondering if changing the relevant settings to zero hours/days would bypass the waiting time during testing.
Earlier on the original server I have tried removing uploads from the newest post revisions but the files were still available after the grace period.
At this point I’d be personally more than delighted to find out any working manual solution for permanently deleting even a single topic with its posts and uploads not referenced anywhere else, but this could be a big problem for other people running Discourse naturally assuming that the cleanup settings in the admin panel would be effective as described but not necessarily noticing if it’s not the case and ending up with potentially sensitive uploads expected to be permanently deleted but in fact staying in the filesystem. Our problem is luckily only considering wasted storage, but for someone else this could be much worse.
There’s another similiar mention just two months back:
[How to Delete Uploaded Files? - #29 by anon98107012](https://meta.discourse.org/t/how-to-delete-uploaded-files/25001/29)
So, any tips on how to figure out if this is a misconfiguration on our end or an actual bug? We’ve been very happy with Discourse otherwise and I’m very motivated to solve this and help others on the way.
---
### Author:  [@Simon\_Manning](https://meta.discourse.org/u/Simon_Manning)
#### Post date: [March 22, 2023, 5:57pm UTC](https://meta.discourse.org/t/uploads-not-getting-orphaned-and-purged/259036/4 "2023-03-22T17:57:15Z")
This is purely speculative but from a quick look at the post, post\_upload and upload models, you can probably find out if you have orphaned uploads (database objects) with this:
```plaintext
Upload.find_by_sql("select * from uploads where id in (select upload_id from post_uploads where post_id not in (select id from posts))")
```
I haven’t tested that so I can’t be sure if it will find orphaned uploads correctly or even execute without error. In case it doesn’t work as-is and someone else might be able to make it work, as well as just for anyone else interested, I’ll break down the intent.
1. `Upload.find_by_sql()` returns a collection of Upload objects that are matched by the provided SQL query.
2. `(select id from posts)` gets all of the IDs for existing posts.
3. `(select upload_id from post_uploads where post_id not in ()` gets all of the IDs for post uploads for which no post exists.
4. `select * from uploads where id in ()` gets all of the uploads matching those post upload IDs.
* * *
That’s just one possible avenue to investigate though, unfortunately I don’t know the upload system well enough to really contribute much otherwise, except to say that the above definitely does _ **not** _ account for all situations. Edited rather than deleted posts being an obvious one.
There are also other types of uploads not accounted for like user uploads which I assume is things like uploading a profile picture.
Plugins can also create and hold on to uploads, I don’t know what happens with those if say the plugin is removed. I think plugin data remains in the database after a plugin is removed which potentially means any uploads created by that plugin are never removed in that situation.
---
### Author:  [@Uphill4721](https://meta.discourse.org/u/Uphill4721)
#### Post date: [March 23, 2023, 7:56am UTC](https://meta.discourse.org/t/uploads-not-getting-orphaned-and-purged/259036/5 "2023-03-23T07:56:27Z")
Thank you for the reply!
The query works but it lists only two uploads and their details. There should be hundreds or thousands of uploads matching the orphan criteria, most are image files originally uploaded by users while making normal posts.
We’re currently using only official plugins:
```plaintext
hooks:
after_code:
- exec:
cd: $home/plugins
cmd:
- git clone https://github.com/discourse/docker_manager.git
- git clone https://github.com/discourse/discourse-chat-integration.git
- git clone https://github.com/discourse/discourse-prometheus.git
- git clone https://github.com/discourse/discourse-bbcode-color
- git clone https://github.com/discourse/discourse-data-explorer
```
There was some sort of an overhaul on the upload process a while after our original installation, wondering if it could be related to our situation somehow: [A new era for file uploads in Discourse](https://meta.discourse.org/t/a-new-era-for-file-uploads-in-discourse/210469)
The grace period should’ve been passed on the staging server by now but I see no effect in the upload directory size and test files are still available. What should I be looking for next? Could this be caused by some faulty filesystem permissions or such, is there an easy way to check? I’m running out of ideas for specific targets, everything else is running great and this is the only problem we currently have.
---
### Author:  [@Uphill4721](https://meta.discourse.org/u/Uphill4721)
#### Post date: [March 25, 2023, 9:30am UTC](https://meta.discourse.org/t/uploads-not-getting-orphaned-and-purged/259036/6 "2023-03-25T09:30:06Z")
Going through similiar topics to gather possibly matching unsolved cases, here’s a good example on how these situations might even cause legal issues due to user uploads not getting orphaned and permanently removed like they should:
> [@I got a copyright strike to PDF file but I'm unable remove it](https://meta.discourse.org/t/i-got-a-copyright-strike-to-pdf-file-but-im-unable-remove-it/242131):
>
> Hello Everyone, I recently received a copyright strike to one PDF file but I am unable to remove it, so please assist me in this process as it is critical to the survival of my server. Regards, Sunder.
Another similiar situation all the way back from 2016:
> [@Is there a way to delete old/irrelevant uploads?](https://meta.discourse.org/t/is-there-a-way-to-delete-old-irrelevant-uploads/49284/10):
>
> My main concern is that I am maintaining and paying for uploads which nobody anymore needs and there isn`t a efficient way to clean up disk space. Even uploads from deleted topics are still kept on disk. All the occasional pornographic pictures and selfies(which users requested to be removed) from more than a year ago are still kept on the disk. I remember one case where one of my users liked Discourse upload feature so much that he attended to upload a couple chapters of comic book scans to the server.
These kinds of conditions create a huge opening for abuse and even targeted attacks for uploading illegal content that might not get permanently removed from the server even when the admins assume it would. Of course deleting single files manually straight from the filesystem is possible, but I don’t think people should be forced to take that route for such a basic need, especially when there’s a GUI setting indicating an automatic purge process and moderators often don’t have direct access to the server anyways. Also manual deletion is not practical with loads of files scattered around in different deleted topics.
**Is here enough basis for an actual bug report?** I’m still not ruling out possible misconfiguration on our end, but I’m baffled by the lack of error messages and everything else seems to be running just fine. I’ve spent a growing amount of days on troubleshooting and testing, gaining more knowledge about Discourse and its components in the process so I think with some guidance I could be able to help figuring out if there is some corner case detail triggering this weird behaviour. I hope it’s OK to ping @zogstrip at this point?
For a temporary solution, is it possible to manually move all uploads to the tombstone directory and use the upload recovery methods to restore only the non-orphan files back to their correct directories? I actually tried to do this today, but `rake uploads:recover_from_tombstone` didn’t restore any files. Could this be pointing to some bigger problem with uploads’ database entries?
---
### Author:  [@groove6j](https://meta.discourse.org/u/groove6j)
#### Post date: [March 26, 2024, 5:59pm UTC](https://meta.discourse.org/t/uploads-not-getting-orphaned-and-purged/259036/7 "2024-03-26T17:59:57Z")
Hello. I am facing the same or similar issue, can’t figure out why the files can’t get deleted. Anyone else having this issue still?
I ran some SQL queries and the “stuck” upload references seems to be all Drafts, but I checked my and other users Drafts and there are none. The Drafts tables are empty.
The orphan cleaning is enabled and settings are set to delete the orphans as fast as possible.
I attached a SQL query.
```plaintext
SELECT
uploads.original_filename,
ROUND(uploads.filesize / 1000000.0, 2) AS size_in_mb,
uploads.extension,
uploads.created_at,
uploads.url,
upload_references.upload_id,
upload_references.target_id,
upload_references.target_type,
upload_references.created_at,
upload_references.updated_at
FROM upload_references
JOIN uploads ON uploads.id = upload_references.upload_id
ORDER BY uploads.filesize DESC
LIMIT 250
```
[sql.csv](https://meta.discourse.org/uploads/short-url/gdMEhZ3My85lm2CHNX3oq1D30NL.csv) (46,1 KB)
This happens since I installed the forum. Even when there was no custom themes or plugins installed.
Heck even the old forum logo I uploaded a few times (the first uploaded file ever) is still rerefenced as Draft and still in the uploads folder. 🤦♂️
Theoretically I could filter all upload references and filter for Drafts by target\_type, then delete from database… and let the sidekiq tasks handle the cleanup (am I right?)
but I’m using a self hosted instance and am quite new to Discourse, so better to ask here…
That would be a workaround, but still there is a question - why is this happening?
Hope someone has some suggestions, my disk space is growing exponentially 😄
---
### Author:  [@Uphill4721](https://meta.discourse.org/u/Uphill4721)
#### Post date: [March 27, 2024, 6:47am UTC](https://meta.discourse.org/t/uploads-not-getting-orphaned-and-purged/259036/8 "2024-03-27T06:47:34Z")
Yeah, we’re still having this issue too.
I’d really like to sort it out somehow, our forum gets a lot of uploads but only a fraction of them need to be preserved long term so lots of disk space is being wasted. Any suggestions for troubleshooting appreciated.
> [@groove6j](#):
>
> Theoretically I could filter all upload references and filter for Drafts by target\_type, then delete from database… and let the sidekiq tasks handle the cleanup (am I right?)
Interested in this as a temporary solution, if it’s practical. 🤔
---
### Author:  [@groove6j](https://meta.discourse.org/u/groove6j)
#### Post date: [March 27, 2024, 8:54am UTC](https://meta.discourse.org/t/uploads-not-getting-orphaned-and-purged/259036/9 "2024-03-27T08:54:48Z")
I installed the forum 2 weeks ago and it has this problem since beginning. Looks like some bug.
Can you run the same SQL query and check if there are lots of stuck “Drafts” references? Its easy to see, I have dozens of them but in drafts table there are 2, maybe 3 real drafts. Looks like they don’t get deleted after editing (not being a draft anymore, but reference left in the database every time a post is edited for example).
I need to figure out how to delete a reference entry from the database and delete the refs for one file at first, then check if the cleanup task works.
I dont know how safe this is to do, but these coutless Drafts entries seem just wrong to me.
I can provide logs to staff/devs, I’m just new to Discourse and I dont know which logs files would help.
**EDIT** :
I’m trying to understand the database structure, and can I delete those upload entries without further problems (I dont want to miss some important DB relations). Also I don’t understand what exactly draft\_sequences are.
But I have to dublicate my production forum to a local VM, only then I can test…
---
### Author:  [@markersocial](https://meta.discourse.org/u/markersocial)
#### Post date: [April 19, 2024, 3:35am UTC](https://meta.discourse.org/t/uploads-not-getting-orphaned-and-purged/259036/10 "2024-04-19T03:35:46Z")
Another relevant topic, I posted on there as I wasn’t aware of this topic.
> [@Auto purge uploads from old deleted posts](https://meta.discourse.org/t/auto-purge-uploads-from-old-deleted-posts/51306):
>
> Discourse already automatically removes orphan unreferenced uploads. Why not expand this functionality and erase uploads from deleted posts? Only staff members are able to see deleted posts and that is very useful. But is it really necessary to keep all the files indefinitely? As administrator I really don`t care about 2 years old deleted pictures. Some of which may even be right out against site guidelines. It would be nice if Discourse would automatically delete these kind old files and place…
I believe the only way to really delete an image in an automatic way is to manually edit it out of the post before deleting it. But I’m not entirely sure that works either. I’m using identical settings to you regarding purging (but use S3 compatible storage) and I can also confirm that images never get purged if the only post containing that image (several posts can contain the same image, presumably avatars and user banners also) is deleted.
I use this solution to search to see if an image is used in additional posts, which was provided by @RGJ
> [@DMCA - Remove Individual Image Best Practices](https://meta.discourse.org/t/dmca-remove-individual-image-best-practices/156817/4):
>
> Just open a rails console and enter the following, replacing the large string with the large string in the image URL. If this is an optimized image then remove everything starting from the first underscore (\_). Upload.find\_by\_sha1('c35ab963fb22f8612697923ebc98d341693cfa1d').posts.each do |p| puts p.url end This will give you the URL(s) to one or more posts that contain the image. This code will give you the paths to the image and all associated optimized images, assuming local storage. …
Would really be great if this could be done automatically. Particularly due to Discourse handling images in a smart way, with preventing creating duplicates files if many posts use the same image. The flip side is it’s very tedious to remove individual images that have been used a lot.
I’ve had someone spam content that needed urgent removal via several accounts before and it was very stressful trying to deal with it and ensure it was entirely removed (all original files, optimised files, cdn cache, posts, avatars, user banners etc.).
I made this feature suggestion, as it would be very helpful imo. If this was implemented, as well as automatically purging content contained in deleted posts, I think all cases would be covered and could be handled without SSH access.
> [@Feature suggestion: Image removal/purge via web dashboard](https://meta.discourse.org/t/feature-suggestion-image-removal-purge-via-web-dashboard/247011):
>
> So the suggestion is: Ability to input an image url in the discourse moderator panel (any variation, original, optimised etc.) for removal. Then remove the image from all cases it’s being used in (posts, profile images like avatar, background etc.) and purge it (all variations) from the image storage (local or S3). Potentially could ban the hash of the image to prevent it being uploaded again in the future also. Discourse handles images in a really nice way, so that identical images being used…