Magento 2 Stock & Product Relation Indexer Stuck on Grouped Products? How We Fixed Duplicate catalog_product_relation Entries (Real Case Study)

Last week, our Magento team at Bliss Web Solution was pulled into a firefight on a large healthcare ecommerce site — a catalog-heavy Magento 2 store with thousands of SKUs and a heavy dependency on grouped products (the parent/child SKU structure that medical-supply retailers use to bundle accessories, variants, and kit items under a single buyable listing). The client is under NDA, so specifics have been anonymized — but the root cause, the diagnostics, and the fix are reproduced exactly as they were applied.

The symptom was simple on the surface. The client reported that grouped products were behaving erratically on the storefront — stock status wasn’t updating, some children weren’t appearing under their parent, and a manual reindex from the admin silently hung. When we ran bin/magento indexer:reindex on the CLI, the Stock and Product/Category Product Relation indexers crawled for a while and then got stuck in the ‘Processing’ state with no error output and no progress after an hour.

This post is the full technical write-up of what we found, why it happened, and the exact SQL we ran to fix it — first on staging, then, after validation, on production. If you are staring at a stuck Magento 2 indexer and the top Google results have you resetting MViews, clearing locks, and still getting nowhere, this is the root cause none of the generic guides surface: duplicate entries in the catalog_product_relation table.

1. The Symptoms — How a Stuck Stock/Product Relation Indexer Shows Up

Before the fix, match symptoms carefully, because this class of bug can look like half a dozen other problems:

• Grouped products on the frontend show ‘Out of Stock’ even when all children are in stock.
• Some child products disappear from the parent grouped product on the PDP.
• The admin System → Tools → Index Management page shows ‘Processing’ for cataloginventory_stock and/or catalog_product_category indexers and never finishes.
• The CLI command bin/magento indexer:reindex appears to run but never prints ‘finished.’ It simply hangs until killed.
• MySQL’s process list shows a long-running query against catalog_product_relation, cataloginventory_stock_status, or catalog_product_index_price in a ‘Sending data’ state.
• Cron job logs show scheduled indexer failures but no actionable stack trace.

If your site runs ‘Update by Schedule’ mode, you will typically also see MView change log tables (*_cl) growing unbounded — Magento’s way of telling you it has a backlog it cannot clear.

2. What catalog_product_relation Is and Why It Matters

To understand the root cause, take 60 seconds on what this table actually does.

catalog_product_relation is one of the most important, least-talked-about tables in Magento 2. It is the junction table that stores the parent→child linkage for every composite product type in Magento — configurable, bundle, and grouped. The schema is small:0

A composite primary key of (parent_id, child_id) is the entire integrity contract — it tells Magento that a given simple product belongs to exactly one relationship with a given parent. A whole series of indexers read from this table to do their work:

• cataloginventory_stock — rolls up child stock status onto composite parents so grouped/configurable products appear in/out of stock correctly.
• catalog_product_category (Product/Category Product Relation) — ensures children appear in the categories their parent is assigned to.
• catalog_product_price — computes final prices that depend on child pricing (especially for grouped and bundle types).
• catalogsearch_fulltext — indexes attributes from children for search against the parent.

If catalog_product_relation has inconsistent or duplicate rows, all of those indexers either produce wrong output or, worse, enter a state where the underlying join cannot converge.

3. The Root Cause — Duplicate Rows in catalog_product_relation

Here is what we found when we started investigating. We ran a diagnostic query:

SELECT parent_id, child_id, COUNT(*) AS duplicate_count
FROM   catalog_product_relation
GROUP  BY parent_id, child_id
HAVING COUNT(*) > 1;

On a healthy Magento 2 store, this query should return zero rows. On the client’s staging clone, it returned a clean list of several dozen duplicates — the same (parent_id, child_id) pair appearing two or three times.

