Transporting tablespace from MySQL 5.6 to MySQL 5.7 (case study)

MySQL 5.7Recently, 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:

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

Then took the backup for the nil database and exported it.

Now on MySQL 5.7, I create a nil table, discarded tablespace, copied the .cfg and .ibd files from backup to the datadir, and set proper permissions.

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.

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.

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 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 bug report to MySQL for this error and inappropriate error message.  https://bugs.mysql.com/bug.php?id=79469

BTW: here 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

Share this post

Comments (4)

  • 李秀丽 Reply

    cool !thank you !

    December 28, 2015 at 2:59 am
  • dathanvp Reply

    Bug also happens on 5.6 to 5.6. Really annoying. Also tracking down a crash bug on partial imports. You can’t drop the database or the tables and must remove the directory from your data dir – otherwise you get a crash.

    August 24, 2016 at 8:50 pm
  • swen Reply

    I got this problem too, resolve it According to your method. thks.

    November 2, 2016 at 10:47 pm
  • roninjoe Reply

    Very nice, saved me from having to mysqldump/load very large tables. Thanks!

    July 26, 2017 at 9:06 am

Leave a Reply