Fix duplicate order increment_ids after migration to Magento 2

The problem

When importing data from Magento 1 to Magento 2, we often see duplicate order numbers appear at stores with more than one storeview.

Duplicate order numbers cause serious problems, most significantly with payments.

Your payment service provider will provide the order increment_id in the return URL that updates the order on payment, thus setting the order status using its increment_id. The increment_id should be unique, like an SKU.

  • Say the shop already contains an order with increment_id 2000484919.
  • Now, say a second order with increment_id 2000484919 is created. (normally this should not happen)
  • On payment, Magento will try to update the status of the second order 2000484919.
  • However, it will find the first order with increment_id 2000484919 and update its status instead.
  • The status of the second order with increment_id 2000484919 will remain processing, no matter whether the order was paid or not.

In short, major headaches all around.

 The cause

The issue is caused when Magento prefixes increment_id for different store views with the same prefix.

Normally, Magento prefixes the increment_id for orders, invoices, shippings and credit memos with the appropriate storeview ID. These prefixes are stored in the sales_sequence tables.

After migrating from Magento 1, the prefixes are often set incorrectly: to the store id, instead of the store view id. So if a store contains more than one storeview, they incorrectly share the same prefix.

As a result, orders for different storeviews, like for instance English and Dutch, will share the same prefix (that of the store) instead of different prefixes. This will result in the dreaded duplicate increment_ids.

The solution

First, check if your sequence tables contain any prefixes that are different from the store_id.

SELECT store_id, prefix, entity_type, sequence_table FROM sales_sequence_meta
JOIN sales_sequence_profile ON sales_sequence_profile.meta_id = sales_sequence_meta.meta_id
ORDER BY store_id;

You now have a list of all meta_ids for each store.

Make sure that the prefix for each record matches the store ID. For example, store 2 should have a prefix of 2. The prefix is ​​the first number of the order increment_id, invoiceincrement_id, shippingincrement_id, returnincrement_id and credit invoiceincrement_id.

Next, match the number of the sequence_table for each record to the store ID. For example, store 2 should have a sequence_table of sequence_order_2.

Of course, also check whether these tables exist.

Troubleshoot

Find all orders with a duplicate order number:

SELECT `sales_order`.`increment_id`,` sales_order`.`entity_id`, `store_id`,` created_at`, CONCAT (`firstname`, '',` lastname`), `customer_email`,` status`, `base_grand_total` FROM sales_order
JOIN sales_order_address ON sales_order_address.parent_id = sales_order.entity_id
WHERE `sales_order`.`increment_id` IN (
 SELECT `increment_id` FROM sales_order
 GROUP BY `increment_id`
 HAVING COUNT (*)> 1
)
AND `address_type` = 'billing'
ORDER BY `increment_id`;

Latest bogs