About

02
Jul

Indexer error after upgrade to Magento 1.7

After upgrading from 1.5.1.0 to 1.7, I received the following error when attempting to reindex the site via the command line:

Product Prices index process unknown error:
exception 'PDOException' with message 'SQLSTATE[21S01]: Insert value list does not match column list: 1136 Column count doesn't match value count at row 1' in /magento/lib/Zend/Db/Statement/Pdo.php:228

The site is running the SimpleConfigurableProducts extension by OrganicInternet, which overrides the pricing indexer. This class is found under /app/code/community/OrganicInternet/SimpleConfigurableProducts/Catalog/Model/Resource/Eav/Mysql4/Product/Indexer/Price/Configurable.php.

It needs to be updated to include a couple of new fields, group_price and base_group_price. The full file, modified, is shown below. After updating this, I was able to reindex the Product Price index without error.

<?php
class OrganicInternet_SimpleConfigurableProducts_Catalog_Model_Resource_Eav_Mysql4_Product_Indexer_Price_Configurable
    extends Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Indexer_Price_Configurable
{
    protected function _isManageStock()
    {
        return Mage::getStoreConfigFlag(Mage_CatalogInventory_Model_Stock_Item::XML_PATH_MANAGE_STOCK);
    }

    #Don't pay any attention to cost of specific conf product options, as SCP doesn't use them
    protected function _applyConfigurableOption()
    {
        return $this;
    }

    #This calculates final price using SCP logic: minimal child product finalprice
    #instead of the just the entered configurable price
    #It uses a subquery/group-by hack to ensure that the various column values are all from the row with the lowest final price.
    #See Kasey Speakman comment here: http://dev.mysql.com/doc/refman/5.1/en/example-maximum-column-group-row.html
    #It's all quite complicated. :/
    protected function _prepareFinalPriceData($entityIds = null)
    {
        $this->_prepareDefaultFinalPriceTable();

        $write  = $this->_getWriteAdapter();
        $select = $write->select()
            ->from(
                array('e' => $this->getTable('catalog/product')),
                array())
            ->joinLeft(
                array('l' => $this->getTable('catalog/product_super_link')),
                'l.parent_id = e.entity_id',
                array())
            ->join(
                array('ce' => $this->getTable('catalog/product')),
                'ce.entity_id = l.product_id',
                array())
            ->join(
                array('pi' => $this->getIdxTable()),
                'ce.entity_id = pi.entity_id',
                array())
            ->join(
                array('cw' => $this->getTable('core/website')),
                'pi.website_id = cw.website_id',
                array())
            ->join(
                array('csg' => $this->getTable('core/store_group')),
                'csg.website_id = cw.website_id AND cw.default_group_id = csg.group_id',
                array())
            ->join(
                array('cs' => $this->getTable('core/store')),
                'csg.default_store_id = cs.store_id AND cs.store_id != 0',
                array())
            ->join(
                array('cis' => $this->getTable('cataloginventory/stock')),
                '',
                array())
            ->joinLeft(
                array('cisi' => $this->getTable('cataloginventory/stock_item')),
                'cisi.stock_id = cis.stock_id AND cisi.product_id = ce.entity_id',
                array())
            ->where('e.type_id=?', $this->getTypeId()); ## is this one needed?


        $productStatusExpr  = $this->_addAttributeToSelect($select, 'status', 'ce.entity_id', 'cs.store_id');

        if ($this->_isManageStock()) {
            $stockStatusExpr = new Zend_Db_Expr('IF(cisi.use_config_manage_stock = 0 AND cisi.manage_stock = 0,' . ' 1, cisi.is_in_stock)');
        } else {
            $stockStatusExpr = new Zend_Db_Expr('IF(cisi.use_config_manage_stock = 0 AND cisi.manage_stock = 1,' . 'cisi.is_in_stock, 1)');
        }
        $isInStockExpr = new Zend_Db_Expr("IF({$stockStatusExpr}, 1, 0)");

        $isValidChildProductExpr = new Zend_Db_Expr("{$productStatusExpr}");

        $select->columns(array(
            'entity_id'         => new Zend_Db_Expr('e.entity_id'),
            'customer_group_id' => new Zend_Db_Expr('pi.customer_group_id'),
            'website_id'        => new Zend_Db_Expr('cw.website_id'),
            'tax_class_id'      => new Zend_Db_Expr('pi.tax_class_id'),
            'orig_price'        => new Zend_Db_Expr('pi.price'),
            'price'             => new Zend_Db_Expr('pi.final_price'),
            'min_price'         => new Zend_Db_Expr('pi.final_price'),
            'max_price'         => new Zend_Db_Expr('pi.final_price'),
            'tier_price'        => new Zend_Db_Expr('pi.tier_price'),
            'base_tier'         => new Zend_Db_Expr('pi.tier_price'),
        	// modifications for 1.7
        	'group_price' 	 	=> new Zend_Db_Expr('pi.group_price'),
        	'base_group_price'  => new Zend_Db_Expr('pi.group_price')
        ));



        if (!is_null($entityIds)) {
            $select->where('e.entity_id IN(?)', $entityIds);
        }

        #Inner select order needs to be:
        #1st) If it's in stock come first (out of stock product prices aren't used if not-all products are out of stock)
        #2nd) Finalprice
        #3rd) $price, in case all finalPrices are NULL. (this gives the lowest price for all associated products when they're all out of stock)
        $sortExpr = new Zend_Db_Expr("${isInStockExpr} DESC, pi.final_price ASC, pi.price ASC");
        $select->order($sortExpr);

        /**
         * Add additional external limitation
         */
        Mage::dispatchEvent('prepare_catalog_product_index_select', array(
            'select'        => $select,
            'entity_field'  => new Zend_Db_Expr('e.entity_id'),
            'website_field' => new Zend_Db_Expr('cw.website_id'),
            'store_field'   => new Zend_Db_Expr('cs.store_id')
        ));


        #This uses the fact that mysql's 'group by' picks the first row, and the subselect is ordered as we want it
        #Bit hacky, but lots of people do it :) 
        $outerSelect = $write->select()
            ->from(array("inner" => $select), 'entity_id')
            ->group(array('inner.entity_id', 'inner.customer_group_id', 'inner.website_id'));

        $outerSelect->columns(array(
            'customer_group_id',
            'website_id',
            'tax_class_id',
            'orig_price',
            'price',
            'min_price',
            'max_price'     => new Zend_Db_Expr('MAX(inner.max_price)'),
            'tier_price',
            'base_tier',
        	// modifications for 1.7
            'group_price',
        	'base_group_price'	
            #'child_entity_id'
        ));
        $query = $outerSelect->insertFromSelect($this->_getDefaultFinalPriceTable());
        $write->query($query);
        #Mage::log("SCP Price inner query: " . $select->__toString());
        #Mage::log("SCP Price outer query: " . $outerSelect->__toString());

        return $this;
    }
}
Be Sociable, Share!

