3

I set up our Magento site for a family member a while ago and everything has been fine... until now...

He went on holiday, when he came back, our front page had an error with a security thing saying that they can't show the error. After following instructions on a Q&A page, the front page now looks like this

the error

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'FLOOR(((e.min_price  ) * 1) / 100) + 1' in 'order clause', query was: SELECT FLOOR(((e.min_price  ) * 1) / 100) + 1 AS `range`, COUNT(*) AS `count` FROM `mage_catalog_product_index_price` AS `e`
 INNER JOIN `mage_catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.visibility IN(2, 4) AND cat_index.category_id = '2'
 LEFT JOIN `mage_cataloginventory_stock_status` AS `cisi` ON (cisi.product_id=e.entity_id) AND (cisi.website_id = '1') WHERE ( e.website_id = '1' ) AND ( e.customer_group_id = 0) AND (e.min_price > 0) GROUP BY FLOOR(((e.min_price  ) * 1) / 100) + 1 ORDER BY `FLOOR(((e`.`min_price  ) * 1) / 100) + 1` ASC

Trace:
#0 /home/classicspares/public_html/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 /home/classicspares/public_html/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 /home/classicspares/public_html/lib/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 /home/classicspares/public_html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT FLOOR(((...', Array)
#4 /home/classicspares/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(428): Zend_Db_Adapter_Pdo_Abstract->query('SELECT FLOOR(((...', Array)
#5 /home/classicspares/public_html/lib/Zend/Db/Adapter/Abstract.php(811): Varien_Db_Adapter_Pdo_Mysql->query(Object(Varien_Db_Select), Array)
#6 /home/classicspares/public_html/app/code/local/Mana/Filters/Resource/Filter/Price.php(121): Zend_Db_Adapter_Abstract->fetchPairs(Object(Varien_Db_Select))
#7 /home/classicspares/public_html/app/code/local/Mana/Filters/Model/Filter/Price.php(174): Mana_Filters_Resource_Filter_Price->countOnCollection(Object(Mage_Catalog_Model_Resource_Product_Collection), Object(Mana_Filters_Model_Filter_Price))
#8 /home/classicspares/public_html/app/code/local/Mana/Filters/Model/Query.php(172): Mana_Filters_Model_Filter_Price->countOnCollection(Object(Mage_Catalog_Model_Resource_Product_Collection))
#9 /home/classicspares/public_html/app/code/local/Mana/Filters/Model/Filter/Price.php(379): Mana_Filters_Model_Query->getFilterCounts('price', false)
#10 /home/classicspares/public_html/app/code/local/Mana/Filters/Model/Filter/Price.php(53): Mana_Filters_Model_Filter_Price->getPriceRange()
#11 /home/classicspares/public_html/app/code/local/Mana/Filters/Model/Filter/Price.php(238): Mana_Filters_Model_Filter_Price->_getItemsData()
#12 /home/classicspares/public_html/app/code/core/Mage/Catalog/Model/Layer/Filter/Abstract.php(120): Mana_Filters_Model_Filter_Price->_initItems()
#13 /home/classicspares/public_html/app/code/core/Mage/Catalog/Model/Layer/Filter/Abstract.php(109): Mage_Catalog_Model_Layer_Filter_Abstract->getItems()
#14 /home/classicspares/public_html/app/code/local/Mana/Filters/Block/Filter.php(88): Mage_Catalog_Model_Layer_Filter_Abstract->getItemsCount()
#15 /home/classicspares/public_html/app/design/frontend/base/default/template/catalog/layer/view.phtml(49): Mana_Filters_Block_Filter->getItemsCount()
#16 /home/classicspares/public_html/app/code/core/Mage/Core/Block/Template.php(241): include('/home/classicsp...')
#17 /home/classicspares/public_html/app/code/core/Mage/Core/Block/Template.php(272): Mage_Core_Block_Template->fetchView('frontend/base/d...')
#18 /home/classicspares/public_html/app/code/core/Mage/Core/Block/Template.php(286): Mage_Core_Block_Template->renderView()
#19 /home/classicspares/public_html/app/code/core/Mage/Core/Block/Abstract.php(919): Mage_Core_Block_Template->_toHtml()
#20 /home/classicspares/public_html/app/code/core/Mage/Core/Block/Text/List.php(43): Mage_Core_Block_Abstract->toHtml()
#21 /home/classicspares/public_html/app/code/core/Mage/Core/Block/Abstract.php(919): Mage_Core_Block_Text_List->_toHtml()
#22 /home/classicspares/public_html/app/code/core/Mage/Core/Block/Abstract.php(637): Mage_Core_Block_Abstract->toHtml()
#23 /home/classicspares/public_html/app/code/core/Mage/Core/Block/Abstract.php(581): Mage_Core_Block_Abstract->_getChildHtml('left', true)
#24 /home/classicspares/public_html/app/design/frontend/base/default/template/page/2columns-left.phtml(50): Mage_Core_Block_Abstract->getChildHtml('left')
#25 /home/classicspares/public_html/app/code/core/Mage/Core/Block/Template.php(241): include('/home/classicsp...')
#26 /home/classicspares/public_html/app/code/core/Mage/Core/Block/Template.php(272): Mage_Core_Block_Template->fetchView('frontend/base/d...')
#27 /home/classicspares/public_html/app/code/core/Mage/Core/Block/Template.php(286): Mage_Core_Block_Template->renderView()
#28 /home/classicspares/public_html/app/code/core/Mage/Core/Block/Abstract.php(919): Mage_Core_Block_Template->_toHtml()
#29 /home/classicspares/public_html/app/code/core/Mage/Core/Model/Layout.php(555): Mage_Core_Block_Abstract->toHtml()
#30 /home/classicspares/public_html/app/code/core/Mage/Core/Controller/Varien/Action.php(390): Mage_Core_Model_Layout->getOutput()
#31 /home/classicspares/public_html/app/code/core/Mage/Cms/Helper/Page.php(137): Mage_Core_Controller_Varien_Action->renderLayout()
#32 /home/classicspares/public_html/app/code/core/Mage/Cms/Helper/Page.php(52): Mage_Cms_Helper_Page->_renderPage(Object(Mage_Cms_IndexController), 'home')
#33 /home/classicspares/public_html/app/code/core/Mage/Cms/controllers/IndexController.php(45): Mage_Cms_Helper_Page->renderPage(Object(Mage_Cms_IndexController), 'home')
#34 /home/classicspares/public_html/app/code/core/Mage/Core/Controller/Varien/Action.php(418): Mage_Cms_IndexController->indexAction()
#35 /home/classicspares/public_html/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(250): Mage_Core_Controller_Varien_Action->dispatch('index')
#36 /home/classicspares/public_html/app/code/core/Mage/Core/Controller/Varien/Front.php(172): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#37 /home/classicspares/public_html/app/code/core/Mage/Core/Model/App.php(354): Mage_Core_Controller_Varien_Front->dispatch()
#38 /home/classicspares/public_html/app/Mage.php(684): Mage_Core_Model_App->run(Array)
#39 /home/classicspares/public_html/index.php(87): Mage::run('', 'store')
#40 {main}

Error log record number: 284895657995

I am not sure what has happened nor what the hell this error is but any help would be greatly appreciated.

5

On line 114 in:

app/code/local/Mana/Filters/Resource/Filter/Price.php

Change:

$select->group($rangeExpr)->order("$rangeExpr ASC");

To:

$select->group($rangeExpr)->order($rangeExpr, "ASC");

This fixed my problem using Manadev Layered Navigation (Mage 1.9.1)

  • I would add that it was caused by change made in Zend_Db_Select::order() method in Zend Framework in Magento 1.9.1. Similar fix could be to change this to $select->group($rangeExpr)->order(new Zend_Db_Expr("$rangeExpr ASC")); – Wojtek Naruniec Feb 13 '15 at 18:21
  • This is great help .. – Pratik Kamani May 19 '15 at 7:26
1

I had the same problem with ManaDev Filters on Magento 1.9.2.3. I needed to 'hack' the ManaDev code. In app/code/local/Mana/Filters/Resource/Filter/Price.php, around line 221. In between

$select->columns(array($maxPriceExpr))->order('m_max_price DESC');

and

$result  = $connection->fetchOne($select) * $filter->getCurrencyRate();

add the following:

// *********************************** START EDIT ******************************************
// Fix for flat table (since the catalog_product_index_price) doesn't have the status-column
if (Mage::getStoreConfig('catalog/frontend/flat_catalog_product')) {
    $wherePart = $select->getPart(Zend_Db_Select::WHERE);
    $count = 0;
    foreach ($wherePart as $key => $wherePartItem) {
        if (trim($wherePartItem) == '(e.status = 1)') {
            unset($wherePart[$key]);
        } else {
            if ($count === 0) {
                $wherePart[$key] = preg_replace('/^and/mUis', '', $wherePart[$key]);
            }
            $count ++;
        }
    }
    $select->setPart(Zend_Db_Select::WHERE, $wherePart);
}
// Rewriting doesn't work for some reason, And I can't use the ugly local-hack, because ManaDev
// stores it's modules in the local-namespace. Therefore I have no option left to edit the module
// files itself... I'm deeply, deeply sorry for that...
// ************************************ END EDIT *******************************************

That did the trick for me.

  • Good find! Thank you for the solution, it works fine. – Roman Snitko Feb 18 '16 at 8:10
0

Magento pro here.

This sounds like an issue with your product/category database indexes. Your periodic CRON tasks must have not triggered when they should have or been interrupted for some reason on your server causing partial or incomplete product index data.

The first thing to try is to rebuild product indexes. This may take several minutes to an hour depending on the number of products/categories you have in your database. If you have under 10,000 products it should take a few minutes only. Clear cache and try the frontend again after doing this. If it does solve the issue I would also speak to a developer to look through your logs and figure out why the process was interrupted so it doesn't happen again.

The second thing I would try is to disable the extension related. DO NOT disable module output like programmer_rkt recommended!! To disable a module in Magento you must go into your file system and find

 app/etc/modules/Mana_Filters.xml

and rename it to

 app/etc/modules/Mana_Filters.xml.off

Afterwards, refresh cache in the backend and try the frontend again. If this solves the issue then contact the extension provider and let them know of the issue.

Hope that helps.

  • i love the second part of your answer and I would upvote you if you explain why my suggestion didnt work. #SpreadTheKnowledge – Rajeev K Tomy Dec 1 '14 at 7:35
  • 1
    Actually, to properly disable a module you should rename the directory itself. Otherwise any class extensions from other modules of said module will still load the class. – Ben Lessani - Sonassi Dec 1 '14 at 11:51
  • @programmer_rkt your suggestion just disables module output. Sorry. – Jay El-Kaake Jan 28 '15 at 3:58
0

I had this problem and all I did was go into Magento Connect Manager and upgraded all Mage files that needed updating. Hope this helps.

0

We can only add, that this issue is fixed and you only needed to update extension to latest version from manadev.com (latest version usually is available only there)

0

I will give you a work around (not a solution) the key part of the log error is this.

#6 /home/classicspares/public_html/app/code/local/Mana/Filters/Resource/Filter/Price.php(121): Zend_Db_Adapter_Abstract->fetchPairs(Object(Varien_Db_Select))
#7 /home/classicspares/public_html/app/code/local/Mana/Filters/Model/Filter/Price.php(174): Mana_Filters_Resource_Filter_Price->countOnCollection(Object(Mage_Catalog_Model_Resource_Product_Collection), Object(Mana_Filters_Model_Filter_Price))
#8 /home/classicspares/public_html/app/code/local/Mana/Filters/Model/Query.php(172): Mana_Filters_Model_Filter_Price->countOnCollection(Object(Mage_Catalog_Model_Resource_Product_Collection))
#9 /home/classicspares/public_html/app/code/local/Mana/Filters/Model/Filter/Price.php(379): Mana_Filters_Model_Query->getFilterCounts('price', false)
#10 /home/classicspares/public_html/app/code/local/Mana/Filters/Model/Filter/Price.php(53): Mana_Filters_Model_Filter_Price->getPriceRange()
#11 /home/classicspares/public_html/app/code/local/Mana/Filters/Model/Filter/Price.php(238): Mana_Filters_Model_Filter_Price->_getItemsData()
#12 /home/classicspares/public_html/app/code/core/Mage/Catalog/Model/Layer/Filter/Abstract.php(120): Mana_Filters_Model_Filter_Price->_initItems()
#13 /home/classicspares/public_html/app/code/core/Mage/Catalog/Model/Layer/Filter/Abstract.php(109): Mage_Catalog_Model_Layer_Filter_Abstract->getItems()
#14 /home/classicspares/public_html/app/code/local/Mana/Filters/Block/Filter.php(88): Mage_Catalog_Model_Layer_Filter_Abstract->getItemsCount()

So the error clearly shows the "influence" of a third party extension here. The third party extension is Mana_Filters and it lives in local codePool(app/code/local/). You can confirm it by disabling this module via admin side.

 System >  Configuration  > Advanced  > {Disable this module}

Also the trace gives exactly where problem occurs. Probability to find this error is more in the file.

 app/code/local/Mana/Filters/Model/Filter/Price.php

around the line (174) and inside the function countOnCollection(). But it is not 100% sure. ( I have used trace #6 to predict it. Like wise the error may be occured any one of the file that is listed in the trace.) But you can find the "wrong" code somewhere inside that module's Model section. Use this trace to find it.

Here Magento again gives more details about the error. It says you that, it could not find a column field with name FLOOR in the table mage_catalog_product_index_price. So it is obvious that, the module is trying to fetch a non-existing field from the table mage_catalog_product_index_price.

So first of all find out the developer of this extension and tell him about the error. If he is not avialable, then use above points for debug more.

Edit

I think I have done a "wrong statement" about disabling the extension in this particular case. When you disable the module via admin side, it actually disables the extension partially !!!!.Damn ! I just forgot that !!

But in order to disable an extension fully, you need to find the activation file of this module. Most probably this file can find at app/ectc/modules/Mana_Filters.xml.Open this fle and make the module disabled by putting

 <active>false</active>

However there is a rare chance for "couldnt see Mana_Filters.xml file". In that case, you need to go through each and every files that comes under app/etc/modules and then find which file that defines configuration of this module and then put false there

Good Luck

  • Disabling the module output through the back-end won't do anything in this case - disabling module output only prevents it's layout xml from being merged. – Erfan Dec 1 '14 at 7:21
  • 1
    @Erfan : Is that so ?? Hm.. I can not say 100% ok on that statement(my statement). So what about disabling through etc/modules ? I think that would do the trick. – Rajeev K Tomy Dec 1 '14 at 7:23
  • Yeah :) that will do the trick – Erfan Dec 1 '14 at 7:48
  • @Erfan : I hope you see my edit. Thanks for notifying me. Keep doing this. More power to you. :-) – Rajeev K Tomy Dec 1 '14 at 7:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.