[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 Created: 2018-06-15  Updated: 2019-01-14  Resolved: 2019-01-14

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table, Full-text Search, Storage Engine - InnoDB, Storage Engine - XtraDB, Virtual Columns
Affects Version/s: 10.0, 10.1
Fix Version/s: 10.4.2, 10.1.38, 10.0.38, 10.2.22, 10.3.13

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Eugene Kosov (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-16500 Assertion `user_table->n_def > table-... Closed
relates to MDEV-16501 [10.1] InnoDB: Failing assertion: !st... Closed

 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.

 Comments   
Comment by Elena Stepanova [ 2018-07-07 ]

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.

Comment by Marko Mäkelä [ 2018-08-29 ]

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.

Comment by Elena Stepanova [ 2018-08-29 ]

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.

Comment by Marko Mäkelä [ 2019-01-14 ]

This was a bogus error message.

Generated at Thu Feb 08 08:29:22 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.