Diagnostic Indexing
Category product assignment changes do not reach the search index
A merchandiser adds a product to a category, or an API call updates catalog_category_product, and the assignment is correct everywhere the admin looks. But the product never appears in category or fulltext search results on the storefront, even with cron running normally. Here is why the catalogsearch_fulltext changelog silently misses these edits, and a script that finds every affected category and SKU pair through the REST API.
Magento's catalogsearch_fulltext indexer reindexes only the rows that show up in its changelog table, and that changelog is filled by triggers Magento generates from each module's mview.xml. The catalog_category_product table, which stores category to product assignments, is not reliably subscribed to the catalogsearch_fulltext view in stock Magento, and when more than one indexer subscribes to the same table with different entity columns, one subscription's triggers can silently overwrite the other's. The result is that a category assignment change never writes a row into catalog_category_product_cl or catalogsearch_fulltext_cl, so cron's Update by Schedule reindex never sees it. Run a small Python or Node.js script that pulls the admin-truth assignment list from /V1/categories/{id}/products, compares it to what the category-filtered /V1/products search actually returns, and rules out legitimate exclusions like disabled or Not Visible Individually products. The full code and a dry run report are below.
The problem in plain words
Assigning a product to a category in Magento writes a row to catalog_category_product. That part always works, and the Admin grid, the category edit page, and the /V1/categories/{id}/products REST endpoint all agree on the assignment the moment you save it.
What the storefront shows for that category, and what fulltext search returns, comes from a completely different place: the catalogsearch_fulltext search index. That index is only refreshed for rows that a change tracking table, the Mview changelog, tells it to refresh. The changelog is populated by MySQL triggers that Magento generates from the mview.xml subscriptions declared by each indexer. If catalog_category_product is not correctly subscribed for the catalogsearch_fulltext view, or if a second indexer's subscription to the same table quietly overwrites the first indexer's triggers when Magento regenerates them, the edit never produces a changelog row. Cron keeps running, the indexer keeps reporting healthy, and the product just never shows up.
Why it happens
- Each indexer declares which tables it watches, and which triggers to generate, in its own module's
etc/mview.xml. The catalog_category_product table is not consistently subscribed for the catalogsearch_fulltext view across stock Magento versions. - When more than one indexer subscribes to the same source table but with different
entity_columnvalues, Magento can regenerate the MySQL triggers for that table in a way that keeps only one indexer's insert, update, and delete trigger bodies, silently dropping the other indexer's changelog writes. - Because Update by Schedule mode reindexes strictly from the changelog table (
catalogsearch_fulltext_cl), a category assignment change that never wrote a changelog row is invisible to every subsequent cron pass, no matter how often cron runs. - Store operators trust that cron running normally means indexing is current, so this failure mode hides for a long time. It surfaces only when someone forces a full
indexer:reindex catalogsearch_fulltextand the missing products suddenly appear.
This exact failure has been reported against core Magento more than once, both as products silently missing from category and search listings, and as the multi-subscription trigger overwrite that causes it. See the citations at the end for the exact issue threads.
The database is not lying to you. /V1/categories/{id}/products and the search index are two different sources of truth that are supposed to stay in sync through a changelog, and here they do not. So the fix is not to poke at the product or the category. It is to compare admin-truth assignment against what the search-index-backed product listing actually returns, rule out any SKU that is legitimately excluded because it is disabled or Not Visible Individually, and treat everything left over as an indexer changelog gap.
The fix, as a flow
A REST-only script cannot safely force a reindex or edit mview.xml and changelog tables directly, that needs CLI access and is outside what this script should touch. So it stays a pure diagnostic. It pulls the assigned SKUs for a category, pulls what the category-filtered product search actually returns, subtracts the second from the first, drops any SKU that is disabled or not individually visible, and reports the rest as stale.
Build it step by step
Get an admin token and pick your categories
Get an admin token by calling POST {MAGENTO_URL}/rest/V1/integration/admin/token with your admin username and password, or use a long lived integration token from an existing integration. Keep the base URL and token in environment variables, and list the category ids you want to check.
pip install requests
export MAGENTO_URL="https://yourstore.example.com"
export MAGENTO_ADMIN_TOKEN="eyJraWQ..."
export CATEGORY_IDS="20,21,35"
export DRY_RUN="true" # this script only ever reports, it never writes// Node 18+ has fetch built in, no dependencies needed
export MAGENTO_URL="https://yourstore.example.com"
export MAGENTO_ADMIN_TOKEN="eyJraWQ..."
export CATEGORY_IDS="20,21,35"
export DRY_RUN="true" // this script only ever reports, it never writesTalk to the Magento REST API
Every call sends the admin token as a bearer header. A small helper wraps GET requests, raises on a bad status code, and returns the parsed JSON body so the rest of the script only deals with plain data.
import os, requests
MAGENTO_URL = os.environ["MAGENTO_URL"].rstrip("/")
TOKEN = os.environ["MAGENTO_ADMIN_TOKEN"]
def 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 get(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 the admin-truth category assignment
Call GET /V1/categories/{'{'}categoryId{'}'}/products. It returns a CategoryProductLink array with the sku, position, and category_id for every product genuinely assigned to that category, regardless of what the search index thinks.
def assigned_skus(category_id):
links = get(f"/categories/{category_id}/products")
return [link["sku"] for link in links]async function assignedSkus(categoryId) {
const links = await get(`/categories/${categoryId}/products`);
return links.map((link) => link.sku);
}Pull what the search-index-backed listing actually returns
Call GET /V1/products with a search criteria filter on category_id. This endpoint reads from the same product collection the storefront category and fulltext search rely on, so any SKU missing here despite being assigned is the exact gap we are hunting. Page through with pageSize and currentPage so large categories are covered.
PAGE_SIZE = 100
def search_index_skus(category_id):
skus, page = [], 1
while True:
params = {
"searchCriteria[filterGroups][0][filters][0][field]": "category_id",
"searchCriteria[filterGroups][0][filters][0][value]": category_id,
"searchCriteria[filterGroups][0][filters][0][conditionType]": "eq",
"searchCriteria[pageSize]": PAGE_SIZE,
"searchCriteria[currentPage]": page,
}
data = get("/products", params)
items = data.get("items", [])
skus.extend(item["sku"] for item in items)
if len(items) < PAGE_SIZE:
return skus
page += 1const PAGE_SIZE = 100;
async function searchIndexSkus(categoryId) {
const skus = [];
let page = 1;
while (true) {
const params = {
"searchCriteria[filterGroups][0][filters][0][field]": "category_id",
"searchCriteria[filterGroups][0][filters][0][value]": categoryId,
"searchCriteria[filterGroups][0][filters][0][conditionType]": "eq",
"searchCriteria[pageSize]": PAGE_SIZE,
"searchCriteria[currentPage]": page,
};
const data = await get("/products", params);
const items = data.items || [];
skus.push(...items.map((item) => item.sku));
if (items.length < PAGE_SIZE) return skus;
page += 1;
}
}Decide, with one pure function
Keep the decision in its own function so it is easy to read and easy to test. It takes the assigned SKUs, the search index SKUs, and a status and visibility lookup keyed by SKU, and returns only the SKUs that are truly stuck in the changelog gap. Anything disabled, or set to Not Visible Individually, is excluded on purpose, that is a legitimate reason to be absent from search, not an indexer bug.
DISABLED_STATUS = 2
NOT_VISIBLE_INDIVIDUALLY = 1
def find_missing_category_assignments(assigned_skus, search_index_skus, product_status_by_sku):
search_index_set = set(search_index_skus)
missing = []
for sku in assigned_skus:
if sku in search_index_set:
continue
info = product_status_by_sku.get(sku)
if info and (info["status"] == DISABLED_STATUS or info["visibility"] == NOT_VISIBLE_INDIVIDUALLY):
continue
missing.append(sku)
return missingconst DISABLED_STATUS = 2;
const NOT_VISIBLE_INDIVIDUALLY = 1;
export function findMissingCategoryAssignments(assignedSkus, searchIndexSkus, productStatusBySku) {
const searchIndexSet = new Set(searchIndexSkus);
const missing = [];
for (const sku of assignedSkus) {
if (searchIndexSet.has(sku)) continue;
const info = productStatusBySku[sku];
if (info && (info.status === DISABLED_STATUS || info.visibility === NOT_VISIBLE_INDIVIDUALLY)) continue;
missing.push(sku);
}
return missing;
}Confirm each candidate and emit a dry run report
For every candidate SKU, call GET /V1/products/{'{'}sku{'}'} to read its status and visibility, feed that into the pure function above, and print the surviving category and SKU pairs. The script never calls a reindex or write endpoint. It recommends running php bin/magento indexer:reindex catalogsearch_fulltext, or resetting the mview_state for that view, because both of those need CLI access this script does not have.
This script is diagnostic only, and DRY_RUN defaults to true and stays true. There is no code path in it that reindexes, edits mview.xml, or writes to the changelog tables, those need CLI access and are outside what a REST client should touch. Treat the report as the trigger to run the reindex command yourself.
The full code
Here is the complete script in one file for each language. It reads settings from the environment, fetches assigned SKUs and search index SKUs per category, confirms product status and visibility for every candidate, and prints a report of category and SKU pairs that look stuck in the changelog gap.
"""Find Magento category product assignments that never reached the search index.
catalog_category_product edits are only visible to a scheduled catalogsearch_fulltext
reindex if they wrote a row into the Mview changelog. When the mview.xml subscription
for that table is missing or overwritten by another indexer, admin and API category
assignments never produce a changelog row, so the product silently never appears in
category or fulltext search until a full reindex is forced.
This script is diagnostic only. It compares the admin-truth assignment list from
/V1/categories/{id}/products against the search-index-backed /V1/products listing,
rules out products that are legitimately absent (disabled or Not Visible Individually),
and reports the rest. DRY_RUN stays true, there is no write or reindex call in here.
"""
import os
import logging
import requests
logging.basicConfig(level=logging.INFO, format="%(asctime)s %(levelname)s %(message)s")
log = logging.getLogger("find_missing_category_assignments")
MAGENTO_URL = os.environ["MAGENTO_URL"].rstrip("/")
TOKEN = os.environ["MAGENTO_ADMIN_TOKEN"]
CATEGORY_IDS = [c.strip() for c in os.environ.get("CATEGORY_IDS", "").split(",") if c.strip()]
DRY_RUN = os.environ.get("DRY_RUN", "true").lower() == "true"
PAGE_SIZE = 100
DISABLED_STATUS = 2
NOT_VISIBLE_INDIVIDUALLY = 1
def 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 assigned_skus(category_id):
links = get(f"/categories/{category_id}/products")
return [link["sku"] for link in links]
def search_index_skus(category_id):
skus, page = [], 1
while True:
params = {
"searchCriteria[filterGroups][0][filters][0][field]": "category_id",
"searchCriteria[filterGroups][0][filters][0][value]": category_id,
"searchCriteria[filterGroups][0][filters][0][conditionType]": "eq",
"searchCriteria[pageSize]": PAGE_SIZE,
"searchCriteria[currentPage]": page,
}
data = get("/products", params)
items = data.get("items", [])
skus.extend(item["sku"] for item in items)
if len(items) < PAGE_SIZE:
return skus
page += 1
def product_status(sku):
product = get(f"/products/{sku}")
return {"status": product.get("status"), "visibility": product.get("visibility")}
def find_missing_category_assignments(assigned, search_index, product_status_by_sku):
search_index_set = set(search_index)
missing = []
for sku in assigned:
if sku in search_index_set:
continue
info = product_status_by_sku.get(sku)
if info and (info["status"] == DISABLED_STATUS or info["visibility"] == NOT_VISIBLE_INDIVIDUALLY):
continue
missing.append(sku)
return missing
def run():
total_gaps = 0
for category_id in CATEGORY_IDS:
assigned = assigned_skus(category_id)
indexed = search_index_skus(category_id)
candidates = [sku for sku in assigned if sku not in set(indexed)]
product_status_by_sku = {sku: product_status(sku) for sku in candidates}
gaps = find_missing_category_assignments(assigned, indexed, product_status_by_sku)
for sku in gaps:
log.warning("Category %s: SKU %s is assigned but missing from the search index.", category_id, sku)
total_gaps += len(gaps)
if total_gaps:
log.info(
"Done. %d category/SKU pair(s) look stuck in the changelog gap. "
"Recommend: php bin/magento indexer:reindex catalogsearch_fulltext "
"(or reset the mview_state for that view). %s",
total_gaps,
"Dry run, no write performed." if DRY_RUN else "This script never writes regardless of DRY_RUN.",
)
else:
log.info("Done. No category/SKU gaps found across %d category/categories.", len(CATEGORY_IDS))
if __name__ == "__main__":
run()/**
* Find Magento category product assignments that never reached the search index.
*
* catalog_category_product edits are only visible to a scheduled catalogsearch_fulltext
* reindex if they wrote a row into the Mview changelog. When the mview.xml subscription
* for that table is missing or overwritten by another indexer, admin and API category
* assignments never produce a changelog row, so the product silently never appears in
* category or fulltext search until a full reindex is forced.
*
* This script is diagnostic only. It compares the admin-truth assignment list from
* /V1/categories/{id}/products against the search-index-backed /V1/products listing,
* rules out products that are legitimately absent (disabled or Not Visible Individually),
* and reports the rest. DRY_RUN stays true, there is no write or reindex call in here.
*
* Guide: https://www.allanninal.dev/magento/category-assignment-missing-from-search-index/
*/
import { pathToFileURL } from "node:url";
const MAGENTO_URL = (process.env.MAGENTO_URL || "https://example.test").replace(/\/$/, "");
const TOKEN = process.env.MAGENTO_ADMIN_TOKEN || "dummy-token";
const CATEGORY_IDS = (process.env.CATEGORY_IDS || "")
.split(",")
.map((c) => c.trim())
.filter(Boolean);
const DRY_RUN = (process.env.DRY_RUN || "true").toLowerCase() === "true";
const PAGE_SIZE = 100;
const DISABLED_STATUS = 2;
const NOT_VISIBLE_INDIVIDUALLY = 1;
export function findMissingCategoryAssignments(assignedSkus, searchIndexSkus, productStatusBySku) {
const searchIndexSet = new Set(searchIndexSkus);
const missing = [];
for (const sku of assignedSkus) {
if (searchIndexSet.has(sku)) continue;
const info = productStatusBySku[sku];
if (info && (info.status === DISABLED_STATUS || info.visibility === NOT_VISIBLE_INDIVIDUALLY)) continue;
missing.push(sku);
}
return missing;
}
async function get(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 assignedSkus(categoryId) {
const links = await get(`/categories/${categoryId}/products`);
return links.map((link) => link.sku);
}
async function searchIndexSkus(categoryId) {
const skus = [];
let page = 1;
while (true) {
const params = {
"searchCriteria[filterGroups][0][filters][0][field]": "category_id",
"searchCriteria[filterGroups][0][filters][0][value]": categoryId,
"searchCriteria[filterGroups][0][filters][0][conditionType]": "eq",
"searchCriteria[pageSize]": PAGE_SIZE,
"searchCriteria[currentPage]": page,
};
const data = await get("/products", params);
const items = data.items || [];
skus.push(...items.map((item) => item.sku));
if (items.length < PAGE_SIZE) return skus;
page += 1;
}
}
async function productStatus(sku) {
const product = await get(`/products/${sku}`);
return { status: product.status, visibility: product.visibility };
}
export async function run() {
let totalGaps = 0;
for (const categoryId of CATEGORY_IDS) {
const assigned = await assignedSkus(categoryId);
const indexed = await searchIndexSkus(categoryId);
const indexedSet = new Set(indexed);
const candidates = assigned.filter((sku) => !indexedSet.has(sku));
const productStatusBySku = {};
for (const sku of candidates) productStatusBySku[sku] = await productStatus(sku);
const gaps = findMissingCategoryAssignments(assigned, indexed, productStatusBySku);
for (const sku of gaps) {
console.warn(`Category ${categoryId}: SKU ${sku} is assigned but missing from the search index.`);
}
totalGaps += gaps.length;
}
if (totalGaps) {
console.log(
`Done. ${totalGaps} category/SKU pair(s) look stuck in the changelog gap. ` +
`Recommend: php bin/magento indexer:reindex catalogsearch_fulltext ` +
`(or reset the mview_state for that view). ` +
(DRY_RUN ? "Dry run, no write performed." : "This script never writes regardless of DRY_RUN.")
);
} else {
console.log(`Done. No category/SKU gaps found across ${CATEGORY_IDS.length} category/categories.`);
}
}
if (import.meta.url === pathToFileURL(process.argv[1]).href) {
run().catch((err) => { console.error(err); process.exit(1); });
}Add a test
find_missing_category_assignments is the part worth testing, because it decides which SKUs get reported as stuck. It is a pure set difference plus a status filter over data already fetched, so the test needs no network and no Magento store. It just feeds in plain lists and dictionaries and checks the answer.
from find_missing_category_assignments import find_missing_category_assignments
def test_reports_assigned_sku_missing_from_index():
assert find_missing_category_assignments(["SKU-1"], [], {}) == ["SKU-1"]
def test_ignores_sku_present_in_index():
assert find_missing_category_assignments(["SKU-1"], ["SKU-1"], {}) == []
def test_excludes_disabled_product():
status = {"SKU-1": {"status": 2, "visibility": 4}}
assert find_missing_category_assignments(["SKU-1"], [], status) == []
def test_excludes_not_visible_individually():
status = {"SKU-1": {"status": 1, "visibility": 1}}
assert find_missing_category_assignments(["SKU-1"], [], status) == []
def test_keeps_enabled_and_visible_product_missing_from_index():
status = {"SKU-1": {"status": 1, "visibility": 4}}
assert find_missing_category_assignments(["SKU-1"], [], status) == ["SKU-1"]
def test_handles_multiple_skus_mixed_outcomes():
assigned = ["SKU-1", "SKU-2", "SKU-3"]
indexed = ["SKU-2"]
status = {"SKU-1": {"status": 1, "visibility": 4}, "SKU-3": {"status": 2, "visibility": 4}}
assert find_missing_category_assignments(assigned, indexed, status) == ["SKU-1"]import { test } from "node:test";
import assert from "node:assert/strict";
import { findMissingCategoryAssignments } from "./find-missing-category-assignments.js";
test("reports assigned sku missing from index", () => {
assert.deepEqual(findMissingCategoryAssignments(["SKU-1"], [], {}), ["SKU-1"]);
});
test("ignores sku present in index", () => {
assert.deepEqual(findMissingCategoryAssignments(["SKU-1"], ["SKU-1"], {}), []);
});
test("excludes disabled product", () => {
const status = { "SKU-1": { status: 2, visibility: 4 } };
assert.deepEqual(findMissingCategoryAssignments(["SKU-1"], [], status), []);
});
test("excludes not visible individually", () => {
const status = { "SKU-1": { status: 1, visibility: 1 } };
assert.deepEqual(findMissingCategoryAssignments(["SKU-1"], [], status), []);
});
test("keeps enabled and visible product missing from index", () => {
const status = { "SKU-1": { status: 1, visibility: 4 } };
assert.deepEqual(findMissingCategoryAssignments(["SKU-1"], [], status), ["SKU-1"]);
});
test("handles multiple skus with mixed outcomes", () => {
const assigned = ["SKU-1", "SKU-2", "SKU-3"];
const indexed = ["SKU-2"];
const status = { "SKU-1": { status: 1, visibility: 4 }, "SKU-3": { status: 2, visibility: 4 } };
assert.deepEqual(findMissingCategoryAssignments(assigned, indexed, status), ["SKU-1"]);
});Case studies
A migration merged two indexers watching the same table
An agency added a third party search extension that also subscribed to catalog_category_product through its own mview.xml, with a different entity_column than Magento's own catalogsearch_fulltext view. After the next setup:upgrade, only one indexer's triggers survived on that table. Category managers kept assigning products all quarter, and half of them silently never reached the storefront category pages.
Running the diagnostic script against every top level category found dozens of enabled, visible SKUs that were assigned but absent from the category filtered product search. That report was the evidence needed to get the extension's mview.xml subscription fixed and to justify a full reindex.
Cron was healthy, the catalog was not
A mid sized catalog store had cron running every minute without a single failed job in months, so the merchandising team assumed indexing was current. New seasonal products kept getting added to category pages through bulk category edits via the API, but conversion on those categories quietly dropped.
The script's weekly run flagged a growing list of assigned SKUs missing from the search index, all enabled and visible, ruling out the usual status or visibility excuses. The team scheduled a nightly full catalogsearch_fulltext reindex as a stopgap while they tracked down the missing mview.xml subscription, and the report gave them a number to watch as it dropped to zero.
Run on a schedule, this script turns a silent, hard to notice indexing gap into a short, specific list of category and SKU pairs, with the disabled and Not Visible Individually noise already filtered out. Nobody has to guess whether cron is broken or whether a product is really eligible. When the list is not empty, the fix is a known, safe CLI command, and when it is empty, the changelog and the search index agree.
FAQ
Why does a product assigned to a category not show up in search?
Magento's catalogsearch_fulltext indexer only reindexes rows that appear in its changelog table, which is filled by triggers defined in mview.xml. The catalog_category_product table is not reliably subscribed for that view in stock Magento, so an admin or API edit to a category assignment never writes a changelog row, and the indexer never learns the product needs to be reindexed.
Why does forcing a full reindex fix it when cron is running fine?
Update by Schedule mode only processes rows that are present in the changelog table on each cron pass. Cron itself can be perfectly healthy and still never touch an assignment change, because that change was never written to the changelog in the first place. A full php bin/magento indexer:reindex catalogsearch_fulltext rebuilds the whole index from source tables instead of the changelog, so it picks up the missing assignment.
Can a REST only script fix this directly?
Not safely. Forcing a reindex or repairing mview.xml trigger subscriptions needs CLI access such as indexer:reindex or an mview_state reset, plus direct changelog table edits, none of which a REST client can perform. A REST script can only detect the gap by diffing the category API against the product search API and report it, which is what the script in this guide does.
Related field notes
Citations
On the problem:
- Magento 2 GitHub issue: products added to a category are not shown at the frontend. github.com/magento/magento2/issues/19417
- Magento 2 GitHub issue: during indexer:reindex catalogsearch_fulltext no products are listed in categories. github.com/magento/magento2/issues/6754
- Catalog Storefront GitHub issue: database table triggers creation using mview multiple subscriptions with different entity columns for one table. github.com/magento/catalog-storefront/issues/458
On the solution:
- Adobe Commerce developer docs: Indexing, Commerce PHP Extensions. developer.adobe.com/commerce/php/development/components/indexing
- Adobe Commerce developer docs: REST endpoints quick reference. developer.adobe.com/commerce/webapi/rest/quick-reference/rest-endpoints
- Adobe Commerce developer docs: perform searches using REST APIs. developer.adobe.com/commerce/webapi/rest/use-rest/performing-searches
Stuck on a tricky one?
If you have a problem in Magento indexing, cron, MSI stock, or order grid sync 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 save you a reindex mystery?
If this saved you hours of chasing a silent indexing gap, 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