The problematic query is the following:
Code:
SELECT a.id, a.title, a.alias, a.introtext, a.`fulltext`, a.catid, a.state, a.access, a.created, a.created_by, a.created_by_alias, a.modified, a.featured, a.language, a.hits, a.publish_up, a.publish_down, a.images, a.urls, a.language, a.metakey, a.metadesc, a.metadata,c.title AS category_title, c.alias AS category_alias,ua.name AS author_name,um.name AS last_modified_by,ROUND(v.rating_sum / v.rating_count, 0) AS rating, v.rating_count as rating_count,vl.title AS access_title,l.title AS language_title
FROM #__content as a
LEFT JOIN #__categories AS c ON c.id = a.catid
LEFT JOIN #__users AS ua ON ua.id = a.created_by
LEFT JOIN #__users AS um ON um.id = a.modified_by
LEFT JOIN #__content_rating AS v ON a.id = v.content_id
LEFT JOIN #__viewlevels AS vl ON a.access = vl.id
LEFT JOIN #__languages AS l ON a.language = l.lang_code
LEFT JOIN #__contentitem_tag_map AS ct ON ct.content_item_id = a.id
LEFT JOIN #__tags AS t ON t.id = ct.tag_id
LEFT OUTER JOIN (select rsi.provider_id, rsi.order from #__roksprocket_items as rsi where module_id = 209) rsi on a.id = rsi.provider_id
WHERE ((a.access IN(1,1,2,3) AND (a.state != -2)) AND a.id IN (0)) OR ((a.access IN(1,1,2,3) AND (a.state != -2)) AND a.catid IN (91))
GROUP BY id
ORDER BY IF(ISNULL(rsi.order),1,0),rsi.order
The exception thrown is:
Code:
JDatabaseExceptionExecuting: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay in /website_path/libraries/joomla/database/driver/mysqli.php:665
Stack trace:
#0 /website_path/libraries/joomla/database/driver.php(1701): JDatabaseDriverMysqli->execute()
#1 /website_path/components/com_roksprocket/lib/RokSprocket/Provider/AbstarctJoomlaBasedProvider.php(61): JDatabaseDriver->loadObjectList('id')
#2 /website_path/components/com_roksprocket/lib/RokSprocket.php(294): RokSprocket_Provider_AbstarctJoomlaBasedProvider->getItems()
#3 /website_path/components/com_roksprocket/lib/RokSprocket.php(230): RokSprocket::getItems(Object(RokSprocket_Provider_Joomla), '209', Array, NULL, 'manual', 'after', false, true, Array)
#4 /website_path/administrator/components/com_roksprocket/models/module.php(1072): RokSprocket::getItemsWithParams('209', Object(RokCommon_Registry), false, true)
#5 /website_path/administrator/components/com_roksprocket/views/module/view.html.php(56): RokSprocketModelModule->getArticles('209', Array)
#6 /website_path/libraries/src/MVC/Controller/BaseController.php(672): RokSprocketViewModule->display()
#7 /website_path/administrator/components/com_roksprocket/controller.php(77): Joomla\CMS\MVC\Controller\BaseController->display()
#8 /website_path/libraries/src/MVC/Controller/BaseController.php(710): RokSprocketController->display()
#9 /website_path/administrator/components/com_roksprocket/roksprocket.php(28): Joomla\CMS\MVC\Controller\BaseController->execute('')
#10 /website_path/libraries/src/Component/ComponentHelper.php(402): require_once('/website_path/...')
#11 /website_path/libraries/src/Component/ComponentHelper.php(377): Joomla\CMS\Component\ComponentHelper::executeComponent('/website_path/...')
#12 /website_path/libraries/src/Application/AdministratorApplication.php(101): Joomla\CMS\Component\ComponentHelper::renderComponent('com_roksprocket')
#13 /website_path/libraries/src/Application/AdministratorApplication.php(159): Joomla\CMS\Application\AdministratorApplication->dispatch()
#14 /website_path/libraries/src/Application/CMSApplication.php(196): Joomla\CMS\Application\AdministratorApplication->doExecute()
#15 /website_path/administrator/index.php(51): Joomla\CMS\Application\CMSApplication->execute()
#16 {main}
The underlying issue in this case will be, that the default for the
@@max_join_size
variable is not the factory shipped default of
18446744073709551615, but has been altered (probably by the provider, if you don't own the server) to something much lower, like
201326592.
Because of missing indices, MySQL has to retrieve the data from the 10 referenced tables as a cartesian product, which could have the potential to be temporarily larger than
@@max_join_size.
To fix this, you can set
@@max_join_size to the factory default value of
18446744073709551615:
Code:
set @@max_join_size = 18446744073709551615;
Alternatively, set
@@sql_big_selects to
1:
Code:
set @@sql_big_selects = 1;
Executing one of those two statements at any time before the failing query (on the same connection), should be enough.