| Bug #119974 | inplace algorithm dropping multi full text indexes only drops FTS AUX tables of the last index, leading orphaned tables | ||
|---|---|---|---|
| Submitted: | 3 Mar 2:29 | Modified: | 3 Mar 3:55 |
| Reporter: | Jinyou Ma (OCA) | Email Updates: | |
| Status: | Open | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 8.0, 8.4, 9.6 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[3 Mar 2:31]
Jinyou Ma
The patch will fix it
Attachment: 0001-fix-innobase_drop_multi_fts_index_table.patch (application/octet-stream, text), 2.51 KiB.
[3 Mar 3:55]
Jinyou Ma
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)
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); }