Bug #82480 Incorrect schema mismatch error message when importing mismatched tables
Submitted: 6 Aug 2016 1:30 Modified: 6 Aug 2016 7:18
Reporter: Davi Arnaut (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.7.13, 5.7.14 OS:Any
Assigned to: CPU Architecture:Any
Tags: ALTER TABLE, IMPORT TABLESPACE, Schema mismatch

[6 Aug 2016 1:30] Davi Arnaut
Description:
When importing a table whose schema does not match that of the
existing schema, the following error is returned:

mysql> ALTER TABLE t1 IMPORT TABLESPACE;
ERROR 1808 (HY000): Schema mismatch (Table flags don't match, server table has 0x4 and the meta-data file has 0x1)

The problem is that the "flags" value of the server table is not the
correct value, making it difficult to troubleshoot the mismatch as
that value does not change regardless of what the value of the flags
actually is.

The actual value being printed is the number of columns in hex.

How to repeat:
mysql> SET GLOBAL innodb_default_row_format = 'compact';
mysql> CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
mysql> FLUSH TABLES t1 FOR EXPORT;
mysql> SYSTEM cp data/test/t1.* /tmp
mysql> UNLOCK TABLES;
mysql> DROP TABLE t1;
mysql> SET GLOBAL innodb_default_row_format = 'dynamic';
mysql> CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
mysql> ALTER TABLE t1 DISCARD TABLESPACE;
mysql> SYSTEM cp /tmp/t1.* data/test/
mysql> ALTER TABLE t1 IMPORT TABLESPACE;
ERROR 1808 (HY000): Schema mismatch (Table flags don't match, server table has 0x4 and the meta-data file has 0x1)
mysql> DROP TABLE t1;
mysql> SYSTEM rm data/test/t1.*
mysql> CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ENGINE=InnoDB;
mysql> SYSTEM cp /tmp/t1.* data/test/
mysql> ALTER TABLE t1 DISCARD TABLESPACE;
mysql> SYSTEM cp /tmp/t1.* data/test/
mysql> ALTER TABLE t1 IMPORT TABLESPACE;
ERROR 1808 (HY000): Schema mismatch (Table flags don't match, server table has 0x5 and the meta-data file has 0x1)
mysql> DROP TABLE t1;
mysql> SYSTEM rm data/test/t1.*
mysql> CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c INT) ENGINE=InnoDB;
mysql> ALTER TABLE t1 DISCARD TABLESPACE;
mysql> SYSTEM cp /tmp/t1.* data/test/
mysql> ALTER TABLE t1 IMPORT TABLESPACE;
ERROR 1808 (HY000): Schema mismatch (Table flags don't match, server table has 0x6 and the meta-data file has 0x1)

Notice incrementing "flags" number as columns are added.

Suggested fix:
Ideally, the error message should note that the row format is not
compatible. That includes displaying the expected row format and the
row format listed in the metadata file.

diff --git a/storage/innobase/row/row0import.cc b/storage/innobase/row/row0import.cc
index 9dbb2f3..238ab6c 100644
--- a/storage/innobase/row/row0import.cc
+++ b/storage/innobase/row/row0import.cc
@@ -1314,7 +1314,7 @@ row_import::match_schema(
                ib_errf(thd, IB_LOG_LEVEL_ERROR, ER_TABLE_SCHEMA_MISMATCH,
                         "Table flags don't match, server table has 0x%lx"
                         " and the meta-data file has 0x%lx",
-                        (ulong) m_table->n_cols, (ulong) m_flags);
+                        (ulong) m_table->flags, (ulong) m_flags);
 
                return(DB_ERROR);
        } else if (m_table->n_cols != m_n_cols) {
[6 Aug 2016 7:18] Umesh Shastry
Hello Davi,

Thank you for the report and contribution.
Verified as described with 5.7.14 build.

Thanks,
Umesh
[8 Aug 2016 3:57] Laurynas Biveinis
Contributed fixes for bug 76142 also include a fix for this bug
[2 Feb 2018 5:28] Laurynas Biveinis
Uploaded a 8.0.4 fix on bug 76142
[3 Aug 2018 5:55] Laurynas Biveinis
Fixed in https://github.com/mysql/mysql-server/commit/e484ab3 in 5.6+ ?