5

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 ?

  • Put more details. – Amit Bera Sep 8 '14 at 5:35
  • 1
    what kind of detail could I add ? right now the only thing clear is that the items_count = 0 create the issue, setting it to 1 fix the problem. – Fra Sep 8 '14 at 5:38
5

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.

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)
1

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).

0

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
  • actually this issue/question is related to Magento 1 – Fra Jan 11 '17 at 14:46
  • 1
    There is this exact same issue on Magento 2 – Antoine Subit Jan 11 '17 at 16:41

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.