cancel
Showing results for 
Search instead for 
Did you mean: 

Exception when Magento executes query to create the menu (SQL_BIG_SELECT)

Exception when Magento executes query to create the menu (SQL_BIG_SELECT)

Hello,

 

I get regularly (but not reproducable) an Exception when Magento (CE 1.9.0.1) tries to execute this query:

SELECT `e`.*, IF(at_is_active.value_id > 0, at_is_active.value, at_is_active_default.value) AS `is_active`, IF(at_include_in_menu.value_id > 0, at_include_in_menu.value, at_include_in_menu_default.value) AS `include_in_menu`, `core_url_rewrite`.`request_path` FROM `catalog_category_entity` AS `e`
 INNER JOIN `catalog_category_entity_int` AS `at_is_active_default` ON (`at_is_active_default`.`entity_id` = `e`.`entity_id`) AND (`at_is_active_default`.`attribute_id` = '34') AND `at_is_active_default`.`store_id` = 0
 LEFT JOIN `catalog_category_entity_int` AS `at_is_active` ON (`at_is_active`.`entity_id` = `e`.`entity_id`) AND (`at_is_active`.`attribute_id` = '34') AND (`at_is_active`.`store_id` = 2)
 INNER JOIN `catalog_category_entity_int` AS `at_include_in_menu_default` ON (`at_include_in_menu_default`.`entity_id` = `e`.`entity_id`) AND (`at_include_in_menu_default`.`attribute_id` = '105') AND `at_include_in_menu_default`.`store_id` = 0
 LEFT JOIN `catalog_category_entity_int` AS `at_include_in_menu` ON (`at_include_in_menu`.`entity_id` = `e`.`entity_id`) AND (`at_include_in_menu`.`attribute_id` = '105') AND (`at_include_in_menu`.`store_id` = 2)
 LEFT JOIN `core_url_rewrite` ON (core_url_rewrite.category_id=e.entity_id) AND (core_url_rewrite.is_system=1 AND core_url_rewrite.store_id='2' AND core_url_rewrite.id_path LIKE 'category/%') WHERE (`e`.`entity_type_id` = '3') AND (IF(at_is_active.value_id > 0, at_is_active.value, at_is_active_default.value) = '1') AND (IF(at_include_in_menu.value_id > 0, at_include_in_menu.value, at_include_in_menu_default.value) = '1') AND (`e`.`parent_id` = '2') ORDER BY `e`.`position` ASC

The Exception is

