Reconciler URL Rewrites
Duplicate or orphaned url_rewrite rows cause 404s
A product or category URL that used to work now 404s, or the storefront quietly serves the wrong page for a path that two rows both claim. Nobody removed the product. Nothing looks wrong in the catalog. What broke is the url_rewrite table itself, usually after a bulk import, a Magento 1 to 2 migration, or a CSV re-import that did not carry full category data. Here is why Magento leaves these rows behind, and a small script that finds the duplicates and orphans over REST and repairs what it safely can.
Magento 2's url_rewrite table enforces one active request_path per store, but bulk imports, Magento 1 to 2 migrations, and CSV re-imports without full category data routinely leave it inconsistent, since Magento 2 rejects true duplicates instead of auto-suffixing them the way Magento 1 did, and removing a product from a category does not always retire the old autogenerated row. Run a small Python or Node.js script that fetches every live product and category id from GET /rest/V1/products and GET /rest/V1/categories/list, cross-references that set against a read-only export of the url_rewrite table, and flags rows sharing the same request_path and store_id as duplicates, and rows whose entity_id is not in the live set as orphans. The safe repair is a PUT to the owning product or category with an unchanged url_key, which makes Magento regenerate and supersede its own rewrite row. Never write to url_rewrite directly. Full code, tests, and sources are below.
The problem in plain words
Every product and category URL on a Magento storefront is backed by a row in url_rewrite: a request_path the browser asks for, a target_path Magento actually serves, and the entity_id it belongs to. Magento 2 treats the pair of request_path and store_id as unique. Only one row is allowed to own a given path per store.
Magento 1 was looser about this. If two entities wanted the same path, Magento 1 quietly auto-suffixed one of them, for example appending -1. Magento 2 does not do that. When the data migration tool copies rows straight out of core_url_rewrite during a Magento 1 to 2 migration, it copies them as is, and Magento 2 rejects the true duplicates that Magento 1 tolerated. Separately, changing a product's url_key, or removing it from a category, makes Magento generate new rewrite rows, but it does not always clean up the old is_autogenerated=1 row for the entity that no longer needs it. That old row stays in the table pointing at a target_path that no longer resolves to anything live.
Why it happens
The table itself is simple. What is not simple is keeping it consistent every time an entity's URL identity changes. A few common ways stores end up with bad rows:
- The data migration tool copies rows from
core_url_rewriteas is during a Magento 1 to 2 migration, and Magento 2 rejects the true duplicates that Magento 1 tolerated by auto-suffixing, a pattern reported inmagento/data-migration-toolissue #81 and issue #606. - A bulk product or category import, or a CSV re-import that does not carry full category data, can regenerate rewrite rows for the entities in the file while leaving stale rows for entities that were not touched this time.
- Changing a product's
url_key, or removing it from a category, generates new rewrite rows for the new path, but does not always retire the oldis_autogenerated=1row, which stays in the table pointing at atarget_pathorentity_idthat no longer resolves. - Deleting a website, store, or store view that had assigned categories can leave orphaned rewrite data behind for those store scopes, the exact shape reported in
magento/magento2issue #9088.
The result is confusing because the catalog itself looks fine. The product exists, the category exists, but the specific URL a customer or a search engine has bookmarked either 404s or resolves to the wrong thing. See the citations at the end for the exact reports.
url_rewrite has no public REST endpoint, and that is by design. Magento writes to it only as a side effect of saving the product or category that owns the rewrite. So a script cannot query it over the API, and it must never write to it directly from a script or a raw query. What a script can do is fetch the live product and category ids over REST, cross-reference that set against a read-only export of url_rewrite, and classify what it finds. The repair for anything fixable is the same PUT you would use to save the entity normally, just with an unchanged url_key, which lets Magento's own logic regenerate and supersede the old row.
The fix, as a flow
We never touch url_rewrite directly. The script reads the live catalog over REST, reads a read-only export of the rewrite table, classifies each row as fine, a duplicate, or an orphan, and for fixable rows resaves the owning product or category through REST with its own unchanged url_key so Magento regenerates the rewrite itself. True orphans with no live entity at all are reported, not auto-repaired, since deleting them needs CLI or database access plus a reindex.
Build it step by step
Get an admin bearer token
Call POST /rest/V1/integration/admin/token with your admin username and password, or use a preconfigured integration token. Either way you end up with a bearer token you send as Authorization: Bearer <token> on every call. Keep the token and the store URL in environment variables, never in the file.
pip install requests
export MAGENTO_URL="https://your-store.example.com"
export MAGENTO_ADMIN_TOKEN="your admin bearer token"
export DRY_RUN="true" # start safe, change to false to allow the repair PUT// Node 18+ has fetch built in, no dependencies needed
export MAGENTO_URL="https://your-store.example.com"
export MAGENTO_ADMIN_TOKEN="your admin bearer token"
export DRY_RUN="true" // start safe, change to false to allow the repair PUTTalk to the Magento REST API
Every call goes to {MAGENTO_URL}/rest/V1 with your token in the Authorization header. A small helper sends the request and raises on a non success status, and we reuse it for reading products, reading categories, and the repair PUT.
import os, requests
MAGENTO_URL = os.environ["MAGENTO_URL"].rstrip("/")
TOKEN = os.environ["MAGENTO_ADMIN_TOKEN"]
HEADERS = {"Authorization": f"Bearer {TOKEN}", "Content-Type": "application/json"}
def api_get(path, params=None):
r = requests.get(f"{MAGENTO_URL}/rest/V1{path}", headers=HEADERS, params=params or {}, timeout=30)
r.raise_for_status()
return r.json()const MAGENTO_URL = (process.env.MAGENTO_URL || "").replace(/\/+$/, "");
const TOKEN = process.env.MAGENTO_ADMIN_TOKEN;
const HEADERS = { Authorization: `Bearer ${TOKEN}`, "Content-Type": "application/json" };
async function apiGet(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: HEADERS });
if (!res.ok) throw new Error(`Magento ${res.status}`);
return res.json();
}Fetch the live product and category ids
GET /rest/V1/products filtered by status equal to 1 (enabled), paged with searchCriteria[pageSize] and [currentPage], gives every live product's sku, id, and its url_key custom attribute. GET /rest/V1/categories/list gives the same for categories. Together they form the set of entity ids that a url_rewrite row is allowed to point at.
def custom_attr(attrs, code, default=None):
for a in attrs or []:
if a.get("attribute_code") == code:
return a.get("value")
return default
def fetch_live_product_ids():
ids, page = set(), 1
while True:
params = {
"searchCriteria[filterGroups][0][filters][0][field]": "status",
"searchCriteria[filterGroups][0][filters][0][value]": 1,
"searchCriteria[filterGroups][0][filters][0][conditionType]": "eq",
"searchCriteria[pageSize]": 100,
"searchCriteria[currentPage]": page,
}
result = api_get("/products", params)
items = result.get("items", [])
for item in items:
ids.add(str(item["id"]))
if len(items) < 100:
return ids
page += 1
def fetch_live_category_ids():
result = api_get("/categories/list", {"searchCriteria[pageSize]": 500})
return {str(item["id"]) for item in result.get("items", [])}function customAttr(attrs, code, fallback = null) {
for (const a of attrs || []) {
if (a.attribute_code === code) return a.value;
}
return fallback;
}
async function fetchLiveProductIds() {
const ids = new Set();
let page = 1;
while (true) {
const params = {
"searchCriteria[filterGroups][0][filters][0][field]": "status",
"searchCriteria[filterGroups][0][filters][0][value]": 1,
"searchCriteria[filterGroups][0][filters][0][conditionType]": "eq",
"searchCriteria[pageSize]": 100,
"searchCriteria[currentPage]": page,
};
const result = await apiGet("/products", params);
const items = result.items || [];
for (const item of items) ids.add(String(item.id));
if (items.length < 100) return ids;
page++;
}
}
async function fetchLiveCategoryIds() {
const result = await apiGet("/categories/list", { "searchCriteria[pageSize]": 500 });
return new Set((result.items || []).map((item) => String(item.id)));
}Decide, with one pure function
Keep the decision in its own function that takes the set of live entity ids and the rewrite rows already fetched from your read-only export, and returns the duplicates and orphans. It groups rows by request_path and store_id to find groups longer than one, keeping the newest url_rewrite_id as the one to leave alone, and separately filters rows whose entity_id is missing from the live set. No network calls, so it is easy to test with fixture arrays.
def classify_url_rewrites(live_entity_ids, rewrite_rows):
groups = {}
for row in rewrite_rows:
key = (row["request_path"], row["store_id"])
groups.setdefault(key, []).append(row)
duplicates = []
for (request_path, store_id), rows in groups.items():
if len(rows) > 1:
duplicates.append({
"request_path": request_path,
"store_id": store_id,
"ids": sorted(r["url_rewrite_id"] for r in rows),
})
orphans = [
{
"url_rewrite_id": row["url_rewrite_id"],
"entity_id": row["entity_id"],
"request_path": row["request_path"],
}
for row in rewrite_rows
if str(row["entity_id"]) not in live_entity_ids
]
return {"duplicates": duplicates, "orphans": orphans}export function classifyUrlRewrites(liveEntityIds, rewriteRows) {
const groups = new Map();
for (const row of rewriteRows) {
const key = `${row.request_path} ${row.store_id}`;
if (!groups.has(key)) groups.set(key, []);
groups.get(key).push(row);
}
const duplicates = [];
for (const [, rows] of groups) {
if (rows.length > 1) {
duplicates.push({
request_path: rows[0].request_path,
store_id: rows[0].store_id,
ids: rows.map((r) => r.url_rewrite_id).sort((a, b) => a - b),
});
}
}
const orphans = rewriteRows
.filter((row) => !liveEntityIds.has(String(row.entity_id)))
.map((row) => ({
url_rewrite_id: row.url_rewrite_id,
entity_id: row.entity_id,
request_path: row.request_path,
}));
return { duplicates, orphans };
}Repair by resaving the owning entity, never by writing to url_rewrite
For a duplicate or orphaned row whose entity_id still points at a live product, the safe fix is PUT /rest/V1/products/{sku} with the product's own current url_key unchanged. That forces Magento to regenerate its own rewrite rows and mark the previous autogenerated row as superseded, the same as if you resaved the product in the admin. The equivalent for a category is PUT /rest/V1/categories/{id}. A row with no live entity_id at all cannot be repaired this way and is only reported.
def api_put(path, payload):
r = requests.put(f"{MAGENTO_URL}/rest/V1{path}", headers=HEADERS, json=payload, timeout=30)
r.raise_for_status()
return r.json()
def repair_product_url_key(sku, url_key):
payload = {"product": {"sku": sku, "custom_attributes": [
{"attribute_code": "url_key", "value": url_key}
]}}
return api_put(f"/products/{sku}", payload)async function apiPut(path, payload) {
const res = await fetch(`${MAGENTO_URL}/rest/V1${path}`, {
method: "PUT",
headers: HEADERS,
body: JSON.stringify(payload),
});
if (!res.ok) throw new Error(`Magento ${res.status}`);
return res.json();
}
async function repairProductUrlKey(sku, urlKey) {
const payload = {
product: { sku, custom_attributes: [{ attribute_code: "url_key", value: urlKey }] },
};
return apiPut(`/products/${sku}`, payload);
}Wire it together with a dry run guard
The loop ties every piece together. In dry run it only logs the sku or entity id, the duplicate or orphaned request_path, and the PUT payload it would send. With DRY_RUN=false it issues the repair PUT one entity at a time, then you can re-fetch the entity to confirm a single active rewrite remains. True orphans with no live entity at all are always reported, never auto-repaired, since removing them needs a CLI or direct database cleanup plus a reindex.
Always start with DRY_RUN=true and read the report before changing anything. Never write directly to the url_rewrite table from a script, and never assume a true orphan is safe to delete without checking why its entity_id is gone.
The full code
Here is the complete script in one file for each language. It reads settings from the environment, logs what it finds, respects the dry run flag, and only ever repairs a row by resaving the owning entity through the normal REST API, one at a time.
"""Detect duplicate and orphaned Magento url_rewrite rows and repair the
fixable ones, safely.
url_rewrite has no public REST endpoint, so this script never writes to it
directly. It fetches the live product and category ids over REST, reads a
read-only export of url_rewrite (CSV or direct DB read), classifies rows into
duplicates (same request_path and store_id) and orphans (entity_id not in the
live set), and repairs a fixable row by PUTting the owning product or
category with its own unchanged url_key, which makes Magento regenerate and
supersede its own rewrite row. True orphans with no live entity_id at all are
only reported, since removing them needs CLI or direct database access plus a
reindex. Report only by default.
"""
import os
import csv
import logging
import requests
logging.basicConfig(level=logging.INFO, format="%(asctime)s %(levelname)s %(message)s")
log = logging.getLogger("url_rewrite_check")
MAGENTO_URL = os.environ["MAGENTO_URL"].rstrip("/")
TOKEN = os.environ["MAGENTO_ADMIN_TOKEN"]
HEADERS = {"Authorization": f"Bearer {TOKEN}", "Content-Type": "application/json"}
DRY_RUN = os.environ.get("DRY_RUN", "true").lower() == "true"
REWRITE_EXPORT_CSV = os.environ.get("URL_REWRITE_EXPORT_CSV", "url_rewrite_export.csv")
def api_get(path, params=None):
r = requests.get(f"{MAGENTO_URL}/rest/V1{path}", headers=HEADERS, params=params or {}, timeout=30)
r.raise_for_status()
return r.json()
def api_put(path, payload):
r = requests.put(f"{MAGENTO_URL}/rest/V1{path}", headers=HEADERS, json=payload, timeout=30)
r.raise_for_status()
return r.json()
def custom_attr(attrs, code, default=None):
for a in attrs or []:
if a.get("attribute_code") == code:
return a.get("value")
return default
def fetch_live_product_ids_and_keys():
ids, keys, page = set(), {}, 1
while True:
params = {
"searchCriteria[filterGroups][0][filters][0][field]": "status",
"searchCriteria[filterGroups][0][filters][0][value]": 1,
"searchCriteria[filterGroups][0][filters][0][conditionType]": "eq",
"searchCriteria[pageSize]": 100,
"searchCriteria[currentPage]": page,
}
result = api_get("/products", params)
items = result.get("items", [])
for item in items:
entity_id = str(item["id"])
ids.add(entity_id)
keys[entity_id] = {
"sku": item["sku"],
"url_key": custom_attr(item.get("custom_attributes"), "url_key"),
}
if len(items) < 100:
return ids, keys
page += 1
def fetch_live_category_ids():
result = api_get("/categories/list", {"searchCriteria[pageSize]": 500})
return {str(item["id"]) for item in result.get("items", [])}
def read_url_rewrite_export(path):
"""Read a read-only export of the url_rewrite table (CSV columns:
url_rewrite_id, entity_type, entity_id, request_path, target_path,
redirect_type, store_id, is_autogenerated). Produced by an Admin grid
export or a read-only DB query, never written back to directly."""
rows = []
with open(path, newline="", encoding="utf-8") as f:
for row in csv.DictReader(f):
rows.append({
"url_rewrite_id": int(row["url_rewrite_id"]),
"entity_type": row["entity_type"],
"entity_id": row["entity_id"],
"request_path": row["request_path"],
"target_path": row.get("target_path", ""),
"redirect_type": int(row.get("redirect_type", 0) or 0),
"store_id": int(row["store_id"]),
"is_autogenerated": int(row.get("is_autogenerated", 0) or 0),
})
return rows
def classify_url_rewrites(live_entity_ids, rewrite_rows):
groups = {}
for row in rewrite_rows:
key = (row["request_path"], row["store_id"])
groups.setdefault(key, []).append(row)
duplicates = []
for (request_path, store_id), rows in groups.items():
if len(rows) > 1:
duplicates.append({
"request_path": request_path,
"store_id": store_id,
"ids": sorted(r["url_rewrite_id"] for r in rows),
})
orphans = [
{
"url_rewrite_id": row["url_rewrite_id"],
"entity_id": row["entity_id"],
"request_path": row["request_path"],
}
for row in rewrite_rows
if str(row["entity_id"]) not in live_entity_ids
]
return {"duplicates": duplicates, "orphans": orphans}
def repair_product_url_key(sku, url_key):
payload = {"product": {"sku": sku, "custom_attributes": [
{"attribute_code": "url_key", "value": url_key}
]}}
return api_put(f"/products/{sku}", payload)
def run():
live_product_ids, product_keys = fetch_live_product_ids_and_keys()
live_category_ids = fetch_live_category_ids()
live_entity_ids = live_product_ids | live_category_ids
rewrite_rows = read_url_rewrite_export(REWRITE_EXPORT_CSV)
result = classify_url_rewrites(live_entity_ids, rewrite_rows)
for dup in result["duplicates"]:
log.warning(
"Duplicate request_path=%s store_id=%s ids=%s",
dup["request_path"], dup["store_id"], dup["ids"],
)
newest_id = dup["ids"][-1]
newest_row = next(r for r in rewrite_rows if r["url_rewrite_id"] == newest_id)
entity_id = newest_row["entity_id"]
info = product_keys.get(entity_id)
if not info or not info.get("url_key"):
log.info("Duplicate entity_id=%s is not a live product, skipping repair", entity_id)
continue
payload_preview = {"product": {"sku": info["sku"], "custom_attributes": [
{"attribute_code": "url_key", "value": info["url_key"]}
]}}
log.info("%s sku=%s payload=%s", "Would PUT" if DRY_RUN else "PUTting", info["sku"], payload_preview)
if not DRY_RUN:
repair_product_url_key(info["sku"], info["url_key"])
for orphan in result["orphans"]:
log.warning(
"Orphan url_rewrite_id=%s entity_id=%s request_path=%s: no live entity, flagged for CLI cleanup",
orphan["url_rewrite_id"], orphan["entity_id"], orphan["request_path"],
)
log.info(
"Done. %d duplicate group(s), %d orphan row(s) found.",
len(result["duplicates"]), len(result["orphans"]),
)
if __name__ == "__main__":
run()/**
* Detect duplicate and orphaned Magento url_rewrite rows and repair the
* fixable ones, safely.
*
* url_rewrite has no public REST endpoint, so this script never writes to it
* directly. It fetches the live product and category ids over REST, reads a
* read-only export of url_rewrite (CSV or direct DB read), classifies rows
* into duplicates and orphans, and repairs a fixable row by PUTting the
* owning product with its own unchanged url_key, which makes Magento
* regenerate and supersede its own rewrite row. True orphans with no live
* entity_id at all are only reported.
*
* Guide: https://www.allanninal.dev/magento/duplicate-url-rewrite-rows-404/
*/
import { pathToFileURL } from "node:url";
import { readFile } from "node:fs/promises";
const MAGENTO_URL = (process.env.MAGENTO_URL || "https://example.test").replace(/\/+$/, "");
const TOKEN = process.env.MAGENTO_ADMIN_TOKEN || "dummy-token";
const HEADERS = { Authorization: `Bearer ${TOKEN}`, "Content-Type": "application/json" };
const DRY_RUN = (process.env.DRY_RUN || "true").toLowerCase() === "true";
const REWRITE_EXPORT_CSV = process.env.URL_REWRITE_EXPORT_CSV || "url_rewrite_export.csv";
export function classifyUrlRewrites(liveEntityIds, rewriteRows) {
const groups = new Map();
for (const row of rewriteRows) {
const key = `${row.request_path} ${row.store_id}`;
if (!groups.has(key)) groups.set(key, []);
groups.get(key).push(row);
}
const duplicates = [];
for (const [, rows] of groups) {
if (rows.length > 1) {
duplicates.push({
request_path: rows[0].request_path,
store_id: rows[0].store_id,
ids: rows.map((r) => r.url_rewrite_id).sort((a, b) => a - b),
});
}
}
const orphans = rewriteRows
.filter((row) => !liveEntityIds.has(String(row.entity_id)))
.map((row) => ({
url_rewrite_id: row.url_rewrite_id,
entity_id: row.entity_id,
request_path: row.request_path,
}));
return { duplicates, orphans };
}
function customAttr(attrs, code, fallback = null) {
for (const a of attrs || []) {
if (a.attribute_code === code) return a.value;
}
return fallback;
}
async function apiGet(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: HEADERS });
if (!res.ok) throw new Error(`Magento ${res.status}`);
return res.json();
}
async function apiPut(path, payload) {
const res = await fetch(`${MAGENTO_URL}/rest/V1${path}`, {
method: "PUT",
headers: HEADERS,
body: JSON.stringify(payload),
});
if (!res.ok) throw new Error(`Magento ${res.status}`);
return res.json();
}
async function fetchLiveProductIdsAndKeys() {
const ids = new Set();
const keys = new Map();
let page = 1;
while (true) {
const params = {
"searchCriteria[filterGroups][0][filters][0][field]": "status",
"searchCriteria[filterGroups][0][filters][0][value]": 1,
"searchCriteria[filterGroups][0][filters][0][conditionType]": "eq",
"searchCriteria[pageSize]": 100,
"searchCriteria[currentPage]": page,
};
const result = await apiGet("/products", params);
const items = result.items || [];
for (const item of items) {
const entityId = String(item.id);
ids.add(entityId);
keys.set(entityId, { sku: item.sku, url_key: customAttr(item.custom_attributes, "url_key") });
}
if (items.length < 100) return { ids, keys };
page++;
}
}
async function fetchLiveCategoryIds() {
const result = await apiGet("/categories/list", { "searchCriteria[pageSize]": 500 });
return new Set((result.items || []).map((item) => String(item.id)));
}
async function readUrlRewriteExport(path) {
// Read a read-only export of the url_rewrite table (CSV columns:
// url_rewrite_id, entity_type, entity_id, request_path, target_path,
// redirect_type, store_id, is_autogenerated). Never written back to directly.
const text = await readFile(path, "utf-8");
const [headerLine, ...lines] = text.trim().split("\n");
const headers = headerLine.split(",");
return lines.filter(Boolean).map((line) => {
const cells = line.split(",");
const row = {};
headers.forEach((h, i) => (row[h] = cells[i]));
return {
url_rewrite_id: Number(row.url_rewrite_id),
entity_type: row.entity_type,
entity_id: row.entity_id,
request_path: row.request_path,
target_path: row.target_path || "",
redirect_type: Number(row.redirect_type || 0),
store_id: Number(row.store_id),
is_autogenerated: Number(row.is_autogenerated || 0),
};
});
}
async function repairProductUrlKey(sku, urlKey) {
const payload = {
product: { sku, custom_attributes: [{ attribute_code: "url_key", value: urlKey }] },
};
return apiPut(`/products/${sku}`, payload);
}
export async function run() {
const { ids: liveProductIds, keys: productKeys } = await fetchLiveProductIdsAndKeys();
const liveCategoryIds = await fetchLiveCategoryIds();
const liveEntityIds = new Set([...liveProductIds, ...liveCategoryIds]);
const rewriteRows = await readUrlRewriteExport(REWRITE_EXPORT_CSV);
const result = classifyUrlRewrites(liveEntityIds, rewriteRows);
for (const dup of result.duplicates) {
console.warn(`Duplicate request_path=${dup.request_path} store_id=${dup.store_id} ids=${dup.ids}`);
const newestId = dup.ids[dup.ids.length - 1];
const newestRow = rewriteRows.find((r) => r.url_rewrite_id === newestId);
const entityId = newestRow.entity_id;
const info = productKeys.get(entityId);
if (!info || !info.url_key) {
console.log(`Duplicate entity_id=${entityId} is not a live product, skipping repair`);
continue;
}
const payloadPreview = {
product: { sku: info.sku, custom_attributes: [{ attribute_code: "url_key", value: info.url_key }] },
};
console.log(`${DRY_RUN ? "Would PUT" : "PUTting"} sku=${info.sku} payload=${JSON.stringify(payloadPreview)}`);
if (!DRY_RUN) await repairProductUrlKey(info.sku, info.url_key);
}
for (const orphan of result.orphans) {
console.warn(
`Orphan url_rewrite_id=${orphan.url_rewrite_id} entity_id=${orphan.entity_id} request_path=${orphan.request_path}: no live entity, flagged for CLI cleanup`
);
}
console.log(`Done. ${result.duplicates.length} duplicate group(s), ${result.orphans.length} orphan row(s) found.`);
}
if (import.meta.url === pathToFileURL(process.argv[1]).href) {
run().catch((err) => { console.error(err); process.exit(1); });
}Add a test
The classification rule is the part most worth testing, because it decides which rows are reported as duplicates or orphans in front of a real store's URLs. Because classify_url_rewrites is pure, the test needs no network and no Magento store. It just feeds in fixture arrays and checks the answer.
from url_rewrite_check import classify_url_rewrites
def row(**over):
base = {
"url_rewrite_id": 1,
"entity_type": "product",
"entity_id": "10",
"request_path": "green-shirt.html",
"target_path": "catalog/product/view/id/10",
"redirect_type": 0,
"store_id": 1,
"is_autogenerated": 1,
}
base.update(over)
return base
def test_no_duplicates_or_orphans_when_all_live_and_unique():
live = {"10", "20"}
rows = [row(url_rewrite_id=1, entity_id="10"), row(url_rewrite_id=2, entity_id="20", request_path="blue-shirt.html")]
result = classify_url_rewrites(live, rows)
assert result["duplicates"] == []
assert result["orphans"] == []
def test_same_request_path_and_store_is_a_duplicate_group():
live = {"10", "11"}
rows = [
row(url_rewrite_id=1, entity_id="10"),
row(url_rewrite_id=2, entity_id="11"),
]
result = classify_url_rewrites(live, rows)
assert result["duplicates"] == [
{"request_path": "green-shirt.html", "store_id": 1, "ids": [1, 2]}
]
def test_same_path_different_store_is_not_a_duplicate():
live = {"10", "11"}
rows = [
row(url_rewrite_id=1, entity_id="10", store_id=1),
row(url_rewrite_id=2, entity_id="11", store_id=2),
]
result = classify_url_rewrites(live, rows)
assert result["duplicates"] == []
def test_missing_entity_id_is_an_orphan():
live = {"10"}
rows = [row(url_rewrite_id=5, entity_id="999", request_path="gone.html")]
result = classify_url_rewrites(live, rows)
assert result["orphans"] == [
{"url_rewrite_id": 5, "entity_id": "999", "request_path": "gone.html"}
]
def test_live_entity_is_not_an_orphan():
live = {"10"}
rows = [row(url_rewrite_id=1, entity_id="10")]
result = classify_url_rewrites(live, rows)
assert result["orphans"] == []
def test_duplicate_ids_are_sorted_ascending():
live = {"10", "11"}
rows = [
row(url_rewrite_id=9, entity_id="10"),
row(url_rewrite_id=3, entity_id="11"),
]
result = classify_url_rewrites(live, rows)
assert result["duplicates"][0]["ids"] == [3, 9]
def test_row_can_be_both_orphan_and_part_of_a_duplicate_group():
live = {"10"}
rows = [
row(url_rewrite_id=1, entity_id="10"),
row(url_rewrite_id=2, entity_id="999"),
]
result = classify_url_rewrites(live, rows)
assert result["duplicates"] == [
{"request_path": "green-shirt.html", "store_id": 1, "ids": [1, 2]}
]
assert result["orphans"] == [
{"url_rewrite_id": 2, "entity_id": "999", "request_path": "green-shirt.html"}
]import { test } from "node:test";
import assert from "node:assert/strict";
import { classifyUrlRewrites } from "./url-rewrite-check.js";
const row = (over = {}) => ({
url_rewrite_id: 1,
entity_type: "product",
entity_id: "10",
request_path: "green-shirt.html",
target_path: "catalog/product/view/id/10",
redirect_type: 0,
store_id: 1,
is_autogenerated: 1,
...over,
});
test("no duplicates or orphans when all live and unique", () => {
const live = new Set(["10", "20"]);
const rows = [row({ url_rewrite_id: 1, entity_id: "10" }), row({ url_rewrite_id: 2, entity_id: "20", request_path: "blue-shirt.html" })];
const result = classifyUrlRewrites(live, rows);
assert.deepEqual(result.duplicates, []);
assert.deepEqual(result.orphans, []);
});
test("same request_path and store is a duplicate group", () => {
const live = new Set(["10", "11"]);
const rows = [row({ url_rewrite_id: 1, entity_id: "10" }), row({ url_rewrite_id: 2, entity_id: "11" })];
const result = classifyUrlRewrites(live, rows);
assert.deepEqual(result.duplicates, [{ request_path: "green-shirt.html", store_id: 1, ids: [1, 2] }]);
});
test("same path different store is not a duplicate", () => {
const live = new Set(["10", "11"]);
const rows = [
row({ url_rewrite_id: 1, entity_id: "10", store_id: 1 }),
row({ url_rewrite_id: 2, entity_id: "11", store_id: 2 }),
];
const result = classifyUrlRewrites(live, rows);
assert.deepEqual(result.duplicates, []);
});
test("missing entity_id is an orphan", () => {
const live = new Set(["10"]);
const rows = [row({ url_rewrite_id: 5, entity_id: "999", request_path: "gone.html" })];
const result = classifyUrlRewrites(live, rows);
assert.deepEqual(result.orphans, [{ url_rewrite_id: 5, entity_id: "999", request_path: "gone.html" }]);
});
test("live entity is not an orphan", () => {
const live = new Set(["10"]);
const rows = [row({ url_rewrite_id: 1, entity_id: "10" })];
const result = classifyUrlRewrites(live, rows);
assert.deepEqual(result.orphans, []);
});
test("duplicate ids are sorted ascending", () => {
const live = new Set(["10", "11"]);
const rows = [row({ url_rewrite_id: 9, entity_id: "10" }), row({ url_rewrite_id: 3, entity_id: "11" })];
const result = classifyUrlRewrites(live, rows);
assert.deepEqual(result.duplicates[0].ids, [3, 9]);
});
test("row can be both orphan and part of a duplicate group", () => {
const live = new Set(["10"]);
const rows = [row({ url_rewrite_id: 1, entity_id: "10" }), row({ url_rewrite_id: 2, entity_id: "999" })];
const result = classifyUrlRewrites(live, rows);
assert.deepEqual(result.duplicates, [{ request_path: "green-shirt.html", store_id: 1, ids: [1, 2] }]);
assert.deepEqual(result.orphans, [{ url_rewrite_id: 2, entity_id: "999", request_path: "green-shirt.html" }]);
});Case studies
A rebranded shirt path 404s after the cutover
A store migrated from Magento 1 with the data migration tool, which copied core_url_rewrite rows over as is. A handful of products that Magento 1 had auto-suffixed to avoid a path collision now had two rows fighting for the same request_path, the exact shape reported in magento/data-migration-tool issue #81. Search traffic to the old bookmarked URL sometimes 404d and sometimes landed on the wrong product, depending on which row Magento picked.
Running the classifier against a read-only export of url_rewrite surfaced every duplicate group with both url_rewrite_ids. Resaving the newer product's own url_key through REST regenerated a single clean rewrite and the older row stopped competing for the path.
A category rename left the old path resolving to nothing
A merchandiser renamed a category and re-imported the catalog by CSV without including full category data for every affected row. The new url_key generated a fresh rewrite, but the old autogenerated row for the same category id stayed in the table with a target_path that no longer matched anything the storefront could resolve, similar to the orphaned data pattern in magento/magento2 issue #9088.
The script flagged the row as an orphan because its entity_id was still a live category, so a normal PUT to the category with its current url_key regenerated the rewrite cleanly, no CLI needed for that part.
After this runs on a schedule against a fresh export, nobody has to guess why a specific product or category link 404s or serves the wrong page. The script tells you exactly which paths are duplicated, which rows are orphaned, and repairs anything that still has a live entity behind it by resaving that entity the normal way, the same action the Admin already trusts. True orphans get called out clearly for the CLI cleanup they actually need.
FAQ
Why do bulk imports and Magento 1 to 2 migrations create duplicate url_rewrite rows?
The url_rewrite table enforces one active request_path per store, but the data migration tool copies rows from core_url_rewrite as is and Magento 2 rejects true duplicates instead of auto-suffixing them the way Magento 1 did. Separately, a CSV re-import without full category data or a changed url_key can regenerate new rewrite rows without retiring the old ones, leaving duplicate or orphaned rows behind.
Can I just delete the bad url_rewrite rows with a script?
Not safely, and not directly. url_rewrite has no public REST endpoint, so writes to it only happen indirectly when you save the owning product or category. The safe repair is to PUT the product or category through the REST API with an unchanged url_key, which forces Magento to regenerate its own rewrite rows and supersede the old autogenerated one.
What is the difference between a duplicate and an orphaned url_rewrite row?
A duplicate is two or more rows sharing the same request_path and store_id, competing for the same URL. An orphan is a row whose entity_id no longer matches any live product or category, so its target_path points at nothing and the request_path 404s. Duplicates are usually fixable by resaving the entity. True orphans with no live entity_id need a CLI or direct database cleanup.
Related field notes
Citations
On the problem:
- magento/data-migration-tool: url_rewrite duplicates. github.com/magento/data-migration-tool/issues/81
- magento/data-migration-tool: Duplicate URL rewrites. github.com/magento/data-migration-tool/issues/606
- magento/magento2: Deleting a website, store, or store view that has assigned categories leaves orphaned data behind. github.com/magento/magento2/issues/9088
On the solution:
- Adobe Commerce/Magento REST API: Products endpoints, GET /V1/products and PUT /V1/products/{sku}. developer.adobe.com orders-order-management
- Adobe Commerce Developer: Search for products with the /search endpoint. developer.adobe.com search-endpoint
- Adobe Commerce/Magento 2: URL Rewrites developer documentation. developer.adobe.com url-rewrite
Stuck on a tricky one?
If you have a problem in Magento catalog data, URL rewrites, cron, or MSI stock 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 clear your 404s?
If this saved you a broken bookmark, a lost search ranking, or a wasted afternoon staring at core_url_rewrite, 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