Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.5, 10.6, 10.11, 11.2, 11.4, 11.6
-
None
Description
InnoDB fulltext doesn't drop common tables even when we drop the last fts index.
fulltext common tables are being dropped only when InnoDB table does rebuild
and there is no user defined fulltext exist. But InnoDB unnecesary drops the common table even InnoDB adds fulltext index.
--source include/have_innodb.inc
|
CREATE TABLE t1 (
|
ID INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
|
title VARCHAR(200), book VARCHAR(200),
|
FULLTEXT fidx(title)) ENGINE = InnoDB;
|
INSERT INTO t1(title) VALUES('database');
|
|
|
ALTER TABLE t1 DROP INDEX fidx;
|
|
|
select * from information_schema.innodb_sys_tables;
|
TABLE_ID NAME FLAG N_COLS SPACE ROW_FORMAT ZIP_PAGE_SIZE SPACE_TYPE
|
14 SYS_DATAFILES 0 5 0 Redundant 0 System
|
11 SYS_FOREIGN 0 7 0 Redundant 0 System
|
12 SYS_FOREIGN_COLS 0 7 0 Redundant 0System
|
13 SYS_TABLESPACES 0 6 0 Redundant 0 System
|
15 SYS_VIRTUAL 0 6 0 Redundant 0 System
|
17 mysql/innodb_index_stats 33 11 2 Dynamic 0 Single
|
16 mysql/innodb_table_stats 33 9 1 Dynamic 0 Single
|
18 mysql/transaction_registry 33 8 3 Dynamic 0 Single
|
21 test/FTS_0000000000000014_BEING_DELETED 33 4 6 Dynamic 0 Single
|
22 test/FTS_0000000000000014_BEING_DELETED_CACHE 33 4 7Dynamic 0 Single
|
23 test/FTS_0000000000000014_CONFIG 33 5 8 Dynamic 0 Single
|
24 test/FTS_0000000000000014_DELETED 33 4 9 Dynamic 0 Single
|
25 test/FTS_0000000000000014_DELETED_CACHE 33 4 10 Dynamic 0 Single
|
20 test/t1 33 7 5 Dynamic 0 Single
|
|
|
ALTER TABLE t1 ADD FULLTEXT fidx_1(book);
|
|
|
select * from information_schema.innodb_sys_tables;
|
TABLE_ID NAME FLAG N_COLS SPACE ROW_FORMAT ZIP_PAGE_SIZE SPACE_TYPE
|
14 SYS_DATAFILES 0 5 0 Redundant 0 System
|
11 SYS_FOREIGN 0 7 0 Redundant 0 System
|
12 SYS_FOREIGN_COLS 0 7 0 Redundant 0 System
|
13 SYS_TABLESPACES 0 6 0 Redundant 0 System
|
15 SYS_VIRTUAL 0 6 0 Redundant 0 System
|
17 mysql/innodb_index_stats 33 11 2 Dynamic 0 Single
|
16 mysql/innodb_table_stats 33 9 1 Dynamic 0 Single
|
18 mysql/transaction_registry 33 8 3 Dynamic 0 Single
|
32 test/FTS_0000000000000014_0000000000000027_INDEX_1 33 8 17 Dynamic 0 Single
|
33 test/FTS_0000000000000014_0000000000000027_INDEX_2 33 8 18 Dynamic 0 Single
|
34 test/FTS_0000000000000014_0000000000000027_INDEX_3 33 8 19 Dynamic 0 Single
|
35 test/FTS_0000000000000014_0000000000000027_INDEX_4 33 8 20 Dynamic 0 Single
|
36 test/FTS_0000000000000014_0000000000000027_INDEX_5 33 8 21 Dynamic 0 Single
|
37 test/FTS_0000000000000014_0000000000000027_INDEX_6 33 8 22 Dynamic 0 Single
|
38 test/FTS_0000000000000014_BEING_DELETED 33 4 23 Dynamic 0 Single
|
39 test/FTS_0000000000000014_BEING_DELETED_CACHE 33 4 24 Dynamic 0 Single
|
40 test/FTS_0000000000000014_CONFIG 33 5 25 Dynamic 0 Single
|
41 test/FTS_0000000000000014_DELETED 33 4 26 Dynamic 0 Single
|
42 test/FTS_0000000000000014_DELETED_CACHE 33 4 27 Dynamic 0 Single
|
20 test/t1 33 7 5 Dynamic 0 Single
|
|
|
DROP TABLE t1;
|
After the first alter (drop fulltext), InnoDB still retain common table. Usually COMMON TABLES gets dropped only
during consecutive alter rebuild table. 2nd ALTER (adding new FTS INDEX) drops the COMMON TABLES unnecessarily and
recreates it again. This can be optimised
Attachments
Issue Links
- relates to
-
-
- Closed
-