Description:
Dropping multiple "Full Text Indexes" in one SQL Command does remove the file at the operating system level, however the file information remains in the data dictionary.
I was able to repeatedly reproduce this on 8.0.35.
How to repeat:
*** (Step 1) Create the table with 2 Full Text Search indexes ***
jenn8035(8.0.35) [(none)]>use test
Database changed
jenn8035(8.0.35) [test]>CREATE TABLE testtab (col1 int unsigned NOT NULL, col2 varchar(255) not null,col3 mediumtext NOT NULL, PRIMARY KEY (col1), fulltext key col2_idx (col2), fulltext key col3_idx (col3));
Query OK, 0 rows affected (0.22 sec)
jenn8035(8.0.35) [test]>show create table testtab;
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| testtab | CREATE TABLE `testtab` (
`col1` int unsigned NOT NULL,
`col2` varchar(255) NOT NULL,
`col3` mediumtext NOT NULL,
PRIMARY KEY (`col1`),
FULLTEXT KEY `col2_idx` (`col2`),
FULLTEXT KEY `col3_idx` (`col3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
*** (Step 2) Check the Information_Schema for FTS files (this so far looks correct) ***
jenn8035(8.0.35) [test]>select file_name from information_schema.files where file_name like '%fts%';
+----------------------------------------------------------+
| FILE_NAME |
+----------------------------------------------------------+
| ./test/fts_0000000000000428_00000000000000a1_index_1.ibd |
| ./test/fts_0000000000000428_00000000000000a1_index_2.ibd |
| ./test/fts_0000000000000428_00000000000000a1_index_3.ibd |
| ./test/fts_0000000000000428_00000000000000a1_index_4.ibd |
| ./test/fts_0000000000000428_00000000000000a1_index_5.ibd |
| ./test/fts_0000000000000428_00000000000000a1_index_6.ibd |
| ./test/fts_0000000000000428_00000000000000a8_index_1.ibd |
| ./test/fts_0000000000000428_00000000000000a8_index_2.ibd |
| ./test/fts_0000000000000428_00000000000000a8_index_3.ibd |
| ./test/fts_0000000000000428_00000000000000a8_index_4.ibd |
| ./test/fts_0000000000000428_00000000000000a8_index_5.ibd |
| ./test/fts_0000000000000428_00000000000000a8_index_6.ibd |
| ./test/fts_0000000000000428_being_deleted.ibd |
| ./test/fts_0000000000000428_being_deleted_cache.ibd |
| ./test/fts_0000000000000428_config.ibd |
| ./test/fts_0000000000000428_deleted.ibd |
| ./test/fts_0000000000000428_deleted_cache.ibd |
+----------------------------------------------------------+
17 rows in set (0.01 sec)
*** (Step 3) Drop both FTS Indexes using two SQL commands (one for each index) ***
jenn8035(8.0.35) [test]>alter table testtab drop index col2_idx;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
jenn8035(8.0.35) [test]>alter table testtab drop index col3_idx;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
*** (Step 4) Check the Information_Schema for FTS files (this so far looks correct) ***
jenn8035(8.0.35) [test]>select file_name from information_schema.files where file_name like '%fts%';
+-----------------------------------------------------+
| FILE_NAME |
+-----------------------------------------------------+
| ./test/fts_0000000000000428_being_deleted.ibd |
| ./test/fts_0000000000000428_being_deleted_cache.ibd |
| ./test/fts_0000000000000428_config.ibd |
| ./test/fts_0000000000000428_deleted.ibd |
| ./test/fts_0000000000000428_deleted_cache.ibd |
+-----------------------------------------------------+
5 rows in set (0.00 sec)
*** (Step 5) Recreate the table again ***
jenn8035(8.0.35) [test]>DROP TABLE testtab;
Query OK, 0 rows affected (0.03 sec)
jenn8035(8.0.35) [test]>select file_name from information_schema.files where file_name like '%fts%';
Empty set (0.00 sec)
jenn8035(8.0.35) [test]>CREATE TABLE testtab (col1 int unsigned NOT NULL, col2 varchar(255) not null,col3 mediumtext NOT NULL, PRIMARY KEY (col1), fulltext key col2_idx (col2), fulltext key col3_idx (col3));
Query OK, 0 rows affected (0.18 sec)
*** (Step 6) Check the Information_Schema for FTS files (this so far looks correct) ***
jenn8035(8.0.35) [test]>select file_name from information_schema.files where file_name like '%fts%';
+----------------------------------------------------------+
| FILE_NAME |
+----------------------------------------------------------+
| ./test/fts_000000000000043a_00000000000000b6_index_1.ibd |
| ./test/fts_000000000000043a_00000000000000b6_index_2.ibd |
| ./test/fts_000000000000043a_00000000000000b6_index_3.ibd |
| ./test/fts_000000000000043a_00000000000000b6_index_4.ibd |
| ./test/fts_000000000000043a_00000000000000b6_index_5.ibd |
| ./test/fts_000000000000043a_00000000000000b6_index_6.ibd |
| ./test/fts_000000000000043a_00000000000000bd_index_1.ibd |
| ./test/fts_000000000000043a_00000000000000bd_index_2.ibd |
| ./test/fts_000000000000043a_00000000000000bd_index_3.ibd |
| ./test/fts_000000000000043a_00000000000000bd_index_4.ibd |
| ./test/fts_000000000000043a_00000000000000bd_index_5.ibd |
| ./test/fts_000000000000043a_00000000000000bd_index_6.ibd |
| ./test/fts_000000000000043a_being_deleted.ibd |
| ./test/fts_000000000000043a_being_deleted_cache.ibd |
| ./test/fts_000000000000043a_config.ibd |
| ./test/fts_000000000000043a_deleted.ibd |
| ./test/fts_000000000000043a_deleted_cache.ibd |
+----------------------------------------------------------+
17 rows in set (0.00 sec)
*** (Step 7) Drop both FTS Indexes using a single SQL ***
jenn8035(8.0.35) [test]>alter table testtab drop index col2_idx, drop index col3_idx;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
*** (Step 8) Check the Information_Schema for FTS files (hmm there are 6 rows in the data dictionary that should not be there) ***
jenn8035(8.0.35) [test]>select file_name from information_schema.files where file_name like '%fts%';
+----------------------------------------------------------+
| FILE_NAME |
+----------------------------------------------------------+
| ./test/fts_000000000000043a_00000000000000b6_index_1.ibd |
| ./test/fts_000000000000043a_00000000000000b6_index_2.ibd |
| ./test/fts_000000000000043a_00000000000000b6_index_3.ibd |
| ./test/fts_000000000000043a_00000000000000b6_index_4.ibd |
| ./test/fts_000000000000043a_00000000000000b6_index_5.ibd |
| ./test/fts_000000000000043a_00000000000000b6_index_6.ibd |
| ./test/fts_000000000000043a_being_deleted.ibd |
| ./test/fts_000000000000043a_being_deleted_cache.ibd |
| ./test/fts_000000000000043a_config.ibd |
| ./test/fts_000000000000043a_deleted.ibd |
| ./test/fts_000000000000043a_deleted_cache.ibd |
+----------------------------------------------------------+
11 rows in set (0.00 sec)
*** (Step 9) Show that the SQL sommand did remove the FTS files correctly. ***
jenn8035(8.0.35) [test]>system ls -l /mysql/data/jenn8035/test/*fts*
-rw-r----- 1 mysql dba 114688 Jan 9 14:44 /mysql/data/jenn8035/test/fts_000000000000043a_being_deleted_cache.ibd
-rw-r----- 1 mysql dba 114688 Jan 9 14:44 /mysql/data/jenn8035/test/fts_000000000000043a_being_deleted.ibd
-rw-r----- 1 mysql dba 114688 Jan 9 14:44 /mysql/data/jenn8035/test/fts_000000000000043a_config.ibd
-rw-r----- 1 mysql dba 114688 Jan 9 14:44 /mysql/data/jenn8035/test/fts_000000000000043a_deleted_cache.ibd
-rw-r----- 1 mysql dba 114688 Jan 9 14:44 /mysql/data/jenn8035/test/fts_000000000000043a_deleted.ibd
Suggested fix:
The Data Dictionary should be updated when Full Text Search Indexes are dropped using a single SQL command.