Syntax error or access violation: 1104 The SELECT would examine more than MAX_JOIN_SIZE rows

[alfonsecar]

I'm restore a backup of my full site, but I can resolved this problem, someone to help me?

thks in advance.

QLSTATE[42000]: Syntax error or access violation: 1104 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

#0 /home2/lacosit2/public_html/magento/lib/Varien/Db/Statement/Pdo/Mysql.php(111): Zend_Db_Statement_Pdo->_execute(Array)
#1 /home2/lacosit2/public_html/magento/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 /home2/lacosit2/public_html/magento/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#3 /home2/lacosit2/public_html/magento/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT `main_ta...', Array)
#4 /home2/lacosit2/public_html/magento/lib/Varien/Db/Adapter/Pdo/Mysql.php(428): Zend_Db_Adapter_Pdo_Abstract->query('SELECT `main_ta...', Array)
#5 /home2/lacosit2/public_html/magento/lib/Zend/Db/Adapter/Abstract.php(734): Varien_Db_Adapter_Pdo_Mysql->query('SELECT `main_ta...', Array)
#6 /home2/lacosit2/public_html/magento/lib/Varien/Data/Collection/Db.php(734): Zend_Db_Adapter_Abstract->fetchAll('SELECT `main_ta...', Array)
#7 /home2/lacosit2/public_html/magento/app/code/core/Mage/Core/Model/Resource/Db/Collection/Abstract.php(521): Varien_Data_Collection_Db->_fetchAll('SELECT `main_ta...', Array)
#8 /home2/lacosit2/public_html/magento/lib/Varien/Data/Collection/Db.php(566): Mage_Core_Model_Resource_Db_Collection_Abstract->getData()
#9 /home2/lacosit2/public_html/magento/app/code/local/Varien/Data/Collection.php(753): Varien_Data_Collection_Db->load()
#10 /home2/lacosit2/public_html/magento/app/code/local/AW/Layerednavigation/Block/Layer.php(87): Varien_Data_Collection->getIterator()
#11 /home2/lacosit2/public_html/magento/app/code/local/AW/Layerednavigation/Block/Layer.php(175): AW_Layerednavigation_Block_Layer->getFilterList()
#12 /home2/lacosit2/public_html/magento/app/code/core/Mage/Core/Block/Abstract.php(293): AW_Layerednavigation_Block_Layer->_prepareLayout()
#13 /home2/lacosit2/public_html/magento/app/code/core/Mage/Core/Model/Layout.php(456): Mage_Core_Block_Abstract->setLayout(Object(Mage_Core_Model_Layout))
#14 /home2/lacosit2/public_html/magento/app/code/core/Mage/Core/Model/Layout.php(472): Mage_Core_Model_Layout->createBlock('catalog/layer_v...', 'catalog.leftnav')
#15 /home2/lacosit2/public_html/magento/app/code/core/Mage/Core/Model/Layout.php(239): Mage_Core_Model_Layout->addBlock('catalog/layer_v...', 'catalog.leftnav')
#16 /home2/lacosit2/public_html/magento/app/code/core/Mage/Core/Model/Layout.php(205): Mage_Core_Model_Layout->_generateBlock(Object(Mage_Core_Model_Layout_Element), Object(Mage_Core_Model_Layout_Element))
#17 /home2/lacosit2/public_html/magento/app/code/core/Mage/Core/Model/Layout.php(210): Mage_Core_Model_Layout->generateBlocks(Object(Mage_Core_Model_Layout_Element))
#18 /home2/lacosit2/public_html/magento/app/code/core/Mage/Core/Controller/Varien/Action.php(344): Mage_Core_Model_Layout->generateBlocks()
#19 /home2/lacosit2/public_html/magento/app/code/core/Mage/Cms/Helper/Page.php(113): Mage_Core_Controller_Varien_Action->generateLayoutBlocks()
#20 /home2/lacosit2/public_html/magento/app/code/core/Mage/Cms/Helper/Page.php(52): Mage_Cms_Helper_Page->_renderPage(Object(Mage_Cms_IndexController), 'home')
#21 /home2/lacosit2/public_html/magento/app/code/core/Mage/Cms/controllers/IndexController.php(45): Mage_Cms_Helper_Page->renderPage(Object(Mage_Cms_IndexController), 'home')
#22 /home2/lacosit2/public_html/magento/app/code/core/Mage/Core/Controller/Varien/Action.php(418): Mage_Cms_IndexController->indexAction()
#23 /home2/lacosit2/public_html/magento/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(254): Mage_Core_Controller_Varien_Action->dispatch('index')
#24 /home2/lacosit2/public_html/magento/app/code/core/Mage/Core/Controller/Varien/Front.php(172): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#25 /home2/lacosit2/public_html/magento/app/code/core/Mage/Core/Model/App.php(354): Mage_Core_Controller_Varien_Front->dispatch()
#26 /home2/lacosit2/public_html/magento/app/Mage.php(684): Mage_Core_Model_App->run(Array)
#27 /home2/lacosit2/public_html/magento/index.php(87): Mage::run('', 'store')
#28 {main}

[Mukesh Tiwari]

Hi @alfonsecar

I think following links may be helpful for you (You may be using a huge dataset)

1) http://stackoverflow.com/questions/943423/mysql-the-select-would-examine-more-than-max-join-size-row...

2) http://stackoverflow.com/questions/19316939/select-would-examine-more-than-max-join-size-rows

[alfonsecar]

How can I do that?, i think is in this secttion, can you help me?

        $statement = $this->_stmt;
        $bindValues = array(); // Separate array with values, as they are bound by reference
        foreach ($params as $name => $param) {
            $dataType = PDO:ARAM_STR;
            $length = null;
            $driverOptions = null;


            if ($param instanceof Varien_Db_Statement_Parameter) {
                if ($param->getIsBlob()) {
                    // Nothing to do there - default options are fine for MySQL driver
                } else {
                    $dataType = $param->getDataType();
                    $length = $param->getLength();
                    $driverOptions = $param->getDriverOptions();
                }
                $bindValues[$name] = $param->getValue();
            } else {
                $bindValues[$name] = $param;
            }

            $paramName = $isPositionalBind ? ($name + 1) : $name;
            $statement->bindParam($paramName, $bindValues[$name], $dataType, $length, $driverOptions);
        }

[SindreM]

If you are restoring from a database dump you can add the following line to the start of the file:

SET SQL_BIG_SELECTS=1;

This should allow the import to succeed.

[alfonsecar · Accepted solution]

I solved it.


Add the line


$this->_connection->query("SET SQL_BIG_SELECTS=1");


In the file -> ...\lib\varien\db\adapter\pdo\mysql.php


Thks for your help.