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
