Description:
If a transaction inserts a row (or updates its FULLTEXT-indexed column) and
then, in the same transaction, changes the row's primary key to a smaller
value, the document is not added to the FULLTEXT index at commit. The row is
stored and found by other access paths, but MATCH ... AGAINST never returns
it. CHECK TABLE ... EXTENDED reports OK, and the row is still missing after
OPTIMIZE TABLE (innodb_optimize_fulltext_only=ON) and a server restart.
Moving the row to a larger primary key in the same transaction, or doing the
insert and the key change in separate transactions, works.
Reproduced on trunk 3b99be40f8a (built from source, RelWithDebInfo) and on
26.7.0, default settings.
How to repeat:
CREATE DATABASE fts; USE fts;
CREATE TABLE t (id INT PRIMARY KEY, body TEXT, FULLTEXT INDEX fx (body)) ENGINE=InnoDB;
-- A: insert, then a smaller key, one transaction
BEGIN; INSERT INTO t VALUES (500, 'alpha'); UPDATE t SET id = 100 WHERE id = 500; COMMIT;
-- B (control): insert, then a larger key, one transaction
BEGIN; INSERT INTO t VALUES (600, 'bravo'); UPDATE t SET id = 900 WHERE id = 600; COMMIT;
-- C: update of the indexed column, then a smaller key, one transaction
INSERT INTO t VALUES (700, 'charlie');
BEGIN; UPDATE t SET body = 'delta' WHERE id = 700; UPDATE t SET id = 300 WHERE id = 700; COMMIT;
-- D (control): insert and key change in separate transactions
INSERT INTO t VALUES (800, 'echo'); UPDATE t SET id = 200 WHERE id = 800;
SELECT * FROM t ORDER BY id;
SELECT id FROM t WHERE MATCH (body) AGAINST ('alpha' IN BOOLEAN MODE);
SELECT id FROM t WHERE MATCH (body) AGAINST ('bravo' IN BOOLEAN MODE);
SELECT id FROM t WHERE MATCH (body) AGAINST ('delta' IN BOOLEAN MODE);
SELECT id FROM t WHERE MATCH (body) AGAINST ('echo' IN BOOLEAN MODE);
CHECK TABLE t EXTENDED;
SET GLOBAL innodb_optimize_fulltext_only = ON; OPTIMIZE TABLE t;
SET GLOBAL innodb_optimize_fulltext_only = OFF;
-- restart the server, then run the four MATCH queries again
Observed on trunk (26.10.0):
SELECT * FROM t: (100,'alpha'), (200,'echo'), (300,'delta'), (900,'bravo')
word rows found by LIKE rows found by MATCH
alpha 100 (none)
bravo 900 900
delta 300 (none)
echo 200 200
CHECK TABLE t EXTENDED: fts.t check status OK
After OPTIMIZE TABLE and a restart: the same, alpha and delta are not found.
Expected: MATCH finds 100 for 'alpha' and 300 for 'delta'.
Suggested fix:
At commit, fts_add_doc_by_id() looks the document up in FTS_DOC_ID_INDEX with
a one-field key (the doc id) and PAGE_CUR_LE, which positions on the last
index entry with that doc id, and skips the document if that entry is
delete-marked:
https://github.com/mysql/mysql-server/blob/3b99be40f8a31d9396cad10accfd3ba51647d613/storag...
After the primary-key change, FTS_DOC_ID_INDEX holds two entries with the same
doc id: the delete-marked one with the old primary key and the live one with
the new key. When the new key is smaller, the delete-marked entry sorts last,
the lookup lands on it, and the document is never tokenized. When the new key
is larger, the live entry sorts last and the document is indexed (case B).
(This explanation is from reading the code; it was not traced in a debugger.)
Suggested fix: when the entry found is delete-marked, keep scanning backwards
over the entries with the same doc id for a live one (or search the clustered
index row by doc id) before concluding that the document was deleted.
© 2026, Oracle Corporation and/or its affiliates
Description: If a transaction inserts a row (or updates its FULLTEXT-indexed column) and then, in the same transaction, changes the row's primary key to a smaller value, the document is not added to the FULLTEXT index at commit. The row is stored and found by other access paths, but MATCH ... AGAINST never returns it. CHECK TABLE ... EXTENDED reports OK, and the row is still missing after OPTIMIZE TABLE (innodb_optimize_fulltext_only=ON) and a server restart. Moving the row to a larger primary key in the same transaction, or doing the insert and the key change in separate transactions, works. Reproduced on trunk 3b99be40f8a (built from source, RelWithDebInfo) and on 26.7.0, default settings. How to repeat: CREATE DATABASE fts; USE fts; CREATE TABLE t (id INT PRIMARY KEY, body TEXT, FULLTEXT INDEX fx (body)) ENGINE=InnoDB; -- A: insert, then a smaller key, one transaction BEGIN; INSERT INTO t VALUES (500, 'alpha'); UPDATE t SET id = 100 WHERE id = 500; COMMIT; -- B (control): insert, then a larger key, one transaction BEGIN; INSERT INTO t VALUES (600, 'bravo'); UPDATE t SET id = 900 WHERE id = 600; COMMIT; -- C: update of the indexed column, then a smaller key, one transaction INSERT INTO t VALUES (700, 'charlie'); BEGIN; UPDATE t SET body = 'delta' WHERE id = 700; UPDATE t SET id = 300 WHERE id = 700; COMMIT; -- D (control): insert and key change in separate transactions INSERT INTO t VALUES (800, 'echo'); UPDATE t SET id = 200 WHERE id = 800; SELECT * FROM t ORDER BY id; SELECT id FROM t WHERE MATCH (body) AGAINST ('alpha' IN BOOLEAN MODE); SELECT id FROM t WHERE MATCH (body) AGAINST ('bravo' IN BOOLEAN MODE); SELECT id FROM t WHERE MATCH (body) AGAINST ('delta' IN BOOLEAN MODE); SELECT id FROM t WHERE MATCH (body) AGAINST ('echo' IN BOOLEAN MODE); CHECK TABLE t EXTENDED; SET GLOBAL innodb_optimize_fulltext_only = ON; OPTIMIZE TABLE t; SET GLOBAL innodb_optimize_fulltext_only = OFF; -- restart the server, then run the four MATCH queries again Observed on trunk (26.10.0): SELECT * FROM t: (100,'alpha'), (200,'echo'), (300,'delta'), (900,'bravo') word rows found by LIKE rows found by MATCH alpha 100 (none) bravo 900 900 delta 300 (none) echo 200 200 CHECK TABLE t EXTENDED: fts.t check status OK After OPTIMIZE TABLE and a restart: the same, alpha and delta are not found. Expected: MATCH finds 100 for 'alpha' and 300 for 'delta'. Suggested fix: At commit, fts_add_doc_by_id() looks the document up in FTS_DOC_ID_INDEX with a one-field key (the doc id) and PAGE_CUR_LE, which positions on the last index entry with that doc id, and skips the document if that entry is delete-marked: https://github.com/mysql/mysql-server/blob/3b99be40f8a31d9396cad10accfd3ba51647d613/storag... After the primary-key change, FTS_DOC_ID_INDEX holds two entries with the same doc id: the delete-marked one with the old primary key and the live one with the new key. When the new key is smaller, the delete-marked entry sorts last, the lookup lands on it, and the document is never tokenized. When the new key is larger, the live entry sorts last and the document is indexed (case B). (This explanation is from reading the code; it was not traced in a debugger.) Suggested fix: when the entry found is delete-marked, keep scanning backwards over the entries with the same doc id for a live one (or search the clustered index row by doc id) before concluding that the document was deleted.