| Bug #87289 | FTS auxiliary tables are not eliminated when dropping FTS indexes | ||
|---|---|---|---|
| Submitted: | 2 Aug 2017 21:31 | Modified: | 15 Aug 2017 11:07 |
| Reporter: | Juan Arruti | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
| Version: | 5.6, 5.7, 5.7.19 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | fts | ||
[3 Aug 2017 5:45]
MySQL Verification Team
Hello Juan Arruti,
Thank you for the report.
My quick tests confirmed that set of index tables which were created as part of FTS are actually dropped, summing up my internal discussion with innodb Developer - " those left overs are called "common aux tables" (not particular to an index) and when we create a FTS index, usually we will add a DOC_ID column to the table. Adding such FTS_DOC_ID column requires a table rebuild. But when you drop FTS index, you don't want to do a rebuild to remove FTS_DOC_ID, so we keep it. Some of the common table like *_CONFIG is needed to manage the DOC_ID.".
We concluded that it is rather a doc request for clarification. Converting as doc request.
## 5.7.19
root@localhost [test]> SELECT table_id, name, space from INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE name LIKE 'test/%';
Empty set (0.00 sec)
root@localhost [test]> CREATE TABLE opening_lines (
-> id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
-> opening_line TEXT(500),
-> author VARCHAR(200),
-> title VARCHAR(200),
-> FULLTEXT idx (opening_line)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)
root@localhost [test]> SELECT table_id, name, space from INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE name LIKE 'test/%';
+----------+----------------------------------------------------+-------+
| table_id | name | space |
+----------+----------------------------------------------------+-------+
| 58 | test/FTS_0000000000000034_000000000000003e_INDEX_1 | 42 |
| 59 | test/FTS_0000000000000034_000000000000003e_INDEX_2 | 43 |
| 60 | test/FTS_0000000000000034_000000000000003e_INDEX_3 | 44 |
| 61 | test/FTS_0000000000000034_000000000000003e_INDEX_4 | 45 |
| 62 | test/FTS_0000000000000034_000000000000003e_INDEX_5 | 46 |
| 63 | test/FTS_0000000000000034_000000000000003e_INDEX_6 | 47 |
| 53 | test/FTS_0000000000000034_BEING_DELETED | 37 |
| 54 | test/FTS_0000000000000034_BEING_DELETED_CACHE | 38 |
| 55 | test/FTS_0000000000000034_CONFIG | 39 |
| 56 | test/FTS_0000000000000034_DELETED | 40 |
| 57 | test/FTS_0000000000000034_DELETED_CACHE | 41 |
| 52 | test/opening_lines | 36 |
+----------+----------------------------------------------------+-------+
12 rows in set (0.00 sec)
root@localhost [test]> alter table opening_lines drop index idx ;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@localhost [test]> SELECT table_id, name, space from INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE name LIKE 'test/%';
+----------+-----------------------------------------------+-------+
| table_id | name | space |
+----------+-----------------------------------------------+-------+
| 53 | test/FTS_0000000000000034_BEING_DELETED | 37 |
| 54 | test/FTS_0000000000000034_BEING_DELETED_CACHE | 38 |
| 55 | test/FTS_0000000000000034_CONFIG | 39 |
| 56 | test/FTS_0000000000000034_DELETED | 40 |
| 57 | test/FTS_0000000000000034_DELETED_CACHE | 41 |
| 52 | test/opening_lines | 36 |
+----------+-----------------------------------------------+-------+
6 rows in set (0.00 sec)
So, test/FTS_0000000000000034_000000000000003e_INDEX_[1-6] <-- are dropped.
root@localhost [test]> system ls -l 87289/test/
total 608
-rw-r----- 1 umshastr common 65 Aug 3 07:11 db.opt
-rw-r----- 1 umshastr common 98304 Aug 3 07:20 FTS_0000000000000034_BEING_DELETED_CACHE.ibd
-rw-r----- 1 umshastr common 98304 Aug 3 07:20 FTS_0000000000000034_BEING_DELETED.ibd
-rw-r----- 1 umshastr common 98304 Aug 3 07:20 FTS_0000000000000034_CONFIG.ibd
-rw-r----- 1 umshastr common 98304 Aug 3 07:20 FTS_0000000000000034_DELETED_CACHE.ibd
-rw-r----- 1 umshastr common 98304 Aug 3 07:20 FTS_0000000000000034_DELETED.ibd
-rw-r----- 1 umshastr common 8668 Aug 3 07:21 opening_lines.frm
-rw-r----- 1 umshastr common 114688 Aug 3 07:20 opening_lines.ibd
root@localhost [test]> \q
Thanks,
Umesh
[14 Aug 2017 19:41]
Daniel Price
Posted by developer: The following content has been revised: https://dev.mysql.com/doc/refman/5.6/en/innodb-fulltext-index.html#innodb-fulltext-index-t... https://dev.mysql.com/doc/refman/5.7/en/innodb-fulltext-index.html#innodb-fulltext-index-t... https://dev.mysql.com/doc/refman/8.0/en/innodb-fulltext-index.html#innodb-fulltext-index-t... https://dev.mysql.com/doc/refman/5.6/en/innodb-fulltext-index.html#innodb-fulltext-index-d... https://dev.mysql.com/doc/refman/5.7/en/innodb-fulltext-index.html#innodb-fulltext-index-d... https://dev.mysql.com/doc/refman/8.0/en/innodb-fulltext-index.html#innodb-fulltext-index-d... Thank you for the bug report.
Description: Tested in MySQL 5.6.36 and MySQL 5.7.18. FTS auxiliary tables are not eliminated when a FTS index is dropped in MySQL 5.6 or 5.7. How to repeat: 1) Create a table with FTS index mysql> CREATE TABLE opening_lines ( -> id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, -> opening_line TEXT(500), -> author VARCHAR(200), -> title VARCHAR(200), -> FULLTEXT idx (opening_line) -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0.21 sec) 2) Drop the FTS index mysql> alter table opening_lines drop index idx ; Query OK, 0 rows affected (0.10 sec) Records: 0 Duplicates: 0 Warnings: 0 3) Check FS for auxiliary tables mysql> \! ls FTS_0000000000000014_BEING_DELETED_CACHE.ibd FTS_0000000000000014_CONFIG.ibd FTS_0000000000000014_DELETED.ibd opening_lines.ibd FTS_0000000000000014_BEING_DELETED.ibd FTS_0000000000000014_DELETED_CACHE.ibd opening_lines.frm 4) Check INNODB_SYS_TABLES table mysql> SELECT table_id, name, space from INFORMATION_SCHEMA.INNODB_SYS_TABLES -> WHERE name LIKE 'test/%'; +----------+-----------------------------------------------+-------+ | table_id | name | space | +----------+-----------------------------------------------+-------+ | 23 | test/FTS_0000000000000014_BEING_DELETED | 9 | | 24 | test/FTS_0000000000000014_BEING_DELETED_CACHE | 10 | | 25 | test/FTS_0000000000000014_CONFIG | 11 | | 21 | test/FTS_0000000000000014_DELETED | 7 | | 22 | test/FTS_0000000000000014_DELETED_CACHE | 8 | | 20 | test/opening_lines | 6 | +----------+-----------------------------------------------+-------+ Suggested fix: Dropped auxiliary tables when dropping FTS indexes.