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

One of the more maddening Magento 2 catalog problems is a self-healing routine that never heals. You write (or inherit) a reconciliation job that 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.

Why Magento 2 keeps re-queuing the same products

The bug lives in how your routine defines "should be indexed." A typical 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 index by status alone. MagentoCatalogSearchModelIndexerFulltextActionGetSearchableProductsSelect::joinAttribute() also filters on the visibility attribute against engine->getAllowedVisibility(), which never includes VISIBILITY_NOT_VISIBLE (=1, "Not Visible Individually"). Those products are skipped from ES by design. Your expected set includes them, ES never will, the diff is permanently non-empty, and the loop never ends.

Common culprits:

• The reconciliation filters on status but forgets visibility — it doesn't mirror the indexer's real filters.
• Enabled visibility=1 products legitimately exist (child simples of configurables/bundles), so a status-only set wrongly counts them.
• The heal step fails silently — it re-queues a reindex, the product still never lands in ES, 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.

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 one row per attribute (often visibility ~99, status ~97). Resolve them at runtime in code — these IDs 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:

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
LIMIT 20;

Spot-check a few IDs with a terms query against your live ES index. You should see 0 hits. These are exactly the products your routine flags forever — and they're not a data error.

3. Add a visibility join to the expected-set query

This is the fix. In the routine that builds your expected indexable set, add a visibility > 1 join alongside the existing status join.

$visAttrId = $this->getAttributeId($connection, 'visibility');
if ($visAttrId !== null) {
    $select->join(
        ['vis' => $connection->getTableName('catalog_product_entity_int')],
        'vis.entity_id = cpe.entity_id AND vis.attribute_id = ' . (int) $visAttrId
            . ' AND vis.store_id = 0',
        []
    )->where('vis.value > ?', 1); // exclude VISIBILITY_NOT_VISIBLE (1)
}

value > 1 keeps In Catalog (2), In Search (3), and Both (4), dropping only Not Visible Individually (1) — mirroring exactly what catalogsearch_fulltext emits. Use store_id=0 unless a store view overrides visibility.

4. Verify the loop converges

Run the reconciliation twice and watch the diff empty out.

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

The second run should print CONVERGED. If products are still flagged, double-check your store_id scope and confirm you compared against the live aliased ES index, not a stale one.

When this points to a deeper problem

A one-line join fixes the symptom, but the pattern is worth auditing across your codebase:

• Duplicated indexability rules. Any "is everything indexed?" check must apply the same filters the real indexer applies — status AND visibility, at the correct store scope — or it will perpetually disagree with reality.
• 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 and engine->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 reconciliation against status only and forgets that the fulltext indexer also gates on visibility. Grep your custom code for any expected-set query that joins the status attribute on catalog_product_entity_int but not visibility — that pattern is the fingerprint.

Add the visibility join, confirm convergence, and the loop stops on the next cron tick. From there, a quick audit of your other auto-heal routines will tell you whether the same blind spot is hiding elsewhere.