What happened?

1. Default Joomla installation on MySQL 8.x. All #_finder* tables use the default collation utf8mb4_unicode_ci, which is accent-insensitive ('korišćenja' = 'koriscenja' evaluates to true).
2. Site content language whose words contain Latin diacritics. Verified with Serbian (latin script); the same applies to Czech, German, Turkish, Romanian, Hungarian, Polish etc.
3. The plugin "Content - Smart Search" (plg_content_finder) is enabled, so articles are indexed on save.
4. In the backend, create a new article whose title contains a word with diacritics, for example: Pravila i uslovi korišćenja usluga i servisa. Leave the alias empty so Joomla transliterates it to pravila-i-uslovi-koriscenja-usluga-i-servisa (this is correct behaviour, diacritics are stripped in the alias).
5. Add any body text and save the article.

Version

6.1

Expected result

The article saves normally. The indexer either treats the two spelling variants of the same word ("korišćenja" from the title/body, "koriscenja" from the alias) as one term or stores them as two distinct terms, but never surfaces an SQL exception to the editor.

Actual result

Saving fails with:

Save failed with the following error: Duplicate entry 'koriscenja-sr' for key '#__finder_terms.idx_term_language'

The article row itself is stored before the indexing event, so every retry creates another duplicate article (with alias suffix -2, -3, ...) and the "Alias already existed" message appears on top of the SQL error, which is very confusing for editors.

System Information

Joomla 6.1.1, PHP 8.4, MySQL 8.4.7, tables collation utf8mb4_unicode_ci, multilingual site (30 content languages).

Additional Comments

Root cause (verified by reading the code and reproducing with a minimal script):

The indexer tokenises the item path/alias in addition to title and body: Result::$defaultInstructions includes Indexer::PATH_CONTEXT => ['path', 'alias'] (Result.php:51), and dashes are converted to spaces (Indexer.php:410-413), so the transliterated alias contributes the ASCII spelling koriscenja while the title/body contribute korišćenja.
#__finder_terms has UNIQUE KEY idx_term_language (term, language) and utf8mb4_unicode_ci is accent-insensitive, so the two spellings count as the SAME key value.
The INSERT that moves new terms from #__finder_tokens_aggregate into #__finder_terms (Indexer.php:522-536) groups by term, stem, ..., SOUNDEX(term). The two spellings differ in stem and SOUNDEX, so they survive as two rows within the same INSERT, and the second row violates the unique key. Whether a given word crashes depends on SOUNDEX/stem/weight quirks, which is why the bug looks random to users.
Because the plain INSERT throws, the save is aborted mid-way (article saved, index not updated).
Historic context: the same class of problem was reported for German ß/ss during the 3.5 utf8mb4 conversion (#9249), addressed by switching com_finder tables to general_ci (#9387), then reverted to unicode_ci in 4.0 (#28425). general_ci does not help for most diacritics (š, ć, ž, í, ş are still equal to their base letters), so the runtime collision is still present in 4.x/5.x/6.x.

Minimal repro (CLI, any 6.1.1 install): build a Result with title = 'Pravila i uslovi korišćenja usluga i servisa', alias = 'pravila-i-uslovi-koriscenja-usluga-i-servisa', language = 'sr-YU', then call (new Indexer())->index($item). It throws Duplicate entry 'koriscenja-sr' deterministically.

Suggested fixes (any of these resolves it): make the final terms INSERT tolerant (INSERT IGNORE / ON CONFLICT DO NOTHING); or group the aggregate strictly by (term, language) before inserting; or use an accent-sensitive collation (e.g. utf8mb4_0900_as_ci) for the term columns; or fold diacritics per language at tokenisation time so index and query meet on the same ASCII form.

Workaround we use in production (option 4, no core change): a small system plugin registers per-language Language subclasses via an spl autoloader appended AFTER the core autoloader (a future core class for the same language wins automatically). Each subclass folds Latin diacritics to ASCII before parent::tokenise(), which makes indexing collision-free and search accent-tolerant on both the index and the query side. Happy to submit this as a PR if there is interest; otherwise approach 1 is a one-line fix.

abstract class AbstractFoldingLanguage extends Language
{
    protected const EXTRA_MAP = [];
    protected const FOLD_MAP = ['š' => 's', 'ć' => 'c', 'ž' => 'z', 'đ' => 'd', 'č' => 'c', /* ... full Latin map ... */];

    public function __construct($locale = null)
    {
        parent::__construct($this->language);
    }

    public function tokenise($input)
    {
        return parent::tokenise($this->fold((string) $input));
    }

    protected function fold(string $input): string
    {
        $input = strtr($input, static::EXTRA_MAP + static::FOLD_MAP);

        return (string) preg_replace('/[\x{0300}-\x{036F}]/u', '', $input);
    }
}

spl_autoload_register(static function (string $class): void {
    $prefix = 'Joomla\\Component\\Finder\\Administrator\\Indexer\\Language\\';

    if (strncmp($class, $prefix, \strlen($prefix)) !== 0) {
        return;
    }

    $code = strtolower(substr($class, \strlen($prefix)));

    if (!preg_match('/^[a-z]{2,3}$/', $code)) {
        return;
    }

    $file = __DIR__ . '/map/' . $code . '.php';

    if (is_file($file)) {
        require_once $file;
    }
});