How can that happen when the table has a composite primary key that should prevent it? The answer: it usually cannot, unless an upgrade, data migration, schema patch, or a third-party module wrote to the table with the primary key temporarily dropped or with INSERT IGNORE semantics that permitted a race. In our case, the trail led back to a historical data import run with a modified schema during a platform migration — the primary key had been reconstructed afterward, but the bad rows remained. (Prevention measures are in section 6.)

The consequence was predictable once we looked at the indexer SQL. The cataloginventory_stock indexer joins catalog_product_relation against cataloginventory_stock_status and catalog_product_entity; duplicates in the relation table multiply the join result, which blows up intermediate tuple counts, which lengthens query execution — and on heavy inventory stores tips the process into effective timeout. That is why manual re-indexing appeared to hang: the query was running, it simply was not going to finish in any reasonable window.

4. The Fix We Applied — Step-by-Step (Tested on Staging First)

Do not run these steps against production without running them on a staging clone first. This is a destructive change to a live catalog table.

Step 1 — Take a full database backup

Non-negotiable. Before you touch the table, dump at least catalog_product_relation — ideally the whole database.

mysqldump -u <user> -p <database> catalog_product_relation \
  > cpr_backup_$(date +%F).sql

Step 2 — Identify and quantify the duplicates

SELECT parent_id, child_id, COUNT(*) AS dup
FROM   catalog_product_relation
GROUP  BY parent_id, child_id
HAVING COUNT(*) > 1
ORDER  BY dup DESC;

Export the result set to CSV and keep it — that is your audit trail of what you changed, and it is how you spot-check later that no legitimate relationships were lost.

Step 3 — Validate that no parent or child IDs on the list are orphaned

Duplicate rows that reference a product that no longer exists are safe to remove outright. Duplicate rows for a product that does exist need to keep at least one row after cleanup, not zero.

SELECT cpr.parent_id, cpr.child_id
FROM   catalog_product_relation cpr
LEFT JOIN catalog_product_entity p ON p.entity_id = cpr.parent_id
LEFT JOIN catalog_product_entity c ON c.entity_id = cpr.child_id
WHERE  p.entity_id IS NULL OR c.entity_id IS NULL;

Any rows returned here can be deleted without concern — they reference ghosts.

Step 4 — Remove duplicates while preserving exactly one row per pair

The safest pattern is a two-step: copy distinct rows to a temporary table, swap, then drop the old. This avoids the footgun of self-joining delete queries.

-- 4a. Copy uniques to a temp table
CREATE TABLE catalog_product_relation_tmp
  LIKE catalog_product_relation;
 
INSERT INTO catalog_product_relation_tmp (parent_id, child_id)
SELECT DISTINCT parent_id, child_id
FROM   catalog_product_relation;
 
-- 4b. Swap tables atomically
RENAME TABLE catalog_product_relation     TO catalog_product_relation_old,
             catalog_product_relation_tmp TO catalog_product_relation;
 
-- 4c. Once verified, drop the old table
-- DROP TABLE catalog_product_relation_old;

RENAME TABLE is atomic in MySQL/MariaDB, so there is no window where Magento sees a missing table.

If you prefer an in-place delete, this works too, but it locks the table longer and is riskier on a busy production host:

DELETE cpr1
FROM   catalog_product_relation cpr1
INNER JOIN catalog_product_relation cpr2
WHERE  cpr1.parent_id = cpr2.parent_id
   AND cpr1.child_id  = cpr2.child_id
   AND cpr1.link_id   > cpr2.link_id;

Note: Magento 2.3+ added a link_id auto-increment column on this table. Use whichever unique per-row column your version exposes as the tiebreaker.

Step 5 — Reset the stuck indexers

With the underlying data clean, reset the indexer state so Magento can start a new run:

bin/magento indexer:reset cataloginventory_stock
bin/magento indexer:reset catalog_product_category
bin/magento indexer:reset catalog_product_price
bin/magento indexer:reset catalogsearch_fulltext

If MView has stale ‘working’ rows blocking the scheduled runner:

