MHNSW: rows become unreachable through a cosine vector index after ON DELETE CASCADE

After a FOREIGN KEY ... ON DELETE CASCADE deletion, a MHNSW vector index with
DISTANCE='cosine' starts returning fewer rows than actually exist, and does not
recover. The rows are present and readable – only the indexed path misses them. No
error is raised.

Environment

• 12.3.3-MariaDB-ubu2404 (mariadb.org binary distribution), InnoDB
• @@mhnsw_default_m = 6, @@mhnsw_ef_search = 20, @@mhnsw_max_cache_size = 16777216

Reproduction

Attached: mdev-mhnsw-cosine-cascade.sql. Self-contained – VECTOR(4) and the
built-in seq_1_to_N engine, no client code.

The shape: 300 rows under parent 1 -> DELETE FROM parent WHERE id = 1 (cascade)
-> insert 25 fresh rows under parent 2 -> ask for the 10 nearest.

live_rows            25

through the index     7

IGNORE INDEX         10

What narrows it down

Same data, same query, one variable changed at a time:

One deleted parent row is enough – this is not about bulk deletion.

Reliability

Intermittent but frequent, and the asymmetry is stable:

• cosine, M='16', CASCADE: 8 of 8 runs reproduced (7/10 five times, 0/10 three times)
• cosine, default M, CASCADE: 7 of 8 runs reproduced; one run returned 10/10
• Control, direct DELETE instead of cascade: 8 of 8 runs clean (10/10)

Workaround, and what does not work

Rebuilding the index restores correct results, with no data loss:

ALTER TABLE child DROP INDEX idx_v;

ALTER TABLE child ADD VECTOR INDEX idx_v (v) `DISTANCE`='cosine' `M`='16';

OPTIMIZE TABLE does not fix it. Inserting more rows does not fix it either
(measured on a 1024-dim table: 0 of 61 rows still found through the index after
adding 60 new ones).

When rebuilding, the original index options must be repeated. A bare
ADD VECTOR INDEX idx_v (v) silently recreates it with the default distance
function and M, which changes query results without any error.

Why this matters beyond search quality

In our use, this index backs a nearest-neighbour lookup used for deduplication. A
dedup that returns nothing reads it as "this is new", so a silently degraded index does
not cause missing results – it causes duplicate writes. The failure is silent, and
the first remedy anyone reaches for (OPTIMIZE TABLE) reports success without fixing
anything.