1

My client had a big traffic on server for 1 day. They added one product (ID X) with quantity = 10000. After few hours they sold about 15000 items of this product - I can say because of simple query:

SELECT SUM(sales_flat_order_item.qty_ordered) FROM sales_flat_order_item INNER JOIN sales_flat_order ON sales_flat_order_item.order_id = sales_flat_order.entity_id WHERE sales_flat_order.created_at >= 'XXXX-XX-XX 08:00:02' AND sales_flat_order.created_at <= 'XXXX-XX-XX 13:00:00' WHERE sales_flat_order_item.product_id = X

So how it's possible that Magento sold so many items? I'm sure that they've started from 8:00 AM with exactly 10000 items.

I've found MySQL log with all queries from that day. There are many weird entries. Starting from 8:00 AM. They started with 10000 items like I wrote before. I can see it from that query:

UPDATE `cataloginventory_stock_item` SET `product_id` = 'X', `stock_id` = '1', `qty` = '10000', `min_qty` = '0', `use_config_min_qty` = '1', `is_qty_decimal` = '0', `backorders` = '0', `use_config_backorders` = '1', `min_sale_qty` = '1', `use_config_min_sale_qty` = '1', `max_sale_qty` = '10', `use_config_max_sale_qty` = '0', `is_in_stock` = '1', `low_stock_date` = 'XXXX-XX-XX 08:00:02', `notify_stock_qty` = NULL, `use_config_notify_stock_qty` = '1', `manage_stock` = '0', `use_config_manage_stock` = '1', `stock_status_changed_auto` = '0', `use_config_qty_increments` = '1', `qty_increments` = '0', `use_config_enable_qty_inc` = '1', `enable_qty_increments` = '0', `is_decimal_divided` = '0' WHERE (item_id='X')

After this it's getting really weird. They set product as visible and they started to sell it. Here are first queries for first orders (shown in execute order):

UPDATE `cataloginventory_stock_item` SET `qty` = CASE product_id WHEN X THEN qty-2 ELSE qty END WHERE (product_id IN (X)) AND (stock_id = 1)
UPDATE `cataloginventory_stock_item` SET `qty` = CASE product_id WHEN X THEN qty-1 ELSE qty END WHERE (product_id IN (X)) AND (stock_id = 1)

So there were two orders, so Magento should substract 3 items, but next query is:

UPDATE `cataloginventory_stock_item` SET `product_id` = 'X', `stock_id` = '1', `qty` = '9998', `min_qty` = '0', `use_config_min_qty` = '1', `is_qty_decimal` = '0', `backorders` = '0', `use_config_backorders` = '1', `min_sale_qty` = '1', `use_config_min_sale_qty` = '1', `max_sale_qty` = '10', `use_config_max_sale_qty` = '0', `is_in_stock` = '1', `low_stock_date` = 'XXXX-XX-XX 08:03:49', `notify_stock_qty` = NULL, `use_config_notify_stock_qty` = '1', `manage_stock` = '0', `use_config_manage_stock` = '1', `stock_status_changed_auto` = '0', `use_config_qty_increments` = '1', `qty_increments` = '0', `use_config_enable_qty_inc` = '1', `enable_qty_increments` = '0', `is_decimal_divided` = '0' WHERE (item_id='X')

There are many entries like this. I don't understand why Magento substracted items before and after this (just second later) tries to set specific quantity.

I've checked custom modules - there are no customisations for orders/quantity/inventory - I'm sure. We use 1.9.0.1 CE.

Please, someone have any idea what could happen?

If someone want to see whole log for "cataloginventory_stock_item" table. Here are some more queries shown in execute order:

