2.0.2 : Attachment Integrity Check : Timing out with error.

[Fearless Freap]

Hi,

I had a disastrous weekend trying to upgrade from SMF 2.0 RC5 to 2.0.2...  I finally did so but had to reset the attachments directory.  I also lost the attachments as well.  I went to the admin / Attachments and Avatars / File Maintenance / Attachment Integrity Check

Total Attachments:                                            36,054
Total Avatars:                                                    32
Total Size of Attachment Directory:                    6,017,402.49 kB
Total Space Available in Attachment Directory:  No maximum directory size is currently set

I understand that my attachment size is huge... but after about 4% completion I get this error...

The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay
File: /hermes/waloraweb004/b1514/xxx.xxxxxxxxxxxx/htdocs/Sources/ManageAttachments.php
Line: 1009

Can someone point me in the right direction?  I know it's working, I did get the attachments, and some of the photos... I just need to get the rest.

Thanks,

-Steve

[Fearless Freap]

I've corresponded with my service provider and they don't believe that there is any errors.

I guess my question is what exactly does the file maintenance do?  And is there a work around for large databases?

Thanks,

-Steve

[kat]

This is a long-shot, from a bit of research...

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_max_join_size

Basically, it means that the select needs to read more rows than is allowed.

That allowance is configured in MySQL.

So... I guess it's a case of "Contact your host and ask them to configure MySQL so that the number of rows allowed is compatible".

Or, host the image somewhere else and link to it.

Heck... That must be some image, if it's over six gig in size!

[Oldiesmann]

The "integrity check" searches for and allows you to fix the following issues:
Thumbnails that don't have a "parent" (eg a thumbnail that is associated to a non-existent attachment)
"Parent" files that have a non-existent thumbnail (eg SMF thinks they have a thumbnail, but the thumbnail file doesn't actually exist)
Attachments that don't actually exist (this can happen if the file is deleted via FTP instead of through SMF)
Attachments whose filesize is different than what SMF thinks it is
Attachments whose filesize is 0 (corrupted files)
Attachments that are associated with a non-existent post
Avatars that are associated with non-existent members
Attachments that are in a different folder than SMF thinks they're in (only applies if you have SMF set up to use multiple attachment directories)

I'm not sure there is a good solution for larger databases, though you can try this (I don't know if a regular MySQL user has the ability to set this option or not):

Sources/ManageAttachments.php

Find

Code Select Expand

// Get stranded thumbnails.

Add before that

Code Select Expand

$smcFunc['db_query']('', 'SET SESSION SQL_BIG_SELECTS={int:one}', array('one' => 1));

[kat]

Ta, Oldies.

I said it was a long-shot.

[Fearless Freap]

I'm going to try the update to the ManageAttachments.php and see what happens.

I'll let you all know the end result.

Thanks,

Steve

[Fearless Freap]

And the award goes to Oldiesmann!

I am so thankful for your assistance...  It didn't hang it gave me the options of what it wrong and what to fix... since it's huge it's still working at 3% but it has definitely gotten past my usual hang up.

Thank you! Thank you! Thank you!

I'm marking this one solved.  (Hopefully I didn't just jinx myslef)