Fixing the “product(s) missing from ES” Infinite Reindex Loop in Magento 2 (Reliable Fix + SQL Repair)

One of the more maddening problems you can hit on a Magento 2 store isn't in Magento at all — it's in a custom auto-heal routine you (or a previous team) bolted on top of it. Stock Magento ships no DB-versus-Elasticsearch reconciliation job, so when one exists it's custom code: it checks whether every product is in the Elasticsearch fulltext index, re-queues the stragglers, and trusts cron to clean up. Instead, every few minutes the same log line returns: Auto-heal detected N product(s) missing from ES. The same IDs. Forever. Each tick burns reindex cycles, inflates queue load, and produces zero convergence — the catalog is fine, but your infrastructure is spinning.

The root cause is almost always the same: your routine's idea of "should be indexed" doesn't match the contract catalogsearch_fulltext actually enforces. Magento's indexer is behaving correctly and by design; the custom reconciliation is the part that's wrong. We'll cover why the loop happens, how to confirm it with SQL, and how to make your routine mirror the indexer instead of fighting it.

Why your custom reconciliation job keeps re-queuing the same products

The bug lives in how your routine defines "should be indexed." A typical hand-rolled reconciliation builds its expected set by querying catalog_product_entity joined to catalog_product_entity_int on the status attribute (store_id=0, value=1) — enabled products only. It then diffs that set against what's actually in Elasticsearch and re-queues the difference. The problem: catalogsearch_fulltext does not select products by status alone.

The authoritative definition of "searchable" is Magento\CatalogSearch\Model\Indexer\Fulltext\Action\GetSearchableProductsSelect. Its execute() applies (at least) three gating joins, in this order:

1. An INNER JOIN on catalog_product_website for the store's website_id — products not assigned to that website are never indexed.
2. A visibility attribute join filtered against engine->getAllowedVisibility() (Magento\CatalogSearch\Model\ResourceModel\EngineInterface), which resolves to getVisibleInSiteIds() = [3, 2, 4] and never includes VISIBILITY_NOT_VISIBLE (=1, "Not Visible Individually").
3. A status attribute join for enabled products.

Both attribute joins are store-scoped: joinAttribute() reads the default row (store_id=0) and the store-view row and resolves the effective value with getCheckSql(store.value_id > 0, store.value, default.value). So a status-only, store_id=0-only expected set disagrees with the indexer on three separate axes. The diff is permanently non-empty, and the loop never ends.

Common culprits:

• The reconciliation filters on status but ignores visibility and website assignment — it doesn't mirror the indexer's real filters.
• Enabled visibility=1 products legitimately exist (for example, child simples of configurables or bundles), so a status-only set wrongly counts them as missing.
• The expected set is resolved at store_id=0 only, ignoring store-view visibility overrides that the indexer honors.
• The heal step fails silently — it re-queues a reindex, the product still never lands as its own ES document, and nothing signals failure.
• No idempotency check: the routine treats its own SQL as ground truth rather than deriving "indexable" from Magento's own rules.

One subtlety worth getting right: composite-product children (configurable/bundle simples) aren't simply "missing" from Elasticsearch — their searchable text is folded into the parent's ES document via DataProvider::getProductChildIds(). They are represented through the parent, never as standalone documents. visibility=1 is the catalog-display convention that accompanies this, not the underlying indexing mechanism. A per-product reconciliation that expects one ES document per child will flag them forever no matter what you set visibility to.

1. Resolve the visibility and status attribute IDs

Never hardcode these — they vary per install. Look them up.

SELECT a.attribute_id, a.attribute_code
FROM eav_attribute a
JOIN eav_entity_type t ON a.entity_type_id = t.entity_type_id
WHERE t.entity_type_code = 'catalog_product'
  AND a.attribute_code IN ('status', 'visibility');

You should get exactly two rows — one per attribute. The numeric IDs differ on every install, so do not copy any value you see in an example; resolve them at runtime in code, where they become your join keys.

2. Confirm the offending products really are absent from ES

Prove the diagnosis before touching code. Find enabled, Not-Visible-Individually products and confirm they are not their own ES documents. Substitute the attribute IDs you resolved in step 1, and pick a real website ID for the store you index:

SELECT cpe.entity_id, cpe.sku
FROM catalog_product_entity cpe
JOIN catalog_product_entity_int v
  ON v.entity_id = cpe.entity_id AND v.attribute_id = <visibility_id>
     AND v.store_id = 0 AND v.value = 1
JOIN catalog_product_entity_int s
  ON s.entity_id = cpe.entity_id AND s.attribute_id = <status_id>
     AND s.store_id = 0 AND s.value = 1
JOIN catalog_product_website w
  ON w.product_id = cpe.entity_id AND w.website_id = <website_id>
LIMIT 20;

Spot-check a few IDs with a terms query against your live ES index. You should see 0 hits — these products are excluded from ES by design, not by accident. If your reconciliation flags these exact IDs every run, you've confirmed the loop. (This store_id=0 query is a quick confirmation only; the real fix in step 3 has to account for store-view scope and website assignment.)

3. Make the expected-set query mirror the indexer

