magento order id increment jumps

Question

For some reason order ID's (increment_id on sales_flat_order table) are not incrementing subsequently on my Magento 1.6.1. This is how it looks after a number of live orders placed:

increment_id   created_at            updated_at
100000001      2011-12-14 12:35:24   2011-12-14 12:35:25
100000002      2011-12-14 13:02:39   2011-12-14 13:02:39
100000003      2011-12-14 13:04:18   2011-12-14 13:04:18
100000004      2012-02-01 16:54:58   2012-02-01 16:54:58
100000005      2012-03-14 12:22:35   2012-03-14 12:22:35
100000006      2012-03-20 13:10:48   2012-03-20 13:10:48
100000011      2012-03-29 20:58:48   2012-03-29 20:58:48
100000012      2012-03-29 21:06:43   2012-03-29 21:06:43
100000013      2012-03-30 10:48:20   2012-03-30 10:48:21
100000014      2012-03-30 13:05:40   2012-03-30 13:05:41
100000015      2012-04-03 15:51:01   2012-04-03 15:51:02
100000016      2012-04-19 15:00:49   2012-04-19 15:00:50
100000017      2012-05-09 12:09:21   2012-05-09 12:09:22
100000019      2012-05-24 05:35:35   2012-05-24 05:35:36
100000020      2012-05-24 05:41:11   2012-05-24 05:41:12
100000008      2012-05-24 05:48:52   2012-05-24 05:48:53

My question is why is Magento jumping increments sometimes? And worse yet, in my example order with increment 100000008 goes after 100000020. Does someone know why this is happening and if there's a way to fix it?

Answer 1

This is normal, albeit understandably disconcerting.

When Magento enters the checkout process it 'reserves' an increment_id and places it on the quote (cart) object. You can see the code that gets an increment id at:

Mage_Eav_Model_Entity_Type::fetchNewIncrementId()

The last used ID for each store is stored in eav_entity_store. If a customer abandons their cart (ie the quote object) before completing the checkout process, the reserved increment_id will never show up on an order. You can see this effect sometimes in the order numbers as they come in on a busy store - occassionally a really old order id comes through in the day's orders from a customer that is checking out an old cart.

This behaviour exists to allow Magento to send payment gateways the final order id (increment_id), before the order is completed allowing the gateway to associate the order id with the order. If the customer abandons the payment process in the gateway, the order id is dead (or more correctly still attached to the quote).

You can see this happening in the PayPal express module at:

Mage_Paypal_Model_Express_Checkout::start()

which calls

Mage_Sales_Model_Quote::reserveOrderId()

If you want to find your 'missing' increment_ids, take a look in sales_flat_quote under the field reserved_order_id. You should see them attached to unconverted quote objects (carts).

This behaviour can create issues with some payment gateways; Moneris comes to mind. When you send Moneris' hosted paypage the same order id twice, it chokes and creates a cryptic error state for the customer. This condition occurs when the customer visits the hosted pay page, backs out and re-visits the page. Hence in some cases, it is necessary to re-generate the order id associated with the quote object programmatically.