what's wrong with this Magento query?

问题:

After upgrade Magento from 1.7 to 1.8 (with some 3rd extensions isntalled), I cannot save product any more. Whenever, I save product, I get this error:

SQL ERROR: SQLSTATE[21S01]: Insert value list does not match column list: 1136 Column count doesn't match value count at row 1

By debugging technique, I can find the error query:

SQL QUERY:

INSERT INTO `catalogrule_product_price`
 SELECT NULL AS `rule_product_price_id`,
 `dates`.`rule_date`,
 `t`.`customer_group_id`,
 `t`.`product_id`,
 MIN(rule_price) AS `rule_price`,
 1 AS `website_id`,
 `t`.`latest_start_date`,
 `t`.`earliest_end_date`
 FROM 
(SELECT `cppt`.`customer_group_id`, 
`cppt`.`product_id`, 
CASE WHEN IFNULL((@group_id), 'N/A') != cppt.grouped_id THEN @price := CASE `cppt`.`action_operator` WHEN 'to_percent' THEN cppt.price * cppt.action_amount/100 WHEN 'by_percent' THEN cppt.price * (1 - cppt.action_amount/100) WHEN 'to_fixed' THEN IF((cppt.action_amount < cppt.price), cppt.action_amount, cppt.price) WHEN 'by_fixed' THEN IF((0 > cppt.price - cppt.action_amount), 0, cppt.price - cppt.action_amount) END WHEN IFNULL((@group_id), 'N/A') = cppt.grouped_id AND IFNULL((@action_stop), 0) = 0 THEN @price := CASE `cppt`.`action_operator` WHEN 'to_percent' THEN @price * cppt.action_amount/100 WHEN 'by_percent' THEN @price * (1 - cppt.action_amount/100) WHEN 'to_fixed' THEN IF((cppt.action_amount < @price), cppt.action_amount, @price) WHEN 'by_fixed' THEN IF((0 > @price - cppt.action_amount), 0, @price - cppt.action_amount) END ELSE @price := @price END AS `rule_price`, `cppt`.`from_date` AS `latest_start_date`, `cppt`.`to_date` AS `earliest_end_date`, CASE WHEN IFNULL((@group_id), 'N/A') != cppt.grouped_id THEN @action_stop := cppt.action_stop WHEN IFNULL((@group_id), 'N/A') = cppt.grouped_id THEN @action_stop := IFNULL((@action_stop), 0) + cppt.action_stop END, @group_id := cppt.grouped_id,
 `cppt`.`from_time`,
 `cppt`.`to_time` 
FROM 
`catalogrule_product_price_tmp` AS `cppt`
 ORDER BY `cppt`.`grouped_id` ASC, 
`cppt`.`sort_order` ASC, 
`cppt`.`rule_product_id` ASC) AS `t` INNER JOIN (SELECT DATE_ADD(FROM_UNIXTIME(1382202000), INTERVAL -1 DAY) AS rule_date UNION SELECT FROM_UNIXTIME(1382202000) AS rule_date UNION SELECT DATE_ADD(FROM_UNIXTIME(1382202000), INTERVAL 1 DAY) AS rule_date) AS `dates` ON 1=1 WHERE (UNIX_TIMESTAMP(dates.rule_date) >= from_time) AND (IF((to_time = 0), 1, UNIX_TIMESTAMP(dates.rule_date) <= to_time)) 
GROUP BY `customer_group_id`, `product_id`, `dates`.`rule_date`;

Can you point me out why this query is having that error? Thanks!

UPDATE : Reason is TBT Rewards module added a column named "rules_hash" in catalogrule_product_price table.

回答1:

Having just upgraded to 1.9.2.2 for a friend's website and running into this problem, I put the following patch in place in the file,

[magento/app/code/core/Mage/CatalogRule/Model/Action/Index/Refresh.php]

I can't say what onflow effects there might be and future updates will have to be careful to not miss this feature if this file is overwritten of course, but to get started add the line:

'rules_hash'            => new Zend_Db_Expr('NULL'),

to make the select statement look as follows:

$select = $this->_connection->select()
        ->from($indexSelect, array())
        ->joinInner(
            array(
                'dates' => $this->_connection->select()->union(
                    array(
                        new Zend_Db_Expr(
                            'SELECT ' . $this->_connection->getDateAddSql(
                                $this->_connection->fromUnixtime($time),
                                -1,
                                Varien_Db_Adapter_Interface::INTERVAL_DAY
                            ) . ' AS rule_date'
                        ),
                        new Zend_Db_Expr('SELECT ' . $this->_connection->fromUnixtime($time) . ' AS rule_date'),
                        new Zend_Db_Expr(
                            'SELECT ' . $this->_connection->getDateAddSql(
                                $this->_connection->fromUnixtime($time),
                                1,
                                Varien_Db_Adapter_Interface::INTERVAL_DAY
                            ) . ' AS rule_date'
                        ),
                    )
                )
            ),
            '1=1',
            array()
        )
        ->columns(
            array(
                'rule_product_price_id' => new Zend_Db_Expr('NULL'),
                'rule_date'             => 'dates.rule_date',
                'customer_group_id'     => 'customer_group_id',
                'product_id'            => 'product_id',
                'rule_price'            => 'MIN(rule_price)',
                'website_id'            => new Zend_Db_Expr($website->getId()),
                'latest_start_date'     => 'latest_start_date',
                'earliest_end_date'     => 'earliest_end_date',
                /**
                Added because rules_hash was created by some module and not handled properly,
                see http://stackoverflow.com/questions/19480415/whats-wrong-with-this-magento-query
                */
                'rules_hash'            => new Zend_Db_Expr('NULL'),
            )
        )
        ->where(new Zend_Db_Expr($this->_connection->getUnixTimestamp('dates.rule_date') . " >= from_time"))
        ->where(
            $this->_connection->getCheckSql(
                new Zend_Db_Expr('to_time = 0'),
                new Zend_Db_Expr(1),
                new Zend_Db_Expr($this->_connection->getUnixTimestamp('dates.rule_date') . " <= to_time")
            )
        )
        ->group(array('customer_group_id', 'product_id', 'dates.rule_date'));

    return $select;

