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

[10.1] ER_NO_SUCH_TABLE_IN_ENGINE followed by "Please drop the table and recreate" upon adding FULLTEXT key to table with virtual column

Details

    Description

      --source include/have_innodb.inc
       
      CREATE TABLE t1 (a INT, b CHAR(12), c INT AS (a) VIRTUAL) ENGINE=InnoDB;
      ALTER TABLE t1 ADD FULLTEXT KEY(b);
       
      # Cleanup
      DROP TABLE t1;
      

      10.1 c22ab56f0d6

      CURRENT_TEST: bug.corrupt2
      mysqltest: At line 4: query 'ALTER TABLE t1 ADD FULLTEXT KEY(b)' failed: 1932: Table 'test.t1' doesn't exist in engine
      

      2018-06-15 19:45:14 140505486965504 [Warning] InnoDB: table test/t1 contains 3 user defined columns in InnoDB, but 3 columns in MySQL. Please check INFORMATION_SCHEMA.INNODB_SYS_COLUMNS and http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how to resolve it
      2018-06-15 19:45:14 140505486965504 [Warning] InnoDB: Cannot open table test/t1 from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.
      

      Not reproducible on 10.2.

      Attachments

        Issue Links

          Activity

            Extra step in the same scenario brings an additional error message, adding it here to make it searchable and indicate it's a part of the same problem:
            Table "test"."t1" is corrupted. Please drop the table and recreate.

            --source include/have_innodb.inc
             
            CREATE TABLE t1 (a INT, b CHAR(12), c INT AS (a) VIRTUAL) ENGINE=InnoDB;
            --error ER_NO_SUCH_TABLE_IN_ENGINE
            ALTER TABLE t1 ADD FULLTEXT KEY(b);
            INSERT INTO t1 (a,b) VALUES (1,'foo');
             
            # Cleanup
            DROP TABLE t1;
            

            10.1 1d10c9afe0f2

            2018-07-07 17:12:58 140446372162304 [Warning] InnoDB: table test/t1 contains 3 user defined columns in InnoDB, but 3 columns in MySQL. Please check INFORMATION_SCHEMA.INNODB_SYS_COLUMNS and http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how to resolve it
            2018-07-07 17:12:58 140446372162304 [Warning] InnoDB: Cannot open table test/t1 from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.
            2018-07-07 17:12:58 140446372162304 [ERROR] InnoDB: Table "test"."t1" is corrupted. Please drop the table and recreate.
            2018-07-07 17:12:58 140446372162304 [Warning] InnoDB: Cannot open table test/t1 from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.
            

            elenst Elena Stepanova added a comment - Extra step in the same scenario brings an additional error message, adding it here to make it searchable and indicate it's a part of the same problem: Table "test"."t1" is corrupted. Please drop the table and recreate. --source include/have_innodb.inc CREATE TABLE t1 (a INT , b CHAR (12), c INT AS (a) VIRTUAL) ENGINE=InnoDB; --error ER_NO_SUCH_TABLE_IN_ENGINE ALTER TABLE t1 ADD FULLTEXT KEY (b); INSERT INTO t1 (a,b) VALUES (1, 'foo' ); # Cleanup DROP TABLE t1; 10.1 1d10c9afe0f2 2018-07-07 17:12:58 140446372162304 [Warning] InnoDB: table test/t1 contains 3 user defined columns in InnoDB, but 3 columns in MySQL. Please check INFORMATION_SCHEMA.INNODB_SYS_COLUMNS and http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how to resolve it 2018-07-07 17:12:58 140446372162304 [Warning] InnoDB: Cannot open table test/t1 from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem. 2018-07-07 17:12:58 140446372162304 [ERROR] InnoDB: Table "test"."t1" is corrupted. Please drop the table and recreate. 2018-07-07 17:12:58 140446372162304 [Warning] InnoDB: Cannot open table test/t1 from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.

            Before 10.2, InnoDB does not know anything about virtual columns. I feel that the creation of any indexes on virtual columns has to be blocked in the SQL layer, before allowing the execution to enter InnoDB, either ALGORITHM=INPLACE (like in this case) or ALGORITHM=COPY.

            Starting with 10.2, InnoDB correctly blocks attempts to create FULLTEXT or SPATIAL index on virtual columns.

            marko Marko Mäkelä added a comment - Before 10.2, InnoDB does not know anything about virtual columns. I feel that the creation of any indexes on virtual columns has to be blocked in the SQL layer, before allowing the execution to enter InnoDB, either ALGORITHM=INPLACE (like in this case) or ALGORITHM=COPY. Starting with 10.2, InnoDB correctly blocks attempts to create FULLTEXT or SPATIAL index on virtual columns.

            Yes, except that in both cases above, the added index is NOT on a virtual column, and not even on the column it references, but on a third unrelated column. And there are no indexes on virtual columns at all, fulltext or not.

            elenst Elena Stepanova added a comment - Yes, except that in both cases above, the added index is NOT on a virtual column, and not even on the column it references, but on a third unrelated column. And there are no indexes on virtual columns at all, fulltext or not.

            This was a bogus error message.

            marko Marko Mäkelä added a comment - This was a bogus error message.

            People

              kevg Eugene Kosov (Inactive)
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.