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

Restoring Partial Backups

DibbleDibble EntrantCurrent User Role Contributor
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.



  • iftaquariftaquar Current User Role Novice
    Have you managed to sort this one out? I m getting into issue
  • jriverajrivera Percona Support Engineer Percona Staff Role
    Please provide table definition here so we can test.

    @iftaquar - please share the same, include exact mysql version and xtrabackup version used as well.
  • jasonhjasonh Current User Role Novice
    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
      CREATE TABLE `date_test` (
                `val` date DEFAULT NULL
              ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
    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.)

  • jasonhjasonh Current User Role Novice
    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

  • jriverajrivera Percona Support Engineer Percona Staff Role
    Looks like a bug since I can reproduce the same error. Please open a JIRA ticket under the PXB project.
  • jriverajrivera Percona Support Engineer Percona Staff Role
    Actually there's a bug reported already -

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.