Issue with quote (wrong items_count): not possible to delete products

Question

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 ?

Answer 1

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.

Answer 2

Mage_Sales_Model_Quote::collectTotals()

The item count on the quote is set in the above method. You could put a log there to see as to what is going on.

 $this->setItemsCount($this->getItemsCount()+1)

Answer 3

I just came across the same problem while I was working on a product sync. I was able to simply delete the products using a CSV source in importexport/import model (hence, performing a product->delete via CSV from backend may fix that problem for one or the other here without much effort).

Answer 4

I've create a patch with the accept anwser : https://github.com/asubit/magento2-patch-item-count

To apply the patch download it. Place it in your project folder and execute the following command :

git apply item_count.patch