Bug #79469 Getting inappropriate error while transport table from MySQL 5.6 to MySQL 5.7
Submitted: 1 Dec 2015 5:43 Modified: 1 Dec 2015 11:25
Reporter: Nilnandan Joshi Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.7, 5.7.9 OS:Any
Assigned to: CPU Architecture:Any
Tags: ALTER TABLE, IMPORT TABLESPACE, MySQL 5.7

[1 Dec 2015 5:43] Nilnandan Joshi
Description:
While transport tablespace from MySQL 5.6 to MySQL 5.7, I got this error. 

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

After checking code, found that there is some mistake. It should print table->flags and not table->n_cols

if (m_flags != m_table->flags) {
               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);

Also, just printing 0x1, doesn't ideally make sense for end-user. Here I think, error message should be improved and decode these flags while printing. like if 0x5 or 0X1 corresponds to ROW_FORMAT_COMPACT then it should say something like ... Table flags don't match, server table has ROW_FORMAT_DYNAMIC and metadata file has ROW_FORMAT_COMPACT ...

It gives actual error when we removed .cfg file. 

mysql> ALTER TABLE nil IMPORT TABLESPACE;
ERROR 1808 (HY000): Schema mismatch (Table has ROW_TYPE_DYNAMIC row format, .ibd file has ROW_TYPE_COMPACT row format.)

But still It would be great help if constant are replaced with more meaningful values at the first stage. 

