Database Error

[flipper79]

Attachment & Avatar maintenance.

Hi I'm running into problems whilst trying to remove attachments older than 365 days old!

Code Select Expand

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: /home/sites/gwentdetecting.co.uk/public_html/Sources/ManageAttachments.php
Line: 1009

I also encounter a similar error when maintaining SMF Media Gallery (check for orphan files)

Code Select Expand

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: /home/sites/gwentdetecting.co.uk/public_html/Sources/MGallery-Admin.php
Line: 1788


Any help or pointers would be greatly received

TIA flip

[MrPhil]

Your host has set limits to how large an operation you can do at once in MySQL. If you can't use one of the suggested flag settings (ask your host where and how you could set them), the easiest thing would be to delete attachments more than, say, 700 days old (go back further if that's still too big). Then do it for 600 days, etc. until you've done through 365 days old. I'm assuming that will break down the task into smaller pieces. If it doesn't, you might have to manually do it in phpMyAdmin some way. Dunno about the orphan child check -- can you think of any way to break it down into bite-sized pieces?

[flipper79]

Hi Phil, Thanks for your reply... thats explains it a little but ive run into more problems now, I was carrying out some Gallery maintenence (Regenerate thumbnails and previews) Now all I'm getting is this Error 


An Error Has Occurred!
Cannot access attachments upload path!

any ideas as i'm unable to even have the option to go back 700 days or further now

Kind regards

flip

[flipper79]

Sorry my own fault... forgot i changed the attatchments directory in 'repair_settings.php' but i still cant delete attachments older than 1000 days 

Ill get onto my host and see if they can remedy a solution

Thanks again for your time

[kat]

Can't you just delete them using FTP, or your site's File Manager?

You can set them to view in date order and just kill the lot of 'em, surely?

[flipper79]

Can't you just delete them using FTP, or your site's File Manager?

You can set them to view in date order and just kill the lot of 'em, surely?

Hi K@

I'm slightly confused but think I'm getting there. I'm slowly ploughing through the attachments, grouping them to 'date created' and deleting them page by page (over 700 pages).. I've managed to get it down from over 10,000 attachments to 7000 but its a laborious process. 

Basically all the attachments older than 365 days are corrupt (wrong file transfer settings in filezilla) and I'm confused to what the maximum amount of attachments I'm allowed before it flips out with this error code again!

Thanks for your reply... flip

[kat]

Sounds like you're REALLY enjoying yourself.

[flipper79]

Update:

I've manually deleted all corrupt image attachments (via the Attachments and Avatars >Browse Files) upto January 26 2012 I can also run & complete the 'Attachment Integrity Check' without getting the dreaded Database error so hope this problem is now fixed   

The problem i have now is the Gallery maintenance side  I'm still encountering this Error

Code Select Expand

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: /home/sites/gwentdetecting.co.uk/public_html/Sources/MGallery-Admin.php
Line: 1788

Would anybody happen to know how i get around this problem as i understand SMF Media Gallery isnt supported any longer. Any help would be appreciated Thanks

...flip

[MrPhil]

The SET commands have to be given to MySQL. If your host can't or won't tell you how to set up a configuration file for MySQL, you might have to add some PHP code to make a query with the SET command within SMF. I think if you do a search on SQL_BIG_SELECTS and/or SQL_MAX_JOIN_SIZE, you'll find some prior discussion on doing this.

[flipper79]

Thanks for the reply.... yes it is all fun and games but as you said MrPhil my host cant tell me how to do this unfortunately

Hi Phil,

Thanks again.

I did take a look at the code in that file but could not see where this would be inserted, the connection to the database may be done in another file. You'd need to contact the developers of the script regarding this to see whether they can advise you further, as this is not something we could assist with unfortunately.

Being a complete beginner with SMF, MySQL, and query's in general... am i in the right place with this thread? and if so  does anybody know where i need to insert the code please... Many thanks ... flip

http://www.simplemachines.org/community/index.php?topic=468966.msg3278328#msg3278328

[Storman™]

Difficult to know exactly as I don't know this mod.

I'd be inclined to insert it on line 1772 of MGallery-Admin.php just before or immediately after the line thats starts:

Code Select Expand

// This code is only there to clean up data from earlier beta........

If you try it BACKUP your database first.