Transporting Tablespace from MySQL 5.6 to MySQL 5.7

Recently, I was working on a MySQL support ticket where a customer was facing an issue while transporting tablespace from MySQL 5.6 to MySQL 5.7.

After closely reviewing the situation, I saw that while importing tablespace they were getting errors such as:

ERROR 1808 (HY000): Schema mismatch (Table flags don't match, server table has 0x10 and the meta-data file has 0x1)

After some research, I found that there is a similar bug reported to MySQL for this issue (https://bugs.mysql.com/bug.php?id=76142), but no solution is mentioned. I tested the scenario locally and found a solution that I will detail in this post.

First, I created a table on MySQL 5.6

nilnandan.joshi@bm-support01:~$ mysql -uroot -p --socket=/tmp/mysql_sandbox5624.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 35
Server version: 5.6.24 MySQL Community Server (GPL)
...
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 I took the backup for the nil database and exported 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 Backup created in directory '/home/njoshi/backup//2015-11-27_01-29-14'
151127 01:29:16 [00] Writing backup-my.cnf
151127 01:29:16 [00] ...done
151127 01:29:16 [00] Writing xtrabackup_info
151127 01:29:16 [00] ...done
xtrabackup: Transaction log of lsn (1695477) to (1695477) was copied.
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
...
xtrabackup: export option is specified.
xtrabackup: export metadata of table 'nil/nil' to file `./nil/nil.exp` (1 indexes)
xtrabackup: name=GEN_CLUST_INDEX, id.low=31, page=3
...
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1706518
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, I created a nil table, discarded tablespace, copied the .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:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 16
Server 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)

After all that, I got the same error but I didn’t find any specific error message about the problem. When I deleted the .cfg file and tried again, I got the exact error message.

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

So, I dropped the table on MySQL 5.7, created the same table with the  “row_format=compact” option, copied both .cfg and .ibd files again, and this time it worked.

mysql> drop table nil;
Query OK, 0 rows affected (0.00 sec)
mysql> create table nil(id int, name varchar(10)) row_format=compact;
Query OK, 0 rows affected (0.00 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.ibd .
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 .
mysql> ALTER TABLE nil IMPORT TABLESPACE;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from nil;
+------+-----------+
| id | name |
+------+-----------+
| 1 | nilnandan |
| 2 | niljoshi |
| 3 | njoshi |
| 4 | joshi |
+------+-----------+
4 rows in set (0.01 sec)

I’m not sure if the same issue occurs each time, but one thing is sure: removing the .cfg file gives you the exact cause of the problem and lets you resolve it.

Why does moving tablespace from MySQL 5.6 to MySQL 5.7 give you this error? The answer is because the default innodb_file_format is changed in MySQL 5.7 from Antelope to Barracuda.

Important Change; InnoDB: The following changes were made to  InnoDB configuration parameter default values:

• The innodb_file_format default value was changed to Barracuda. The previous default value was Antelope. This change allows tables to use Compressed or Dynamic row formats.

https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-7.html

I’ve already submitted a bug report to MySQL for this error and inappropriate error message. https://bugs.mysql.com/bug.php?id=79469

By the way, here is the the link for how we can transport tablespace with Xtrabackup.
https://www.percona.com/doc/percona-xtrabackup/2.3/howtos/recipes_ibkx_partition.html