6

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

vestland
  • 55,229
  • 37
  • 187
  • 305
Lorena
  • 193
  • 7
  • 18

3 Answers3

8

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

vvigilante
  • 81
  • 1
  • 1
  • 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')))` – Luke Jan 19 '17 at 17:36
  • 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. – frijj2k Mar 07 '18 at 11:53
  • Best solution to this problem I've seen. – mickburkejnr Mar 15 '18 at 15:35
6

Please try this:

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

It works for me.

Noor
  • 144
  • 2
  • 5
  • any info about why this is happening ? – WonderLand Aug 27 '14 at 10:18
  • 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 – Noor Sep 15 '14 at 06:42
  • 2
    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 – WonderLand Sep 15 '14 at 17:34
  • 3
    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 – WonderLand Sep 15 '14 at 17:39
2

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

keyur
  • 37
  • 2