Import tablespace after exchange partition, percona server 5.7.26-29

MySQL & MariaDB
1

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.

2

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.

3

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

4

This is in the subject: percona server 5.7.26-29

5

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 which unfortunately is not fixed as of now. So you might need to use the cfg file.

6

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

7

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.

8

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.

9

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