Bug #100570 Auxiliary tables for all fts index for a table without renaming

Description:
Hi, all

Drop the full-text index of the table firstly, and then rename the table.
We will find that auxiliary tables for all fts index for a table without renaming. These auxiliary tables remain in the original directory.

==============================================================================
./mtr case1 --record

[ 50%] main.case1                                [ fail ]
--- ../mysql-8.0.21/cmake_build/mysql-test/var/tmp/check-mysqld_1.result      2020-08-19 09:59:59.558000000 +0300
+++ ../mysql-8.0.21/cmake_build/mysql-test/var/tmp/check-mysqld_1.reject      2020-08-19 10:00:01.353000000 +0300
@@ -932,6 +932,11 @@
 ./mtr/test_suppressions.ibd    TABLESPACE      mtr/test_suppressions   InnoDB
 ./mysql.ibd    TABLESPACE      mysql   InnoDB
 ./sys/sys_config.ibd   TABLESPACE      sys/sys_config  InnoDB
+./test/fts_0000000000000427_being_deleted.ibd  TABLESPACE      test/fts_0000000000000427_being_deleted InnoDB
+./test/fts_0000000000000427_being_deleted_cache.ibd    TABLESPACE      test/fts_0000000000000427_being_deleted_cache   InnoDB
+./test/fts_0000000000000427_config.ibd TABLESPACE      test/fts_0000000000000427_config        InnoDB
+./test/fts_0000000000000427_deleted.ibd        TABLESPACE      test/fts_0000000000000427_deleted       InnoDB
+./test/fts_0000000000000427_deleted_cache.ibd  TABLESPACE      test/fts_0000000000000427_deleted_cache InnoDB
 ./undo_001     UNDO LOG        innodb_undo_001 InnoDB
 ./undo_002     UNDO LOG        innodb_undo_002 InnoDB
 tables_in_test

How to repeat:
MTR case

mysql-test/t/case1.test
#############################################################################
```
let $MYSQLD_DATADIR= `SELECT @@global.datadir`;
CREATE DATABASE test1;
CREATE TABLE t1(a int, t1_new_col varchar(100));
ALTER TABLE t1 ADD fulltext index full_t2_idx(t1_new_col);
ALTER TABLE t1 DROP index full_t2_idx;
--echo "List files under test"
--list_files $MYSQLD_DATADIR/test
RENAME TABLE t1 TO test1.t2;
--echo "List files under test"
--list_files $MYSQLD_DATADIR/test
--echo "List files under test1"
--list_files $MYSQLD_DATADIR/test1
DROP TABLE test1.t2;
DROP DATABASE test1;
```

MySQL8.0.21(mysql-test/r/case1.result)
#############################################################################
```
CREATE DATABASE test1;
CREATE TABLE t1(a int, t1_new_col varchar(100));
ALTER TABLE t1 ADD fulltext index full_t2_idx(t1_new_col);
Warnings:
Warning 124     InnoDB rebuilding table to add column FTS_DOC_ID
ALTER TABLE t1 DROP index full_t2_idx;
"List files under test"
fts_0000000000000427_being_deleted.ibd
fts_0000000000000427_being_deleted_cache.ibd
fts_0000000000000427_config.ibd
fts_0000000000000427_deleted.ibd
fts_0000000000000427_deleted_cache.ibd
t1.ibd
RENAME TABLE t1 TO test1.t2;
"List files under test"
fts_0000000000000427_being_deleted.ibd
fts_0000000000000427_being_deleted_cache.ibd
fts_0000000000000427_config.ibd
fts_0000000000000427_deleted.ibd
fts_0000000000000427_deleted_cache.ibd
"List files under test1"
t2.ibd
DROP TABLE test1.t2;
DROP DATABASE test1;
```

MySQL5.7.30 (mysql-test/r/case1.result)
#############################################################################
```
CREATE DATABASE test1;
CREATE TABLE t1(a int, t1_new_col varchar(100));
ALTER TABLE t1 ADD fulltext index full_t2_idx(t1_new_col);
Warnings:
Warning 124     InnoDB rebuilding table to add column FTS_DOC_ID
ALTER TABLE t1 DROP index full_t2_idx;
"List files under test"
FTS_0000000000000025_BEING_DELETED.ibd
FTS_0000000000000025_BEING_DELETED_CACHE.ibd
FTS_0000000000000025_CONFIG.ibd
FTS_0000000000000025_DELETED.ibd
FTS_0000000000000025_DELETED_CACHE.ibd
t1.frm
t1.ibd
RENAME TABLE t1 TO test1.t2;
"List files under test"
"List files under test1"
FTS_0000000000000025_BEING_DELETED.ibd
FTS_0000000000000025_BEING_DELETED_CACHE.ibd
FTS_0000000000000025_CONFIG.ibd
FTS_0000000000000025_DELETED.ibd
FTS_0000000000000025_DELETED_CACHE.ibd
db.opt
t2.frm
t2.ibd
DROP TABLE test1.t2;
DROP DATABASE test1;
```

We find that auxiliary tables remain in the original directory in mysql-8.0.21.

Hello zhijun long,

Thank you for the report and test case.

regards,
Umesh

there is another FTS issue related to AUX Index tables in 8.0 series only (the one you reported is AUX config tables). See https://bugs.mysql.com/bug.php?id=98427

[9 Oct 2020 11:21] Daniel T Price

Fixed as of the 8.0.23 release, and here's the proposed changelog entry from the documentation team:
 
After dropping a FULLTEXT index and renaming the table to move it to a
new schema, the FULLTEXT auxiliary tables were not renamed accordingly and
remained in the old schema directory.