Restoring Partial Backups

MySQL & MariaDB Percona XtraBackup
1

Hi
I am testing partial incremental backups with XtraBackup. All goes well until I try to run ‘ALTER TABLE datbase.table IMPORT TABLESPACE’ for a table that has a decimal column. When I run this sql command for a table with a decimal column I receive the error ‘Error Code: 1808. Schema mismatch (Column columnName precise type mismatch.)’ where columnName is a decimal column.
If a table does not have a decimal column I do not run into any problems

I am running
mysq: Version: 8.0.16
xtrabackup: 8.0.6
OS: CentOS Linux release 7.6.1810

The steps I followed are listed below

A) Full/incremental backups for database ‘dbname’
xtrabackup --user=xxx --host=localhost --password=123 --backup --databases=‘dbname’ --target-dir=/backups/partial/mysql
xtrabackup --user=xxx --host=localhost --password=123 --backup --databases=‘dbname’ --target-dir=/backups/partial/inc001/ --incremental-basedir=/backups/partial/mysql
xtrabackup --user=xxx–host=localhost --password=123 --backup --databases=‘dbname’ --target-dir=/backups/partial/inc002/ --incremental-basedir=/backups/partial/inc001

B) Preparation
xtrabackup --prepare --apply-log-only --export --target-dir=/backups/partial/mysql
xtrabackup --prepare --apply-log-only --export --target-dir=/backups/partial/mysql --incremental-dir=/backups/partial/inc001
xtrabackup --prepare --apply-log-only --export --target-dir=/backups/partial/mysql --incremental-dir=/backups/partial/inc002
xtrabackup --prepare --export --target-dir=/backups/partial/mysql

C) Restore to same database on same server
i) Delete and recreate all tables in the database as before
ii) ‘ALTER TABLE database.table DISCARD TABLESPACE’ for all tables
iii) Copy the exported files (.ibd and .cfg) from ‘/backups/partial/mysql’ to the subdirectory of the destination server’s data directory (and change owner/group to mysql)
iv) ‘ALTER TABLE database.table IMPORT TABLESPACE’ - which runs into the issue for tables with columns with decimal values

I am wondering if anybody else has run into this issue.

Thanks

2

Have you managed to sort this one out? I m getting into issue

3

Please provide table definition here so we can test.


@iftaquar - please share the same, include exact mysql version and xtrabackup version used as well.

4

I’m having the same issue. Testing for it is trivially easy.

  1. Create a new database and simple table with a date column:
    create database test1;
    create table date_test (val date default null);
    SHOW CREATE TABLE date_test\G

  2. Perform a partial dump with xtrabackup:
    xtrabackup --user=root --password=XXX --databases=test1 --backup --target-dir=/backups/partial
    xtrabackup --user=root --password=XXX --databases=test1 --prepare --export --apply-log-only --target-dir=/backups/partial
  3. ALTER TABLE test1.date_test DISCARD TABLESPACE;
  4. Copy date_test.ibd and date_test.cfg back into the data directory and fix ownership/permissions.
  5. ALTER TABLE test1.date_test IMPORT TABLESPACE;
    ERROR 1808 (HY000): Schema mismatch (Column val precise type mismatch.)



5

I apologize for the double comment…the system was acting odd.Also, here are the software versions i was using: MySQL 8.0.19 and xtrabackup 8.0.11

6

Looks like a bug since I can reproduce the same error. Please open a JIRA ticket under the PXB project.

7

Actually there’s a bug reported already - [PXB-2132] Error: "Schema mismatch (Column date precise type mismatch.)" during a partial backup import - Percona JIRA