UPDATE mview_state   SET status = 'idle'    WHERE status = 'working';
UPDATE indexer_state SET status = 'invalid' WHERE status = 'working';

Step 6 — Re-run the indexers

bin/magento indexer:reindex cataloginventory_stock \
                             catalog_product_category \
                             catalog_product_price \
                             catalogsearch_fulltext
bin/magento cache:flush

On the client’s production environment, the stock indexer — which had previously hung for over an hour — finished in under four minutes once the duplicates were gone. That is the ‘huh, so that was the problem’ moment.

Step 7 — Verify on the storefront and admin

• Spot-check 5–10 grouped products on the frontend for correct stock status.
• Confirm the Index Management page now shows ‘Ready’ for all indexers.
• Tail var/log/system.log and var/log/exception.log for 10–15 minutes of live traffic.

After staging behaved cleanly for a full business day, we repeated the exact sequence on production inside a scheduled maintenance window.

5. Why the Generic ‘Reset Indexer’ Advice Did Not Work Here

If you have been searching for this problem, you have likely tried:

• bin/magento indexer:reset
• Updating indexer_state.status to ‘invalid’
• Clearing mview_state
• Truncating *_cl changelog tables
• Flushing cache
• Running bin/magento setup:di:compile and setup:upgrade

None of those touch the actual problem. They all address the symptom — a stuck indexer — by nudging Magento into trying again. But if the underlying dataset in catalog_product_relation is still producing a multiplied join, ‘trying again’ just reproduces the hang. We have seen teams spin their wheels for days on this class of issue because the top-ranking results push you toward surface-level indexer hygiene and never into the data layer itself.

The diagnostic shortcut is: if resetting the indexer and rerunning produces the exact same hang in the exact same indexer(s), suspect data integrity before you suspect the indexer.

6. Preventing the Problem from Coming Back

Once the site was stable, we put guardrails in place so this could not happen again quietly.Nightly integrity check. A simple cron that runs the duplicate-detection query and alerts on non-zero results:

SELECT COUNT(*) FROM (
  SELECT parent_id, child_id
  FROM   catalog_product_relation
  GROUP  BY parent_id, child_id
  HAVING COUNT(*) > 1
) dupes;

We emit the count to the monitoring stack and alert if it is ever greater than zero.

Audit of third-party modules that write to catalog relations. Any extension that manipulates configurable, bundle, or grouped products — product-feed importers, catalog sync tools, marketplace connectors — gets a review on how it writes to catalog_product_relation. Raw SQL writes should be replaced with calls through the repository/API where possible, because the API path enforces the uniqueness contract.

Import-time validation. For CSV and API imports, we run a pre-flight SQL check right after the import finishes, in the same deploy transaction. If duplicates appear, the import is flagged for review before indexers ever run.

