DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports
Events Video Library
Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
View Events Video Library
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Integrating PostgreSQL Databases with ANF: Join this workshop to learn how to create a PostgreSQL server using Instaclustr’s managed service

Mobile Database Essentials: Assess data needs, storage requirements, and more when leveraging databases for cloud and edge applications.

Monitoring and Observability for LLMs: Datadog and Google Cloud discuss how to achieve optimal AI model performance.

Automated Testing: The latest on architecture, TDD, and the benefits of AI and low-code tools.

Related

  • MySQL Multi-Source Replication
  • An In-Depth Look at Oracle MySQL HeatWave
  • SQL Query Performance Tuning in MySQL
  • Kubernetes Evolution: Transitioning from etcd to Distributed SQL

Trending

  • Top 7 Best Practices DevSecOps Team Must Implement in the CI/CD Process
  • Auto-Scaling DynamoDB Streams Applications on Kubernetes
  • Essential Complexity Is the Developer's Unique Selling Point
  • LTS JDK 21 Features
  1. DZone
  2. Data Engineering
  3. Databases
  4. Transporting Tablespace from MySQL 5.6 to MySQL 5.7

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 and came across something interesting...

Peter Zaitsev user avatar by
Peter Zaitsev
·
Dec. 15, 15 · Tutorial
Like (3)
Save
Tweet
Share
5.28K Views

Join the DZone community and get the full member experience.

Join For Free

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

MySQL

Published at DZone with permission of Peter Zaitsev, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • MySQL Multi-Source Replication
  • An In-Depth Look at Oracle MySQL HeatWave
  • SQL Query Performance Tuning in MySQL
  • Kubernetes Evolution: Transitioning from etcd to Distributed SQL

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends: