EmergencyEMERGENCY? Get 24/7 Help Now!

Recover orphaned InnoDB partition tablespaces in MySQL

 | October 14, 2014 |  Posted In: InnoDB, MySQL

PREVIOUS POST
NEXT POST

A few months back, Michael wrote about reconnecting orphaned *.ibd files using MySQL 5.6. I will show you the same procedure, this time for partitioned tables. An InnoDB partition is also a self-contained tablespace in itself so you can use the same method described in the previous post.

To begin with, I have an example table with a few orphaned partitions and we will reconnect each partition one by one to the original table.

The first step is to create a dummy table and remove partitioning so that we can reattach individual partitions to this table.


Once the tablespace for our dummy table has been discarded, we copy  one of the partitions to take the place of the dummy table’s tablespace. For example, we copy  t1#P#p0.ibd  as  t1_t.ibd  into the MySQL data directory, of course taking into account the permissions afterward. The next step is to import the tablespace to the dummy table.


And for the secret sauce, we will exchange our dummy table recently imported tablespace to replace the target partition in our original table.


You can do the same with subpartitions, too! Here’s my slightly different table with subpartitions where I reconnect one of the orphaned tablespaces t2#P#px#SP#pxsp1.ibd .


Once again, after copying  t2#P#px#SP#pxsp1.ibd  to replace  t2_t.ibd  in the MySQL data directory and setting the correct permissions, we can import it into our dummy table and exchange the same to our original table. In this case, on a table with subpartitions, a tablespace is a combined unit of partition and subpartition, hence in our partition name above P#px is our main partition and SP#pxsp1 the subpartition name. For our exchange command below, we will only need the later portion.


But wait there’s more: Do you know that in MySQL 5.7, you can take the full shortcut? Directly import all partitions back to the original table, sweet! A quick example below on MySQL 5.7.5, I created the same t2 table above, DISCARD TABLESPACE , copy the partition tablespaces from my test 5.6 instance and IMPORT TABLESPACE  And done! 🙂

 

PREVIOUS POST
NEXT POST
Jervin Real

As Senior Consultant, Jervin partners with Percona’s customers on building reliable and highly performant MySQL infrastructures while also doing other fun stuff like watching cat videos on the internet. Jervin joined Percona in Apr 2010.

3 Comments

  • One of the things which people don’t really realise is that the process of importing a tablespace requires rewriting every single page in that tablespace with a new space id. That’s very heavy and time consuming for any reasonably large table. The examples shown here are small (a few hundred MB) tables so it may not be so obvious to people that this process may take a lot more time than you think.

    So transportable tables are good but this extra I/O and processing to import them is a real overhead which compared to say MyISAM tables which can be “rsynced from one place to another easily” makes it much less convenient than it would otherwise be.

    I still think the exchange partition with the required create syntax is ugly, and would love to see something a bit more convenient so that moving data from one server to another could be easier and faster.

  • the document is for recovering the orphaned tables. What about if i want to get rid of these orphaned tables? i can’t find a good documentation about removing the orphaned tables.

    I have tried the create/drop/move but didn’t succeed. If i try just deleting the files from /data/mysql/$db, though the DB is up, it’s complaining that there are some tablespace missing. Also tried ‘create table #sql-ibxxxxx then drop it, but still showing in information_schema db.

  • @Fedora, I would assume you already have tried the MariaDB published technique to do this – have you posted this problem on our forums? If the ibd and frm exists but not recognized, perhaps an IMPORT TABLESPACE might do something about it – otherwuse a full dump and reload might be the only clean way to remove the orphaned record from the data dictionary.

Leave a Reply