UPDATE `cataloginventory_stock_item` SET `qty` = CASE product_id WHEN X THEN qty-2 ELSE qty END WHERE (product_id IN (X)) AND (stock_id = 1)
UPDATE `cataloginventory_stock_item` SET `qty` = CASE product_id WHEN X THEN qty-1 ELSE qty END WHERE (product_id IN (X)) AND (stock_id = 1)
UPDATE `cataloginventory_stock_item` SET `product_id` = 'X', `stock_id` = '1', `qty` = '9998', `min_qty` = '0', `use_config_min_qty` = '1', `is_qty_decimal` = '0', `backorders` = '0', `use_config_backorders` = '1', `min_sale_qty` = '1', `use_config_min_sale_qty` = '1', `max_sale_qty` = '10', `use_config_max_sale_qty` = '0', `is_in_stock` = '1', `low_stock_date` = 'XXXX-XX-XX 08:03:49', `notify_stock_qty` = NULL, `use_config_notify_stock_qty` = '1', `manage_stock` = '0', `use_config_manage_stock` = '1', `stock_status_changed_auto` = '0', `use_config_qty_increments` = '1', `qty_increments` = '0', `use_config_enable_qty_inc` = '1', `enable_qty_increments` = '0', `is_decimal_divided` = '0' WHERE (item_id='X')
UPDATE `cataloginventory_stock_item` SET `qty` = CASE product_id WHEN X THEN qty-5 ELSE qty END WHERE (product_id IN (X)) AND (stock_id = 1)
UPDATE `cataloginventory_stock_item` SET `qty` = CASE product_id WHEN X THEN qty+1 ELSE qty END WHERE (product_id IN (X)) AND (stock_id = 1)
UPDATE `cataloginventory_stock_item` SET `qty` = CASE product_id WHEN X THEN qty-3 ELSE qty END WHERE (product_id IN (X)) AND (stock_id = 1)
UPDATE `cataloginventory_stock_item` SET `qty` = CASE product_id WHEN X THEN qty-1 ELSE qty END WHERE (product_id IN (X)) AND (stock_id = 1)
UPDATE `cataloginventory_stock_item` SET `qty` = CASE product_id WHEN X THEN qty-10 ELSE qty END WHERE (product_id IN (X)) AND (stock_id = 1)
UPDATE `cataloginventory_stock_item` SET `qty` = CASE product_id WHEN X THEN qty-1 ELSE qty END WHERE (product_id IN (X)) AND (stock_id = 1)
UPDATE `cataloginventory_stock_item` SET `product_id` = 'X', `stock_id` = '1', `qty` = '9993', `min_qty` = '0', `use_config_min_qty` = '1', `is_qty_decimal` = '0', `backorders` = '0', `use_config_backorders` = '1', `min_sale_qty` = '1', `use_config_min_sale_qty` = '1', `max_sale_qty` = '10', `use_config_max_sale_qty` = '0', `is_in_stock` = '1', `low_stock_date` = 'XXXX-XX-XX 08:05:29', `notify_stock_qty` = NULL, `use_config_notify_stock_qty` = '1', `manage_stock` = '0', `use_config_manage_stock` = '1', `stock_status_changed_auto` = '0', `use_config_qty_increments` = '1', `qty_increments` = '0', `use_config_enable_qty_inc` = '1', `enable_qty_increments` = '0', `is_decimal_divided` = '0' WHERE (item_id='X')
UPDATE `cataloginventory_stock_item` SET `qty` = CASE product_id WHEN X THEN qty+3 ELSE qty END WHERE (product_id IN (X)) AND (stock_id = 1)
UPDATE `cataloginventory_stock_item` SET `qty` = CASE product_id WHEN X THEN qty-1 ELSE qty END WHERE (product_id IN (X)) AND (stock_id = 1)
UPDATE `cataloginventory_stock_item` SET `qty` = CASE product_id WHEN X THEN qty-5 ELSE qty END WHERE (product_id IN (X)) AND (stock_id = 1)
UPDATE `cataloginventory_stock_item` SET `qty` = CASE product_id WHEN X THEN qty+1 ELSE qty END WHERE (product_id IN (X)) AND (stock_id = 1)
UPDATE `cataloginventory_stock_item` SET `product_id` = 'X', `stock_id` = '1', `qty` = '9980', `min_qty` = '0', `use_config_min_qty` = '1', `is_qty_decimal` = '0', `backorders` = '0', `use_config_backorders` = '1', `min_sale_qty` = '1', `use_config_min_sale_qty` = '1', `max_sale_qty` = '10', `use_config_max_sale_qty` = '0', `is_in_stock` = '1', `low_stock_date` = 'XXXX-XX-XX 08:05:48', `notify_stock_qty` = NULL, `use_config_notify_stock_qty` = '1', `manage_stock` = '0', `use_config_manage_stock` = '1', `stock_status_changed_auto` = '0', `use_config_qty_increments` = '1', `qty_increments` = '0', `use_config_enable_qty_inc` = '1', `enable_qty_increments` = '0', `is_decimal_divided` = '0' WHERE (item_id='X')
UPDATE `cataloginventory_stock_item` SET `qty` = CASE product_id WHEN X THEN qty-1 ELSE qty END WHERE (product_id IN (X)) AND (stock_id = 1)
UPDATE `cataloginventory_stock_item` SET `qty` = CASE product_id WHEN X THEN qty-1 ELSE qty END WHERE (product_id IN (X)) AND (stock_id = 1)
UPDATE `cataloginventory_stock_item` SET `qty` = CASE product_id WHEN X THEN qty-10 ELSE qty END WHERE (product_id IN (X)) AND (stock_id = 1)
UPDATE `cataloginventory_stock_item` SET `qty` = CASE product_id WHEN X THEN qty-1 ELSE qty END WHERE (product_id IN (X)) AND (stock_id = 1)
UPDATE `cataloginventory_stock_item` SET `qty` = CASE product_id WHEN X THEN qty-10 ELSE qty END WHERE (product_id IN (X)) AND (stock_id = 1)
UPDATE `cataloginventory_stock_item` SET `qty` = CASE product_id WHEN X THEN qty-1 ELSE qty END WHERE (product_id IN (X)) AND (stock_id = 1)
UPDATE `cataloginventory_stock_item` SET `qty` = CASE product_id WHEN Y THEN qty-1 WHEN X THEN qty-10 ELSE qty END WHERE (product_id IN (Y, X)) AND (stock_id = 1)
UPDATE `cataloginventory_stock_item` SET `product_id` = 'X', `stock_id` = '1', `qty` = '9979', `min_qty` = '0', `use_config_min_qty` = '1', `is_qty_decimal` = '0', `backorders` = '0', `use_config_backorders` = '1', `min_sale_qty` = '1', `use_config_min_sale_qty` = '1', `max_sale_qty` = '10', `use_config_max_sale_qty` = '0', `is_in_stock` = '1', `low_stock_date` = 'XXXX-XX-XX 08:06:26', `notify_stock_qty` = NULL, `use_config_notify_stock_qty` = '1', `manage_stock` = '0', `use_config_manage_stock` = '1', `stock_status_changed_auto` = '0', `use_config_qty_increments` = '1', `qty_increments` = '0', `use_config_enable_qty_inc` = '1', `enable_qty_increments` = '0', `is_decimal_divided` = '0' WHERE (item_id='X')
0

This is a known Magento bug. No fix is available so far. The only solution for now is to set "Notify for quantity below" to 0.

For more info: Post on Magento forum bug reported here: Bug report (requires user account)

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.