0

Hi hope this is a quick fix, I'm getting the following error from the MySql log in Magento 1.9.2 when updating the Product Price Index.

The screen says Cannot initialize the indexer process. and the following is placed in the log.

I can see it relates to Simple Configurable products but that's been installed for ages without any real issues. I have tried reinstalling this extension with no joy.

I have read other solutions which suggest the server doesn't have enough RAM for large catalogues but this only has a handful of products and other installations with hundreds of thousands of products run fine.

Thanks in advance for any help

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 /Applications/MAMP/htdocs/milliesv2/lib/Zend/Db/Statement/Pdo.php:228
Stack trace:
#0 /Applications/MAMP/htdocs/milliesv2/lib/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /Applications/MAMP/htdocs/milliesv2/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#2 /Applications/MAMP/htdocs/milliesv2/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#3 /Applications/MAMP/htdocs/milliesv2/lib/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#4 /Applications/MAMP/htdocs/milliesv2/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO `ma...', Array)
#5 /Applications/MAMP/htdocs/milliesv2/lib/Varien/Db/Adapter/Pdo/Mysql.php(504): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO `ma...', Array)
#6 /Applications/MAMP/htdocs/milliesv2/app/code/community/OrganicInternet/SimpleConfigurableProducts/Catalog/Model/Resource/Eav/Mysql4/Product/Indexer/Price/Configurable.php(133): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO `ma...')
#7 /Applications/MAMP/htdocs/milliesv2/app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Price/Configurable.php(48): OrganicInternet_SimpleConfigurableProducts_Catalog_Model_Resource_Eav_Mysql4_Product_Indexer_Price_Configurable->_prepareFinalPriceData()
#8 /Applications/MAMP/htdocs/milliesv2/app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Price.php(385): Mage_Catalog_Model_Resource_Product_Indexer_Price_Configurable->reindexAll()
#9 /Applications/MAMP/htdocs/milliesv2/app/code/core/Mage/Index/Model/Indexer/Abstract.php(143): Mage_Catalog_Model_Resource_Product_Indexer_Price->reindexAll()
#10 /Applications/MAMP/htdocs/milliesv2/app/code/core/Mage/Index/Model/Process.php(212): Mage_Index_Model_Indexer_Abstract->reindexAll()
#11 /Applications/MAMP/htdocs/milliesv2/app/code/core/Mage/Index/Model/Process.php(260): Mage_Index_Model_Process->reindexAll()
#12 /Applications/MAMP/htdocs/milliesv2/shell/indexer.php(168): Mage_Index_Model_Process->reindexEverything()
#13 /Applications/MAMP/htdocs/milliesv2/shell/indexer.php(216): Mage_Shell_Compiler->run()
#14 {main}

Next exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[21S01]: Insert value list does not match column list: 1136 Column count doesn't match value count at row 1, query was: INSERT INTO `mage_catalog_product_index_price_final_idx` SELECT `inner`.`entity_id`, `inner`.`customer_group_id`, `inner`.`website_id`, `inner`.`tax_class_id`, `inner`.`orig_price`, `inner`.`price`, `inner`.`min_price`, MAX(inner.max_price) AS `max_price`, `inner`.`tier_price`, `inner`.`base_tier` FROM (SELECT e.entity_id AS `entity_id`, pi.customer_group_id AS `customer_group_id`, cw.website_id AS `website_id`, pi.tax_class_id AS `tax_class_id`, pi.price AS `orig_price`, pi.final_price AS `price`, pi.final_price AS `min_price`, pi.final_price AS `max_price`, pi.tier_price AS `tier_price`, pi.tier_price AS `base_tier` FROM `mage_catalog_product_entity` AS `e`
 LEFT JOIN `mage_catalog_product_super_link` AS `l` ON l.parent_id = e.entity_id
 INNER JOIN `mage_catalog_product_entity` AS `ce` ON ce.entity_id = l.product_id
 INNER JOIN `mage_catalog_product_index_price_idx` AS `pi` ON ce.entity_id = pi.entity_id
 INNER JOIN `mage_core_website` AS `cw` ON pi.website_id = cw.website_id
 INNER JOIN `mage_core_store_group` AS `csg` ON csg.website_id = cw.website_id AND cw.default_group_id = csg.group_id
 INNER JOIN `mage_core_store` AS `cs` ON csg.default_store_id = cs.store_id AND cs.store_id != 0
 CROSS JOIN `mage_cataloginventory_stock` AS `cis`
 LEFT JOIN `mage_cataloginventory_stock_item` AS `cisi` ON cisi.stock_id = cis.stock_id AND cisi.product_id = ce.entity_id
 LEFT JOIN `mage_catalog_product_entity_int` AS `tad_status` ON tad_status.entity_id = ce.entity_id AND tad_status.attribute_id = 96 AND tad_status.store_id = 0
 LEFT JOIN `mage_catalog_product_entity_int` AS `tas_status` ON tas_status.entity_id = ce.entity_id AND tas_status.attribute_id = 96 AND tas_status.store_id = cs.store_id WHERE (e.type_id='configurable') ORDER BY IF(IF(cisi.use_config_manage_stock = 0 AND cisi.manage_stock = 0, 1, cisi.is_in_stock), 1, 0) DESC, pi.final_price ASC, pi.price ASC) AS `inner` GROUP BY `inner`.`entity_id`,
    `inner`.`customer_group_id`,
    `inner`.`website_id` ON DUPLICATE KEY UPDATE `tax_class_id` = VALUES(`tax_class_id`), `orig_price` = VALUES(`orig_price`), `price` = VALUES(`price`), `min_price` = VALUES(`min_price`), `max_price` = VALUES(`max_price`), `tier_price` = VALUES(`tier_price`), `base_tier` = VALUES(`base_tier`), `group_price` = VALUES(`group_price`), `base_group_price` = VALUES(`base_group_price`)' in /Applications/MAMP/htdocs/milliesv2/lib/Zend/Db/Statement/Pdo.php:235
