Copying MySQL Tablespaces from 5.6 to 5.7

TL;DR: Watch out for the ROW_FORMAT of your source and destination tables when using transportable tablespaces. The warning is hidden at the bottom of this guide, but the error message given by MySQL is not helpful at all.

Note: It looks like Nilnandan Joshi ran into the same problem, and solved it in the same way (even down to throwing out that .cfg file) a week ago. He also wrote about it much better than I could have. Check out his post here.

I stay up late the night before a MySQL GA release so I can be the first in line to do everything the documentation tells you not to. This time, it’s got to do with transportable tablespaces.

Transportable tablespaces are a “really rad” way to move data between MySQL instances. The premise is that you create an empty table on the importing instance matching the definition of the table you’d like to import and discard its tablespace. On the exporting instance you FLUSH … FOR EXPORT a file-per-table table, which outputs a .cnf file that has some metadata and quiesces the .ibd (data) file. Copy those suckers to the importing instance’s datadir and you’re ready to load them up and have a clean copy of the table without using innobackupex or waiting for mysqldump and its ilk.

You may want to copy data from one instance to another to update the major release of logical dataset without any downtime. Unfortunately the gods have conspired against you: transportable tablespaces are only officialy supported within the same GA version.

Despite it being unsupported, let’s give it a shot.

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

Not gone well.

Good news— it’s not as bad as it seems. Back when this was all really, really unsupported (5.5 -> 5.6), you didn’t even get a config file telling the importing instance what’s going on with the .ibd file it’s looking at. When you tried to import the tablespace it would still work if the schemas were the same. If we delete the TABLENAME.cfg (DON’T)when importing we get a slightly more interesting error:

ERROR 1808 (HY000): Schema mismatch (Table has ROW_TYPE_DYNAMIC row format, .ibd file has ROW_TYPE_COMPACT row format.)

Suprise! As a bonus it deletes the tablespace you were trying to import, so be careful out there.

Now we’re getting somewhere. It looks like the default ROW_FORMAT changed in MySQL 5.7.9. That matters because we need the table definitions to match perfectly. SHOW CREATE TABLE doesn’t show the ROW_FORMAT, so you have to be careful to mimic it when creating the destination tables.

So if SHOW CREATE TABLE on 5.6 returns:

CREATE TABLE `foo` ( . . . ) ENGINE=InnoDB

When trying to create a table with the same definition on 5.7, you’ll need to change it to:

CREATE TABLE `foo` (. . .) ENGINE=InnoDB ROW_FORMAT=compact

After this it seems to work upon cursory examination:

Query OK, 0 rows affected (0.07 sec)
Query OK, 0 rows affected (2.19 sec)
+ — — — — — +
| count(*) |
+ — — — — — +
| 10000 |
+ — — — — — +
1 row in set (0.19 sec)

A cursory examination is good enough for me to promote this little guy to the single point of failure in my cigarette sharing-economy startup, so I’m set. Use this new table in your cigarette sharing-economy startup at your own risk. Remember: this isn’t supported, and though I don’t see any issues it will likely fail at an infinitely devious time in the indeterminate future.

PS: this is not nearly as much fun as trying to transport tablespaces from 5.5 to 5.6 which involved breaking out xxd and editing some really special bytes in the table’s .frm (definition) file (look for “Due to the temporal type changes…”) Oh, and you’d need to do that once for every partition on partitioned tables. And don’t even get me started on transporting partitioned tables.

PPS: It even seems to work with partitioned tables. Support for transporting partitioned tables is one of my favorite features of MySQL 5.7. Engage with me on social media and tell me about yours. All of my best friends are brands.