Magento error on product listing after adding a configurable product

0
votes

After adding a configurable product I'm not able to view that category in my frontend.

I'm just getting the following error:

There has been an error processing your request.

This is what I have tried without any help:

  1. Deleted the product that made the problem.
  2. Deleted the attribute for configurable item from all attribute sets.
  3. Flushed Cache Deleted all other products in the category.
  4. Added new product in that category Changed to the default templates

Even after all that still I can't list that category without error. If it's empty it show up without error and if I make product in other category it works well. Magento version 1.9.0.1

Error log is like this:

a:5:{i:0;s:1223:"SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.`tax_class_id WHEN 2 THEN       0`.`2500 WHEN 5 THEN       0`.`1500  ELSE 0 END' at line 2, query was: SELECT FLOOR((ROUND(((e.min_price +(e.min_price*CASE e.tax_class_id WHEN 2 THEN       0.2500 WHEN 5 THEN       0.1500  ELSE 0 END))) * 1, 2)) / 100) + 1 AS `range`, COUNT(*) AS `count` FROM `catalog_product_index_price` AS `e`
 INNER JOIN `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 = '3' WHERE ( e.website_id = '1' ) AND ( e.customer_group_id = 0) AND ((e.min_price +(e.min_price*CASE e.tax_class_id WHEN 2 THEN       0.2500 WHEN 5 THEN       0.1500  ELSE 0 END)) IS NOT NULL) GROUP BY FLOOR((ROUND(((e.min_price +(e.min_price*CASE e.tax_class_id WHEN 2 THEN       0.2500 WHEN 5 THEN       0.1500  ELSE 0 END))) * 1, 2)) / 100) + 1 ORDER BY `FLOOR((ROUND(((e`.`min_price +(e`.`min_price*CASE e`.`tax_class_id WHEN 2 THEN       0`.`2500 WHEN 5 THEN       0`.`1500  ELSE 0 END))) * 1, 2)) / 100) + 1` ASC";i:1;s:4946:"#0 /home/31/w266847/www/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 /home/31/w266847/www/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 /home/31/w266847/www/lib/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 /home/31/w266847/www/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT FLOOR((R...', Array)
#4 /home/31/w266847/www/lib/Varien/Db/Adapter/Pdo/Mysql.php(428): Zend_Db_Adapter_Pdo_Abstract->query('SELECT FLOOR((R...', Array)
#5 /home/31/w266847/www/lib/Zend/Db/Adapter/Abstract.php(811): Varien_Db_Adapter_Pdo_Mysql->query(Object(Varien_Db_Select), Array)
#6 /home/31/w266847/www/app/code/core/Mage/Catalog/Model/Resource/Layer/Filter/Price.php(274): Zend_Db_Adapter_Abstract->fetchPairs(Object(Varien_Db_Select))
#7 /home/31/w266847/www/app/code/core/Mage/Catalog/Model/Layer/Filter/Price.php(158): Mage_Catalog_Model_Resource_Layer_Filter_Price->getCount(Object(Mage_Catalog_Model_Layer_Filter_Price), 100)
#8 /home/31/w266847/www/app/code/core/Mage/Catalog/Model/Layer/Filter/Price.php(115): Mage_Catalog_Model_Layer_Filter_Price->getRangeItemCounts(100)
#9 /home/31/w266847/www/app/code/core/Mage/Catalog/Model/Layer/Filter/Price.php(314): Mage_Catalog_Model_Layer_Filter_Price->getPriceRange()
#10 /home/31/w266847/www/app/code/core/Mage/Catalog/Model/Layer/Filter/Abstract.php(151): Mage_Catalog_Model_Layer_Filter_Price->_getItemsData()
#11 /home/31/w266847/www/app/code/core/Mage/Catalog/Model/Layer/Filter/Abstract.php(120): Mage_Catalog_Model_Layer_Filter_Abstract->_initItems()
#12 /home/31/w266847/www/app/code/core/Mage/Catalog/Model/Layer/Filter/Abstract.php(109): Mage_Catalog_Model_Layer_Filter_Abstract->getItems()
#13 /home/31/w266847/www/app/code/core/Mage/Catalog/Block/Layer/Filter/Abstract.php(132): Mage_Catalog_Model_Layer_Filter_Abstract->getItemsCount()
#14 /home/31/w266847/www/app/code/core/Mage/Catalog/Block/Layer/View.php(218): Mage_Catalog_Block_Layer_Filter_Abstract->getItemsCount()
#15 /home/31/w266847/www/app/code/core/Mage/Catalog/Block/Layer/View.php(233): Mage_Catalog_Block_Layer_View->canShowOptions()
#16 /home/31/w266847/www/app/design/frontend/base/default/template/catalog/layer/view.phtml(34): Mage_Catalog_Block_Layer_View->canShowBlock()
#17 /home/31/w266847/www/app/code/core/Mage/Core/Block/Template.php(241): include('/home/31/w26684...')
#18 /home/31/w266847/www/app/code/core/Mage/Core/Block/Template.php(272): Mage_Core_Block_Template->fetchView('frontend/base/d...')
#19 /home/31/w266847/www/app/code/core/Mage/Core/Block/Template.php(286): Mage_Core_Block_Template->renderView()
#20 /home/31/w266847/www/app/code/core/Mage/Core/Block/Abstract.php(919): Mage_Core_Block_Template->_toHtml()
#21 /home/31/w266847/www/app/code/core/Mage/Core/Block/Text/List.php(43): Mage_Core_Block_Abstract->toHtml()
#22 /home/31/w266847/www/app/code/core/Mage/Core/Block/Abstract.php(919): Mage_Core_Block_Text_List->_toHtml()
#23 /home/31/w266847/www/app/code/core/Mage/Core/Block/Abstract.php(637): Mage_Core_Block_Abstract->toHtml()
#24 /home/31/w266847/www/app/code/core/Mage/Core/Block/Abstract.php(581): Mage_Core_Block_Abstract->_getChildHtml('left', true)
#25 /home/31/w266847/www/app/design/frontend/default/hellowired/template/page/3columns.phtml(48): Mage_Core_Block_Abstract->getChildHtml('left')
#26 /home/31/w266847/www/app/code/core/Mage/Core/Block/Template.php(241): include('/home/31/w26684...')
#27 /home/31/w266847/www/app/code/core/Mage/Core/Block/Template.php(272): Mage_Core_Block_Template->fetchView('frontend/defaul...')
#28 /home/31/w266847/www/app/code/core/Mage/Core/Block/Template.php(286): Mage_Core_Block_Template->renderView()
#29 /home/31/w266847/www/app/code/core/Mage/Core/Block/Abstract.php(919): Mage_Core_Block_Template->_toHtml()
#30 /home/31/w266847/www/app/code/core/Mage/Core/Model/Layout.php(555): Mage_Core_Block_Abstract->toHtml()
#31 /home/31/w266847/www/app/code/core/Mage/Core/Controller/Varien/Action.php(390): Mage_Core_Model_Layout->getOutput()
#32 /home/31/w266847/www/app/code/core/Mage/Catalog/controllers/CategoryController.php(161): Mage_Core_Controller_Varien_Action->renderLayout()
#33 /home/31/w266847/www/app/code/core/Mage/Core/Controller/Varien/Action.php(418): Mage_Catalog_CategoryController->viewAction()
#34 /home/31/w266847/www/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(250): Mage_Core_Controller_Varien_Action->dispatch('view')
#35 /home/31/w266847/www/app/code/core/Mage/Core/Controller/Varien/Front.php(172): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#36 /home/31/w266847/www/app/code/core/Mage/Core/Model/App.php(354): Mage_Core_Controller_Varien_Front->dispatch()
#37 /home/31/w266847/www/app/Mage.php(684): Mage_Core_Model_App->run(Array)
#38 /home/31/w266847/www/index.php(87): Mage::run('', 'store')
#39 {main}";s:3:"url";s:29:"/kjaeledyr/hund/hundefor.html";s:11:"script_name";s:10:"/index.php";s:4:"skin";s:7:"default";}
2
phpmagento
Added numbered list for better readability – Andrea

2 Answers

1
votes

I had the same issue, and found out something strange in the query:

There are many back ticks in the SQL statement, but, once those removed, the SQL statement ran perfectly fine.

In the query below:

SELECT FLOOR((ROUND(((e.min_price -(e.min_price/(1+(CASE e.tax_class_id WHEN 2 THEN       0.2000 WHEN 4 THEN       0.2000  ELSE 0 END))*CASE e.tax_class_id WHEN 2 THEN       0.2000 WHEN 4 THEN       0.2000  ELSE 0 END)+((e.min_price-(e.min_price/(1+(CASE e.tax_class_id WHEN 2 THEN       0.2000 WHEN 4 THEN       0.2000  ELSE 0 END))*CASE e.tax_class_id WHEN 2 THEN       0.2000 WHEN 4 THEN       0.2000  ELSE 0 END))*CASE e.tax_class_id WHEN 2 THEN       0.2000 WHEN 4 THEN       0.2000  ELSE 0 END))) * 1, 2)) / 10) + 1 AS `range`, COUNT(*) AS `count` FROM `catalog_product_index_price` AS `e`
INNER JOIN `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 = '16' AND cat_index.is_parent=1 WHERE ( e.website_id = '1' ) AND ( e.customer_group_id = '1') AND ((e.min_price -(e.min_price/(1+(CASE e.tax_class_id WHEN 2 THEN       0.2000 WHEN 4 THEN       0.2000  ELSE 0 END))*CASE e.tax_class_id WHEN 2 THEN       0.2000 WHEN 4 THEN       0.2000  ELSE 0 END)+((e.min_price-(e.min_price/(1+(CASE e.tax_class_id WHEN 2 THEN       0.2000 WHEN 4 THEN       0.2000  ELSE 0 END))*CASE e.tax_class_id WHEN 2 THEN       0.2000 WHEN 4 THEN       0.2000  ELSE 0 END))*CASE e.tax_class_id WHEN 2 THEN       0.2000 WHEN 4 THEN       0.2000  ELSE 0 END)) IS NOT NULL) GROUP BY FLOOR((ROUND(((e.min_price -(e.min_price/(1+(CASE e.tax_class_id WHEN 2 THEN       0.2000 WHEN 4 THEN       0.2000  ELSE 0 END))*CASE e.tax_class_id WHEN 2 THEN       0.2000 WHEN 4 THEN       0.2000  ELSE 0 END)+((e.min_price-(e.min_price/(1+(CASE e.tax_class_id WHEN 2 THEN       0.2000 WHEN 4 THEN       0.2000  ELSE 0 END))*CASE e.tax_class_id WHEN 2 THEN       0.2000 WHEN 4 THEN       0.2000  ELSE 0 END))*CASE e.tax_class_id WHEN 2 THEN       0.2000 WHEN 4 THEN       0.2000  ELSE 0 END))) * 1, 2)) / 10) + 1 ORDER BY `FLOOR((ROUND(((e`.`min_price -(e`.`min_price/(1+(CASE e`.`tax_class_id WHEN 2 THEN       0`.`2000 WHEN 4 THEN       0`.`2000  ELSE 0 END))*CASE e`.`tax_class_id WHEN 2 THEN       0`.`2000 WHEN 4 THEN       0`.`2000  ELSE 0 END)+((e`.`min_price-(e`.`min_price/(1+(CASE e`.`tax_class_id WHEN 2 THEN       0`.`2000 WHEN 4 THEN       0`.`2000  ELSE 0 END))*CASE e`.`tax_class_id WHEN 2 THEN       0`.`2000 WHEN 4 THEN       0`.`2000  ELSE 0 END))*CASE e`.`tax_class_id WHEN 2 THEN       0`.`2000 WHEN 4 THEN       0`.`2000  ELSE 0 END))) * 1, 2)) / 10) + 1` ASC

The issue was coming as soon as it hits the last part:

ORDER BY `FLOOR

The back tick there was misplaced in my opinion.

I solved it by editing the Price.php file on line 272

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

to $select->group($rangeExpr)->order($rangeExpr);

0
votes

I've got this exact problem to and like to kick it, just to find out how to solve it. I've got the exact same Magento version as well.

If you remove the code from this template,

/home/31/w266847/www/app/design/frontend/base/default/template/catalog/layer/view.phtml(34): Mage_Catalog_Block_Layer_View->canShowBlock(), it will work, but I can't figure out which part of it is killing it.