How to repeat:
mysql> create database nil;
Query OK, 1 row affected (0.02 sec)
mysql> use nil;
Database changed
mysql> create table nil(id int, name varchar(10));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into nil values (1, 'nilnandan'),(2, 'niljoshi'),(3, 'njoshi'),(4,'joshi');
Query OK, 4 rows affected (0.10 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from nil;
+------+-----------+
| id | name |
+------+-----------+
| 1 | nilnandan |
| 2 | niljoshi |
| 3 | njoshi |
| 4 | joshi |
+------+-----------+
4 rows in set (0.02 sec)
Then take the backup for nil database and export it.
nilnandan.joshi@bm-support01:~$ innobackupex --defaults-file=/home/njoshi/sandboxes/msb_5_6_24/my.sandbox.cnf --user=root --password=msandbox --databases="nil" /home/njoshi/backup/
151127 01:29:14 innobackupex: Starting the backup operation
....

151127 01:29:16 completed OK!
nilnandan.joshi@bm-support01:~$
nilnandan.joshi@bm-support01:~$ innobackupex --apply-log --export backup/2015-11-27_01-29-14
151127 01:32:25 innobackupex: Starting the apply-log operation
...
151127 01:32:28 completed OK!
nilnandan.joshi@bm-support01:~$ 
nilnandan.joshi@bm-support01:~/backup/2015-11-27_01-29-14/nil$ ll
total 140
drwx------ 2 nilnandan.joshi percona 4096 Nov 27 01:32 ./
drwx------ 3 nilnandan.joshi percona 4096 Nov 27 01:32 ../
-rw-r----- 1 nilnandan.joshi percona 65 Nov 27 01:29 db.opt
-rw-r--r-- 1 nilnandan.joshi percona 421 Nov 27 01:32 nil.cfg
-rw-r--r-- 1 nilnandan.joshi percona 16384 Nov 27 01:32 nil.exp
-rw-r----- 1 nilnandan.joshi percona 8586 Nov 27 01:29 nil.frm
-rw-r----- 1 nilnandan.joshi percona 98304 Nov 27 01:29 nil.ibd
nilnandan.joshi@bm-support01:~/backup/2015-11-27_01-29-14/nil$

Now on MySQL 5.7, create nil table, discard tablespace, copy .cfg and .ibd files from backup to the datadir and set proper permissions.

nilnandan.joshi@bm-support01:~/backup/2015-11-27_01-29-14/nil$ mysql -uroot -p --socket=/tmp/mysql_sandbox13454.sock
Enter password: 
ver version: 5.7.9-log MySQL Community Server (GPL)
...
mysql> create database nil;
Query OK, 1 row affected (0.04 sec)
mysql> use nil
Database changed
mysql> create table nil(id int, name varchar(10));
Query OK, 0 rows affected (0.04 sec)
mysql> ALTER TABLE nil DISCARD TABLESPACE;
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
nilnandan.joshi@bm-support01:/home/nilnandan.joshi/sandboxes/rsandbox_mysql-5_7_9/master/data/nil$ sudo cp /home/njoshi/backup/2015-11-27_01-29-14/nil/nil.cfg .
nilnandan.joshi@bm-support01:/home/nilnandan.joshi/sandboxes/rsandbox_mysql-5_7_9/master/data/nil$ sudo cp /home/njoshi/backup/2015-11-27_01-29-14/nil/nil.ibd .
nilnandan.joshi@bm-support01:/home/nilnandan.joshi/sandboxes/rsandbox_mysql-5_7_9/master/data/nil$ 
nilnandan.joshi@bm-support01:/home/nilnandan.joshi/sandboxes/rsandbox_mysql-5_7_9/master/data/nil$ mysql -uroot -p --socket=/tmp/mysql_sandbox13454.sock
Enter password: 
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 17
Server version: 5.7.9-log MySQL Community Server (GPL)
...
mysql> use nil
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------+
| Tables_in_nil |
+---------------+
| nil |
+---------------+
1 row in set (0.00 sec)
mysql> ALTER TABLE nil IMPORT TABLESPACE;
ERROR 1808 (HY000): Schema mismatch (Table flags don't match, server table has 0x5 and the meta-data file has 0x1)

Suggested fix:
Create table with row_format=compact on MySQL 5.7 and then import tablespace. 

create table nil(id int, name varchar(10)) row_format=compact;
[1 Dec 2015 11:25] Umesh Shastry
Hello Nilnandan,

Thank you for the report.
Imho this is reproducible just even with both source/target with 5.7.9.
Per manual, when you have diff ROW_FORMAT on source and target instances then this could result in a schema mismatch error. But, I'm verifying it for the clear/meaningful error message.

Thanks,
Umesh
[1 Dec 2015 11:26] Umesh Shastry
// Source Server - create table with ROW_FORMAT=COMPACT 

[umshastr@hod03]/export/umesh/server/binaries/mysql-5.7.9: bin/mysql  -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.9 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql> show variables like 'innodb_default_row_format';
+---------------------------+---------+
| Variable_name             | Value   |
+---------------------------+---------+
| innodb_default_row_format | dynamic |
+---------------------------+---------+
1 row in set (0.00 sec)

mysql> CREATE TABLE t(c1 INT) engine=InnoDB row_format=compact;
Query OK, 0 rows affected (0.01 sec)

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `c1` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT
1 row in set (0.01 sec)

mysql> FLUSH TABLES t FOR EXPORT;
Query OK, 0 rows affected (0.00 sec)

mysql> \! ls -l /export/umesh/server/binaries/mysql-5.7.9/79469/test
total 116
-rw-r----- 1 umshastr common    65 Dec  1 12:06 db.opt
-rw-r----- 1 umshastr common   355 Dec  1 12:08 t.cfg
-rw-r----- 1 umshastr common  8556 Dec  1 12:08 t.frm
-rw-r----- 1 umshastr common 98304 Dec  1 12:08 t.ibd
mysql> \! cp /export/umesh/server/binaries/mysql-5.7.9/79469/test/t.{ibd,cfg} /tmp/
mysql> \! ls -l /tmp/t.{ibd,cfg}
-rw-r----- 1 umshastr common   355 Dec  1 12:09 /tmp/t.cfg
-rw-r----- 1 umshastr common 98304 Dec  1 12:09 /tmp/t.ibd
mysql>

-- target server 
-- create table and leave ROW_FORMAT for default

[umshastr@hod03]/export/umesh/server/binaries/mysql-5.7.9: bin/mysql  -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.9 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed
mysql> CREATE TABLE t(c1 INT) engine=InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE t DISCARD TABLESPACE;
Query OK, 0 rows affected (0.01 sec)

mysql> \! cp /tmp/t.{ibd,cfg} /export/umesh/server/binaries/mysql-5.7.9/79469/test/
mysql>
mysql>  \! ls -l /export/umesh/server/binaries/mysql-5.7.9/79469/test
total 116
-rw-r----- 1 umshastr common    65 Dec  1 12:11 db.opt
-rw-r----- 1 umshastr common   355 Dec  1 12:13 t.cfg
-rw-r----- 1 umshastr common  8556 Dec  1 12:11 t.frm
-rw-r----- 1 umshastr common 98304 Dec  1 12:13 t.ibd
mysql>
mysql> ALTER TABLE t IMPORT TABLESPACE;
ERROR 1808 (HY000): Schema mismatch (Table flags don't match, server table has 0x4 and the meta-data file has 0x1)
mysql>
[8 Jan 2016 11:49] Umesh Shastry
Bug #79888 marked as duplicate of this
[20 Jan 2016 7:55] Daniƫl van Eeden
Related:
Bug #80057 	View for InnoDB Flags
[27 Jun 2017 5:15] Umesh Shastry
Bug#86824 marked as duplicate of this one