InnoDB FULLTEXT: MATCH does not return a live row that LIKE finds in the same column

I had the report generated by an AI, after we isolated the issue. Please don't simply discard it for that matter. This is a real problem we're running into in our daily business.

Thanks for looking into

Dierk Droth
CEO Unusual Software GmbH

1. InnoDB FULLTEXT: live rows silently unfindable by MATCH, four occurrences on 12.3

*Draft for jira.mariadb.org, project MDEV, component "Full-text Search". Replace `<VERSION>` with the output of
`SELECT VERSION();` before filing — our own note recording 12.3.2 is from 2026-08-29 and the box tracks the 12.3
series repository, so it may have moved since.*

—

1. 1. Summary

On MariaDB `<VERSION>`, a row that is present in the table with its text in the indexed column is not returned by
`MATCH … AGAINST` against that column. `LIKE` on the same column of the same row returns it. No error is raised
anywhere — the application simply shows an empty search result.

Four occurrences since 2026-08-29, across two servers and five tenant schemas, on four different tables. Each was
repaired with `ALTER TABLE … FORCE` and two of them came back later.

1. 1. Environment

• MariaDB `<VERSION>`, Ubuntu, official MariaDB repository, one instance per environment
• InnoDB, one schema per tenant, ~30 schemas per instance, identical table definitions in each
• The affected tables carry one `FULLTEXT` index over a single `LONGTEXT` column holding a generated "search
  document"; some carry a second `FULLTEXT` index over a separate body column
• `innodb_ft_enable_stopword` is set `OFF` on the session that creates each index
• No `SYSTEM VERSIONING`, no `LOAD DATA INFILE`, no partitioning, no custom stopword table, ascending `BIGINT`
  primary keys

1. 1. How it presents

A probe against one row, by its own primary key, using a word taken out of that row's own stored text:

```sql
SELECT COUNT FROM t WHERE Id = :id AND `SearchDoc` LIKE '%Vexbourne%'; – 1
SELECT COUNT FROM t WHERE Id = :id
AND MATCH(`SearchDoc`) AGAINST ('Vexbourne*' IN BOOLEAN MODE); – 0
```

The same probe against a different table in the same schema, same word, same statement shape, returns 1. The
boundary is exactly `innodb_ft_min_token_size`: a two-character prefix (which the application matches with an
anchored `LIKE` instead) finds the row, three characters and up finds nothing.

1. 1. Two distinct shapes

*A — the row's words are in the index, the row is masked.* 2026-08-29 and 2026-09-20.

On a 503-row table, four rows contained the token. `INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE` held it at
`DOC_ID` 673, 674, 676 and 677. `INNODB_FT_DELETED` held 196 entries, `DOC_ID` 422–696 — and 673 and 676 were
among them, while 674 and 677 were not. So two of the four live rows carrying that word were masked and two were
reachable. `INNODB_FT_CONFIG.synced_doc_id` was 678.

*B — the row's words never reached the index at all.* 2026-09-05 and 2026-09-19.

On a table holding *one* live row, `_UnusualSearchDoc` 83 characters carrying the token twice:

• `MATCH` → 0, `LIKE` → 1
• `INNODB_FT_DELETED`: 1848 entries, `DOC_ID` *1 to 1848 with no gaps*
• `INNODB_FT_CONFIG`: `synced_doc_id` 877 (i.e. below the highest deleted id)
• the row's token in *neither* `INNODB_FT_INDEX_TABLE` *nor* `INNODB_FT_INDEX_CACHE`, before or after an
  `OPTIMIZE TABLE`

1. 1. What we ruled out

We wrote a standalone reproducer that does to a scratch table exactly what the application does to a real one,
with no application code in the loop: insert 200 rows, `UPDATE` the indexed column of each three times, then
`DELETE … WHERE Id >= 0` (never `TRUNCATE`), repeat; after each cycle insert one row with a unique token and
probe for it by primary key.

*600 cycles did not reproduce it.* The end state was `INNODB_FT_DELETED` = 480,600 entries, `DOC_ID` 1–480,600
contiguous, against one live row — that is, the shape of occurrence B, at 260× the scale — and every probe was
found. A clean `systemctl restart mariadb` at that point left `synced_doc_id` at 480,601, correctly past the
maximum deleted id, and the probe still succeeded.

So neither churn on its own, nor the size or contiguity of the deleted-docs list, nor the doc-id counter being
re-derived from an emptied table at a clean startup, is sufficient. The inference we had been working from — that
a newly written row is handed a doc id already on the deleted list — does not survive this.

*What the reproducer does not do*, and what we therefore still suspect, in order:

1. *An unclean stop.* Our test restarted the server gracefully. On 2026-09-16 the kernel OOM-killed `mariadbd`
on the very box where occurrence B was found three days later.
2. *Concurrency.* The test is single-session and serial; the real tables are written concurrently by several
connections.
3. *Two `FULLTEXT` indexes on one table.* Occurrence A's table has two; the scratch table has one.

1. 1. What we could not determine

*The unfindable row's own `FTS_DOC_ID`.* It is hidden because the tables do not declare the column, and the
indirect route (`INNODB_FT_INDEX_TABLE`) cannot show a row whose words are not on disk — which is precisely
shape B. This is the one reading that would distinguish "masked as deleted" from "never indexed", and we would
welcome guidance on how to obtain it on a live table without recreating it (which destroys the state).

1. 1. Workarounds

1. 1. Related

• *MDEV-19073* — fixed 2019-10-25 in 10.1.42 / 10.2.28 / 10.3.19 / 10.4.9. After crash recovery, an FTS query
  returned fewer rows than the table held, traced to the redo log not restoring FTS state for a committed
  transaction. That reported symptom is ours. We are six years and several major versions past that fix, and see the same outcome — and the
  strongest remaining lead above is an unclean stop. If that fix has a gap on a later code path, this may be it.
• *MySQL Bug #86460* — "Deleted DOCID are not maintained during OPTIMIZE of InnoDB FULLTEXT tables", Verified
  2017-05-31, still open. Matches our `OPTIMIZE` observation exactly, including the `ALTER TABLE … ENGINE=InnoDB`
  workaround, but describes only storage and query cost, not a live row becoming unfindable.
• *MDEV-34673* — "innodb_optimize_fulltext_only=ON may corrupt fulltext indexes", open. We set that variable
  once while diagnosing, before occurrence B, and cannot exclude that it made a bad table worse.

1. 1. Impact

Silent. There is no error, no warning and nothing in the log; a module's search simply returns nothing and the
data is intact underneath. We found the first three occurrences by accident and now run a nightly per-row probe
across every tenant, because nothing in the server surfaces it.