Safe to import backup without .cfg?

MySQL & MariaDB Percona XtraBackup
1

Hello,

After a backup of a MySQL 8.0.28 DB with Xtrabackup 8.0.28 when I try to import the entire DB it works fine but when I try to import individual tables it will sometimes fail with metadata mismatch errors such as :

ERROR 1808 (HY000) at line 132: Schema mismatch (Index search_idx field site_name is ascending which does not match metadata file which is descending)

I couldn’t really find any solution to this. The schema is exactly the same as I’m trying to import an individual table AFTER importing same entire backup so it’s literally the same data.

So my only solution to import my .idb without the .cfg. The official Oracle documentation seems to suggest it’s fine as long as you’re sure the schema matches the incoming data but I’d like to know if that’s really OK to do so or if i’m shooting myself in the foot?

Thanks.

1 Like
2

I’m curious to know if you first rebuilt the table ALTER TABLE XX ENGINE=InnoDB, then took the backup and tried to restore/import, would that help. MySQL 8 introduced descending indexes and that error message seems like the default assumption is descending index if metadata is not present.

1 Like
3

Rebuilt the table :

ALTER TABLE mydb.mytable ENGINE=InnoDB
2489 row(s) affected Records: 2489  Duplicates: 0  Warnings: 0	6.298 sec

Then ran a partial backup on this table :

xtrabackup --backup --datadir=/mysql --target-dir=/partial-backup --tables="^mydb[.]mytable"

Then tried to import using :

SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE `mydb`.`mytable` DISCARD TABLESPACE;
\! echo "copying mydb.mytable"
\! cp /partial-test/mydb/mytable.* "/mysql/mydb/"
\! chown mysql:mysql /mysql/mydb/mytable.*
ALTER TABLE `mydb`.`mytable` IMPORT TABLESPACE;
SET FOREIGN_KEY_CHECKS=1;

But I still get the same mismatch error when importing :

# mysql -u root -p < import.sql
copying mydb.mytable
ERROR 1808 (HY000) at line 6: Schema mismatch (Index search_idx field site_name is ascending which does not match metadata file which is descending)
1 Like
4

Hi,

Have you run xtrabackup --prepare --export --target-dir=/partial-backup prior to attempt to import it ?

1 Like
5

Sorry I forgot to include it but yes I did prepare the backup with --export before importing

1 Like
6

Are you sure you are using PXB 8.0.28?

In version 8.0.27 we have extended the .cfg format to support new version formats - [PXB-2543] Add IB_EXPORT_CFG_VERSION_V6 for exporting cfg file for a table - Percona JIRA

Also, I have created a test run for PXB 8.0.28 and I confirm I can import tables with ASCENDING and DESCENDING indexes - https://forums.percona.com/t/safe-to-import-backup-without-cfg/15931/4 · GitHub

Using /work/pxb/ins/8.0/xtrabackup-test/test/var as test root
Detecting server version...
Running against Percona Server 8.0.28-19 (XtraDB 8.0.28-19)
Using 'xtrabackup' as xtrabackup binary
Autodetected number of cores: 32
Limiting to 16 to avoid excessive resource consumption
Using 16 parallel workers

==============================================================================
TEST                                   WORKER    RESULT     TIME(s) or COMMENT
------------------------------------------------------------------------------
descending                               w1	[passed]    21
==============================================================================
Spent 21 of 30 seconds executing testcases

SUMMARY: 1 run, 1 successful, 0 skipped, 0 failed

See results/ for detailed output

Can you share your table definition to see if we can reproduce it ?

EDIT: Alternativelly, create you table with search_idx(site_name DESC) and you should be able to import it. Seems like you have a mismatch on table definition between the table at the time the backup was taken versun the Definition of the server you want to import it.

1 Like
7 
$ xtrabackup --version
xtrabackup version 8.0.28-20 based on MySQL server 8.0.28 Linux (x86_64) (revision id: 4cc3081873d)

$ mysqld --version
/usr/sbin/mysqld  Ver 8.0.28 for Linux on x86_64 (MySQL Community Server - GPL)

I’ve run some more tests this time importing entire databases but each table individually and It seems the issue occurs every time a full-text index is involved

I manage to consistently reproduce the issue with the following basic table definition :

CREATE TABLE `informations` (
  `information_id` int NOT NULL AUTO_INCREMENT,
  `site_name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`information_id`),
  FULLTEXT KEY `search_idx` (`site_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

EDIT : simplified the definition even further

The Oracle documentation seems to suggest creating a DESCENDING full-text index isn’t even possible and trying to ALTER one in workbench doesn’t work either so it seems odd the metadata would contain a descending index ? MySQL :: MySQL 8.0 Reference Manual :: 15.6.2.4 InnoDB Full-Text Indexes

1 Like
8

Here’s an archive with a prepared (--export) .idb and .cfg files along with the create statement and import script. I just tried spinning up a docker container with mysql:8.0.28 and I get the same behavior.

1 Like
9

@tenseraven this seems like a bug since you can consistently reproduce it.
Would you mind raising a bug so we can start to validate and prioritize it?

https://jira.percona.com/projects/PXB

Thanks in advance.

1 Like
10

Thank you for your reply, I created an issue here [PXB-2797] Schema mismatch when importing table with full-text index from xtrabackup backup - Percona JIRA

1 Like