回答2:

Follow @Dale Holborow also replace below code in app\code\community\TBT\Rewards\Model\Observer\Catalog\Product\Flat\Update\Product.php

    if ($product) {
        $target_product_id = $product->getEntityId ();
        if (! $target_product_id)
            $target_product_id = null;

    //if no product id
    //available, reset our assumption because this must be some other
    //unrecognized request.
    } 

replace with

    if ( $product ) {
        if ($product instanceof Mage_Catalog_Model_Product) {
            $target_product_id = $product->getEntityId();
        } elseif(is_numeric($product)) {
            $target_product_id = $product;
        }

        if ( ! $target_product_id ) 
            $target_product_id = null;
    }

回答3:

Have you identified which extension is generating that query? It seems like the insert statement isn't taking into account any new fields in the catalogrule_product_price table. You can fix it by limiting the INSERT to specific columns:

INSERT INTO `catalogrule_product_price`
VALUES (rule_product_price_id, rule_date, customer_group_id, product_id, rule_price, website_id, latest_start_date, earliest_end_date)
 SELECT NULL AS `rule_product_price_id`,
 `dates`.`rule_date`,
 `t`.`customer_group_id`,
 `t`.`product_id`,
 MIN(rule_price) AS `rule_price`,
 1 AS `website_id`,
 `t`.`latest_start_date`,
 `t`.`earliest_end_date`
 FROM 
(SELECT `cppt`.`customer_group_id`, 
`cppt`.`product_id`, 
CASE WHEN IFNULL((@group_id), 'N/A') != cppt.grouped_id THEN @price := CASE `cppt`.`action_operator` WHEN 'to_percent' THEN cppt.price * cppt.action_amount/100 WHEN 'by_percent' THEN cppt.price * (1 - cppt.action_amount/100) WHEN 'to_fixed' THEN IF((cppt.action_amount < cppt.price), cppt.action_amount, cppt.price) WHEN 'by_fixed' THEN IF((0 > cppt.price - cppt.action_amount), 0, cppt.price - cppt.action_amount) END WHEN IFNULL((@group_id), 'N/A') = cppt.grouped_id AND IFNULL((@action_stop), 0) = 0 THEN @price := CASE `cppt`.`action_operator` WHEN 'to_percent' THEN @price * cppt.action_amount/100 WHEN 'by_percent' THEN @price * (1 - cppt.action_amount/100) WHEN 'to_fixed' THEN IF((cppt.action_amount < @price), cppt.action_amount, @price) WHEN 'by_fixed' THEN IF((0 > @price - cppt.action_amount), 0, @price - cppt.action_amount) END ELSE @price := @price END AS `rule_price`, `cppt`.`from_date` AS `latest_start_date`, `cppt`.`to_date` AS `earliest_end_date`, CASE WHEN IFNULL((@group_id), 'N/A') != cppt.grouped_id THEN @action_stop := cppt.action_stop WHEN IFNULL((@group_id), 'N/A') = cppt.grouped_id THEN @action_stop := IFNULL((@action_stop), 0) + cppt.action_stop END, @group_id := cppt.grouped_id,
 `cppt`.`from_time`,
 `cppt`.`to_time` 
FROM 
`catalogrule_product_price_tmp` AS `cppt`
 ORDER BY `cppt`.`grouped_id` ASC, 
`cppt`.`sort_order` ASC, 
`cppt`.`rule_product_id` ASC) AS `t` INNER JOIN (SELECT DATE_ADD(FROM_UNIXTIME(1382202000), INTERVAL -1 DAY) AS rule_date UNION SELECT FROM_UNIXTIME(1382202000) AS rule_date UNION SELECT DATE_ADD(FROM_UNIXTIME(1382202000), INTERVAL 1 DAY) AS rule_date) AS `dates` ON 1=1 WHERE (UNIX_TIMESTAMP(dates.rule_date) >= from_time) AND (IF((to_time = 0), 1, UNIX_TIMESTAMP(dates.rule_date) <= to_time)) 
GROUP BY `customer_group_id`, `product_id`, `dates`.`rule_date`;