[4.x]: purgeIncompleteCarts() never deletes searchindex rows — subquery resolves after the cascade has removed the orders"

[antcooper]

What happened?

Description

Carts::purgeIncompleteCarts() attempts to delete searchindex rows belonging to purged
carts, but the delete always matches zero rows. The searchindex rows for every purged
cart are therefore orphaned permanently.

The cause is statement ordering. The method builds a Query object (not a materialised
array of IDs), then reuses it for two deletes:

https://github.com/craftcms/commerce/blob/4.11.2/src/services/Carts.php#L381-L411

$cartIdsQuery = (new Query())
    ->select(['orders.id'])
    ->where(['not', ['isCompleted' => true]])
    ->andWhere('[[orders.dateUpdated]] <= :edge', ['edge' => Db::prepareDateForDb($edge)])
    ->from(['orders' => Table::ORDERS]);

// Delete the elements table rows, which will cascade across all other InnoDB tables
Craft::$app->getDb()->createCommand()
    ->delete('{{%elements}}', ['id' => $cartIdsQuery])
    ->execute();

// The searchindex table is probably MyISAM, though
Craft::$app->getDb()->createCommand()
    ->delete('{{%searchindex}}', ['elementId' => $cartIdsQuery])
    ->execute();

return $cartIdsQuery->count();

$cartIdsQuery selects from commerce_orders. The first delete removes the elements
rows, and commerce_orders.id has a foreign key to elements.id with
ON DELETE CASCADE, so the matching commerce_orders rows are removed as part of that
statement.

By the time the second delete runs, $cartIdsQuery is re-executed as a subquery against a
table whose rows no longer exist. It returns an empty set, so the searchindex delete
affects zero rows.

Foreign key confirmed on a Craft 4 / Commerce 4 schema:

CONSTRAINT_NAME                          DELETE_RULE  TABLE_NAME       COLUMN_NAME  REFERENCED_TABLE_NAME
fk_sdqcfbkusgqmgvsrrpcymjspbgyrkgfaylre  CASCADE      commerce_orders  id           elements

Steps to reproduce

1. On a Commerce site with purgeInactiveCarts enabled, allow some incomplete carts to
   age past purgeInactiveCartsDuration.
2. Note the searchindex rows belonging to those carts:

   SELECT COUNT(*) FROM searchindex s
   JOIN commerce_orders o ON o.id = s.elementId
   WHERE o.isCompleted = 0;

3. Run php craft gc (or otherwise trigger purgeIncompleteCarts()).
4. Confirm the carts are gone from commerce_orders.
5. Confirm their searchindex rows remain, now orphaned:

   SELECT COUNT(*) FROM searchindex s
   LEFT JOIN elements_sites es
     ON es.elementId = s.elementId AND es.siteId = s.siteId
   WHERE es.elementId IS NULL;

Expected behaviour

searchindex rows belonging to purged carts are deleted along with the carts.

Actual behaviour

Zero searchindex rows are deleted. They accumulate indefinitely, one set per purged
cart.

Why this compounds

Craft's own orphan cleanup cannot recover from this at scale.
craft\services\Gc::run() calls hardDeleteElements() before
_deleteOrphanedSearchIndexes(), and Search::deleteOrphanedIndexes() performs a single
unbatched DELETE ... JOIN with no LIMIT. On a large table that statement does not
complete within typical timeouts, so the orphans are never cleared and each subsequent
purge adds more.

The practical result is unbounded growth of searchindex on any Commerce site with cart
churn.

Real-world impact

On the production site where we found this:

	rows
Orphaned (no matching elements_sites row)	30,737,883
Attached to live elements but with empty keywords	2,421,550
Legitimate, searchable rows	638,319
Total	33,797,752

searchindex reached ~3,153 MB, roughly 89% of the entire database; the next largest
table was 87 MB. The site experienced downtime, and the hosting provider flagged the table
as the cause. Cleanup required deleting ~31 million rows in bounded chunks, because the
built-in cleanup paths could not complete.

Suggested fix

The minimal change is to reorder the two deletes so the subquery still resolves when the
searchindex delete runs:

// Delete the search index rows first, while the orders still exist for the subquery
Craft::$app->getDb()->createCommand()
    ->delete('{{%searchindex}}', ['elementId' => $cartIdsQuery])
    ->execute();

Craft::$app->getDb()->createCommand()
    ->delete('{{%elements}}', ['id' => $cartIdsQuery])
    ->execute();

Alternatively, materialise the IDs once and use them for both statements, which is more
explicit about intent and avoids re-running the query three times:

$cartIds = $cartIdsQuery->column();

if (empty($cartIds)) {
    return 0;
}

Craft::$app->getDb()->createCommand()
    ->delete('{{%searchindex}}', ['elementId' => $cartIds])
    ->execute();

Craft::$app->getDb()->createCommand()
    ->delete('{{%elements}}', ['id' => $cartIds])
    ->execute();

return count($cartIds);

Chunking either form would also be worth considering, since a first-ever purge on a
long-running site can span a very large number of carts in one statement.

Two related observations

The return value is also affected. return $cartIdsQuery->count(); on line 410 runs
after the elements delete, so it counts a table whose rows are gone and returns 0.
purgeIncompleteCarts() therefore reports zero carts purged even when it has just purged
thousands, which makes the problem invisible in logs.

The comment on line 405 is out of date. "The searchindex table is probably MyISAM,
though" — on Craft 4 with MySQL 5.6+/8, searchindex is created with the default engine,
which is InnoDB. This doesn't affect the bug, but the comment appears to be the reason the
searchindex delete is a separate statement rather than relying on the cascade, so it may
be worth revisiting.

Versions

• Craft CMS: 4.18.2
• Craft Commerce: 4.11.2
• Craft Commerce Stripe: 4.1.8.2
• PHP: 8.2 in local dev
• Database (production): Percona XtraDB Cluster 8.0.36-28.1
• Database (local reproduction): MySQL 8.0.40
• Relevant settings: purgeInactiveCarts enabled, purgeInactiveCartsDuration = P7D,
  autoSetCartShippingMethodOption = true

Code references are to craftcms/commerce 4.11.2, src/services/Carts.php lines 381-411.
The same ordering is worth checking on Commerce 5.

Craft CMS version

4.18.2

Craft Commerce version

4.11.2

PHP version

8.2

Operating system and version

No response

Database type and version

No response

Image driver and version

No response

Installed plugins and versions

[linear-code]

COM-595

[lukeholder]

Thanks for reporting this issue! I have fixed it for the next release.

To get the fix early, change your craftcms/commerce requirement in composer.json to:

"require": {
  "craftcms/commerce": "5.x-dev#c58207f3c0c6bb6e7a8f4c1b6bb2a1096178a631 as 5.7.1",
  "...": "..."
}

Then run composer update.

We will update this ticket once the release is out. Thanks again!

[antcooper]

Thanks for fixing @lukeholder. Any chance you could backport this to the 4.x branch please? Our client hasn't moved up to 5.x yet (we're working on them!).

[nfourtythree]

Commerce 5.7.2 has now been released with this fix included.

Thanks!

[lukeholder]

Went ahead and back ported it to 4.12.2 for you @antcooper

[antcooper]

@lukeholder @nfourtythree thank you both for your help with this. Much appreciated 🙏