Where the open source database community meets: Use code PERCONA75 and secure your spot for Percona Live. Register

Downloads

Blog

Contact Us

Recover orphaned InnoDB partition tablespaces in MySQL

October 14, 2014

Author

Jervin Real

MySQL

Share this 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.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

mysql [localhost] {msandbox} (recovery) > SHOW CREATE TABLE t1 G

*************************** 1. row ***************************

Table: t1

Create Table: CREATE TABLE `t1` (

[...]

KEY `h_date` (`h_date`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

/*!50100 PARTITION BY RANGE (year(h_date))

(PARTITION p0 VALUES LESS THAN (2006) ENGINE = InnoDB,

PARTITION p1 VALUES LESS THAN (2010) ENGINE = InnoDB,

PARTITION px VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

1 row in set (0.00 sec)

mysql [localhost] {msandbox} (recovery) > SELECT COUNT(*) FROM t1;

+----------+

| COUNT(*) |

+----------+

| 0 |

+----------+

1 row in set (0.00 sec)

-rw-rw----. 1 revin revin 176M Oct 8 08:41 t1#P#p0.ibd

-rw-rw----. 1 revin revin 612M Oct 8 08:41 t1#P#p1.ibd

-rw-rw----. 1 revin revin 932M Oct 8 08:42 t1#P#px.ibd

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

1

2

3

4

5

6

7

8

9

mysql [localhost] {msandbox} (recovery) > CREATE TABLE t1_t LIKE t1;

Query OK, 0 rows affected (0.02 sec)

mysql [localhost] {msandbox} (recovery) > ALTER TABLE t1_t REMOVE PARTITIONING;

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql [localhost] {msandbox} (recovery) > ALTER TABLE t1_t DISCARD TABLESPACE;

Query OK, 0 rows affected (0.05 sec)

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.

1 2	mysql [localhost] {msandbox} (recovery) > ALTER TABLE t1_t IMPORT TABLESPACE; Query OK, 0 rows affected, 1 warning (7.34 sec)

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

1

2

3

4

5

6

7

8

9

10

mysql [localhost] {msandbox} (recovery) > ALTER TABLE t1 EXCHANGE PARTITION px WITH TABLE t1_t;

Query OK, 0 rows affected (6.42 sec)

mysql [localhost] {msandbox} (recovery) > SELECT COUNT(*) FROM t1;

+----------+

| COUNT(*) |

+----------+

| 8523686 |

+----------+

1 row in set (2.50 sec)

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 .

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

mysql [localhost] {msandbox} (recovery) > SHOW CREATE TABLE t2 G

*************************** 1. row ***************************

Table: t2

Create Table: CREATE TABLE `t2` (

[...]

KEY `h_date` (`h_date`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

/*!50100 PARTITION BY RANGE (year(h_date))

SUBPARTITION BY HASH (u_id)

SUBPARTITIONS 2

(PARTITION p0 VALUES LESS THAN (2006) ENGINE = InnoDB,

PARTITION p1 VALUES LESS THAN (2010) ENGINE = InnoDB,

PARTITION px VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

1 row in set (0.00 sec)

mysql [localhost] {msandbox} (recovery) > SELECT COUNT(*) FROM t2;

+----------+

| COUNT(*) |

+----------+

| 0 |

+----------+

1 row in set (0.94 sec)

-rw-rw----. 1 revin revin 92M Oct 8 08:44 t2#P#p0#SP#p0sp0.ibd

-rw-rw----. 1 revin revin 92M Oct 8 08:44 t2#P#p0#SP#p0sp1.ibd

-rw-rw----. 1 revin revin 304M Oct 8 08:44 t2#P#p1#SP#p1sp0.ibd

-rw-rw----. 1 revin revin 316M Oct 8 08:44 t2#P#p1#SP#p1sp1.ibd

-rw-rw----. 1 revin revin 480M Oct 8 08:45 t2#P#px#SP#pxsp0.ibd

-rw-rw----. 1 revin revin 460M Oct 8 08:45 t2#P#px#SP#pxsp1.ibd

mysql [localhost] {msandbox} (recovery) > CREATE TABLE t2_t LIKE t2;

Query OK, 0 rows affected (0.02 sec)

mysql [localhost] {msandbox} (recovery) > ALTER TABLE t2_t REMOVE PARTITIONING;

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql [localhost] {msandbox} (recovery) > ALTER TABLE t2_t DISCARD TABLESPACE;

Query OK, 0 rows affected (0.04 sec)

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.

1

2

3

4

5

6

7

8

9

10

11

12

13

mysql [localhost] {msandbox} (recovery) > ALTER TABLE t2_t IMPORT TABLESPACE;

Query OK, 0 rows affected, 1 warning (2.49 sec)

mysql [localhost] {msandbox} (recovery) > ALTER TABLE t2 EXCHANGE PARTITION pxsp1 WITH TABLE t2_t;

Query OK, 0 rows affected (3.11 sec)

mysql [localhost] {msandbox} (recovery) > SELECT COUNT(*) FROM t2;

+----------+

| COUNT(*) |

+----------+

| 4546036 |

+----------+

1 row in set (0.94 sec)

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! 🙂

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

mysql [localhost] {msandbox} (test) > SELECT COUNT(*) FROM t2;

+----------+

| COUNT(*) |

+----------+

| 0 |

+----------+

1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > ALTER TABLE t2 DISCARD TABLESPACE;

Query OK, 0 rows affected (0.02 sec)

mysql [localhost] {msandbox} (test) > ! cp -v /sbx/msb/msb_5_6_210/data/test/t2#P#* /sbx/msb/msb_5_7_5/data/test/

`/sbx/msb/msb_5_6_210/data/test/t2#P#p0#SP#p0sp0.ibd' -> `/sbx/msb/msb_5_7_5/data/test/t2#P#p0#SP#p0sp0.ibd'

`/sbx/msb/msb_5_6_210/data/test/t2#P#p0#SP#p0sp1.ibd' -> `/sbx/msb/msb_5_7_5/data/test/t2#P#p0#SP#p0sp1.ibd'

`/sbx/msb/msb_5_6_210/data/test/t2#P#p1#SP#p1sp0.ibd' -> `/sbx/msb/msb_5_7_5/data/test/t2#P#p1#SP#p1sp0.ibd'

`/sbx/msb/msb_5_6_210/data/test/t2#P#p1#SP#p1sp1.ibd' -> `/sbx/msb/msb_5_7_5/data/test/t2#P#p1#SP#p1sp1.ibd'

`/sbx/msb/msb_5_6_210/data/test/t2#P#px#SP#pxsp0.ibd' -> `/sbx/msb/msb_5_7_5/data/test/t2#P#px#SP#pxsp0.ibd'

`/sbx/msb/msb_5_6_210/data/test/t2#P#px#SP#pxsp1.ibd' -> `/sbx/msb/msb_5_7_5/data/test/t2#P#px#SP#pxsp1.ibd'

mysql [localhost] {msandbox} (test) > ALTER TABLE t2 IMPORT TABLESPACE;

Query OK, 0 rows affected, 6 warnings (11.36 sec)

mysql [localhost] {msandbox} (test) > SHOW WARNINGS G

*************************** 1. row ***************************

Level: Warning

Code: 1810

Message: InnoDB: IO Read error: (2, No such file or directory) Error opening './test/t2#P#p0#SP#p0sp0.cfg', will attempt to import without schema verification

[...]

0 0 votes

Article Rating

Subscribe

3 Comments

Oldest

Newest Most Voted

Inline Feedbacks

View all comments

Simon J Mudd

11 years ago

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.

0

Reply

[email protected]

8 years ago

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.

0

Reply

viiin

8 years ago

‘@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.

0

Reply