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

InnoDB Inplace and Copy algorithm differs when FTS is involved

    XMLWordPrintable

Details

    Description

      There are 2 test case where innodb behaves different with copy and inplace algorithm

      Test case 1:

      --source include/have_innodb.inc
      CREATE TABLE t1 (
              f1 INT NOT NULL PRIMARY KEY,
              f2 VARCHAR(64), FULLTEXT ft(f2)) ENGINE=InnoDB;
      INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
      connect(con1,localhost,root,,,);
      START TRANSACTION WITH CONSISTENT SNAPSHOT;
       
      connection default;
      DELETE FROM t1 WHERE f1 = 2;
      ALTER TABLE t1 DROP INDEX ft;
      ALTER TABLE t1 ADD FULLTEXT INDEX ft (f2);
      INSERT INTO t1 VALUES (3, 'innodb fts search');
      SET GLOBAL innodb_optimize_fulltext_only=ON;
      OPTIMIZE TABLE t1;
      SET GLOBAL innodb_ft_aux_table = 'test/t1';
      SELECT max(DOC_ID) FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
      SELECT * FROM t1 WHERE MATCH(f2) AGAINST("+innodb +search" IN BOOLEAN MODE);
      DROP TABLE t1;
      disconnect con1;
      SET GLOBAL innodb_optimize_fulltext_only=OFF;
      SET GLOBAL innodb_ft_aux_table = default;
       
       
      By inplace algorithm:
       
      SELECT max(DOC_ID) FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
      max(DOC_ID)
      3
       
       
      By copy algorithm:
       
      SELECT max(DOC_ID) FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
      max(DOC_ID)
      2
      

      Test case 2:

      CREATE TABLE wp(
         FTS_DOC_ID bigint PRIMARY KEY,
         title VARCHAR(255) NOT NULL DEFAULT '',
         text MEDIUMTEXT NOT NULL ) ENGINE=InnoDB;
       
      INSERT INTO wp (FTS_DOC_ID, title, text) VALUES
         (1, 'MySQL Tutorial','DBMS stands for DataBase ...'),
         (2, 'How To Use MySQL Well','After you went through a ...');
       
      --error ER_INNODB_FT_WRONG_DOCID_COLUMN
      CREATE FULLTEXT INDEX idx ON wp(title, text);
       
      DROP TABLE wp;
       
       
      By inplace alorithm:
       
      CREATE FULLTEXT INDEX idx ON wp(title, text);
      ERROR HY000: Column 'FTS_DOC_ID' is of wrong type for an InnoDB FULLTEXT index
       
       
      By Copy algorithm:
       
      CREATE FULLTEXT INDEX idx ON wp(title, text);
      - Succeeds without failing
      

      Attachments

        Activity

          People

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

            Dates

              Created:
              Updated:

              Git Integration

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