Comment

  1. Peter
    July 8, 2012 at 12:10 pm Reply

    I had to re-index the other indexes first and then I could index PRICE as well succesfully.

  2. Andrew
    July 14, 2012 at 1:23 pm Reply

    worked perfectly, thank you.

  3. Coen Ponsen
    July 15, 2012 at 8:36 am Reply

    I have the same error.. But thank god I dont use scp…

    However error lines and files and upgradinbg situation is the same.. Any ideas?

  4. Steve
    August 4, 2012 at 10:45 pm Reply

    I just upgraded from 1.4 to 1.7 and have been struggling to implement SCP with 1.7, so thank you for this post.

    Everything appears to be working on the product page, but I can’t figure out why the pricing does not appear on category pages.

    With 1.4 pricing would display as “Price From: $xx.xx where xx.xx was the lowest price of one the simple products associated with the configurable product., and now 1.7 it simply displays $0.00.

    Do you have any ideas to correct this problem on the category page?

    Cheers,

    Steve

  5. polly
    September 10, 2012 at 2:19 pm Reply

    You saved my day….. THX!

  6. James
    September 21, 2012 at 7:00 pm Reply

    Thanks, this worked first time!

  7. Anand
    October 8, 2012 at 7:25 am Reply

    Thanks. worked perfectly for me.

  8. Jake
    November 7, 2012 at 4:37 pm Reply

    Perfect! Thanks for posting this.

  9. Mjamb
    November 26, 2012 at 11:20 pm Reply

    Thanks!

  10. Hellau
    February 21, 2013 at 1:32 pm Reply

    Works great !
    Thx !

  11. Jano
    April 24, 2013 at 6:33 am Reply

    Works great. Thanks :)

  12. Andy Critchlow
    June 17, 2013 at 11:26 pm Reply

    Worked great for me to thanks folks legendary.

  13. Urfin
    August 22, 2013 at 4:58 am Reply

    This mistake cost me 2 weeks !

    A lot of THANKS, DANKE , СПАСИБО !

    :)

  14. Md Enamul Haque
    August 25, 2013 at 3:19 pm Reply

    Thanks a lot . It solved the problem in no time.

  15. Tutela Franck
    September 2, 2013 at 11:58 am Reply

    Works great. Thanks

  16. Telmo Teixeira
    October 25, 2013 at 4:00 pm Reply

    thanks, it works for 1.8 also!

  17. Karla
    December 7, 2013 at 3:27 am Reply

    Amazing. Researched for so long and finally found this answer. THANK YOU SO MUCH!

  18. Werner
    January 23, 2014 at 3:53 am Reply

    Thank you. This saves me hours on searching for the error. I wish OrganicInternet would change their extension with it.

  19. Tinkesh Kumar
    June 4, 2014 at 12:21 pm Reply

    Thanks. its works perfectly. Thanks for your valuable support.

Leave a Reply

Your email address will not be published. Required fields are marked *