This is the fix. The durable version is not "add one more join" — it's to stop hand-rolling the expected set and derive it from the same source the indexer uses. The most reliable approach is to build your reconciliation's expected set by reusing GetSearchableProductsSelect (or at minimum EngineInterface::getAllowedVisibility()), run per store, so you inherit the website join, the allowed-visibility list, and the store-view scope resolution for free.

If you must keep a SQL-based check, it has to gate on website assignment AND visibility AND status, at the right scope. The minimum corrections to the expected-set query are the website join and the allowed-visibility filter:

$visAttrId = $this->getAttributeId($connection, 'visibility');
$allowedVisibility = $this->engine->getAllowedVisibility(); // [3, 2, 4]

// Website assignment — the indexer INNER JOINs this before anything else.
$select->join(
    ['pw' => $connection->getTableName('catalog_product_website')],
    'pw.product_id = cpe.entity_id AND pw.website_id = ' . (int) $websiteId,
    []
);

// Visibility — mirror getAllowedVisibility() exactly, per store, with the
// store-view value overriding the default (store_id=0) value when present.
$select->joinLeft(
    ['vis_d' => $connection->getTableName('catalog_product_entity_int')],
    'vis_d.entity_id = cpe.entity_id AND vis_d.attribute_id = ' . (int) $visAttrId
        . ' AND vis_d.store_id = 0',
    []
)->joinLeft(
    ['vis_s' => $connection->getTableName('catalog_product_entity_int')],
    'vis_s.entity_id = cpe.entity_id AND vis_s.attribute_id = ' . (int) $visAttrId
        . ' AND vis_s.store_id = ' . (int) $storeId,
    []
)->where(
    $connection->getCheckSql('vis_s.value_id > 0', 'vis_s.value', 'vis_d.value')
        . ' IN (?)',
    $allowedVisibility
);

getAllowedVisibility() returns [3, 2, 4] — In Search (3), In Catalog (2), and Both (4) — and excludes only Not Visible Individually (1). The indexer builds a WHERE <visibility> IN (2,3,4) list from this, so use the same list rather than a value > 1 shortcut: the two happen to agree only because visibility is bounded to {1,2,3,4}, and the IN list is what Magento actually emits. Resolving the value with getCheckSql(store.value_id > 0, store.value, default.value) is what lets a store-view override of visibility win over the default — exactly as joinAttribute() does. Apply the same default/store-view overlay to your status join too, and run the whole check once per non-default store.

If your catalog is genuinely single-store with no store-view visibility overrides, you can drop the vis_s join and compare the default value directly — but only then.

4. Verify the loop converges

Run the reconciliation twice and watch the diff empty out. Use a fixed-string grep so the literal symptom is matched exactly:

bin/magento <your:reconcile:command>; sleep 2; \
bin/magento <your:reconcile:command> 2>&1 \
  | grep -F 'missing from ES' || echo 'CONVERGED: nothing flagged missing'

The second run should print CONVERGED. If products are still flagged, confirm three things: you mirrored the website join, you compared against the live aliased ES index (not a stale one), and you resolved visibility per store rather than at store_id=0 only.

When this points to a deeper problem

A faithful expected-set query fixes the symptom, but the pattern is worth auditing across your codebase:

• Duplicated indexability rules. Any custom "is everything indexed?" check must apply the same gates the real indexer applies — website assignment AND visibility AND status, at the correct store scope — or it will perpetually disagree with reality. Stock Magento ships no such reconciliation job, so every one of these is custom and owns this risk.
• Per-product checks against composite catalogs. If your check expects one ES document per product, it will fight Magento on every configurable/bundle child, because those children are folded into the parent document. Scope the expected set to the actual parent relationship, not to every visibility=1 product in the catalog.
• Silent loops with no backstop. A heal routine that re-queues work without detecting that the work never succeeds needs a per-ID attempt cap or an alert on a stable, non-empty diff.
• Custom SQL as authority. The single source of truth for what catalogsearch_fulltext emits is GetSearchableProductsSelect together with EngineInterface::getAllowedVisibility() — derive from those, don't re-invent them.
• Cron amplification. A non-converging diff on a five-minute schedule becomes continuous wasted reindex load, not a one-off error.

We've seen this most often when a team writes a custom reconciliation against status only and forgets that the fulltext indexer also gates on website assignment and visibility, store-scoped. Grep your custom modules for any expected-set query that joins the status attribute on catalog_product_entity_int but skips catalog_product_website or the visibility check:

grep -rnE "catalog_product_entity_int.*status|status.*catalog_product_entity_int" app/code \
  | grep -L catalog_product_website

Resetting the queue and reindexing buys you a quiet hour, not a fix — the loop returns on the next tick until the expected-set logic matches the indexer's contract. A short audit of your other auto-heal routines is the durable answer; it'll tell you whether the same blind spot is hiding elsewhere.

Add the website join and the per-store allowed-visibility filter, confirm convergence over two runs, and the loop stops. From there you'll have a reconciliation that agrees with Magento instead of arguing with it — which is the only state in which an auto-heal job can ever actually heal.