InnoDB full-text index corruption

In a recent customer engagement we experienced a lot of corrupted (normal) indexes and also corrupted full-text indexes on InnoDB tables in a Galera Cluster (v10.4.13).

In the error log we did not see which table it was but we have only a few log entries every here and there indicating a full-table index is corrupt:

2020-07-08 22:09:03 0 [ERROR] InnoDB: (Duplicate key) writing word node to FTS auxiliary index table.
2020-07-08 22:09:06 0 [ERROR] InnoDB: (Duplicate key) writing word node to FTS auxiliary index table.
2020-07-08 22:09:09 0 [ERROR] InnoDB: (Duplicate key) writing word node to FTS auxiliary index table.

So we tried to figure out which database files were touch in the indicated time:

shell> ls -ltr */FTS_*.ibd | tail -n8
-rw-rw---- 1 mysql mysql    98304 Jul  8 22:09 test/FTS_00000000000005ee_00000000000011d3_INDEX_4.ibd
-rw-rw---- 1 mysql mysql    98304 Jul  8 22:09 test/FTS_00000000000005ee_00000000000011d3_INDEX_5.ibd
-rw-rw---- 1 mysql mysql    98304 Jul  8 22:09 test/FTS_00000000000005ee_00000000000011d3_INDEX_6.ibd
-rw-rw---- 1 mysql mysql   163840 Jul  8 22:09 test/FTS_00000000000005ee_00000000000011cc_INDEX_3.ibd
-rw-rw---- 1 mysql mysql   163840 Jul  8 22:09 test/FTS_00000000000005ee_00000000000011cc_INDEX_5.ibd
-rw-rw---- 1 mysql mysql   196608 Jul  8 22:09 test/FTS_00000000000005ee_00000000000011cc_INDEX_4.ibd
-rw-rw---- 1 mysql mysql    98304 Jul  8 22:10 test/FTS_00000000000005ee_CONFIG.ibd
-rw-rw---- 1 mysql mysql 19922944 Jul  8 22:11 test/FTS_00000000000005ee_00000000000011cc_INDEX_2.ibd

With these results we were capable to find which tables were involved:<7p>

SQL> SELECT ist.name AS fts_file
     , CONV(SUBSTR(ist.name, INSTR(ist.name, '/')+5,16), 16, 10) AS parent_table_id
     , CONV(SUBSTR(ist.name, INSTR(ist.name, '/')+5+16+1,16), 16, 10) AS fts_index_id
     , isi.name, isi.n_fields
     , ist2.name AS table_name
  FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES AS ist
  LEFT JOIN INFORMATION_SCHEMA.INNODB_SYS_INDEXES AS isi ON CONV(SUBSTR(ist.name, INSTR(ist.name, '/')+5+16+1,16), 16, 10) = isi.index_id
  LEFT JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS ist2 ON ist2.table_id = CONV(SUBSTR(ist.name, INSTR(ist.name, '/')+5,16), 16, 10)
 WHERE ist.name LIKE 'test/FTS_%INDEX%'
;
+----------------------------------------------------+-----------------+--------------+------+----------+------------+
| fts_file                                           | parent_table_id | fts_index_id | name | n_fields | table_name |
+----------------------------------------------------+-----------------+--------------+------+----------+------------+
| test/FTS_00000000000005ee_00000000000011cc_INDEX_1 | 1518            | 4556         | data |        1 | test/test  |
| test/FTS_00000000000005ee_00000000000011cc_INDEX_2 | 1518            | 4556         | data |        1 | test/test  |
| test/FTS_00000000000005ee_00000000000011cc_INDEX_3 | 1518            | 4556         | data |        1 | test/test  |
| test/FTS_00000000000005ee_00000000000011cc_INDEX_4 | 1518            | 4556         | data |        1 | test/test  |
| test/FTS_00000000000005ee_00000000000011cc_INDEX_5 | 1518            | 4556         | data |        1 | test/test  |
| test/FTS_00000000000005ee_00000000000011cc_INDEX_6 | 1518            | 4556         | data |        1 | test/test  |
| test/FTS_00000000000005ee_00000000000011d3_INDEX_1 | 1518            | 4563         | d    |        1 | test/test  |
| test/FTS_00000000000005ee_00000000000011d3_INDEX_2 | 1518            | 4563         | d    |        1 | test/test  |
| test/FTS_00000000000005ee_00000000000011d3_INDEX_3 | 1518            | 4563         | d    |        1 | test/test  |
| test/FTS_00000000000005ee_00000000000011d3_INDEX_4 | 1518            | 4563         | d    |        1 | test/test  |
| test/FTS_00000000000005ee_00000000000011d3_INDEX_5 | 1518            | 4563         | d    |        1 | test/test  |
| test/FTS_00000000000005ee_00000000000011d3_INDEX_6 | 1518            | 4563         | d    |        1 | test/test  |
+----------------------------------------------------+-----------------+--------------+------+----------+------------+

After repairing those full-text indexes the messages disappeared (for a while?)...

Example full-text index table

CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `data` varchar(128) DEFAULT NULL,
  `ts` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `d` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `data` (`data`),
  FULLTEXT KEY `d` (`d`)
) ENGINE=InnoDB AUTO_INCREMENT=3443073 DEFAULT CHARSET=latin1

See also bugs: MDEV-12676 and MDEV-15237, MDEV-18868