Bug #119974 inplace algorithm dropping multi full text indexes only drops FTS AUX tables of the last index, leading orphaned tables

Description:
When dropping more than one index with inplace algorithm DDL, it only removes the FTS AUX tablespace for the last index.

```
    for (ulint i = 0; i < ctx->num_to_drop_index; i++) {
      dict_index_t *index = ctx->drop_index[i];
      assert(index->is_committed());
      assert(index->table == ctx->new_table);

      if (index->type & DICT_FTS) {
        assert(index->type == DICT_FTS || index->is_corrupted());
        assert(index->table->fts);
        ctx->fts_drop_aux_vec = new aux_name_vec_t;
        fts_drop_index(index->table, index, trx, ctx->fts_drop_aux_vec,
                       adding_fts_index);
      }
```
According to "ctx->fts_drop_aux_vec = new aux_name_vec_t;", only the last index will be kept.

After dropping the table, the FTS AUX tablespace still exists.

mysql> CREATE TABLE t (
    ->  a VARCHAR(8),
    ->  b VARCHAR(8),
    ->  FULLTEXT KEY idx1(a),
    ->  FULLTEXT KEY idx2(b)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> ALTER TABLE t DROP KEY idx1, DROP KEY idx2, algorithm=inplace;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DROP TABLE t;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT name FROM information_schema.INNODB_TABLESPACES WHERE name LIKE 'test%fts%index%';
+----------------------------------------------------+
| name                                               |
+----------------------------------------------------+
| test/fts_0000000000000428_00000000000000a5_index_1 |
| test/fts_0000000000000428_00000000000000a5_index_2 |
| test/fts_0000000000000428_00000000000000a5_index_3 |
| test/fts_0000000000000428_00000000000000a5_index_4 |
| test/fts_0000000000000428_00000000000000a5_index_5 |
| test/fts_0000000000000428_00000000000000a5_index_6 |
+----------------------------------------------------+
6 rows in set (0.00 sec)

How to repeat:
CREATE TABLE t (
 a VARCHAR(8),
 b VARCHAR(8),
 FULLTEXT KEY idx1(a),
 FULLTEXT KEY idx2(b)
);
ALTER TABLE t DROP KEY idx1, DROP KEY idx2, algorithm=inplace;
DROP TABLE t;
SELECT name FROM information_schema.INNODB_TABLESPACES WHERE name LIKE 'test%fts%index%';

Suggested fix:
diff --git a/storage/innobase/handler/handler0alter.cc b/storage/innobase/handler/handler0alter.cc
index 6b1b4c1c054..00bf7289a78 100644
--- a/storage/innobase/handler/handler0alter.cc
+++ b/storage/innobase/handler/handler0alter.cc
@@ -7248,7 +7248,8 @@ after a successful commit_try_norebuild() call.
       if (index->type & DICT_FTS) {
         assert(index->type == DICT_FTS || index->is_corrupted());
         assert(index->table->fts);
-        ctx->fts_drop_aux_vec = new aux_name_vec_t;
+        if (ctx->fts_drop_aux_vec == nullptr)
+          ctx->fts_drop_aux_vec = new aux_name_vec_t;
         fts_drop_index(index->table, index, trx, ctx->fts_drop_aux_vec,
                        adding_fts_index);
       }

The patch will fix it

Attachment: 0001-fix-innobase_drop_multi_fts_index_table.patch (application/octet-stream, text), 2.51 KiB.

You can try manually hooking the fts_drop_dd_tables to remove the orphaned tablespace with gdb.

- orphaned tablespace 
mysql> SELECT name FROM information_schema.INNODB_TABLESPACES WHERE name LIKE 'test%fts%index%';
+----------------------------------------------------+
| name                                               |
+----------------------------------------------------+
| test/fts_000000000000044c_00000000000000cf_index_1 |
| test/fts_000000000000044c_00000000000000cf_index_2 |
| test/fts_000000000000044c_00000000000000cf_index_3 |
| test/fts_000000000000044c_00000000000000cf_index_4 |
| test/fts_000000000000044c_00000000000000cf_index_5 |
| test/fts_000000000000044c_00000000000000cf_index_6 |
+----------------------------------------------------+
6 rows in set (0.00 sec)

- create table for dropping
use test

CREATE TABLE t (
 a VARCHAR(8),
 b VARCHAR(8),
 FULLTEXT KEY idx1(a)
);

- gdb attach to mysql and add breakpoint in session 2
shell> gdb -p $(pidof mysqld)

(gdb) b fts_drop_dd_tables
Breakpoint 1 at 0x228ce50: file /usr/src/debug/percona-server-8.4.6-6.1.el8.aarch64/percona-server-8.4.6-6/storage/innobase/fts/fts0fts.cc, line 1253.
(gdb) c
Continuing.

- dropping index to enter the fts_drop_dd_tables in session 1
ALTER TABLE t DROP KEY idx1, algorithm=inplace;

- removing orphaned tablespace in gdb
call dd_drop_fts_table("test/fts_000000000000044c_00000000000000cf_index_1", file_per_table)
call dd_drop_fts_table("test/fts_000000000000044c_00000000000000cf_index_2", file_per_table)
call dd_drop_fts_table("test/fts_000000000000044c_00000000000000cf_index_3", file_per_table)
call dd_drop_fts_table("test/fts_000000000000044c_00000000000000cf_index_4", file_per_table)
call dd_drop_fts_table("test/fts_000000000000044c_00000000000000cf_index_5", file_per_table)
call dd_drop_fts_table("test/fts_000000000000044c_00000000000000cf_index_6", file_per_table)

For example, the result will be

Thread 37 "connection" hit Breakpoint 1, fts_drop_dd_tables (aux_vec=aux_vec@entry=0xffff3014c9b0, file_per_table=true)
    at /usr/src/debug/percona-server-8.4.6-6.1.el8.aarch64/percona-server-8.4.6-6/storage/innobase/fts/fts0fts.cc:1253
1253      if (aux_vec == nullptr || aux_vec->aux_name.size() == 0) {
(gdb) call dd_drop_fts_table("test/fts_000000000000044c_00000000000000cf_index_1", file_per_table)
$33 = true
(gdb) call dd_drop_fts_table("test/fts_000000000000044c_00000000000000cf_index_2", file_per_table)
$34 = true
(gdb) call dd_drop_fts_table("test/fts_000000000000044c_00000000000000cf_index_3", file_per_table)
$35 = true
(gdb) call dd_drop_fts_table("test/fts_000000000000044c_00000000000000cf_index_4", file_per_table)
$36 = true
(gdb) call dd_drop_fts_table("test/fts_000000000000044c_00000000000000cf_index_5", file_per_table)
$37 = true
(gdb) call dd_drop_fts_table("test/fts_000000000000044c_00000000000000cf_index_6", file_per_table)
$38 = true
(gdb) quit
A debugging session is active.

    Inferior 1 [process 4647] will be detached.

Quit anyway? (y or n) y
Detaching from program: /usr/sbin/mysqld, process 4647
[Inferior 1 (process 4647) detached]

- checking the orphaned tablespace
mysql> SELECT name FROM information_schema.INNODB_TABLESPACES WHERE name LIKE 'test%fts%index%';
Empty set (0.00 sec)

bug#119661 - Duplicate