I am using Percona-Server 8.0.x and I am following percona xtraback documentation.
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. [LIST=1]
[]Use xtraback to export the selected databases.
[]Prepare the backup
[]On the destination server, discard tablespace ALTER TABLE db.table_name DISCARD TABLESPACE
[]Move .ibd and .cfg file to @@datadir/database/ with proper permission
[*]Import the table space. ALTER TABLE db.table_name IMPORT TABLESPACE
[/LIST] Error I am getting is following.
Schema mismatch (Column 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.
[url]mysql-server/row0import.cc at 91a17cedb1ee880fe7915fb14cfd74c04e8d6588 · mysql/mysql-server · GitHub
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.