/myMagentoRoot/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 /myMagentoRoot/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 /myMagentoRoot/lib/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 /myMagentoRoot/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT `e`.*, I...', Array)
#4 /myMagentoRoot/lib/Varien/Db/Adapter/Pdo/Mysql.php(428): Zend_Db_Adapter_Pdo_Abstract->query('SELECT `e`.*, I...', Array)
#5 /myMagentoRoot/lib/Zend/Db/Adapter/Abstract.php(737): Varien_Db_Adapter_Pdo_Mysql->query('SELECT `e`.*, I...', Array)
#6 /myMagentoRoot/lib/Varien/Data/Collection/Db.php(734): Zend_Db_Adapter_Abstract->fetchAll('SELECT `e`.*, I...', Array)
#7 /myMagentoRoot/app/code/core/Mage/Eav/Model/Entity/Collection/Abstract.php(1047): Varien_Data_Collection_Db->_fetchAll('SELECT `e`.*, I...')
#8 /myMagentoRoot/app/code/core/Mage/Eav/Model/Entity/Collection/Abstract.php(871): Mage_Eav_Model_Entity_Collection_Abstract->_loadEntities(false, false)
#9 /myMagentoRoot/app/code/core/Mage/Catalog/Model/Resource/Category/Collection.php(225): Mage_Eav_Model_Entity_Collection_Abstract->load(false, false)
#10 /myMagentoRoot/lib/Varien/Data/Collection.php(741): Mage_Catalog_Model_Resource_Category_Collection->load()
#11 /myMagentoRoot/app/code/community/RicoNeitzel/VertNav/Block/Navigation.php(230): Varien_Data_Collection->getIterator()
#12 /myMagentoRoot/app/design/frontend/default/Buddha/template/vertnav/left.phtml(57): RicoNeitzel_VertNav_Block_Navigation->toLinearArray(Object(Mage_Catalog_Model_Resource_Category_Collection))
#13 /myMagentoRoot/app/code/core/Mage/Core/Block/Template.php(241): include('/<part of myMagentoRoot>...')
#14 /myMagentoRoot/app/code/core/Mage/Core/Block/Template.php(272): Mage_Core_Block_Template->fetchView('frontend/defaul...')
#15 /myMagentoRoot/app/code/core/Mage/Core/Block/Template.php(286): Mage_Core_Block_Template->renderView()
#16 /myMagentoRoot/app/code/core/Mage/Core/Block/Abstract.php(919): Mage_Core_Block_Template->_toHtml()
#17 /myMagentoRoot/app/code/core/Mage/Core/Block/Text/List.php(43): Mage_Core_Block_Abstract->toHtml()
#18 /myMagentoRoot/app/code/core/Mage/Core/Block/Abstract.php(919): Mage_Core_Block_Text_List->_toHtml()
#19 /myMagentoRoot/app/code/core/Mage/Core/Block/Abstract.php(637): Mage_Core_Block_Abstract->toHtml()
#20 /myMagentoRoot/app/code/core/Mage/Core/Block/Abstract.php(581): Mage_Core_Block_Abstract->_getChildHtml('left', true)
#21 /myMagentoRoot/app/design/frontend/default/Buddha/template/page/2columns-left.phtml(48): Mage_Core_Block_Abstract->getChildHtml('left')
#22 /myMagentoRoot/app/code/core/Mage/Core/Block/Template.php(241): include('/<part of myMagentoRoot>...')
#23 /myMagentoRoot/app/code/core/Mage/Core/Block/Template.php(272): Mage_Core_Block_Template->fetchView('frontend/defaul...')
#24 /myMagentoRoot/app/code/core/Mage/Core/Block/Template.php(286): Mage_Core_Block_Template->renderView()
#25 /myMagentoRoot/app/code/core/Mage/Core/Block/Abstract.php(919): Mage_Core_Block_Template->_toHtml()
#26 /myMagentoRoot/app/code/core/Mage/Core/Model/Layout.php(555): Mage_Core_Block_Abstract->toHtml()
#27 /myMagentoRoot/app/code/core/Mage/Core/Controller/Varien/Action.php(390): Mage_Core_Model_Layout->getOutput()
#28 /myMagentoRoot/app/code/core/Mage/Catalog/Helper/Product/View.php(147): Mage_Core_Controller_Varien_Action->renderLayout()
#29 /myMagentoRoot/app/code/core/Mage/Catalog/controllers/ProductController.php(132): Mage_Catalog_Helper_Product_View->prepareAndRender(26705, Object(Mage_Catalog_ProductController), Object(Varien_Object))
#30 /myMagentoRoot/app/code/core/Mage/Core/Controller/Varien/Action.php(418): Mage_Catalog_ProductController->viewAction()
#31 /myMagentoRoot/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(250): Mage_Core_Controller_Varien_Action->dispatch('view')
#32 /myMagentoRoot/app/code/core/Mage/Core/Controller/Varien/Front.php(201): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#33 /myMagentoRoot/app/code/core/Mage/Core/Model/App.php(354): Mage_Core_Controller_Varien_Front->dispatch()
#34 /myMagentoRoot/app/Mage.php(684): Mage_Core_Model_App->run(Array)
#35 /myMagentoRoot/index.php(87): Mage::run('storename', 'store')

When I try to execute the query in PHPMyAdmin, I get the following 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

If I prefix the query with the SET SQL_BIG_SELECTS statement, it works, without it doesn't. After putting the shop in maintenance mode for a couple of minutes and rerun the query, it works again. Also Magento is then working again.

 

If I let me EXPLAIN the query while it doesn't work, I get the following results:

id 	select_type table						key													ref 							rows Extra
1 	SIMPLE 		at_include_in_menu_default 	IDX_CATALOG_CATEGORY_ENTITY_INT_ATTRIBUTE_ID		const 							883  Using where; Using temporary; Using filesort
1 	SIMPLE 		e							PRIMARY												at_include_in_menu_default... 	1 	 Using where
1 	SIMPLE 		at_include_in_menu			IDX_CATALOG_CATEGORY_ENTITY_INT_ENTITY_ID			e.entity_id 					4 	 Using where
1 	SIMPLE 		at_is_active				IDX_CATALOG_CATEGORY_ENTITY_INT_ENTITY_ID			e.entity_id 					4 	 
1 	SIMPLE		at_is_active_default		IDX_CATALOG_CATEGORY_ENTITY_INT_ENTITY_ID			e.entity_id 					4 	 Using where
1 	SIMPLE 		core_url_rewrite			FK_CORE_URL_REWRITE_CTGR_ID_CAT_CTGR_ENTT_ENTT_ID	at_include_in_menu_default... 	919 

 When it works, the rownumbers of the last table are around 500 and is varying.

 

I have to Magento instances running, but only one shows this problem. This shop has approx. 484 categories (for all store views, some categories are not active). The instance which has no problems has approx. 70 categories (for all store views, some categories are not active).

 

==> Why is that so (not reproducable, only sometimes) and how can I solve this problem? Is it really a "good" way to use SET SQL_BIG_SELECTS and if so, how can I tell Magento to do so? Or should I let my hoster configure MySql for a bigger MAX_JOIN_SIZE and how big should it be? As I understand it, this also is kind of a safety mechanism from MySQL and I don't want to just cut it off...

 

Kind regards,

 

Alex