Bug #113597 Data Dictionary issues when dropping multiple FTS indexes
Submitted: 9 Jan 2024 20:14 Modified: 16 Jan 2024 6:40
Reporter: Jennifer Smith Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:8.0.35 OS:Red Hat (4.18.0-477.27.1.el8_8.x86_64)
Assigned to: CPU Architecture:x86

[9 Jan 2024 20:14] Jennifer Smith
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.
[16 Jan 2024 6:40] MySQL Verification Team
Hello Jennifer,

Thank you for the report and test case.
Verified as described.

regards,
Umesh