What happened?

Taxonomy::removeOrphanNodes() (administrator/components/com_finder/src/Indexer/Taxonomy.php) deletes orphan leaf nodes in a loop and reuses the same query object on every pass:

$query2->clear('where')->whereIn($db->quoteName('id'), $nodes);

clear('where') does not remove the values bound by whereIn(). On the second pass the query has more bound values than placeholders, and executing it throws ExecutionFailureException: HY093. A second pass happens whenever orphan nodes are chained over two or more levels (deleting the leaves turns their parents into orphan leaves).

The method is called by Indexer::optimize() (end of "Index", and Maintenance → Optimise), Indexer::remove() and Adapter::reindex().

Minimal reproduction (CLI script on Joomla 6.2.0; it only deletes negative ids, so no data is changed):

$query2 = $db->createQuery()->delete($db->quoteName('#__finder_taxonomy'));
foreach ([[-1, -2], [-3]] as $nodes) {
    $query2->clear('where')->whereIn($db->quoteName('id'), $nodes); // same line as in removeOrphanNodes()
    echo (string) $query2, ' | bound values: ', count($query2->getBounded()), PHP_EOL;
    $db->setQuery($query2)->execute();
}

Output:

DELETE FROM `#__finder_taxonomy` WHERE `id` IN (:preparedArray1,:preparedArray2) | bound values: 2
DELETE FROM `#__finder_taxonomy` WHERE `id` IN (:preparedArray3) | bound values: 3
Joomla\Database\Exception\ExecutionFailureException: HY093

With ->clear('bounded') added, the second pass has 1 bound value and runs fine.

How we hit it on a real site: the Smart Search index had been built under Joomla 5.1 (top-level branches Type, Language, Category and Author stored with language ''). After the update to 6.2.0, an "Index" run without a purge created new top-level branches with language * and mapped every item to them, which left the whole old Category subtree (Category › Products › …) orphaned over several levels. All batches succeeded, then the indexer.optimize request failed.

The same code is in 5.4-dev, 6.2-dev and 6.3-dev.

Version

6.2

Expected result

Orphan taxonomy nodes are removed over as many passes as needed, and indexing finishes with the success message.

Actual result

• ExecutionFailureException: HY093 in Taxonomy.php (line 454), called from Indexer::optimize().
• The indexer modal only shows a generic error ("An error has occurred while fetching the JSON data: HTTP status code 520" behind Cloudflare), and Apache logs [proxy_fcgi:error] Invalid status line from script 'index.php': 0 (see Additional Comments).

System Information

Joomla 6.2.0, PHP 8.4 (FPM), MariaDB 10.11, database driver mysql (PDO), Apache with proxy_fcgi.

Additional Comments

Proposed fix (one line), tested on the site above: Optimise then succeeds, and a two-level orphan chain is removed:

$query2->clear('where')->clear('bounded')->whereIn($db->quoteName('id'), $nodes);

(Creating a new delete query on each pass would work too.)

Related, separate problem that hides the real message: IndexerController::sendResponse() calls $app->setHeader('status', $data->getCode()). Database exceptions have code 0 here, so PHP sends Status: 0, Apache rejects it (Invalid status line ... 0), and the user gets a generic server error instead of the exception message. Falling back to 500 when the code is not a valid HTTP status would show the real message.