Problem with attachments upgrade

[shawnb61]

You're still running the old version, run the new one.

At first glance, it looks like there is a very high number of stray files in those folders that are not valid attachments.

Also, it looks like lots of attachment records have been deleted, but the files were left in the file system.

At the same time, it looks it can't find a lot of files in your attachments2 directory...

Let's re-look when you have the new version up there...

[shawnb61]

I would actually NOT share the utility output publicly...   For lots of reasons... 

Share a new link via PM.  And delete the old utility.  Don't use the same name again.

[Aleksi "Lex" Kilpinen]

I removed the link, because it potentially gives away a little too much to keep public.
It's fine if you want to share it privately, but probably better if you don't share it live.

[shawnb61]

I spent a fair amount of time looking at the report & looking at the old & new forums, and as near as I can tell, the only REAL issue is that the attachments2 folder is incomplete.  I'd compare the file counts in the file system between the 2.0 & 2.1 test.  I suspect you will find that not all files were copied over before the upgrade for attachments2.  (E.g., msg 2931145, attach 52323)

There are some formatting issues - I suspect there was an inline attachment mod used in 2.0, & those aren't displaying correctly in 2.1.  That needs to be taken care of.

Other than that, there are a LOT of extraneous files laying around.  Most of which appear to be from failed uploads, failed thumbnail generation, saved backup copies, etc., over many years.  I don't think they're hurting anything.  Just extraneous.

If you PM me a link to the new utility, I'll look closer.  Using the new one is important; the old one would sometimes get confused where dupes were found (and there's a lot of that here...). 

[Sir Osis of Liver]

I've run this query on tgsmf21 database -

  SELECT id_attach FROM smf_attachments WHERE id_attach > 0 AND file_hash > 0;

Outputs this -

  Showing rows 0 - 24 (44327 total, Query took 0.0003 seconds.)

Which somewhat agrees with file maintenance total 41527. 

It also outputs thousands of these -

  Warning: #1292 Truncated incorrect DOUBLE value: '517780a1812f187727b73ebb3cebcd159c8acc87'

[Sir Osis of Liver]

SELECT id_attach FROM smf_attachments;
   Showing rows 0 - 24 (74546 total, Query took 0.0001 seconds.)

SELECT id_attach FROM smf_attachments WHERE file_hash <> "";
   Showing rows 0 - 24 (73948 total, Query took 0.0002 seconds.)

SELECT * FROM smf_attachments WHERE file_hash = "";
   Showing rows 0 - 24 (598 total, Query took 0.0008 seconds.)

SELECT * FROM smf_attachments WHERE id_attach = ""; 
   MySQL returned an empty result set (i.e. zero rows). (Query took 0.0002 seconds.)

SELECT id_attach FROM smf_attachments WHERE id_attach > 0 AND file_hash > 0;
   Showing rows 0 - 24 (44327 total, Query took 0.0003 seconds.)

SELECT id_attach FROM smf_attachments WHERE file_hash > 0;
   Showing rows 0 - 24 (44327 total, Query took 0.0003 seconds.)

I think the last two results are not valid, mysql apparently doesn't recognize letters as > 0.

[shawnb61]

file_hash is a text field you're comparing to a number, so it's trying to be friendly and convert it...  That's where those errors are coming from.

When looking at attachment stats, to compare the DB to the file system, you need to exclude avatars, because they're no longer in the attachment folders in the file system.

id_attach is required and numeric, it'll never be an empty string. 

The blank file hashes, I believe, are very old.  I think SMF 1.x did that???  I'd give those a closer look...

I've added stats that factor this all in to the utility.  Feel free to grab the latest & greatest!   
https://raw.githubusercontent.com/sbulen/sjrbTools/refs/heads/master/smf_attachment_dirs.php