SQL MAX_JOIN_SIZE error

Welcome, Guest

(1 viewing) (1) Guest

Reply Topic

New Topic

Page:
1

TOPIC: SQL MAX_JOIN_SIZE error

SQL MAX_JOIN_SIZE error 21 May 2011 21:53 #2621

Ian
OFFLINE
Fresh Boarder
Posts: 16

Hello,

I seem to have hit a limit adding studies (sermons) to my website. I've successfully added five or six sermons this evening, but when I add one more I run into problems:

The first thing I noticed was that the Studies List was blank (both in the Backend and Frontend). Eventually after turning on debug in Joomla, I noticed that there is an SQL error reported in the frontend view:

"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 ..."

My hosting company sets the SQL parameters and MAX_JOIN_SIZE is set to 900,000, and SQL_BIG_SELECTS is OFF and I can't change these.

I have just less than 500 sermons, so I'm surprised if I'm hitting a database size limit?

Two questions therefore:

1) Any ideas? I couldn't find any similar problems in the forums

2) Some sites suggest adding a command to set SQL_BIG_SELECTS just before the query:

db_query("SET OPTION SQL_BIG_SELECTS=1");

Where would I put this command?

Many thanks in advance!

(using component version 6.2.4 with Joomla 1.5.23)

Last Edit: 21 May 2011 22:05 by Ian.

Reply Quote

Re: SQL MAX_JOIN_SIZE error 22 May 2011 12:40 #2622

Tom
OFFLINE
Moderator
Posts: 316

Hi Ian,

We are aware of the problem - though I didn't see it until working on JBS 7.0. From what we can figure it is actually a problem with the Joomla pagination procedure where Joomla runs the query from studieslist without any WHERE so the SELECT can get really big.

Anyway, there are some suggestions for fixing Joomla 1.6 but in the meantime we need to handle it on our own.

You are exactly right, here is the code - put it in /components/com_biblestudy/controller.php.

 function display()
    {
        //attempt to change mysql for error in large select
        $db = JFactory::getDBO();
        $db->setQuery('SET SQL_BIG_SELECTS=1');
        $db->query();
 
        parent::display();
    }

Just replace the current display function with this. Let me know if it works for you.

Tom

Reply Quote

Re: SQL MAX_JOIN_SIZE error 22 May 2011 13:46 #2623

Ian
OFFLINE
Fresh Boarder
Posts: 16

Tom,

Thanks for the quick reply (on a Sunday too!). That worked, with a minor syntax fix - missing OPTION, see below:

 
 function display()
    {
        //attempt to change mysql for error in large select
        $db = JFactory::getDBO();
        $db->setQuery('SET OPTION SQL_BIG_SELECTS=1');
        $db->query();
 
        parent::display();
    }

I put the same fix in the backend, adding it to the same function in controller.php in the /administrator/components/com_biblestudy directory. This works, but I've noticed that if I change one of the sort orders (reverse the date order for example), I lose the list of studies. Reselecting Studies fixes the problem. Not a big issue at all, but I suspect it's a related problem.

Thanks again for your help - very much appreciated.

If anyone's interested, here's a link to our downloads page: http://www.stjohnshartford.org/resources/sermonsbydate

Reply Quote

Re: SQL MAX_JOIN_SIZE error 29 May 2011 06:17 #2628

Brent Cordis NOW ONLINE Administrator Posts: 59	We will look into it and see if we can fix that in the next update.
	Thanks, Brent Cordis Joomla Bible Study Team Member Reply Quote

Re: SQL MAX_JOIN_SIZE error 31 May 2011 20:48 #2635

Ian OFFLINE Fresh Boarder Posts: 16	Great - thanks for your help.
	Reply Quote

Reply Topic

New Topic

Page:
1

BibleStudy

BibleStudy 6.2

Usage help

Moderators: Richard Hunt

Time to create page: 0.77 seconds