MDEV-26668 CHECK TABLE fails to report schema mismatchbb-10.2-MDEV-26668-schema-mismatch

check_index_consistency(): always check clustered index against all
fields defined in MariaDB table. Also check for a possible FTS index
at the end.

innobase_match_index_columns(): the change is not a refactoring only.
Notice a while loop which was removed. Once it encounters a DATA_SYS
field it skips all the left fields! This is fixed now.

ha_innobase::open(): now returns an error when MariaDB and InnoDB table
definitions differ

innodb_strict_mode: affects the above check in a sandard way:
makes it an error or a warning.

MDEV-18186 test case was moved to another file because previous
if (have_debug) code in the test case makes SHOW CREATE TABLE behave
differently in release and debug modes.

9 files changed, 253 insertions, 98 deletions

diff --git a/mysql-test/suite/innodb/r/innodb-alter-tempfile.result b/mysql-test/suite/innodb/r/innodb-alter-tempfile.result
index ad6e17b920c..30082c737ec 100644
--- a/mysql-test/suite/innodb/r/innodb-alter-tempfile.result
+++ b/mysql-test/suite/innodb/r/innodb-alter-tempfile.result
@@ -43,12 +43,7 @@ connection default;
 set DEBUG_SYNC="now WAIT_FOR default_signal";
 disconnect con1;
 SHOW KEYS FROM t1;
-Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
-t1	1	f1	1	f1	A	2	NULL	NULL		BTREE		
-t1	1	f2	1	f2	A	NULL	NULL	NULL		Corrupted		
-Warnings:
-Warning	1082	InnoDB: Table test/t1 contains 1 indexes inside InnoDB, which is different from the number of indexes 2 defined in the MariaDB 
-Warning	1082	InnoDB: Table test/t1 contains 1 indexes inside InnoDB, which is different from the number of indexes 2 defined in the MariaDB 
+ERROR 42S02: Table 'test.t1' doesn't exist in engine
 DROP TABLE t1;
 #
 # MDEV-25503 InnoDB hangs on startup during recovery
diff --git a/mysql-test/suite/innodb/r/innodb-corrupted-table.result b/mysql-test/suite/innodb/r/innodb-corrupted-table.result
index 1a8cea06c4c..3649bd80ef8 100644
--- a/mysql-test/suite/innodb/r/innodb-corrupted-table.result
+++ b/mysql-test/suite/innodb/r/innodb-corrupted-table.result
@@ -1,11 +1,13 @@
 call mtr.add_suppression("Table .* has a primary key in InnoDB data dictionary, but not in MariaDB.*");
 call mtr.add_suppression("InnoDB: Table .* contains .* indexes inside InnoDB, which is different from the number of indexes .* defined in the MariaDB.*");
+call mtr.add_suppression("InnoDB indexes are inconsistent with what defined in .frm for table");
 create table t1 (pk int, i int, key(i)) engine=InnoDB;
 insert into t1 values (1,1),(2,2);
 flush tables;
 # Save the .frm file without the PK
 alter table t1 add primary key (pk);
 # Stop the server, replace the frm with the old one and restart the server
+set global innodb_strict_mode=off;
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
@@ -14,6 +16,7 @@ t1	CREATE TABLE `t1` (
   KEY `i` (`i`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
 Warnings:
+Warning	1932	test
 Warning	1082	InnoDB: Table test/t1 has a primary key in InnoDB data dictionary, but not in MariaDB!
 Warning	1082	InnoDB: Table test/t1 contains 2 indexes inside InnoDB, which is different from the number of indexes 1 defined in the MariaDB 
 select * from t1;
@@ -34,6 +37,7 @@ t1	CREATE TABLE `t1` (
   `j` int(11) DEFAULT NULL,
   KEY `i` (`i`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
+set global innodb_strict_mode=on;
 alter table t1 add primary key (pk);
 show warnings;
 Level	Code	Message
diff --git a/mysql-test/suite/innodb/r/innodb-dict.result b/mysql-test/suite/innodb/r/innodb-dict.result
index e3b2f0d5288..4fce78861f8 100644
--- a/mysql-test/suite/innodb/r/innodb-dict.result
+++ b/mysql-test/suite/innodb/r/innodb-dict.result
@@ -1,3 +1,5 @@
+call mtr.add_suppression("InnoDB indexes are inconsistent with what defined in .frm for table");
+call mtr.add_suppression("Innodb: Table `test`.`t1` is corrupted. Please drop the table and recreate.");
 CREATE TABLE t1 (D INT) ENGINE=innodb;
 INSERT INTO t1 VALUES (10);
 ALTER TABLE t1 MODIFY COLUMN d INT;
@@ -38,3 +40,23 @@ t1	CREATE TABLE `t1` (
   KEY `my_d` (`D`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
 DROP TABLE t1;
+#
+#  MDEV-18186 assertion failure on missing InnoDB index
+#
+CREATE TABLE t (a INT, INDEX i1 (a)) ENGINE=INNODB;
+DROP TABLE t;
+CREATE TABLE t (a INT) ENGINE=INNODB;
+SHOW CREATE TABLE t;
+ERROR 42S02: Table 'test.t' doesn't exist in engine
+DROP TABLE t;
+CREATE TABLE t1 (a INT, b VARCHAR(255)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES(0,'');
+ALTER TABLE t1 MODIFY a INT AFTER b;
+FLUSH TABLES;
+CHECK TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	check	Error	Table 'test.t1' doesn't exist in engine
+test.t1	check	status	Operation failed
+SELECT * FROM t1;
+ERROR 42S02: Table 'test.t1' doesn't exist in engine
+DROP TABLE t1;
diff --git a/mysql-test/suite/innodb/r/innodb-index.result b/mysql-test/suite/innodb/r/innodb-index.result
index 5e91efb7062..fa3e2f503c6 100644
--- a/mysql-test/suite/innodb/r/innodb-index.result
+++ b/mysql-test/suite/innodb/r/innodb-index.result
@@ -1925,19 +1925,3 @@ f1
 SELECT * FROM t2;
 f1
 DROP TABLE t1, t2;
-#
-#  MDEV-18186 assertion failure on missing InnoDB index
-#
-CREATE TABLE t (a INT, INDEX i1 (a)) ENGINE=INNODB;
-DROP TABLE t;
-CREATE TABLE t (a INT) ENGINE=INNODB;
-SHOW CREATE TABLE t;
-Table	Create Table
-t	CREATE TABLE `t` (
-  `a` int(11) DEFAULT NULL,
-  KEY `i1` (`a`)
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-Warnings:
-Warning	1082	InnoDB: Table test/t contains 0 indexes inside InnoDB, which is different from the number of indexes 1 defined in the MariaDB 
-Warning	1082	InnoDB: Table test/t contains 0 indexes inside InnoDB, which is different from the number of indexes 1 defined in the MariaDB 
-DROP TABLE t;
diff --git a/mysql-test/suite/innodb/t/innodb-alter-tempfile.test b/mysql-test/suite/innodb/t/innodb-alter-tempfile.test
index 7e4244d09f8..0cc63a8d284 100644
--- a/mysql-test/suite/innodb/t/innodb-alter-tempfile.test
+++ b/mysql-test/suite/innodb/t/innodb-alter-tempfile.test
@@ -68,6 +68,7 @@ set DEBUG_SYNC="now WAIT_FOR default_signal";
 --let $shutdown_timeout=0
 --source include/restart_mysqld.inc
 disconnect con1;
+--error ER_NO_SUCH_TABLE_IN_ENGINE
 SHOW KEYS FROM t1;
 DROP TABLE t1;
 remove_files_wildcard $datadir/test #sql-*.frm;
diff --git a/mysql-test/suite/innodb/t/innodb-corrupted-table.test b/mysql-test/suite/innodb/t/innodb-corrupted-table.test
index a81235a9dd4..c67e193d7d6 100644
--- a/mysql-test/suite/innodb/t/innodb-corrupted-table.test
+++ b/mysql-test/suite/innodb/t/innodb-corrupted-table.test
@@ -7,7 +7,7 @@
 
 call mtr.add_suppression("Table .* has a primary key in InnoDB data dictionary, but not in MariaDB.*");
 call mtr.add_suppression("InnoDB: Table .* contains .* indexes inside InnoDB, which is different from the number of indexes .* defined in the MariaDB.*");
-
+call mtr.add_suppression("InnoDB indexes are inconsistent with what defined in .frm for table");
 create table t1 (pk int, i int, key(i)) engine=InnoDB;
 insert into t1 values (1,1),(2,2);
 
@@ -35,11 +35,13 @@ alter table t1 add primary key (pk);
 --source include/wait_until_connected_again.inc
 
 --enable_prepare_warnings
+set global innodb_strict_mode=off;
 show create table t1;
 select * from t1;
 alter table t1 add j int;
 show warnings;
 show create table t1;
+set global innodb_strict_mode=on;
 alter table t1 add primary key (pk);
 show warnings;
 show create table t1;
diff --git a/mysql-test/suite/innodb/t/innodb-dict.test b/mysql-test/suite/innodb/t/innodb-dict.test
index 25a284569db..aff663d1f83 100644
--- a/mysql-test/suite/innodb/t/innodb-dict.test
+++ b/mysql-test/suite/innodb/t/innodb-dict.test
@@ -1,5 +1,10 @@
 --source include/have_innodb.inc
 
+call mtr.add_suppression("InnoDB indexes are inconsistent with what defined in .frm for table");
+call mtr.add_suppression("Innodb: Table `test`.`t1` is corrupted. Please drop the table and recreate.");
+
+let $datadir=`select @@datadir`;
+
 #
 # Fix MySQL Bug#20755615: InnoDB compares column names case sensitively,
 # while according to Storage Engine API column names should be compared
@@ -29,3 +34,43 @@ EXPLAIN SELECT d FROM t1 WHERE d = 5;
 EXPLAIN SELECT D FROM t1 WHERE D = 5;
 SHOW CREATE TABLE t1;
 DROP TABLE t1;
+
+--echo #
+--echo #  MDEV-18186 assertion failure on missing InnoDB index
+--echo #
+
+--disable_query_log
+call mtr.add_suppression("Cannot find index i1 in InnoDB index dictionary");
+call mtr.add_suppression("InnoDB indexes are inconsistent with what defined");
+call mtr.add_suppression("Table test/t contains 0 indexes");
+call mtr.add_suppression("InnoDB could not find key no");
+--enable_query_log
+
+# Test an attempt to rename a nonexistent index inside InnoDB
+-- let $MYSQL_DATA_DIR = `SELECT @@datadir`
+CREATE TABLE t (a INT, INDEX i1 (a)) ENGINE=INNODB;
+-- copy_file $MYSQL_DATA_DIR/test/t.frm $MYSQL_DATA_DIR/test/t.fr_
+DROP TABLE t;
+CREATE TABLE t (a INT) ENGINE=INNODB;
+-- remove_file $MYSQL_DATA_DIR/test/t.frm
+-- move_file $MYSQL_DATA_DIR/test/t.fr_ $MYSQL_DATA_DIR/test/t.frm
+--enable_prepare_warnings
+--error ER_NO_SUCH_TABLE_IN_ENGINE
+SHOW CREATE TABLE t;
+--disable_prepare_warnings
+DROP TABLE t;
+
+
+CREATE TABLE t1 (a INT, b VARCHAR(255)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES(0,'');
+--copy_file $datadir/test/t1.frm $datadir/test/old.frm
+ALTER TABLE t1 MODIFY a INT AFTER b;
+FLUSH TABLES;
+
+--remove_file $datadir/test/t1.frm
+--move_file $datadir/test/old.frm $datadir/test/t1.frm
+
+CHECK TABLE t1;
+--error ER_NO_SUCH_TABLE_IN_ENGINE
+SELECT * FROM t1;
+DROP TABLE t1;
diff --git a/mysql-test/suite/innodb/t/innodb-index.test b/mysql-test/suite/innodb/t/innodb-index.test
index 474b0e08935..2834f695e8f 100644
--- a/mysql-test/suite/innodb/t/innodb-index.test
+++ b/mysql-test/suite/innodb/t/innodb-index.test
@@ -1167,30 +1167,6 @@ SELECT * FROM t2;
 
 DROP TABLE t1, t2;
 
---echo #
---echo #  MDEV-18186 assertion failure on missing InnoDB index
---echo #
-
---disable_query_log
-call mtr.add_suppression("Cannot find index i1 in InnoDB index dictionary");
-call mtr.add_suppression("InnoDB indexes are inconsistent with what defined");
-call mtr.add_suppression("Table test/t contains 0 indexes");
-call mtr.add_suppression("InnoDB could not find key no");
---enable_query_log
-
-# Test an attempt to rename a nonexistent index inside InnoDB
--- let $MYSQL_DATA_DIR = `SELECT @@datadir`
-CREATE TABLE t (a INT, INDEX i1 (a)) ENGINE=INNODB;
--- copy_file $MYSQL_DATA_DIR/test/t.frm $MYSQL_DATA_DIR/test/t.fr_
-DROP TABLE t;
-CREATE TABLE t (a INT) ENGINE=INNODB;
--- remove_file $MYSQL_DATA_DIR/test/t.frm
--- move_file $MYSQL_DATA_DIR/test/t.fr_ $MYSQL_DATA_DIR/test/t.frm
---enable_prepare_warnings
-SHOW CREATE TABLE t;
---disable_prepare_warnings
-DROP TABLE t;
-
 --disable_query_log
 
 call mtr.add_suppression("InnoDB: Tablespace .* was not found at .*t[12].ibd.");
diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc
index 37748cb497a..f94a003a43e 100644
--- a/storage/innobase/handler/ha_innodb.cc
+++ b/storage/innobase/handler/ha_innodb.cc
@@ -5744,6 +5744,54 @@ test_ut_format_name()
 }
 #endif /* !DBUG_OFF */
 
+/** Compares InnoDB type against MariaDB type and reports mismatch */
+static bool match_column_type(const dict_field_t *&ib_field,
+                              const dict_field_t *ib_field_end,
+                              const Field *field)
+{
+  ut_ad(ib_field != ib_field_end);
+
+  size_t is_unsigned;
+  size_t col_type= get_innobase_type_from_mysql_type(&is_unsigned, field);
+
+  /* Ignore InnoDB specific system columns. */
+  while (ib_field->col->mtype == DATA_SYS)
+  {
+    ib_field++;
+
+    if (ib_field >= ib_field_end)
+      return false;
+  }
+
+  /* MariaDB-5.5 compatibility */
+  if ((field->real_type() == MYSQL_TYPE_ENUM ||
+       field->real_type() == MYSQL_TYPE_SET) &&
+      ib_field->col->mtype == DATA_FIXBINARY)
+    col_type= DATA_FIXBINARY;
+
+  if (col_type != ib_field->col->mtype)
+  {
+    /* If the col_type we get from mysql type is a geometry
+    data type, we should check if mtype is a legacy type
+    from 5.6, either upgraded to DATA_GEOMETRY or not.
+    This is indeed not an accurate check, but should be
+    safe, since DATA_BLOB would be upgraded once we create
+    spatial index on it and we intend to use DATA_GEOMETRY
+    for legacy GIS data types which are of var-length. */
+    switch (col_type) {
+    case DATA_GEOMETRY:
+      if (ib_field->col->mtype == DATA_BLOB)
+        break;
+      /* Fall through */
+    default:
+      /* Column type mismatches */
+      return false;
+    }
+  }
+
+  return true;
+}
+
 /** Match index columns between MySQL and InnoDB.
 This function checks whether the index column information
 is consistent between KEY info from mysql and that from innodb index.
@@ -5781,49 +5829,10 @@ innobase_match_index_columns(
 	One hidden assumption here is that the index column sequences
 	are matched up between those in mysql and InnoDB. */
 	for (; key_part != key_end; ++key_part) {
-		ulint	col_type;
-		ibool	is_unsigned;
-		ulint	mtype = innodb_idx_fld->col->mtype;
-
-		/* Need to translate to InnoDB column type before
-		comparison. */
-		col_type = get_innobase_type_from_mysql_type(
-			&is_unsigned, key_part->field);
-
-		/* Ignore InnoDB specific system columns. */
-		while (mtype == DATA_SYS) {
-			innodb_idx_fld++;
-
-			if (innodb_idx_fld >= innodb_idx_fld_end) {
-				DBUG_RETURN(FALSE);
-			}
-		}
 
-		/* MariaDB-5.5 compatibility */
-		if ((key_part->field->real_type() == MYSQL_TYPE_ENUM ||
-		     key_part->field->real_type() == MYSQL_TYPE_SET) &&
-		    mtype == DATA_FIXBINARY) {
-			col_type= DATA_FIXBINARY;
-		}
-
-		if (col_type != mtype) {
-			/* If the col_type we get from mysql type is a geometry
-			data type, we should check if mtype is a legacy type
-			from 5.6, either upgraded to DATA_GEOMETRY or not.
-			This is indeed not an accurate check, but should be
-			safe, since DATA_BLOB would be upgraded once we create
-			spatial index on it and we intend to use DATA_GEOMETRY
-			for legacy GIS data types which are of var-length. */
-			switch (col_type) {
-			case DATA_GEOMETRY:
-				if (mtype == DATA_BLOB) {
-					break;
-				}
-				/* Fall through */
-			default:
-				/* Column type mismatches */
-				DBUG_RETURN(false);
-			}
+		if (!match_column_type(innodb_idx_fld, innodb_idx_fld_end,
+				       key_part->field)) {
+			DBUG_RETURN(false);
 		}
 
 		innodb_idx_fld++;
@@ -6041,6 +6050,24 @@ innobase_build_v_templ(
 	DBUG_VOID_RETURN;
 }
 
+static bool is_part_of_a_primary_key(const Field *field)
+{
+  const TABLE_SHARE *s= field->table->s;
+
+  if (s->primary_key == MAX_KEY)
+    return false;
+
+  const KEY &key= field->table->key_info[s->primary_key];
+
+  for (uint i= 0; i < key.user_defined_key_parts; i++)
+  {
+    if (!strcmp(field->field_name, key.key_part[i].field->field_name))
+      return true;
+  }
+
+  return false;
+}
+
 /** Check consistency between .frm indexes and InnoDB indexes.
 @param[in]	table	table object formed from .frm
 @param[in]	ib_table	InnoDB table definition
@@ -6050,14 +6077,12 @@ check_index_consistency(const TABLE* table, const dict_table_t* ib_table)
 {
 	ulint mysql_num_index = table->s->keys;
 	ulint ib_num_index = UT_LIST_GET_LEN(ib_table->indexes);
-	bool ret = true;
 
 	/* If there exists inconsistency between MySQL and InnoDB dictionary
 	(metadata) information, the number of index defined in MySQL
 	could exceed that in InnoDB, return error */
 	if (ib_num_index < mysql_num_index) {
-		ret = false;
-		goto func_exit;
+		return false;
 	}
 
 	/* For each index in the mysql key_info array, fetch its
@@ -6072,8 +6097,7 @@ check_index_consistency(const TABLE* table, const dict_table_t* ib_table)
 			sql_print_error("Cannot find index %s in InnoDB"
 					" index dictionary.",
 					table->key_info[count].name);
-			ret = false;
-			goto func_exit;
+			return false;
 		}
 
 		/* Double check fetched index has the same
@@ -6083,13 +6107,103 @@ check_index_consistency(const TABLE* table, const dict_table_t* ib_table)
 			sql_print_error("Found index %s whose column info"
 					" does not match that of MariaDB.",
 					table->key_info[count].name);
-			ret = false;
-			goto func_exit;
+			return false;
 		}
 	}
 
-func_exit:
-	return ret;
+	const dict_index_t *clust = dict_table_get_first_index(ib_table);
+	ut_ad(clust);
+
+	const dict_field_t *ib_field = clust->fields;
+	const dict_field_t *ib_field_end = clust->fields + clust->n_fields;
+
+	std::vector<const char *> pk_fields;
+
+	if (table->s->primary_key != MAX_KEY) {
+		const KEY &pk = table->key_info[table->s->primary_key];
+
+		pk_fields.reserve(pk.user_defined_key_parts);
+
+		for (const KEY_PART_INFO *it = pk.key_part,
+			*end = pk.key_part + pk.user_defined_key_parts;
+			it != end; it++) {
+			const Field *field= it->field;
+
+			if (field->vcol_info
+			    && !field->vcol_info->stored_in_db) {
+				continue;
+			}
+
+			pk_fields.push_back(field->field_name);
+
+			if (!match_column_type(ib_field, ib_field_end,
+					       field)) {
+				return false;
+			}
+
+			ib_field++;
+		}
+	} else if (ib_field->col->mtype != DATA_SYS) {
+		return false;
+	}
+
+        /* Ignore InnoDB specific system columns. */
+	while (ib_field != ib_field_end && ib_field->col->mtype == DATA_SYS) {
+		ib_field++;
+	}
+
+	for (uint i = 0; i < table->s->fields; i++) {
+		const Field *field= table->field[i];
+
+		for (;;) {
+			if (ib_field == ib_field_end) {
+				break;
+			}
+
+			bool skip_field = false;
+			for (std::vector<const char *>::const_iterator
+				it = pk_fields.begin(),
+				end = pk_fields.end(); it != end; ++it) {
+				if (!innobase_strcasecmp(ib_field->name,
+							 *it)) {
+					skip_field = true;
+					break;
+				}
+			}
+
+			if (skip_field) {
+				ib_field++;
+			} else {
+				break;
+			}
+		}
+
+                if (!is_part_of_a_primary_key(field)) {
+			if (field->vcol_info
+			    && !field->vcol_info->stored_in_db) {
+				continue;
+			}
+
+			if (!match_column_type(ib_field, ib_field_end,
+					       field)) {
+				return false;
+			}
+
+			ib_field++;
+		}
+	}
+
+	if (ib_field != ib_field_end) {
+		if (ib_field_end - ib_field != 1) {
+			return false;
+		}
+
+		if (strcmp(ib_field->name, FTS_DOC_ID_COL_NAME)) {
+			return false;
+		}
+	}
+
+	return true;
 }
 
 /********************************************************************//**
@@ -6384,6 +6498,18 @@ no_such_table:
 		sql_print_error("InnoDB indexes are inconsistent with what "
 				"defined in .frm for table %s",
 				name);
+		if (THDVAR(thd, strict_mode)) {
+			ib_table->file_unreadable = true;
+			ib_table->corrupted = true;
+			dict_table_close(ib_table, false, false);
+			set_my_errno(ENOENT);
+			DBUG_RETURN(HA_ERR_NO_SUCH_TABLE);
+		} else {
+			push_warning_printf(thd,
+				Sql_condition::WARN_LEVEL_WARN,
+				ER_NO_SUCH_TABLE_IN_ENGINE,
+				table->s->db.str, table->s->table_name);
+		}
 	}
 
 	/* Allocate a buffer for a 'row reference'. A row reference is