Failed to prepare incremental backup if general tablespace has been recreated between backups

[Shahriyar Rzayev]

Simple case:

1. Create general tablespace:

mysql> CREATE TABLESPACE `ts1` ADD DATAFILE '/var/lib/mysql_tablespaces/ts1.ibd' Engine=InnoDB; Query OK, 0 rows affected (0,02 sec)

2. Take full backup:

./xtrabackup --defaults-file=/etc/my.cnf --backup --datadir=/var/lib/mysql/ --target-dir=/home/backup_dir/full/ --user=root --password=Baku12345# --no-version-check

3. Drop and recreate with different page size:

mysql> CREATE TABLESPACE `ts1` ADD DATAFILE '/var/lib/mysql_tablespaces/ts1.ibd' FILE_BLOCK_SIZE = 8192 Engine=InnoDB;
Query OK, 0 rows affected (0,03 sec)

4. Take incremental backup:

./xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/home/backup_dir/inc/inc1 --incremental-basedir=/home/backup_dir/full --datadir=/var/lib/mysql/ --user=root --password=Baku12345# --no-version-check

5. Prepare full & inc backups:

./xtrabackup --defaults-file=/home/backup_dir/full/backup-my.cnf --prepare --apply-log-only --target-dir=/home/backup_dir/full

./xtrabackup --defaults-file=/home/backup_dir/full/backup-my.cnf --prepare --target-dir=/home/backup_dir/full --incremental-dir=/home/backup_dir/inc/inc1

incremental backup from 9216231610 is enabled.
xtrabackup: cd to /home/backup_dir/full
xtrabackup: This target seems to be already prepared with --apply-log-only.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(9216232731)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = /home/backup_dir/inc/inc1
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 8388608
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 17 for mysql/slave_worker_info, old maximum was 0
xtrabackup: page size for /home/backup_dir/inc/inc1/ibdata1.delta is 16384 bytes
Applying /home/backup_dir/inc/inc1/ibdata1.delta to ./ibdata1...
xtrabackup: page size for /home/backup_dir/inc/inc1/ts1.ibd.delta is 8192 bytes
xtrabackup: Renaming ts1 to (null)/xtrabackup_tmp_#42.ibd
InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: File ./ts1.ibd: 'rename' returned OS error 71.
xtrabackup: Cannot rename ts1 to (null)/xtrabackup_tmp_#42
xtrabackup: error: cannot open ./ts1.ibd
xtrabackup: Error: xtrabackup_apply_delta(): failed to apply /home/backup_dir/inc/inc1/ts1.ibd.delta to ./ts1.ibd.
[root@mysql-57 bin]# perror 71
OS error code 71: Protocol error

[Shahriyar Rzayev (rzayev-sehriyar)]

The most easy way to reproduce:

mysql> create tablespace ts3 add datafile 'ts3.ibd' engine=innodb;
Query OK, 0 rows affected (0,02 sec)

Then take Full backup:

./xtrabackup --defaults-file=/etc/my.cnf --backup --datadir=/var/lib/mysql/ --target-dir=/home/backup_dir/full/ --user=root --password=Baku12345# --no-version-check

Then drop ts3 tablespace and create in remote folder:

mysql> drop tablespace ts3;
Query OK, 0 rows affected (0,00 sec)

mysql> create tablespace ts3 add datafile '/home/tablespaces/ts3.ibd' engine=innodb;
Query OK, 0 rows affected (0,01 sec)

Take incremental backup:

./xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/home/backup_dir/inc/inc1 --incremental-basedir=/home/backup_dir/full --datadir=/var/lib/mysql/ --user=root --password=Baku12345# --no-version-check

Prepare:

./xtrabackup --defaults-file=/home/backup_dir/full/backup-my.cnf --prepare --apply-log-only --target-dir=/home/backup_dir/full

./xtrabackup --defaults-file=/home/backup_dir/full/backup-my.cnf --prepare --target-dir=/home/backup_dir/full --incremental-dir=/home/backup_dir/inc/inc1

incremental backup from 2619657407 is enabled.
xtrabackup: cd to /home/backup_dir/full
xtrabackup: This target seems to be already prepared with --apply-log-only.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(2619658455)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = /home/backup_dir/inc/inc1
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 8388608
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 26 for dbtest/sbtest1, old maximum was 0
xtrabackup: page size for /home/backup_dir/inc/inc1/ibdata1.delta is 16384 bytes
Applying /home/backup_dir/inc/inc1/ibdata1.delta to ./ibdata1...
xtrabackup: page size for /home/backup_dir/inc/inc1/ts3.ibd.delta is 16384 bytes
xtrabackup: Renaming ts3 to (null)/xtrabackup_tmp_#32.ibd
InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: File ./ts3.ibd: 'rename' returned OS error 71.
xtrabackup: Cannot rename ts3 to (null)/xtrabackup_tmp_#32
xtrabackup: error: cannot open ./ts3.ibd
xtrabackup: Error: xtrabackup_apply_delta(): failed to apply /home/backup_dir/inc/inc1/ts3.ibd.delta to ./ts3.ibd.

[Sergei Glushchenko (sergei.glushchenko)]

Can it be reproduced with 2.3.x or earlier version?

[Shahriyar Rzayev (rzayev-sehriyar)]

Can not reproduce with XB 2.3.3 using PS 5.6.28 for compressed tables:

mysql> show create table sbtest1\G
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Take Full backup:

xtrabackup --defaults-file=/etc/mysql/my.cnf --backup --datadir=/var/lib/mysql/ --target-dir=/home/backup_dir/full/ --user=root --password=12345 --no-version-check

Drop table and recreate:

mysql> show create table sbtest1\G
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
1 row in set (0.00 sec)

Take incremental one:

xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/home/backup_dir/inc/inc1 --incremental-basedir=/home/backup_dir/full --datadir=/var/lib/mysql/ --user=root --password=12345 --no-version-check

Prepare:

xtrabackup --defaults-file=/home/backup_dir/full/backup-my.cnf --prepare --apply-log-only --target-dir=/home/backup_dir/full
xtrabackup --defaults-file=/home/backup_dir/full/backup-my.cnf --prepare --target-dir=/home/backup_dir/full --incremental-dir=/home/backup_dir/inc/inc1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 4295276054
160126 17:53:58 completed OK!

[Shahriyar Rzayev (rzayev-sehriyar)]

Also tested with outside datadir using XB 3.3 and PS 5.6.28:
1.
CREATE TABLE `sbtest1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
)

2. Full backup
3. drop table
4.
CREATE TABLE `sbtest1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='/var/lib/mysql_binary_logs/'
5. Incremental backup
6. Prepare:

InnoDB: Shutdown completed; log sequence number 4295293974
160127 11:29:06 completed OK!

[Shahriyar Rzayev (rzayev-sehriyar)]

Same OK with compressed + outside datadir:

CREATE TABLE `sbtest1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
)

CREATE TABLE `sbtest1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 DATA DIRECTORY='/var/lib/mysql_binary_logs/'

[Sergei Glushchenko (sergei.glushchenko)]

https://github.com/percona/percona-xtrabackup/pull/157

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PXB-457