# Import tablespace after exchange partition, percona server 5.7.26-29

**URL:** <https://forums.percona.com/t/import-tablespace-after-exchange-partition-percona-server-5-7-26-29/22211>\
**Category:** MySQL & MariaDB\
**Created:** [May 20, 2023, 5:23pm UTC](https://forums.percona.com/t/import-tablespace-after-exchange-partition-percona-server-5-7-26-29/22211 "2023-05-20T17:23:20Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![egel](https://avatars.discourse-cdn.com/v4/letter/e/e36b37/32.png) [@egel](https://forums.percona.com/u/egel)\
**Post date:** [May 20, 2023, 5:23pm UTC](https://forums.percona.com/t/import-tablespace-after-exchange-partition-percona-server-5-7-26-29/22211/1 "2023-05-20T17:23:20Z")

</div>

Hello there,

The situation is as follows:

I am attempting to copy a table using the export/import tablespace method. Here are the details of the source and destination servers:

Source server (srcsrv):  
Large table with few hundreds of partitions partitioned\_table1.

Destination server (dstsrv):  
Not patitioned table table1.

The flow:  
on srcsrv:  
sql\> create database db1.  
sql\> use db1  
sql\> create table table1 (source /tmp/table1.sql when table1.sql is actually script that creates srcdb.partitioned\_table1.sql without partitions)  
sql\> alter table srcdb.partitioned\_table1 exchange partition p\_XXX with table db1.table1 without validation;  
unix: cp /var/lib/mysql/db1/table1.ibd /tmp/  
sql\> unlock tables;  
sql\> alter table srcdb.partitioned\_table1 exchange partition p\_XXX with table db1.table1 without validation;

By the end we have all data “back” to srcdb.partitioned\_table1 and db1.table1 is empty.

on destsrv:  
sql\> create database db1.  
sql\> use db1  
sql\> create table table1 (source /tmp/table1.sql when table1.sql is actually script that creates srcdb.partitioned\_table1.sql without partitions)  
sql\> alter table db1.table1 discard tablespace  
unix\> cp /tmp//table1.ibd /var/lib/mysql/db1/table1.ibd  
sql\> alter table db1.table1 import tablespace;  
 → at this point of time server going down with:  
[ERROR] InnoDB: Trying to access page number XXX in space XXX , space name db1/table1, which is outside the tablespace bounds. Byte offset 0, len 8192, i/o type read  
. If you get this error at mysqld startup, please check that your my.cnf matches the ibdata files that you have in the MySQL server.  
unix\> sudo systemctl start mysqld

At this point, the server goes down with the following error:  
[ERROR] InnoDB: Trying to access page number XXX in space XXX, space name db1/table1, which is outside the tablespace bounds. Byte offset 0, len 8192, i/o type read.  
If you encounter this error at mysqld startup, please check that your my.cnf file matches the ibdata files you have in the MySQL server.

After the dstsrv server restarts, db1.table1 is in a healthy state, and the data is imported successfully.

If I take table1.cfg from srcsrv and use it for the import, there are no issues.

According to the documentation, there should be no need for a .cfg file for the import tablespace process, especially in my situation where it is used to speed up the data copying process and there are no chances of having incorrect metadata (since everything is created as part of the process).

Do you have any thoughts or insights on this matter?

Thanks.

---

<div class="post-metadata">

**Author:** ![egel](https://avatars.discourse-cdn.com/v4/letter/e/e36b37/32.png) [@egel](https://forums.percona.com/u/egel)\
**Post date:** [May 24, 2023, 2:47pm UTC](https://forums.percona.com/t/import-tablespace-after-exchange-partition-percona-server-5-7-26-29/22211/2 "2023-05-24T14:47:02Z")

</div>

I see that I forgot to include “flush tables db1.table1 for export” in the code section on source server.  
Although it should be obvious.  
I can’t edit my post but for sure, I am doing flush tables for export.

---

<div class="post-metadata">

**Author:** ![Ivan\_Groenewold](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/ivan_groenewold/32/6299_2.png) [@Ivan\_Groenewold](https://forums.percona.com/u/Ivan_Groenewold)\
**Post date:** [May 29, 2023, 11:42am UTC](https://forums.percona.com/t/import-tablespace-after-exchange-partition-percona-server-5-7-26-29/22211/3 "2023-05-29T11:42:12Z")

</div>

Hi, which version are you using? is it Oracle MySQL or Percona?

---

<div class="post-metadata">

**Author:** ![egel](https://avatars.discourse-cdn.com/v4/letter/e/e36b37/32.png) [@egel](https://forums.percona.com/u/egel)\
**Post date:** [May 30, 2023, 1:46am UTC](https://forums.percona.com/t/import-tablespace-after-exchange-partition-percona-server-5-7-26-29/22211/4 "2023-05-30T01:46:33Z")

</div>

This is in the subject: percona server 5.7.26-29

---

<div class="post-metadata">

**Author:** ![Ivan\_Groenewold](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/ivan_groenewold/32/6299_2.png) [@Ivan\_Groenewold](https://forums.percona.com/u/Ivan_Groenewold)\
**Post date:** [May 30, 2023, 10:52am UTC](https://forums.percona.com/t/import-tablespace-after-exchange-partition-percona-server-5-7-26-29/22211/5 "2023-05-30T10:52:24Z")

</div>

Sorry my bad! I didn’t notice this. Can you paste here SHOW CREATE TABLE for both table1 and partitioned\_table1 ? you can rename any sensitive information.  
It seems you are hitting [https://bugs.mysql.com/bug.php?id=88418](https://bugs.mysql.com/bug.php?id=88418) which unfortunately is not fixed as of now. So you might need to use the cfg file.

---

<div class="post-metadata">

**Author:** ![egel](https://avatars.discourse-cdn.com/v4/letter/e/e36b37/32.png) [@egel](https://forums.percona.com/u/egel)\
**Post date:** [May 30, 2023, 5:18pm UTC](https://forums.percona.com/t/import-tablespace-after-exchange-partition-percona-server-5-7-26-29/22211/6 "2023-05-30T17:18:31Z")

</div>

Sure.  
There is the table (ommited some columns) table1:  
CREATE TABLE `i_t` (  
`autoid` bigint(20) unsigned NOT NULL AUTO\_INCREMENT,  
`u_id` char(36) CHARACTER SET latin1 COLLATE latin1\_bin NOT NULL DEFAULT ‘’,  
`asset_id` char(36) CHARACTER SET latin1 COLLATE latin1\_bin NOT NULL,  
`name` varchar(512) NOT NULL,  
`job` varchar(50) NOT NULL,  
PRIMARY KEY (`autoid`,`job`),  
UNIQUE KEY `u_id_unique` (`u_id`,`job`),  
KEY `asset_id` (`asset_id`),  
KEY `name` (`name`)  
) ENGINE=InnoDB AUTO\_INCREMENT=228418239 DEFAULT CHARSET=latin1 ROW\_FORMAT=COMPRESSED KEY\_BLOCK\_SIZE=8

The partitioned table have ~800 partitions (I’ve ommited all but 3):  
CREATE TABLE `i_t` (  
`autoid` bigint(20) unsigned NOT NULL AUTO\_INCREMENT,  
`u_id` char(36) CHARACTER SET latin1 COLLATE latin1\_bin NOT NULL DEFAULT ‘’,  
`asset_id` char(36) CHARACTER SET latin1 COLLATE latin1\_bin NOT NULL,  
`name` varchar(512) NOT NULL,  
`job` varchar(50) NOT NULL,  
PRIMARY KEY (`autoid`,`job`),  
UNIQUE KEY `u_id_unique` (`u_id`,`job`),  
KEY `asset_id` (`asset_id`),  
KEY `name` (`name`)  
) ENGINE=InnoDB AUTO\_INCREMENT=228418239 DEFAULT CHARSET=latin1 ROW\_FORMAT=COMPRESSED KEY\_BLOCK\_SIZE=8  
PARTITION BY LIST COLUMNS(job)  
(PARTITION p\_800 VALUES IN (‘AAB’) ENGINE = InnoDB,  
PARTITION p\_AAD VALUES IN (‘AAC’) ENGINE = InnoDB,  
PARTITION p\_ABA VALUES IN (‘XXX’) ENGINE = InnoDB)

Thanks

---

<div class="post-metadata">

**Author:** ![egel](https://avatars.discourse-cdn.com/v4/letter/e/e36b37/32.png) [@egel](https://forums.percona.com/u/egel)\
**Post date:** [May 30, 2023, 5:26pm UTC](https://forums.percona.com/t/import-tablespace-after-exchange-partition-percona-server-5-7-26-29/22211/7 "2023-05-30T17:26:38Z")

</div>

I have rewritten the code to utilize a cfg file, and it is currently functioning correctly. Now, my main question is: is it necessary to use a cfg file when performing the same operation on non-partitioned tables? The approach is almost identical for non-partitioned tables as well:

1. Create a new database.
2. Create new tables.
3. Execute the “insert into .” command.
4. Flush the data for export.
5. Copy the ibd files.
6. Import the data into the newly created database and tables.

Please advise if you think I should rewrite the code for non-partitioned tables as well, even though it’s not failing. I want to ensure I address any potential issues proactively.

Thank you.

---

<div class="post-metadata">

**Author:** ![Ivan\_Groenewold](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/ivan_groenewold/32/6299_2.png) [@Ivan\_Groenewold](https://forums.percona.com/u/Ivan_Groenewold)\
**Post date:** [May 30, 2023, 5:28pm UTC](https://forums.percona.com/t/import-tablespace-after-exchange-partition-percona-server-5-7-26-29/22211/8 "2023-05-30T17:28:58Z")

</div>

Thanks, looks like charset and collations are identical which was one of my concerns. As per the comments on the bug I linked, you should probably use the cfg file to avoid any failures.

---

<div class="post-metadata">

**Author:** ![egel](https://avatars.discourse-cdn.com/v4/letter/e/e36b37/32.png) [@egel](https://forums.percona.com/u/egel)\
**Post date:** [May 30, 2023, 5:33pm UTC](https://forums.percona.com/t/import-tablespace-after-exchange-partition-percona-server-5-7-26-29/22211/9 "2023-05-30T17:33:43Z")

</div>

Thanks.  
Then I trhink we perfectly fine and the thread is answered.  
Best Regards  
Evgeni