Fix Quote Item Count Issue – Unable to Delete Products in Magento 1.9

deletemagento-1.9productquote

For some of the products I got the following error while trying to delete them using the backend:

SQLSTATE[22003]: Numeric value out of range: 1690 BIGINT UNSIGNED value is out of range in '([DB].q.items_count – 1)'

How can I debug this issue and understand what is the "guilty" code ?
I think I could add some log but I'm not sure where.

Some body claims is M2E to create the wrong quote, but M2E support answered me it is not true.

A possible solution is to act directly on the DB:

UPDATE sales_flat_quote_item qi 
INNER JOIN sales_flat_quote q 
ON qi.quote_id = q.entity_id 
SET q.items_count = 1 
WHERE q.items_count = 0 
AND qi.product_id = PRODUCT_ID

But this is not exactly a solution …

Update
After some investigation I found out many people reporting the same issue.
One of the solution proposed it to change the DB:

  • remove "UNSIGNED" attribute value for column items_count in table sales_flat_quote

is it safe ?

Best Answer

When you try to delete a product and observer call Mage_Sales_Model_Resource_Quote::substractProductFromQuotes

This method subtract 1 to the items_count of the quotes containing you product ... in case this is already 0 you got the out of range... exception. ( still not sure why in some occasion you already have it to 0 ... )

Solution:
I'm still not sure what is the cause of this, anyway instead of change DB structure I prefer to override the above method and apply a little modify to the SQL so that It never try to update the items_count with a value < 0:

public function substractProductFromQuotes($product)
    {
        $productId = (int)$product->getId();
        if (!$productId) {
            return $this;
        }
        $adapter = $this->_getWriteAdapter();
        $subSelect = $adapter->select();

        // FIX force the new value for items_count to be > 0 and respect the column attribute UNSIGNED
        $conditionCheck = $adapter->quoteIdentifier('q.items_count') . " > 0";
        $conditionTrue = $adapter->quoteIdentifier('q.items_count') . ' - 1';
        $ifSql = "IF (" . $conditionCheck . "," . $conditionTrue . ", 0)";

        $subSelect->from(false, array(
            'items_qty' => new Zend_Db_Expr(
                $adapter->quoteIdentifier('q.items_qty') . ' - ' . $adapter->quoteIdentifier('qi.qty')),
            'items_count' => new Zend_Db_Expr($ifSql)
        ))
            ->join(
                array('qi' => $this->getTable('sales/quote_item')),
                implode(' AND ', array(
                    'q.entity_id = qi.quote_id',
                    'qi.parent_item_id IS NULL',
                    $adapter->quoteInto('qi.product_id = ?', $productId)
                )),
                array()
            );

        $updateQuery = $adapter->updateFromSelect($subSelect, array('q' => $this->getTable('sales/quote')));
        $adapter->query($updateQuery);

        return $this;
    }

Extra note:
I'm not sure it is realted to this, but probably it is: I experienced problem in the frontend/add to cart for some customer getting the following exception:

Call to a member function setFinalPrice() on a non-object in /app/code/core/Mage/Sales/Model/Quote/Item/Abstract.php on line 73

The reason looks to be related to some quote set active but with items_count 0 ... the solution for me was to have a cron cleaning those quote.

Related Topic