Stack trace:
#0 /Applications/MAMP/htdocs/milliesv2/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 /Applications/MAMP/htdocs/milliesv2/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 /Applications/MAMP/htdocs/milliesv2/lib/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 /Applications/MAMP/htdocs/milliesv2/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO `ma...', Array)
#4 /Applications/MAMP/htdocs/milliesv2/lib/Varien/Db/Adapter/Pdo/Mysql.php(504): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO `ma...', Array)
#5 /Applications/MAMP/htdocs/milliesv2/app/code/community/OrganicInternet/SimpleConfigurableProducts/Catalog/Model/Resource/Eav/Mysql4/Product/Indexer/Price/Configurable.php(133): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO `ma...')
#6 /Applications/MAMP/htdocs/milliesv2/app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Price/Configurable.php(48): OrganicInternet_SimpleConfigurableProducts_Catalog_Model_Resource_Eav_Mysql4_Product_Indexer_Price_Configurable->_prepareFinalPriceData()
#7 /Applications/MAMP/htdocs/milliesv2/app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Price.php(385): Mage_Catalog_Model_Resource_Product_Indexer_Price_Configurable->reindexAll()
#8 /Applications/MAMP/htdocs/milliesv2/app/code/core/Mage/Index/Model/Indexer/Abstract.php(143): Mage_Catalog_Model_Resource_Product_Indexer_Price->reindexAll()
#9 /Applications/MAMP/htdocs/milliesv2/app/code/core/Mage/Index/Model/Process.php(212): Mage_Index_Model_Indexer_Abstract->reindexAll()
#10 /Applications/MAMP/htdocs/milliesv2/app/code/core/Mage/Index/Model/Process.php(260): Mage_Index_Model_Process->reindexAll()
#11 /Applications/MAMP/htdocs/milliesv2/shell/indexer.php(168): Mage_Index_Model_Process->reindexEverything()
#12 /Applications/MAMP/htdocs/milliesv2/shell/indexer.php(216): Mage_Shell_Compiler->run()
#13 {main}
iMac:shell Alex$ 
0

The configurable.php file needs to be updated to reflect database changes that happened after 1.6 was released

app/code/community/OrganicInternet/SimpleConfigurableProducts/Catalog/Model/Resource/Eav/Mysql4/Product/Indexer/Price/Configurable.php

Same issue is on github here: https://github.com/organicinternet/magento-configurable-simple/issues/108

Here is a link to the file to use:

https://github.com/organicinternet/magento-configurable-simple/blob/4d9cbe81be103c12b2e478811281395748e32fc3/app/code/community/OrganicInternet/SimpleConfigurableProducts/Catalog/Model/Resource/Eav/Mysql4/Product/Indexer/Price/Configurable.php

Just replace this file and it should work fine - I have just tested it on Magento ver. 1.9.2.4

0

1) replace file content with this content in : /app/code/community/OrganicInternet/SimpleConfigurableProducts/Catalog/Model/Resource/Eav/Mysql4/Product/Indexer/Price/Configurable.php

#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'),
        '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',
        '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;
}

}

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.