Deleting unused attachments

[davo88]

SMF 2.0.19

There are about 11000 attachments in my SMF forum which is now about 18 years old.
I need to free up some server space and wish to delete any attachments that are not actually being used in topics.

So I set out to identify orphaned attachment files by doing the following.

Exported the attachments table from the database to a spreadsheet, sorted it by the 'downloads' field, then by the 'attachment_type' field.

All those with 0 in the 'downloads' field, 0 in the 'id_msg' field, and 0 in the 'attachment_type' field, look like orphaned files that have been created during failed attachment attempts by members.

If they have 0 in the 'id_msg' field and they are not avatars (which have 1 in the 'attachment_type' field), and they have never been downloaded, am I right in assuming these are orphaned files not linked to any message and can therefore be deleted?

I would be very grateful if anyone can confirm this conclusion.

[Sir Osis of Liver]

Before you delete anything, try running Admin > Attachments and Avatars > File Maintenance > Attachment Integrity Check.  It's a cleaner way to remove stray attachments.

[Kindred]

Why not just run the maintenance task that does all those checks fir you?  AND handles the deletion cleanly

[davo88]

Thanks for the suggestions, but I have never found that integrity check to be of much use.
When I run it now, I get this:

The follow errors were found during maintenance. Check the box next to the errors you wish to fix and hit continue.
2 attachments/avatars have an entry but no longer exist on disk
1 attachments/avatars are being reported as the wrong filesize

Yet There are 469 entries in the attachments table that meet the criteria I described above.

[Arantor]

How exactly did you determine that? The attachments table doesn't just reference the attachments themselves, but their thumbnails and user avatars as well.

[davo88]

These are my assumptions

Avatar
id_msg = 0
attachment_type = 1
downloads = 0

In-use Thumbnail
id_msg > 0
attachment_type = 3
downloads = 0
filename extn = .jpg_thumb

Unused Thumbnail
id_msg = 0
attachment_type = 3
downloads = 0
filename extn = .jpg_thumb

In-use attachment
id_msg > 0
attachment_type = 0
downloads > 0

Unused attachment
id_msg = 0
attachment_type = 0
downloads = 0

[Arantor]

That's a mostly correct assessment, but I'd start from a more fundamental basis.

Attachment type 0 = attachment, 1 = avatar, 3 = thumbnail. I wouldn't even make *any* assumptions about filename extensions because thumbnails for example don't have to be .jpg_thumb. (They *probably* will be but it's not guaranteed by the attachments code.)

The integrity check makes no assumptions about download count - why would it? Download count is of no concern for integrity purposes.

Thing is, there's actually no reason for the system operating normally to produce id_msg = 0 AND attachment_type = 0. This would imply an attachment that was stored in the database but somehow wasn't attached to a message - and I don't see why that would be a thing, unless you have mods doing something *else* with the attachments system.

When a message is saved, attachments are saved first, and then updated with message id once the message is also created. I find it concerning that attachments made it into the database (since that's statistically more likely to fail than message creation is), but not the adjoining messages, especially since deletion of messages doesn't delete the attachments with an UPDATE query to remove the message id (nor are there triggers for cascading updates)

[davo88]

Thanks very much Arantor. That's exactly what I needed.

Thing is, there's actually no reason for the system operating normally to produce id_msg = 0 AND attachment_type = 0. This would imply an attachment that was stored in the database but somehow wasn't attached to a message - and I don't see why that would be a thing, unless you have mods doing something *else* with the attachments system.

No, there are no mods relating to attachments. These 'orphaned' attachments seem to be produced when the attachment process fails for some reason. This forum has been hosted on quite a few different web servers over the years and we have had members reporting failed attaching attempts all along. I try to set server memory and timeout settings to give plenty of time and resources for the attaching process to complete but it still happens from time to time.

Members aren't very good at reporting error messages but "failed security checks" is one that gets mentioned quite regularly.

Strange thing is, that a member can try attaching some images to a post and it fails. I ask him to forward the images to me via email and then try attaching them myself. It works most of the time. So I wonder if it was something to do with his Internet connection.

I am hoping 2.1 might bring some improvements in this area.

Thanks again.

[Steve]

@davo88 ... so this can be marked solved then?