Is there a fix?

[lazysheepmedia]

Chaps

I have a lot of elements and Big Selects are all on in the 13 lists I have - When I try to view the elements I ma getting the MAX_JOIN 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 SQL=SELECT e.*, e.ordering AS ordering,u.name AS editor, (SELECT DISTINCT( IF( ISNULL(jj.table_join), CONCAT(ll.db_table_name, '___', ee.name), CONCAT(jj.table_join, '___', ee.name)) ) FROM jos_fabrik_elements AS ee LEFT JOIN jos_fabrik_joins AS jj ON jj.group_id = ee.group_id LEFT JOIN jos_fabrik_formgroup as fg ON fg.group_id = ee.group_id LEFT JOIN jos_fabrik_lists AS ll ON ll.form_id = fg.form_id WHERE (jj.list_id != 0 AND jj.element_id = 0) AND ee.id = e.id AND ee.group_id <> 0 LIMIT 1) AS full_element_name, g.name AS group_name, l.db_table_name FROM jos_fabrik_elements AS e LEFT JOIN jos_users AS u ON checked_out = u.id LEFT JOIN jos_fabrik_groups AS g ON e.group_id = g.id LEFT JOIN jos_fabrik_formgroup AS fg ON fg.group_id = e.group_id LEFT JOIN jos_fabrik_lists AS l ON l.form_id = fg.form_id WHERE (e.published IN (0, 1)) ORDER BY ordering 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 e.*, e.ordering AS ordering,u.name AS editor, (SELECT DISTINCT( IF( ISNULL(jj.table_join), CONCAT(ll.db_table_name, '___', ee.name), CONCAT(jj.table_join, '___', ee.name)) ) FROM jos_fabrik_elements AS ee LEFT JOIN jos_fabrik_joins AS jj ON jj.group_id = ee.group_id LEFT JOIN jos_fabrik_formgroup as fg ON fg.group_id = ee.group_id LEFT JOIN jos_fabrik_lists AS ll ON ll.form_id = fg.form_id WHERE (jj.list_id != 0 AND jj.element_id = 0) AND ee.id = e.id AND ee.group_id <> 0 LIMIT 1) AS full_element_name, g.name AS group_name, l.db_table_name FROM jos_fabrik_elements AS e LEFT JOIN jos_users AS u ON checked_out = u.id LEFT JOIN jos_fabrik_groups AS g ON e.group_id = g.id LEFT JOIN jos_fabrik_formgroup AS fg ON fg.group_id = e.group_id LEFT JOIN jos_fabrik_lists AS l ON l.form_id = fg.form_id WHERE (e.published IN (0, 1)) ORDER BY ordering

Is there a workaround I can try please?

Thanks

 

[lazysheepmedia]

OK with the kind assistance of Mr Hugh Cheesegrits, it has been solved.

In ./administrator/components/com_fabrik/models/elements.php find around line 45

$db = $this->getDbo();

Immediately after add

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

This over rides the MySql restrictions many shared hosts impose on you but for now it has been resolved - Thanks Hugh!

 

[cheesegrits]

I've raised a github issue on this:

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

As per my comments in that issue, I really don't see why your MySQL thinks that query is going to select a huge amount of rows, but it's probably down to some very restrictive setting on your shared server's MySQL config.

Until we resolve the issue, remember you'll have to re-apply that bandaid to your elements.php any time you update.

-- hugh

 

[lazysheepmedia]

Thanks Hugh - I have closed this thread as solved