Not the answer you need?
Register and ask your own question!

Tablespace import issue with the database exported using percona xtrabackup

jspateljspatel EntrantCurrent User Role Contributor
I am using Percona-Server 8.0.x and I am following percona xtraback documentation.

https://www.percona.com/doc/percona-xtrabackup/8.0/xtrabackup_bin/restoring_individual_tables.html

I am exporting all tables in one or more databases from a single server. I am trying to restore them on a different server by using discard and import tablespace query. However the import works for some tables (not containing the certain column type) but not for many others. It fails exactly on particular type of data column type.

Steps.
  1. Use xtraback to export the selected databases.
  2. Prepare the backup
  3. On the destination server, discard tablespace ALTER TABLE db.table_name DISCARD TABLESPACE
  4. Move .ibd and .cfg file to @@datadir/database/ with proper permission
  5. Import the table space. ALTER TABLE db.table_name IMPORT TABLESPACE
Error I am getting is following.

Schema mismatch (Column <colname> precise type mismatch.)

Here in all the failures I am facing are related to TEXT or LONGTEXT datatype. The original schema were created in MySQL 5.5. era. Also I am running percona-server with non strict SQL mode sql_mode=''

So I scanned the MySQL source code and I found this are where it throws the error.
// Check if the table schema that was read from the .cfg file matches the in memory table definition.
https://github.com/mysql/mysql-server/blob/91a17cedb1ee880fe7915fb14cfd74c04e8d6588/storage/innobase/row/row0import.cc#L1192

So I performed TABLESPACE export and IMPORT using MySQL prompt. That works fine.
One thing I noticed that cfg file generated using xtrabackp and MySQL are different in size. Because cfg is a binary file and I don't have the structure information, I couldn't exactly tell which bytes are causing the mismatch and IMPORT to break on MySQL side when the data is exported using Percona Xtrabackp.

Do you have any workaround for this? This is a complete blocker for the work I am currently doing.

Comments

Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.