mysql database question - attachments table

[EL34]

About 10 years ago I converted a YABB forum into SMF 1.x.x
Then this week I converted the 1.1.19 forum into SMF 2.0.7

Not sure if all the attachments from the YABB forum were actually being used in messages?
I also deleted the Aeva 1.4 mod recently and there was a bunch of images that may be orphaned?

I have almost 30,000 files in my attachments folder on the server
I have a feeling that there are a bunch of these files that do not belong to any messages on the forum

Looking at the attachments table in phpMyadmin, I see that there are a bunch of records that have 0 in the id_msg column
avatars would not have a id_msg, but I would think that all other attachments should have something in the id_msg column?

Am I correct in thinking that every attachment should have a non zero number in the id_msg column? (except avatars)

I am wondering if I can purge a bunch of these items from the attachment table based on a zero id_message (excluding avatar files)

See my screen shot

[Kindred]

don't ever do a direct database edit of those tables.

There is a maintenance routine to check and clean up the attachments tables

[EL34]

Hi Kindred,

Yes, I have done the attachment maintenance

It does not do anything for all the orphaned records in the attachments database table
I have checked the before and after and the number of records does not change in the attachments table
It does repair SMF messages errors regarding attachments

I still have about 2000 records in the attachment table that have a zero in the id_msg column and no hash total
So if they are zero, they do not belong to any message and are orphaned records (except avatars)

These orphaned records can be from the Aeva media mod that was un-installed and also
they may go back as far as the YABB upgrade 10 years ago

From what I can see, all valid attachments have a id_msg number that points to the message it belongs to
So my question still remains
If an attachment record has a id_msg of zero, it must be an orphan record? (excluding avatars)

[Kindred]

aeva does not add anything to the attachments table or in attachments directory.
Aeva uses it's own tables and its own directories

[EL34]

I solved the slow attachments upload problem

I had already purged older messages after the 5 year mark in an attempt to reduce the number attachments
Deleting the attachments older than a certain date is an option, but my forum members like having the attachments as part of the post

The attachment integrity check does not work
The screen just keeps refreshing but it stays at 1%
Eventually I come back to the screen and it is back at admin login.
So I have no idea if anything actually happened or got fixed using this utility

But first in order to clean up everything after 10 years of smf and previously YABB,
I wrote a vb.net program that looked at the attachments table and then compared that to the messages table to find orphaned attachments that did not belong to any messages.

That resulted in over 2000 attachments that were orphaned in the attachments table that did not belong to any message
The attachments table was purged of these orphaned records

Then I compared the valid attachments in the attachments table to the actual file names on the server.
That resulted in over 3000 files on the server that were not in the attachments table

So now the attachments listed in the attachments table actually belonged to a message
And the files on the server matched what was listed in the attachments table

Also there were corrupted attachment file names and files with zero size on the server and those got deleted

Cringe at the thought of operating on the database tables directly, but the SMF attachment utility did nothing to fix these issues.
It's nice to have that all mess cleaned up and in working order which dropped my total attachments from over 25,000 to 21,000+ files on the server

So after the big cleanup, the attachment uploads were still slower than they should be and so I created another attachment folder named attachments2 and SMF is using that folder now.

Attachments now upload very quickly into the new attachments folder
So in the future, I think I will create a new attachment folder maybe around the 10,000 file mark to keep things speedy