This error appears in your error log. It means there is a duplicate key in your full text index, sadly it doesn’t tell you which table or which full text index.

HINT for MySQL/MariaDB/Percona devs: just add the index_id to the error message.

SQL to find all text indexes in your db:

select table_schema,table_name,index_name, index_type from information_schema.statistics
where index_type = 'FULLTEXT' order by table_schema;

+-----------------+-----------------------+-------------+------------+
| table_schema    | table_name            | index_name  | index_type |
+-----------------+-----------------------+-------------+------------+
| db0001_fullname | Activity_fullname_log | message     | FULLTEXT   |
| db0002_fullname | Activity_fullname_log | message     | FULLTEXT   |
+-----------------+-----------------------+-------------+------------+
2 rows in set (0.715 sec)

So what is the next step?

If you follow the documentation it will tell you to rebuild the table or use OPTIMIZE TABLE with innodb_optimize_fulltext_only=ON to rebuild just the text index.

https://dev.mysql.com/doc/refman/8.0/en/fulltext-fine-tuning.html#fulltext-rebuild-innodb-indexes

https://dev.mysql.com/doc/refman/8.0/en/fulltext-fine-tuning.html#fulltext-optimize

My recommendation: Rebuild the table (including the full text indexes)

-- Plain old ALTER TABLE
use db0001_fullname;
ALTER ONLINE TABLE Activity_fullname_log ENGINE=INNODB;

-- Or pt-osc equivalent

pt-online-schema-change --dry-run --alter="ENGINE=INNODB" \
--alter-foreign-keys-method=rebuild_constraints --max-lag=60 \
--max-load Threads_running=50 --critical-load Threads_running=60 \
h=localhost,D=db0001_fullname,t=Activity_fullname_log

pt-online-schema-change --execute --alter="ENGINE=INNODB" \
--alter-foreign-keys-method=rebuild_constraints --max-lag=60 \
--max-load Threads_running=50 --critical-load Threads_running=60 \
h=localhost,D=db0001_fullname,t=Activity_fullname_log

The OPTIMIZE TABLE method:

The trouble with this method is it will only do a set number of words per execution of OPTIMIZE TABLE.

SQL to set the specific table so you can query INNODB_FT_INDEX_TABLE

set global innodb_ft_aux_table = 'db0001_fullname/Activity_fullname_log';
Query OK, 0 rows affected (0.002 sec)

SQL to find the number of distinct words in a specific text index:

SELECT count(distinct word) FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
+----------------------+
| count(distinct word) |
+----------------------+
|               600000 |
+----------------------+
1 row in set (1 min 53.333 sec)

Depending how much impact you want per OPTIMIZE TABLE set innodb_ft_num_word_optimize appropriately.

The remaining steps to rebuilding your full text index using OPTIMIZE TABLE

set GLOBAL innodb_ft_num_word_optimize=10000;
set GLOBAL innodb_optimize_fulltext_only=ON;
OPTIMIZE TABLE Activity_fullname_log; 
OPTIMIZE TABLE Activity_fullname_log;
OPTIMIZE TABLE Activity_fullname_log;
OPTIMIZE TABLE Activity_fullname_log;
OPTIMIZE TABLE Activity_fullname_log;
... -- repeat round(distinct words/innodb_ft_num_word_optimize)+1
OPTIMIZE TABLE Activity_fullname_log;

Until next time…

Your favourite monitoring software throws an alert or sends an email after it finds the following in the mysql error log:

InnoDB: page [page id: space=8858, page number=206777] (95 records, index id 29361)

How do you find which index is corrupted from this line?

Example using the above index id listed in the error log message:

 select * from information_schema.INNODB_SYS_INDEXES where index_id = 29361;

+----------+--------------------------------+----------+------+----------+---------+-------+-----------------+
| INDEX_ID | NAME                           | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE | MERGE_THRESHOLD |
+----------+--------------------------------+----------+------+----------+---------+-------+-----------------+
|    29361 | blah_longwinded_name_for_index |    11238 |    0 |        3 |       4 |  8858 |              50 |
+----------+--------------------------------+----------+------+----------+---------+-------+-----------------+
1 row in set (0.009 sec)

How do you find which table is corrupted?

Example using the above table_id listed in the output from the above SQL statement.

select * from information_schema.INNODB_SYS_TABLES where table_id = 11238;

+----------+-----------------------------------------------------+------+--------+-------+------------+---------------+------------+
| TABLE_ID | NAME                                                | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE |
+----------+-----------------------------------------------------+------+--------+-------+------------+---------------+------------+
|    11238 | database0001_production/blah_longwinded_name_table  |   41 |      8 |  8858 | Compressed |          8192 | Single     |
+----------+-----------------------------------------------------+------+--------+-------+------------+---------------+------------+

What about just as one statement…

select t.name as table_name,i.name as index_name,t.table_id,i.index_id 
from information_schema.INNODB_SYS_TABLES t
JOIN information_schema.INNODB_SYS_INDEXES i on t.table_id=i.table_id
where index_id = 29361;

+-----------------------------------------------------+--------------------------------+----------+----------+
| table_name                                          | index_name                     | table_id | index_id |
+-----------------------------------------------------+--------------------------------+----------+----------+
| database0001_production/blah_longwinded_name_table  | blah_longwinded_name_for_index | 11238    | 29361    |
+-----------------------------------------------------+--------------------------------+----------+----------+

How do I fix this corrupted index?

Use a null alter to rebuild the table.

WARNING:

1. Check the size first.
2. use a tool like pt-online-schema-change if the table is large or accessed heavily, or you are running on Galera, Percona Cluster or equivalent.
3. Did you get permission to run this on production?

eg:

ALTER TABLE <tablename> ENGINE=INNODB ;