Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-35183

ADD FULLTEXT INDEX unnecessarily DROPS FTS COMMON TABLES

Details

    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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-35170 Merge applicable changes from InnoDB 8.0.40

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            thiru Thirunarayanan Balathandayuthapani created issue -
            thiru Thirunarayanan Balathandayuthapani made changes -
            Field Original Value New Value
            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.

            {code}
            --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('mysql');
            INSERT INTO t1(title) VALUES('database');
            ALTER TABLE t1 DROP INDEX fidx;
            ALTER TABLE t1 ADD FULLTEXT fidx_1(book);
            DROP TABLE t1;
            {code}

            		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.

            {code}
            --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;
            {code}

            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
            marko Marko Mäkelä made changes -
            thiru Thirunarayanan Balathandayuthapani made changes -
            Status Open [ 1 ] Confirmed [ 10101 ]
            thiru Thirunarayanan Balathandayuthapani made changes -
            Assignee Thirunarayanan Balathandayuthapani [ thiru ] Marko Mäkelä [ marko ]
            Status Confirmed [ 10101 ] In Review [ 10002 ]
            marko Marko Mäkelä made changes -
            Assignee Marko Mäkelä [ marko ] Thirunarayanan Balathandayuthapani [ thiru ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            thiru Thirunarayanan Balathandayuthapani made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            thiru Thirunarayanan Balathandayuthapani made changes -
            Fix Version/s 10.5.27 [ 29902 ]
            Fix Version/s 10.5 [ 23123 ]
            Fix Version/s 10.6 [ 24028 ]
            Fix Version/s 10.11 [ 27614 ]
            Fix Version/s 11.2 [ 28603 ]
            Fix Version/s 11.4 [ 29301 ]
            Fix Version/s 11.6 [ 29515 ]
            Resolution Fixed [ 1 ]
            Status In Progress [ 3 ] Closed [ 6 ]
            JIraAutomate JiraAutomate made changes -
            Fix Version/s 10.6.20 [ 29903 ]
            Fix Version/s 10.11.10 [ 29904 ]
            Fix Version/s 11.2.6 [ 29906 ]
            Fix Version/s 11.4.4 [ 29907 ]

            People

              thiru Thirunarayanan Balathandayuthapani
              thiru Thirunarayanan Balathandayuthapani
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.