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

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-16500 Assertion `user_table->n_def > table->s->fields' failed in innobase_get_col_names upon ALTER TABLE with virtual columns

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-16501 [10.1] InnoDB: Failing assertion: !strcmp(from, s) in dict0mem.cc upon ALTER TABLE with virtual column

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            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.

            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.
            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.

            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.
            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.

            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.
            marko Marko Mäkelä added a comment -

            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.