Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

Unable to delete Catalog products
 
Bargain2Express
Jr. Member
 
Total Posts:  5
Joined:  2012-03-02
 

Hi,
I used to have M2E pro, and for some reason when an Item(3rd party listing) sold; a product was created in the catalog field. The problem is that I’ve uninstalled M2E and I also would like to delete these products. However when I try to do this, I get the following error

SQLSTATE[22003]: Numeric value out of range: 1690 BIGINT UNSIGNED value is out of range in ‘(`bargain2_magento`.`q`.`items_count` - 1)’

Also, is there a way to delete the order history ?

 
Magento Community Magento Community
Magento Community
Magento Community
 
askeuhd
Jr. Member
 
Total Posts:  1
Joined:  2010-10-26
 

Hi

I had the same problem.

There was some trash data in the product description which I removed, and then I was able to delete it.

 
Magento Community Magento Community
Magento Community
Magento Community
 
zzgus
Jr. Member
 
Total Posts:  12
Joined:  2012-05-27
 

Same here.
Any solution?

Thankyou
Gus

 
Magento Community Magento Community
Magento Community
Magento Community
 
diocleziano
Jr. Member
 
Avatar
Total Posts:  12
Joined:  2010-04-06
 

I’ve the same problem. Can anyone reply?

 
Magento Community Magento Community
Magento Community
Magento Community
 
viator
Jr. Member
 
Total Posts:  1
Joined:  2012-12-01
 

Hi, I don’t know if this is the perfect solution, but it worked for me…

the SOLUTION is to remove ‘UNSIGNED’ attribute from `time_end` and `time_start` columns in MySQL table structure.

To do this
I’m using the example of the error location (the one it showed when you got the error) in the form of dbName:tableName:fieldName so if your data base is called “mage”, your table is called “tag_summary” and the field it has a problem with is called “products”
1) go into your phpMyAdmin,
2) select the database for your Magento installation (mage in our example) from the left column,
3) Select the table name in the left column ("tag_summary")
4) Select structure from the top menu
5) Select “change” to the right the field name from the list (products)
6) find the “attributes” drop down and change from “unsigned” to the very top one (it is blank)
7) hit “save”
8) go back to your Magento store and refresh the page, the error will be gone and you can delete the products!

In the first post he used “‘(`bargain2_magento`.`q`.`items_count` - 1)’” that is dbName = “bargain2_magento” tableName = “q” fieldName = “items_count” just thought I would add an actual example for clarity.

 
Magento Community Magento Community
Magento Community
Magento Community
 
fancyfiber
Jr. Member
 
Avatar
Total Posts:  27
Joined:  2009-12-25
 

What I discovered while looking into this same issue on a site is that the table q is a temporary table created during the delete process, so changing to an unsigned isn’t possible for this particular instance. It really isn’t the best solution anyway, but I think we realized that already.

The problem in my case is that there are quotes created in the sales_flat_quote table with an item_count of 0. During the product delete process, it attempts to reduce the item_count in the quote by 1.

My process of fixing this was to run a select statement on the sales_flat_quote table to find any records with item_count of 0. Then I exported these records and removed them from the DB. Once these records were removed, the delete completed properly. I put these records back in the database when I was done, which may not be needed, but I felt better having them there since they were there before.

I believe this is really an issue with the M2E plugin not properly putting data in the sales_flat_quote table, but this is pure speculation on my part, but so far in all the research I did on the subject, it appears that only sites running the M2E plugin were having this problem, and it may be fixed in later releases. The site I was working on was not up to date. Hope this helps someone.

I put up a more detailed description of what I found and how I fixed the issue on my client’s site on my blog at: http://hosting.fancyfiber.com/2013/02/01/sqlstate22003-numeric-value-out-of-range/

 
Magento Community Magento Community
Magento Community
Magento Community
 
frano9
Jr. Member
 
Avatar
Total Posts:  20
Joined:  2010-01-21
Manchester
 

If you want to avoid changing stuff in the database,

I changed the “substractProductFromQuotes” function in Mage/Sales/Model/Resource/Quote.php to be :

‘items_count’ => new Zend_Db_Expr(’IF(’.$adapter->quoteIdentifier(’q.items_count’) . ‘ > 0, ‘.$adapter->quoteIdentifier(’q.items_count’) .’,1) - 1’)

And it seems to be working!

 
Magento Community Magento Community
Magento Community
Magento Community
 
borriglione_le
Jr. Member
 
Avatar
Total Posts:  18
Joined:  2009-02-16
 

Identified the products
=======================

SELECT q.*
FROM sales_flat_quote_item qi
INNER JOIN sales_flat_quote q
ON qi.quote_id = q.entity_id
WHERE q.items_count = 0
AND qi.product_id = PRODUCT_ID

Increased the products
=====================

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

 
Magento Community Magento Community
Magento Community
Magento Community
 
rodrigofox
Jr. Member
 
Total Posts:  5
Joined:  2011-05-25
 

Hi borriglione_le,

borriglione_le - 30 May 2013 05:24 AM

Identified the products
=======================

SELECT q.*
FROM sales_flat_quote_item qi
INNER JOIN sales_flat_quote q
ON qi.quote_id = q.entity_id
WHERE q.items_count = 0
AND qi.product_id = PRODUCT_ID


Increased the products
=====================

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

Thank you! I was studying a lot about it, the problem was solved.

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top