Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

Index Management | Product Prices | ReIndex Data Problem
 
webmacster
Jr. Member
 
Total Posts:  9
Joined:  2011-08-15
 

After upgrading a from 1.4.2 to 1.7.0.2

I am not able to reindex the Product Prices. Message: There was a problem with reindexing process.

exception.log:

2012-07-27T09:10:05+00:00 DEBUG (7): Exception message: SQLSTATE[21S01]: Insert value list does not match column list: 1136 Column count doesn’t match value count at row 1
Trace: #0 /chroot/home/html/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 /chroot/home/html/lib/Zend/Db/Statement.php(300): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 /chroot/home/html/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#3 /chroot/home/html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query(’INSERT INTO `ca...’, Array)
#4 /chroot/home/html/lib/Varien/Db/Adapter/Pdo/Mysql.php(419): Zend_Db_Adapter_Pdo_Abstract->query(’INSERT INTO `ca...’, Array)
#5 /chroot/home/html/app/code/community/OrganicInternet/SimpleConfigurableProducts/Catalog/Model/Resource/Eav/Mysql4/Product/Indexer/Price/Configurable.php(133): Varien_Db_Adapter_Pdo_Mysql->query(’INSERT INTO `ca...’)
#6 /chroot/home/html/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 /chroot/home/html/app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Price.php(385): Mage_Catalog_Model_Resource_Product_Indexer_Price_Configurable->reindexAll()
#8 /chroot/home/html/app/code/core/Mage/Index/Model/Indexer/Abstract.php(143): Mage_Catalog_Model_Resource_Product_Indexer_Price->reindexAll()
#9 /chroot/home/html/app/code/core/Mage/Index/Model/Process.php(209): Mage_Index_Model_Indexer_Abstract->reindexAll()
#10 /chroot/home/html/app/code/core/Mage/Index/Model/Process.php(255): Mage_Index_Model_Process->reindexAll()
#11 /chroot/home/html/app/code/core/Mage/Index/controllers/Adminhtml/ProcessController.php(124): Mage_Index_Model_Process->reindexEverything()
#12 /chroot/home/html/app/code/core/Mage/Core/Controller/Varien/Action.php(419): Mage_Index_Adminhtml_ProcessController->reindexProcessAction()
#13 /chroot/home/html/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(250): Mage_Core_Controller_Varien_Action->dispatch(’reindexProcess’)
#14 /chroot/home/html/app/code/core/Mage/Core/Controller/Varien/Front.php(176): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#15 /chroot/home/html/app/code/core/Mage/Core/Model/App.php(354): Mage_Core_Controller_Varien_Front->dispatch()
#16 /chroot/home/html/app/Mage.php(683): Mage_Core_Model_App->run(Array)
#17 /chroot/home/html/index.php(87): Mage::run(’’, ‘store’)
#18 {main}

________________________________________

 
Magento Community Magento Community
Magento Community
Magento Community
 
APLUK
Jr. Member
 
Total Posts:  5
Joined:  2012-07-29
 

Hi there, Any solution to this? Fingers crossed.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Sindre|ProperHost
Mentor
 
Avatar
Total Posts:  1158
Joined:  2008-04-24
 

The problem is caused by this extension: http://www.magentocommerce.com/magento-connect/simple-configurable-products.html

It is not compatible with Magento 1.7.

Fortunately, there is a fix available here: https://github.com/organicinternet/magento-configurable-simple/pull/85

It will update the SQL script with the new columns.

Replace

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

with this:

<?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'),
            
'group_price'       => new Zend_Db_Expr('pi.group_price'),
            
'base_group_price'  => new Zend_Db_Expr('pi.base_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;
    
}
}
 
Magento Community Magento Community
Magento Community
Magento Community
 
Richardmaximun
Jr. Member
 
Total Posts:  30
Joined:  2010-01-27
 

I confirm it.

I fixed with those solution both stores.

Regards,

RichardMax

Sindre|ProperHost - 14 August 2012 11:21 AM

The problem is caused by this extension: http://www.magentocommerce.com/magento-connect/simple-configurable-products.html

It is not compatible with Magento 1.7.

Fortunately, there is a fix available here: https://github.com/organicinternet/magento-configurable-simple/pull/85

It will update the SQL script with the new columns.

Replace

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

with this:
<?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'),
            
'group_price'       => new Zend_Db_Expr('pi.group_price'),
            
'base_group_price'  => new Zend_Db_Expr('pi.base_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;
    
}
}
 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top