Awareness of the MView limitation. There is an open Magento GitHub issue (#31061) noting that catalog_product_relation is not ideally suited for MView subscription because of how foreign keys and cascading deletes interact. If you are building a custom indexer that watches product variants, handle deletes with an afterDelete plugin rather than relying on the usual MView trigger pattern.

Cron health. An indexer in scheduled mode depends entirely on cron. A half-running cron process can silently let changelog tables grow. We monitor the cron heartbeat with a dead-man switch.

7. Advanced Troubleshooting If Your Fix Does Not Stick

If you deduplicated catalog_product_relation and the indexer is still hanging, here is the short list of secondary suspects.

Changelog table bloat. *_cl tables (for example cataloginventory_stock_cl, catalog_product_category_cl) can accumulate millions of rows if cron has been broken for weeks. Safe procedure: take a MySQL snapshot, then truncate the *_cl table, reset MView, and force a full reindex.

Stale MView version pointers. mview_state tracks version_id per MView. If it points at a version that no longer exists in the changelog, the indexer tries to read rows that are not there. Reset with:

UPDATE mview_state SET version_id = 0, status = 'idle'
 WHERE view_id IN ('cataloginventory_stock','catalog_product_category');

Duplicate entries in other relation tables. The same class of bug can appear in catalog_url_rewrite_product_category and catalog_category_product. Run the same GROUP BY / HAVING COUNT(*) > 1 query against those tables.

MySQL/MariaDB query planner regressions. Some MariaDB 10.3 builds show pathological plans on category indexer queries (see Magento GitHub issue #25199). If you are on that track, upgrading to a newer point release or adjusting optimizer_switch is a known workaround.

Module conflict. Third-party modules that override Magento\CatalogInventory\Model\Indexer\Stock\Action\Full or Magento\Catalog\Model\Indexer\Product\Category sometimes introduce their own SQL that does not tolerate duplicates. Disable them one by one in staging.

8. When to Bring Bliss Web Solution Into the Fix

There is a point at which SQL courage stops being a virtue. The incident above is exactly the class of work our Magento engineering team at Bliss Web Solution handles on a recurring basis — stuck indexers, data drift, catalog-layer corruption, migration cleanup. That is why we are comfortable publishing the whole fix in the open. If you recognize any of the situations below, bring us in before the DELETE runs:

• No staging environment that mirrors production. Bliss can stand up a safe, data-accurate staging clone before any destructive SQL is touched on live — something no certified-partner checklist substitutes for.
• No verified, recent database backup. Our team will build and test a backup + rollback plan end to end before executing the fix, so you are never one query away from a restore you have not validated.
• Live store losing measurable revenue per hour of downtime. Bliss works inside tight maintenance windows with a rollback path pre-staged, so the fix lands fast and the downtime clock stops where it should.
• The issue persists after deduplication. That usually signals deeper data corruption in related tables (catalog_category_product, catalog_url_rewrite_product_category, catalog_product_index_price). Bliss runs a structured Magento Database Integrity Audit built exactly for this scenario.
• Older Magento line (2.3.x or earlier) that is out of security support. We plan the version upgrade alongside the fix so you do not pay for the same downtime window twice — one-shot triage plus upgrade roadmap.
• Third-party modules in the catalog stack that you do not fully trust. Bliss has in-house patterns for auditing product-feed importers, marketplace sync tools, and ERP connectors that touch catalog_product_relation — the same patterns we applied in the fix above.

The catalog_product_relation table is structural — bad changes here cascade into stock, pricing, search, and category assignment simultaneously. If you are uncertain, get a second set of eyes before the DELETE runs. Our Magento team at Bliss Web Solution is on call for exactly this kind of triage — reach out and we will match you with the engineer who has seen your specific flavor of this bug before.

9. Frequently Asked Questions (FAQ)

10. About Bliss Web Solution

We are a Magento-focused ecommerce engineering team that has been building, rescuing, and scaling Magento 2 and Adobe Commerce stores for clients across the globe. The backend engineers and DBAs on our team handle production incidents of exactly this shape on a regular basis — stuck indexers, data drift, migration cleanups, and performance triage.

If you are facing a stuck indexer that no one else can diagnose, reach out. We do not sell guesses — we sell reproducible, tested fixes with full audit trails, just like the one above.

Closing note: That is the unabridged version of how we fixed a stuck Magento 2 stock and product relation indexer on a live healthcare ecommerce store by finding — and removing — duplicate rows in the one table most troubleshooting guides forget: catalog_product_relation. If this exact symptom brought you here, run the diagnostic query first. Odds are, the fix is closer than the search results suggested.

Sources & Further Reading

• Magento GitHub issue #31061 — catalog_product_relation is unsuitable for mview
• Magento GitHub issue #15939 — Duplicated product is out of stock when index set to UPDATE BY SCHEDULE
• Magento GitHub issue #37816 — Unnecessary GROUP BY in SQL causing products to be indexed multiple times
• Magento GitHub issue #25199 — Catalog Category Indexing takes very long on MariaDB 10.3 with many products
• Adobe Commerce — Official Indexing Component Documentation