Error in magento when I try to delete a product

Question

When I try to delete a product in Magento I get the next error and I don't know where is the problem.

SQLSTATE[22003]: Numeric value out of range: 1690 BIGINT UNSIGNED value is out of range

Avrilavrit

Take a look @ magentocommerce.com/boards/viewthread/286946 – Width

are you still looking for a solution ? let me know and I will post my fix – Antiquarian

Answer 1

Please try this:

DELETE FROM sales_flat_quote WHERE updated_at < DATE_SUB(Now(),INTERVAL 30 DAY);

It works for me.

Eam

any info about why this is happening ? – Antiquarian

Magento follows vast scale of data structure for any execution. When your DB size is large, it will show execution time error. In that case, you can increase apache execution time from htdocs/phpmyadmin. Thanks – Eam

I found out this a bug in Magento ... basically the observer that update the quotes when we delete a product is not taking care that the column items_qty in the DB is UNSIGNED ... sometime this observer try to insert -1 and here comes the issue – Antiquarian

beside your solution may work, 1' the problematic quote could be > 30 days, 2' delete quote is not nice, 3' it is not a real fix as it is not fixing the root of the issue – Antiquarian

Answer 2

I managed to make it work editing the file app/code/core/Mage/Sales/Model/Resource/Quote.php at row 221:

'items_count' => new Zend_Db_Expr("CASE WHEN q.items_count>0 THEN " . $adapter->quoteIdentifier('q.items_count') . ' - 1 ELSE 0 END')

basically I added the case when statement to prevent it subtracting 1 when the value already is 0

Janitajanith

This should be the accepted answer as it is the only one that actually fixes the bug in the magento core. It could be improved slightly by making sure both instances of q.items_count are quoted into the sql: 'items_count' => new Zend_Db_Expr(sprintf('CASE WHEN %1$s > 0 THEN %1$s - 1 ELSE 0 END', $adapter->quoteIdentifier('q.items_count'))) – Deception

Agreed, though you shouldn't be editing Magento core files directly. Instead, override the file by placing a copy in: app/code/local/Mage/Sales/Model/Resource/Quote.php and make your changes there. – Tavel

Best solution to this problem I've seen. – Kiwi

Answer 3

to solve this issue follow below step

1. Go into your database using PhpMyAdmin.
2. Do a backup (always a good practice to back-up first).
3. Select the Magento database you’re using.
4. Select table sales_flat_quote (on second page).
5. Select structure tab
6. Select ‘change‘ on row called ‘items_count‘
7. 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‘ .
8. Click save, and you’re good to go!

and delete product now

Morse

It is not a good idea to change Magento's default database settings! – Gleason