Diagnostic Orders and Grid Sync
Order sequence drifts from auto increment after migration
A store just moved off Magento 1, or a DBA restored a backup, and a week later two customers are staring at the same order number, or the numbers jump from 100004521 straight to 100009000 with nothing in between. Nothing about sales_order looks wrong. The rows are all there, entity_id ticks up cleanly. The part that broke lives somewhere the migration script never touched: a separate sequence table that hands out the next human facing order number. Here is why that table falls behind and a small script that finds exactly which stores drifted.
Magento 2 does not number orders from the sales_order table's own entity_id auto increment column. It numbers them from dedicated sequence_order_<store> tables, tracked through sales_sequence_meta and sales_sequence_profile, and completely separate from entity_id. A migration from Magento 1 with the data-migration-tool, or a manual DB import or restore, commonly copies sales_order rows correctly but never re-seeds the matching sequence table's last issued value. The sequence then hands out an increment_id that already exists (a collision) or jumps far past the last real order (a gap). There is no REST endpoint that rewrites sequence state, so the safe move is to page through GET /rest/V1/orders sorted by increment_id, group by store_id, and report any duplicates or unexplained numeric jumps, leaving the actual ALTER TABLE ... AUTO_INCREMENT repair to a DBA. Full code, tests, and a dry run guard are below.
The problem in plain words
Every Magento order has two numbers living in two different places. entity_id is the plain auto increment primary key on sales_order, the internal row id nobody outside the database ever sees. increment_id is the order number a customer actually reads, something like 100004521, and it is issued by SequenceManager from a dedicated table named sequence_order_<store>, one per store view, with its own separate AUTO_INCREMENT counter.
Those two counters have never been the same thing and were never meant to move together. entity_id just counts rows as they are inserted into sales_order. increment_id comes from a row being inserted into the sequence table purely to mint a new number, which is then formatted with a prefix and a step and stamped onto the order. When a migration copies sales_order rows from Magento 1, or a DBA restores a backup into a fresh database, the rows land with their original increment_id values intact, but nothing tells the sequence table what the highest of those values was. The sequence table starts wherever its own default was, and the very next order minted from it can be a number that already belongs to an order sitting three rows above it.
Why it happens
- Magento 2 generates order, invoice, shipment, and credit memo numbers from dedicated
sequence_order_<store>tables, tracked viasales_sequence_metaandsales_sequence_profile, which are separate from thesales_ordertable's ownentity_idauto increment column. - Data migrations from Magento 1 with the data-migration-tool, or manual DB imports and restores, commonly copy
sales_orderrows without correctly re-seeding the corresponding sequence table'sAUTO_INCREMENTor last issued value. - Because
SequenceManagerpulls the next number purely from the sequence table's own state, inserting intosequence_order_<store>to get a new auto increment value and formatting it with a prefix and step, a stale sequence reissuesincrement_idvalues that already exist, or skips a large range if the sequence was seeded too far ahead instead of too far behind. - This is exactly the class of bug reported across multiple Magento 1 to Magento 2 migrations, and it shows up per store view, since each store has its own sequence table and its own drift.
Store owners usually notice this the same way: a customer support ticket about an order number that "belongs to someone else," or a finance report where order numbers jump by thousands overnight with no matching spike in sales. See the citations at the end for the exact issue threads.
sequence_order_<store> is a database table with no REST endpoint of its own, and there is no public API that reports its AUTO_INCREMENT value. What the REST API does expose is the finished product of that sequence, the increment_id string on every order returned by GET /rest/V1/orders. Sorting those orders per store and scanning for the two failure signatures, the same numeric value on two different entity_id rows, or a numeric jump far larger than the store's normal order volume, is the only way to prove drift from the outside, and it is exactly what a script can do safely without ever touching the database directly.
The fix, as a flow
We do not try to patch the sequence table over the API, because there is no safe public way to do that. Instead we add a job that pulls every order per store sorted by increment_id, strips each store's known prefix to get the plain numeric value, and runs it through a pure function that finds duplicates and gaps and reports the recommended AUTO_INCREMENT reset value, leaving the actual repair to a DBA.
Build it step by step
Get an admin bearer token
Authenticate the same way as any other Magento REST client. Either call POST /rest/V1/integration/admin/token with an admin username and password, or create an integration and use its token directly. Keep the store URL and token in environment variables, never in the file.
pip install requests
export MAGENTO_URL="https://your-store.example.com"
export MAGENTO_ADMIN_TOKEN="eyJraWQ..."
export GAP_THRESHOLD="1000"
export DRY_RUN="true" # report-only either way, this only affects log verbosity// Node 18+ has fetch built in, no dependencies needed
export MAGENTO_URL="https://your-store.example.com"
export MAGENTO_ADMIN_TOKEN="eyJraWQ..."
export GAP_THRESHOLD="1000"
export DRY_RUN="true" // report-only either way, this only affects log verbosityTalk to the Magento REST API
Every call sends Authorization: Bearer <token> to a /rest/V1 route. A small helper wraps the request and raises on a non 200 response, since Magento returns structured error bodies worth surfacing as is.
import os, requests
MAGENTO_URL = os.environ["MAGENTO_URL"].rstrip("/")
TOKEN = os.environ["MAGENTO_ADMIN_TOKEN"]
def magento_get(path, params=None):
r = requests.get(
f"{MAGENTO_URL}/rest/V1{path}",
params=params or {},
headers={"Authorization": f"Bearer {TOKEN}"},
timeout=30,
)
r.raise_for_status()
return r.json()const MAGENTO_URL = (process.env.MAGENTO_URL || "").replace(/\/$/, "");
const TOKEN = process.env.MAGENTO_ADMIN_TOKEN;
async function magentoGet(path, params = {}) {
const url = new URL(`${MAGENTO_URL}/rest/V1${path}`);
for (const [k, v] of Object.entries(params)) url.searchParams.set(k, v);
const res = await fetch(url, { headers: { Authorization: `Bearer ${TOKEN}` } });
if (!res.ok) throw new Error(`Magento ${res.status}`);
return res.json();
}Pull every order's increment_id, per store
Page through GET /rest/V1/orders with searchCriteria[sortOrders][0][field]=increment_id&searchCriteria[sortOrders][0][direction]=ASC, collecting entity_id, store_id, increment_id, and created_at for every item. This always reads sales_order directly, the same table that stayed correct through the migration. Group the results by store_id once collected, since each store has its own sequence table and its own possible drift.
def orders_sorted_by_increment(page_size=200, current_page=1):
params = {
"searchCriteria[sortOrders][0][field]": "increment_id",
"searchCriteria[sortOrders][0][direction]": "ASC",
"searchCriteria[pageSize]": page_size,
"searchCriteria[currentPage]": current_page,
}
return magento_get("/orders", params)["items"]
def all_orders_sorted_by_increment(page_size=200):
page = 1
while True:
items = orders_sorted_by_increment(page_size, page)
if not items:
return
for item in items:
yield item
if len(items) < page_size:
return
page += 1async function ordersSortedByIncrement(pageSize = 200, currentPage = 1) {
const params = {
"searchCriteria[sortOrders][0][field]": "increment_id",
"searchCriteria[sortOrders][0][direction]": "ASC",
"searchCriteria[pageSize]": pageSize,
"searchCriteria[currentPage]": currentPage,
};
const data = await magentoGet("/orders", params);
return data.items;
}
async function* allOrdersSortedByIncrement(pageSize = 200) {
let page = 1;
while (true) {
const items = await ordersSortedByIncrement(pageSize, page);
if (!items.length) return;
for (const item of items) yield item;
if (items.length < pageSize) return;
page += 1;
}
}Decide, with one pure function
Keep the decision in its own function that takes the full list of orders and a prefix map, and returns duplicates, gaps, and a recommended reset value per store. A pure function like this is easy to read and easy to test, which we do later. It strips each store's known prefix from increment_id to get the plain numeric value, groups by store_id, sorts ascending, then walks the sorted list once: the same numeric value on more than one distinct entity_id is a collision already in progress, and a jump between consecutive numeric values bigger than the configured threshold is a gap.
def strip_prefix(increment_id, prefix):
value = increment_id[len(prefix):] if prefix and increment_id.startswith(prefix) else increment_id
return int(value.lstrip("0") or "0")
def detect_sequence_drift(orders, prefix_by_store, gap_threshold=1000):
by_store = {}
for o in orders:
by_store.setdefault(o["storeId"], []).append(o)
duplicates = []
gaps = []
max_numeric_by_store = {}
for store_id, store_orders in by_store.items():
prefix = prefix_by_store.get(store_id, "")
rows = sorted(
(
{
"entityId": o["entityId"],
"numeric": strip_prefix(o["incrementId"], prefix),
"incrementId": o["incrementId"],
}
for o in store_orders
),
key=lambda r: r["numeric"],
)
seen = {}
for r in rows:
seen.setdefault(r["numeric"], []).append(r["entityId"])
for numeric, entity_ids in seen.items():
distinct = sorted(set(entity_ids))
if len(distinct) > 1:
duplicates.append({
"storeId": store_id,
"incrementId": next(r["incrementId"] for r in rows if r["numeric"] == numeric),
"entityIds": distinct,
})
for prev, curr in zip(rows, rows[1:]):
gap_size = curr["numeric"] - prev["numeric"]
if gap_size > gap_threshold:
gaps.append({
"storeId": store_id,
"fromIncrement": prev["numeric"],
"toIncrement": curr["numeric"],
"gapSize": gap_size,
})
max_numeric_by_store[store_id] = max((r["numeric"] for r in rows), default=0)
return {
"duplicates": duplicates,
"gaps": gaps,
"maxNumericByStore": max_numeric_by_store,
}export function stripPrefix(incrementId, prefix) {
const value = prefix && incrementId.startsWith(prefix) ? incrementId.slice(prefix.length) : incrementId;
const stripped = value.replace(/^0+/, "");
return stripped === "" ? 0 : parseInt(stripped, 10);
}
export function detectSequenceDrift(orders, prefixByStore, gapThreshold = 1000) {
const byStore = new Map();
for (const o of orders) {
if (!byStore.has(o.storeId)) byStore.set(o.storeId, []);
byStore.get(o.storeId).push(o);
}
const duplicates = [];
const gaps = [];
const maxNumericByStore = {};
for (const [storeId, storeOrders] of byStore) {
const prefix = prefixByStore[storeId] || "";
const rows = storeOrders
.map((o) => ({
entityId: o.entityId,
numeric: stripPrefix(o.incrementId, prefix),
incrementId: o.incrementId,
}))
.sort((a, b) => a.numeric - b.numeric);
const seen = new Map();
for (const r of rows) {
if (!seen.has(r.numeric)) seen.set(r.numeric, []);
seen.get(r.numeric).push(r.entityId);
}
for (const [numeric, entityIds] of seen) {
const distinct = [...new Set(entityIds)].sort((a, b) => a - b);
if (distinct.length > 1) {
const match = rows.find((r) => r.numeric === numeric);
duplicates.push({ storeId, incrementId: match.incrementId, entityIds: distinct });
}
}
for (let i = 0; i < rows.length - 1; i++) {
const gapSize = rows[i + 1].numeric - rows[i].numeric;
if (gapSize > gapThreshold) {
gaps.push({ storeId, fromIncrement: rows[i].numeric, toIncrement: rows[i + 1].numeric, gapSize });
}
}
maxNumericByStore[storeId] = rows.length ? Math.max(...rows.map((r) => r.numeric)) : 0;
}
return { duplicates, gaps, maxNumericByStore };
}Report by default, never fake a repair
The output is a structured report per affected store_id: the current max numeric increment_id in sales_order, any duplicate collisions found with the colliding entity_id values, any gaps larger than the threshold, and the recommended AUTO_INCREMENT reset value, which is always the max numeric value plus one. There is no code path in this script that touches sequence_order_<store>, because there is no REST way to do that safely. The recommended repair is always the DBA running ALTER TABLE sequence_order_<store>_<entity_type> AUTO_INCREMENT = <value> against the actual database.
Exit non-zero when drift is found
When any store has duplicates or gaps, the script logs the full report and exits with a non-zero status, so it fails loudly in a cron job or CI check instead of quietly passing. Nothing is written. A human runs the ALTER TABLE or re-seed step, places a real test order to confirm the next increment_id is correct, and only then is the flag considered cleared.
This script never writes to sequence_order_<store> and never touches an order's status or total. DRY_RUN defaults to true and only report mode ever runs unattended, since Magento has no REST endpoint to rewrite sequence state in the first place. The actual fix is always a manual, DBA-run ALTER TABLE, per Adobe's own documented procedure.
The full code
Here is the complete script in one file for each language. It reads settings from the environment, pulls every order sorted by increment_id, groups by store, runs the pure detection function, and prints a structured report. It never writes to the sequence tables, so it is safe to run again and again.
"""Flag Magento 2 order sequence drift after a data migration or DB restore.
Magento 2 numbers orders from dedicated sequence_order_ tables, tracked
via sales_sequence_meta and sales_sequence_profile, completely separate from
the sales_order table's own entity_id auto increment column. A migration from
Magento 1, or a manual DB import or restore, commonly copies sales_order rows
without correctly re-seeding the sequence table's last issued value, so the
next order minted from it can collide with an existing increment_id or skip a
huge range. There is no REST endpoint to rewrite sequence state, so this only
reports the drift and the recommended AUTO_INCREMENT reset value. Run on a
schedule. Safe to run again and again.
"""
import os
import sys
import logging
import requests
logging.basicConfig(level=logging.INFO, format="%(asctime)s %(levelname)s %(message)s")
log = logging.getLogger("detect_sequence_drift")
MAGENTO_URL = os.environ["MAGENTO_URL"].rstrip("/")
TOKEN = os.environ["MAGENTO_ADMIN_TOKEN"]
GAP_THRESHOLD = int(os.environ.get("GAP_THRESHOLD", "1000"))
PAGE_SIZE = int(os.environ.get("PAGE_SIZE", "200"))
DRY_RUN = os.environ.get("DRY_RUN", "true").lower() == "true"
def magento_get(path, params=None):
r = requests.get(
f"{MAGENTO_URL}/rest/V1{path}",
params=params or {},
headers={"Authorization": f"Bearer {TOKEN}"},
timeout=30,
)
r.raise_for_status()
return r.json()
def orders_sorted_by_increment(page_size=200, current_page=1):
params = {
"searchCriteria[sortOrders][0][field]": "increment_id",
"searchCriteria[sortOrders][0][direction]": "ASC",
"searchCriteria[pageSize]": page_size,
"searchCriteria[currentPage]": current_page,
}
return magento_get("/orders", params)["items"]
def all_orders_sorted_by_increment(page_size=200):
page = 1
while True:
items = orders_sorted_by_increment(page_size, page)
if not items:
return
for item in items:
yield item
if len(items) < page_size:
return
page += 1
def normalize_order(item):
return {
"entityId": item.get("entity_id"),
"storeId": item.get("store_id"),
"incrementId": item.get("increment_id"),
"createdAt": item.get("created_at"),
}
def strip_prefix(increment_id, prefix):
value = increment_id[len(prefix):] if prefix and increment_id.startswith(prefix) else increment_id
return int(value.lstrip("0") or "0")
def detect_sequence_drift(orders, prefix_by_store, gap_threshold=1000):
by_store = {}
for o in orders:
by_store.setdefault(o["storeId"], []).append(o)
duplicates = []
gaps = []
max_numeric_by_store = {}
for store_id, store_orders in by_store.items():
prefix = prefix_by_store.get(store_id, "")
rows = sorted(
(
{
"entityId": o["entityId"],
"numeric": strip_prefix(o["incrementId"], prefix),
"incrementId": o["incrementId"],
}
for o in store_orders
),
key=lambda r: r["numeric"],
)
seen = {}
for r in rows:
seen.setdefault(r["numeric"], []).append(r["entityId"])
for numeric, entity_ids in seen.items():
distinct = sorted(set(entity_ids))
if len(distinct) > 1:
duplicates.append({
"storeId": store_id,
"incrementId": next(r["incrementId"] for r in rows if r["numeric"] == numeric),
"entityIds": distinct,
})
for prev, curr in zip(rows, rows[1:]):
gap_size = curr["numeric"] - prev["numeric"]
if gap_size > gap_threshold:
gaps.append({
"storeId": store_id,
"fromIncrement": prev["numeric"],
"toIncrement": curr["numeric"],
"gapSize": gap_size,
})
max_numeric_by_store[store_id] = max((r["numeric"] for r in rows), default=0)
return {
"duplicates": duplicates,
"gaps": gaps,
"maxNumericByStore": max_numeric_by_store,
}
def run():
raw_orders = list(all_orders_sorted_by_increment(PAGE_SIZE))
orders = [normalize_order(item) for item in raw_orders]
# No REST field exposes a store's increment_id prefix, so an empty prefix
# is assumed unless overridden per store via PREFIX_BY_STORE, e.g. "1:ORD-,2:EU-".
prefix_by_store = {}
for pair in os.environ.get("PREFIX_BY_STORE", "").split(","):
if ":" in pair:
store_id, prefix = pair.split(":", 1)
prefix_by_store[int(store_id.strip())] = prefix.strip()
result = detect_sequence_drift(orders, prefix_by_store, GAP_THRESHOLD)
for d in result["duplicates"]:
log.warning(
"Store %s: increment_id %s is duplicated across entity_id %s.",
d["storeId"], d["incrementId"], d["entityIds"],
)
for g in result["gaps"]:
log.warning(
"Store %s: gap of %s between increment %s and %s.",
g["storeId"], g["gapSize"], g["fromIncrement"], g["toIncrement"],
)
affected_stores = {d["storeId"] for d in result["duplicates"]} | {g["storeId"] for g in result["gaps"]}
if affected_stores:
for store_id in sorted(affected_stores, key=str):
reset_value = result["maxNumericByStore"].get(store_id, 0) + 1
log.error(
"Store %s sequence drift detected. Recommended repair: "
"ALTER TABLE sequence_order_%s AUTO_INCREMENT = %d (run by a DBA, not this script).",
store_id, store_id, reset_value,
)
log.error("%d store(s) affected. Exiting non-zero. No sequence table was written.", len(affected_stores))
sys.exit(1)
else:
log.info("Done. No sequence drift found across %d order(s).", len(orders))
if __name__ == "__main__":
run() /**
* Flag Magento 2 order sequence drift after a data migration or DB restore.
*
* Magento 2 numbers orders from dedicated sequence_order_ tables, tracked
* via sales_sequence_meta and sales_sequence_profile, completely separate from
* the sales_order table's own entity_id auto increment column. A migration from
* Magento 1, or a manual DB import or restore, commonly copies sales_order rows
* without correctly re-seeding the sequence table's last issued value, so the
* next order minted from it can collide with an existing increment_id or skip a
* huge range. There is no REST endpoint to rewrite sequence state, so this only
* reports the drift and the recommended AUTO_INCREMENT reset value. Run on a
* schedule. Safe to run again and again.
*
* Guide: https://www.allanninal.dev/magento/order-sequence-drift-after-migration/
*/
import { pathToFileURL } from "node:url";
const MAGENTO_URL = (process.env.MAGENTO_URL || "https://demo.example.com").replace(/\/$/, "");
const TOKEN = process.env.MAGENTO_ADMIN_TOKEN || "token_dummy";
const GAP_THRESHOLD = Number(process.env.GAP_THRESHOLD || 1000);
const PAGE_SIZE = Number(process.env.PAGE_SIZE || 200);
const DRY_RUN = (process.env.DRY_RUN || "true").toLowerCase() === "true";
export function stripPrefix(incrementId, prefix) {
const value = prefix && incrementId.startsWith(prefix) ? incrementId.slice(prefix.length) : incrementId;
const stripped = value.replace(/^0+/, "");
return stripped === "" ? 0 : parseInt(stripped, 10);
}
export function detectSequenceDrift(orders, prefixByStore, gapThreshold = 1000) {
const byStore = new Map();
for (const o of orders) {
if (!byStore.has(o.storeId)) byStore.set(o.storeId, []);
byStore.get(o.storeId).push(o);
}
const duplicates = [];
const gaps = [];
const maxNumericByStore = {};
for (const [storeId, storeOrders] of byStore) {
const prefix = prefixByStore[storeId] || "";
const rows = storeOrders
.map((o) => ({
entityId: o.entityId,
numeric: stripPrefix(o.incrementId, prefix),
incrementId: o.incrementId,
}))
.sort((a, b) => a.numeric - b.numeric);
const seen = new Map();
for (const r of rows) {
if (!seen.has(r.numeric)) seen.set(r.numeric, []);
seen.get(r.numeric).push(r.entityId);
}
for (const [numeric, entityIds] of seen) {
const distinct = [...new Set(entityIds)].sort((a, b) => a - b);
if (distinct.length > 1) {
const match = rows.find((r) => r.numeric === numeric);
duplicates.push({ storeId, incrementId: match.incrementId, entityIds: distinct });
}
}
for (let i = 0; i < rows.length - 1; i++) {
const gapSize = rows[i + 1].numeric - rows[i].numeric;
if (gapSize > gapThreshold) {
gaps.push({ storeId, fromIncrement: rows[i].numeric, toIncrement: rows[i + 1].numeric, gapSize });
}
}
maxNumericByStore[storeId] = rows.length ? Math.max(...rows.map((r) => r.numeric)) : 0;
}
return { duplicates, gaps, maxNumericByStore };
}
async function magentoGet(path, params = {}) {
const url = new URL(`${MAGENTO_URL}/rest/V1${path}`);
for (const [k, v] of Object.entries(params)) url.searchParams.set(k, v);
const res = await fetch(url, { headers: { Authorization: `Bearer ${TOKEN}` } });
if (!res.ok) throw new Error(`Magento ${res.status}`);
return res.json();
}
async function ordersSortedByIncrement(pageSize = 200, currentPage = 1) {
const params = {
"searchCriteria[sortOrders][0][field]": "increment_id",
"searchCriteria[sortOrders][0][direction]": "ASC",
"searchCriteria[pageSize]": pageSize,
"searchCriteria[currentPage]": currentPage,
};
const data = await magentoGet("/orders", params);
return data.items;
}
async function* allOrdersSortedByIncrement(pageSize = 200) {
let page = 1;
while (true) {
const items = await ordersSortedByIncrement(pageSize, page);
if (!items.length) return;
for (const item of items) yield item;
if (items.length < pageSize) return;
page += 1;
}
}
function normalizeOrder(item) {
return {
entityId: item.entity_id,
storeId: item.store_id,
incrementId: item.increment_id,
createdAt: item.created_at,
};
}
function parsePrefixByStore(raw) {
const map = {};
for (const pair of (raw || "").split(",")) {
if (pair.includes(":")) {
const [storeId, prefix] = pair.split(":");
map[Number(storeId.trim())] = prefix.trim();
}
}
return map;
}
export async function run() {
const rawOrders = [];
for await (const item of allOrdersSortedByIncrement(PAGE_SIZE)) rawOrders.push(item);
const orders = rawOrders.map(normalizeOrder);
const prefixByStore = parsePrefixByStore(process.env.PREFIX_BY_STORE);
const result = detectSequenceDrift(orders, prefixByStore, GAP_THRESHOLD);
for (const d of result.duplicates) {
console.warn(`Store ${d.storeId}: increment_id ${d.incrementId} is duplicated across entity_id ${JSON.stringify(d.entityIds)}.`);
}
for (const g of result.gaps) {
console.warn(`Store ${g.storeId}: gap of ${g.gapSize} between increment ${g.fromIncrement} and ${g.toIncrement}.`);
}
const affectedStores = new Set([
...result.duplicates.map((d) => d.storeId),
...result.gaps.map((g) => g.storeId),
]);
if (affectedStores.size) {
for (const storeId of [...affectedStores].sort()) {
const resetValue = (result.maxNumericByStore[storeId] || 0) + 1;
console.error(
`Store ${storeId} sequence drift detected. Recommended repair: ` +
`ALTER TABLE sequence_order_${storeId} AUTO_INCREMENT = ${resetValue} (run by a DBA, not this script).`
);
}
console.error(`${affectedStores.size} store(s) affected. Exiting non-zero. No sequence table was written.`);
process.exit(1);
} else {
console.log(`Done. No sequence drift found across ${orders.length} order(s).`);
}
}
if (import.meta.url === pathToFileURL(process.argv[1]).href) {
run().catch((err) => { console.error(err); process.exit(1); });
} Add a test
The detection rule is the part most worth testing, because it decides which stores get flagged for a DBA repair. Because we kept detect_sequence_drift pure, the test needs no network and no Magento store. It just feeds in plain fixture order arrays and checks the answer.
from detect_sequence_drift import detect_sequence_drift, strip_prefix
def order(**over):
base = {"entityId": 1, "storeId": 1, "incrementId": "100000001", "createdAt": "2026-07-01 00:00:00"}
base.update(over)
return base
def test_no_drift_on_clean_sequential_orders():
orders = [
order(entityId=1, incrementId="100000001"),
order(entityId=2, incrementId="100000002"),
order(entityId=3, incrementId="100000003"),
]
result = detect_sequence_drift(orders, {}, gap_threshold=1000)
assert result["duplicates"] == []
assert result["gaps"] == []
assert result["maxNumericByStore"][1] == 100000003
def test_duplicate_increment_id_across_two_entity_ids():
orders = [
order(entityId=10, incrementId="100000050"),
order(entityId=11, incrementId="100000050"),
order(entityId=12, incrementId="100000051"),
]
result = detect_sequence_drift(orders, {}, gap_threshold=1000)
assert len(result["duplicates"]) == 1
assert result["duplicates"][0]["entityIds"] == [10, 11]
assert result["duplicates"][0]["incrementId"] == "100000050"
def test_gap_beyond_threshold_is_flagged():
orders = [
order(entityId=1, incrementId="100004521"),
order(entityId=2, incrementId="100009000"),
]
result = detect_sequence_drift(orders, {}, gap_threshold=1000)
assert len(result["gaps"]) == 1
assert result["gaps"][0]["fromIncrement"] == 100004521
assert result["gaps"][0]["toIncrement"] == 100009000
assert result["gaps"][0]["gapSize"] == 4479
def test_gap_within_threshold_is_not_flagged():
orders = [
order(entityId=1, incrementId="100000001"),
order(entityId=2, incrementId="100000500"),
]
result = detect_sequence_drift(orders, {}, gap_threshold=1000)
assert result["gaps"] == []
def test_stores_are_isolated_from_each_other():
orders = [
order(entityId=1, storeId=1, incrementId="100000001"),
order(entityId=2, storeId=2, incrementId="200000001"),
order(entityId=3, storeId=2, incrementId="200000001"),
]
result = detect_sequence_drift(orders, {}, gap_threshold=1000)
assert len(result["duplicates"]) == 1
assert result["duplicates"][0]["storeId"] == 2
assert result["maxNumericByStore"][1] == 100000001
def test_strip_prefix_handles_store_prefix():
assert strip_prefix("ORD-000123", "ORD-") == 123
def test_strip_prefix_handles_no_prefix():
assert strip_prefix("100000042", "") == 100000042
def test_max_numeric_by_store_recommends_reset_value():
orders = [
order(entityId=1, incrementId="100000001"),
order(entityId=2, incrementId="100000099"),
]
result = detect_sequence_drift(orders, {}, gap_threshold=1000)
recommended_reset = result["maxNumericByStore"][1] + 1
assert recommended_reset == 100000100import { test } from "node:test";
import assert from "node:assert/strict";
import { detectSequenceDrift, stripPrefix } from "./detect-sequence-drift.js";
const order = (over = {}) => ({
entityId: 1,
storeId: 1,
incrementId: "100000001",
createdAt: "2026-07-01 00:00:00",
...over,
});
test("no drift on clean sequential orders", () => {
const orders = [
order({ entityId: 1, incrementId: "100000001" }),
order({ entityId: 2, incrementId: "100000002" }),
order({ entityId: 3, incrementId: "100000003" }),
];
const result = detectSequenceDrift(orders, {}, 1000);
assert.deepEqual(result.duplicates, []);
assert.deepEqual(result.gaps, []);
assert.equal(result.maxNumericByStore[1], 100000003);
});
test("duplicate increment_id across two entity_ids", () => {
const orders = [
order({ entityId: 10, incrementId: "100000050" }),
order({ entityId: 11, incrementId: "100000050" }),
order({ entityId: 12, incrementId: "100000051" }),
];
const result = detectSequenceDrift(orders, {}, 1000);
assert.equal(result.duplicates.length, 1);
assert.deepEqual(result.duplicates[0].entityIds, [10, 11]);
assert.equal(result.duplicates[0].incrementId, "100000050");
});
test("gap beyond threshold is flagged", () => {
const orders = [
order({ entityId: 1, incrementId: "100004521" }),
order({ entityId: 2, incrementId: "100009000" }),
];
const result = detectSequenceDrift(orders, {}, 1000);
assert.equal(result.gaps.length, 1);
assert.equal(result.gaps[0].fromIncrement, 100004521);
assert.equal(result.gaps[0].toIncrement, 100009000);
assert.equal(result.gaps[0].gapSize, 4479);
});
test("gap within threshold is not flagged", () => {
const orders = [
order({ entityId: 1, incrementId: "100000001" }),
order({ entityId: 2, incrementId: "100000500" }),
];
const result = detectSequenceDrift(orders, {}, 1000);
assert.deepEqual(result.gaps, []);
});
test("stores are isolated from each other", () => {
const orders = [
order({ entityId: 1, storeId: 1, incrementId: "100000001" }),
order({ entityId: 2, storeId: 2, incrementId: "200000001" }),
order({ entityId: 3, storeId: 2, incrementId: "200000001" }),
];
const result = detectSequenceDrift(orders, {}, 1000);
assert.equal(result.duplicates.length, 1);
assert.equal(result.duplicates[0].storeId, 2);
assert.equal(result.maxNumericByStore[1], 100000001);
});
test("stripPrefix handles store prefix", () => {
assert.equal(stripPrefix("ORD-000123", "ORD-"), 123);
});
test("stripPrefix handles no prefix", () => {
assert.equal(stripPrefix("100000042", ""), 100000042);
});
test("maxNumericByStore recommends reset value", () => {
const orders = [
order({ entityId: 1, incrementId: "100000001" }),
order({ entityId: 2, incrementId: "100000099" }),
];
const result = detectSequenceDrift(orders, {}, 1000);
const recommendedReset = result.maxNumericByStore[1] + 1;
assert.equal(recommendedReset, 100000100);
});Case studies
Two customers, one order number
A furniture retailer migrated off Magento 1 over a weekend using the data-migration-tool. The migration itself reported success, and every historical order looked right in the admin grid. Three weeks later, a customer emailed asking why their invoice had the same order number as a stranger's shipping confirmation forwarded to them by mistake.
The detection script, run against the live store, sorted every order by store and found a handful of increment_id values shared across two distinct entity_id rows, exactly at the point where new post-migration orders started overlapping the tail end of the migrated history. A DBA reset the affected store's sequence AUTO_INCREMENT to the reported max plus one, and every order minted after that carried a number nothing else had ever used.
The report that jumped by four thousand overnight
A multi-store B2B site restored a production database backup into a new environment after a hosting migration, and the sequence tables came back seeded far ahead of the actual order history for one store view. Finance noticed the new store's invoice numbers had jumped from the four thousands into the nine thousands with no matching sales spike, and worried the missing range meant lost orders.
Running the script with GAP_THRESHOLD=1000 confirmed no orders were missing. sales_order was complete and correct, and the gap was purely in the sequence, which had simply been re-seeded too far ahead rather than too far behind. The report gave finance the exact numeric jump and the store id, closing the "lost orders" concern in minutes instead of a week of manual reconciliation.
After this runs on a schedule following a migration, a collision or a gap is caught before it reaches a second customer's inbox or a monthly finance report. The report carries the exact store_id, the colliding entity_id values or the numeric jump, and the recommended AUTO_INCREMENT reset value, so a DBA can run one precise ALTER TABLE per affected store rather than guessing at a store-wide fix.
FAQ
Why do new Magento orders collide with old increment_ids after a migration?
Magento 2 generates order, invoice, shipment, and credit memo numbers from dedicated sequence_order_<store> tables, tracked through sales_sequence_meta and sales_sequence_profile, which are completely separate from the sales_order table's own entity_id auto increment column. A migration or manual DB restore commonly copies sales_order rows without correctly re-seeding the sequence table's last issued value, so the sequence falls behind the highest increment_id already in sales_order and reissues numbers that already exist.
Can I fix sequence drift through the Magento REST API?
No. Magento does not expose a REST endpoint that rewrites sequence state. The documented fix is a manual ALTER TABLE sequence_order_<store>_<entity_type> AUTO_INCREMENT = <value> run directly against the database by a DBA, per Adobe's own Commerce Knowledge Base article on changing the increment ID for a DB entity on a particular store. A script can only detect the drift over REST and report the recommended reset value.
What is the difference between entity_id and increment_id on a Magento order?
entity_id is the sales_order table's own internal auto increment primary key, invisible to customers. increment_id is the human facing order number, formatted with a prefix and step by SequenceManager and issued from a separate sequence_order_<store> table. A migration can leave entity_id perfectly intact while increment_id collides or jumps, because the two live in completely different tables with no shared counter.
Related field notes
Citations
On the problem:
- GitHub Issue: next generated increment_id completely wrong after DB conversion. github.com/magento/data-migration-tool/issues/615
- GitHub Issue: after migration, order increment id is duplicate. github.com/magento/data-migration-tool/issues/731
- GitHub Issue: order increment id collision. github.com/magento/magento2/issues/33457
On the solution:
- Adobe Commerce Knowledge Base: change increment ID for a DB entity on a particular store. experienceleague.adobe.com change increment ID
- Adobe Commerce Configuration Guide: change increment ID. experienceleague.adobe.com change increment ID (multi-sites)
- Adobe Commerce: search using REST endpoints. developer.adobe.com performing searches
Stuck on a tricky one?
If you have a problem in Magento 2 or Adobe Commerce orders, cron, catalog data, or inventory that you would rather hand off, this is the kind of work I do. Message me and we can work through it together.
Did this catch a collision before a customer did?
If this saved you a confusing support escalation or a scramble after a migration, you can buy me a coffee. It is the best way to keep these field notes free and growing.
Buy me a coffee on Ko-fi