MAX_JOIN_SIZE error on back-end - big selects already enabled!

[Matthews]

I have been getting the following type of error when viewing any list on the back-end:

getJoinsToThisKey: 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 SQL=SELECT db_table_name, name, plugin, l.label AS listlabel, l.id as list_id, el.id AS element_id, el.label AS element_label, f.id AS form_id, el.params AS element_params FROM cs0iq_fabrik_elements AS el LEFT JOIN cs0iq_fabrik_formgroup AS fg ON fg.group_id = el.group_id LEFT JOIN cs0iq_fabrik_forms AS f ON f.id = fg.form_id LEFT JOIN cs0iq_fabrik_lists AS l ON l.form_id = f.id WHERE el.published = 1 AND (plugin = 'databasejoin' AND el.params like '%"join_db_name":"bm_bookings"%' AND el.params like '%"join_conn_id":"1%') OR (plugin = 'cascadingdropdown' AND el.params like '"%cascadingdropdown_table":"1"%' AND el.params like '"%cascadingdropdown_connection":"1"%') AND (plugin = 'user' AND el.params like '%"join_conn_id":"1%"' )

I have enabled big selects on all lists in my system.

I have also added

$db->setQuery("SET OPTION SQL_BIG_SELECTS=1");
$db->query();

after:

$db = $this->getDbo();

in administrator/components/com_fabrik/models/elements.php

I am getting this error every time I open a list to edit it's properties... any help is greatly appreciated.

 

[cheesegrits]

Your host must have set some VERY restrictive parameters on MySQL.

That tweak I showed you in elements.php only affects displaying of the Element list (where your issue first popped up), not the Lists list. We still have an issue open on this:

https://github.com/Fabrik/fabrik/issues/401

... which I'll try and get done soon. But for now, you'll have to apply a similar fix in your list model (lists.php in the same folder you edited element.php), around line 4417 you'll find:

			// Select the required fields from the table.

Immediately after that, put those same two lines I gave you before.

I beginning to suspect your host has simply put a very small limit on the number of JOIN's allowed in a statement before it triggers that threshold error warning, as there is no way that query is going to select anything more than a handful of rows.

Just to be clear, the warning being triggered is when MySQL "thinks" running a query may generate a huge number of rows. There are half a dozen or so threshold triggers, configured either at compile time or in the MySQL ini, which affect this decision. Typically, hosts who load up a LOT of shared or virtual sites using the same backend MySQL server will tend to push those limits way down, in an effort to dissuade people from running complex / database heavy apps, i.e. encourage them to move up the hosting ladder.

From the look of the two queries which have so far triggered that warning, they both have half a dozen or so JOIN's, which may be what is hitting your limit, without setting BIG_SELECTS.

-- hugh

 

[Matthews]

I've tried applying the two lines to administrator/components/com_fabrik/models/lists.php as I did in the elements.php file, but it hasn't made any difference - I still get exactly the same message.

 

[lazysheepmedia]

Guys I have this problem - I have set big selects to yes globally but it appears that this option is no longer available in the advanced tab of the list in administrator

So now I am getting this error. I have tried adding the lines above but it has not made any difference

Any clues on how to fix? It happens when I refresh a list in the front end as well

Thanks

 

[cheesegrits]

I think I fixed this today.

Can someone test the latest github and let me know. Works for me, and I can clearly see what the problem was - lists created since we dropped the per-list option would have worked, but pre-existing lists would then continue to use their per-list behavior, ignoring the global setting, and no way to change it (without manually editing the params field in the #__fabrik_lists table).

-- hugh

 

[cheesegrits]

Oh, yeah, one thing to note is that if you had any lists set to something which is not your new global setting, they will change their behavior. Big Selects is now On or Off globally.

-- hugh

 

[lazysheepmedia]

Just uploading the latest and will come back shortly

 

[lazysheepmedia]

Hi Hugh

I have loaded the latest github but still the problem remains

Although it is gone in the front end now

It is set to yes in the global settings so no change for me in admin - sorry

 

[lazysheepmedia]

Hi Hugh

The error is still there but now the error is still being thrown in the front end as well


Is it possible to have the Big Select added back under the advanced tab in the list within admin? That used to solve the problem for me at least.

Regards

Mark

 

[cheesegrits]

When you talk about the backend, are you talking about just List display (as in View Data on a List)? Or is it happening when you display things like the list of elements?

The original poster in this thread had issues with displaying the list of elements on the Element tab, which is a different issue to the one I think you are talking about. which is about display actual Lists themselves.

Unlikely we'll go back to a per-list option for this. We just need to find out why my fix isn't working for you, as it works fine for me. Or at least, it triggers the SQL_BIG_SELECTS query, I don't actually have any Lists which cause the error itself, so all I can do is make sure we are setting BIG SELECTS.

Catch me on Skype, I'll have to put some debug code in your site, see what's going on.

-- hugh

 

[cheesegrits]

OK, yup, your problem was of a similar nature to the original poster, in that the error was coming from a different database session.

We open a new database connection (session) for each Fabrik list we display. Currently, the 'big selects' option only applies to those connections, i.e. for queries directly on the underlying table for your List.

But the error you are getting is coming from the default J! database connection, which is what we use for our "internal" queries, looking up information about the lists, forms, elements etc. in our own tables (like #__fabrik_elements, etc).

I've made another tweak to the way we apply big selects, which I applied to the code on your server, and you are testing for me at the moment over on our Skype session.

And apparently it worked:

[3:28:43 PM] Mark Reynolds: Working perfectly front and back end



I'll commit that fix to github once I've had a chance to sanity check it with Rob, as it's in our main FabrikHelper::getDbo() routine, which I don't want to change without talking to The Boss.

-- hugh

 

[lazysheepmedia]

Yes thanks Hugh it is working great now - thanks for your time and help - grat as always


Sent via my iPhone using Tapatalk app