Magento – Cannot delete products from admin

admindatabaseerrormagento-1.9product

When i delete products from admin it give error message

SQLSTATE[22003]: Numeric value out of range: 1690 BIGINT UNSIGNED value is out of range in '(`ponicosmetics`.`tag_summary`.`products` - 1)'

query was:

UPDATE `tag_summary` SET `products` = products - 1 WHERE (tag_id IN ('7', '8', '30', '60', '61', '65', '86', '87', '88', '210'))

this.

I already follow following steps:

Go into your database using PhpMyAdmin.
Do a backup (always a good practice to back-up first).
Select the Magento database you’re using.
Select table sales_flat_quote (on second page).
Select structure tab
Select ‘change‘ on row called ‘items_count‘
Go to the drop-down on the column named ‘Attributes‘ and change value to the very top value which is blank ‘(no value)‘, as opposed to the default selection ‘UNSIGNED‘ .
Click save, and you’re good to go!

But this error "SQLSTATE[22003]:

Numeric value out of range: 1690 BIGINT UNSIGNED value is out of range in '(`ponicosmetics`.`tag_summary`.`products` - 1)', query was: UPDATE `tag_summary` SET `products` = products - 1 WHERE (tag_id IN ('7', '8', '30', '60', '61', '65', '86', '87', '88', '210')) "

has comes .
Please suggest me any solution.

Best Answer

The message says, that the column is still UNSIGNED. The steps that you followed have changed the items_count column, but actually the problem is in the products column.

But this would only be a workaround to allow negative numbers in the column. A negative product count does not make sense, so the root problem is that your data in tag_summary index is inconsistent.

You should reindex the Tag Aggregation Data index and try again.

Related Solutions

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

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.

Magento – After update i got strange error

A (temporary - until the developers release a new version) fix that worked for me:

In file

app/code/community/Magestore/Bannerslider/Model/Mysql4/Banner/Collection.php

at line 60 comment the following:

return parent::addFieldToFilter("IF($field.value IS NULL, main_table.$field, $field.value)", $condition);

and replace it with

 $this->getSelect()->where("IF($field.value IS NULL, main_table.$field, $field.value) = 0");

As per answer found here: http://answers.magentocommerce.com/answers/4643-en_us/product/3912/magestore-banner-slider-questions-answers/questions.htm?sort=recentq#BVQASAnswerText598988

Best Answer

Related Solutions

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

Magento – After update i got strange error

Related Topic