Innodb general tablespace file missing from backupdir

[Shahriyar Rzayev]

Test Tree:

https://github.com/gl-sergei/percona-xtrabackup/tree/xb-5.7.9

I have created general tablespace file in default datadir as:

mysql> CREATE TABLESPACE `sbtest2` ADD DATAFILE 'sbtest2.ibd' Engine=InnoDB;
Query OK, 0 rows affected (0,00 sec)

mysql> show create table sbtest1;

| sbtest1 | 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` (`k`)
) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB AUTO_INCREMENT=25661595 DEFAULT CHARSET=latin1 |

mysql> create table sbtest2 like sbtest1;
Query OK, 0 rows affected (0,01 sec)

mysql> insert into sbtest2 select * from sbtest1;
Query OK, 25661594 rows affected (7 min 52,96 sec)
Records: 25661594 Duplicates: 0 Warnings: 0

mysql> alter table sbtest2 tablespace = sbtest2;
Query OK, 0 rows affected (2 min 41,21 sec)
Records: 0 Duplicates: 0 Warnings: 0

[root@mysql-57 ~]# ls -lh /var/lib/mysql | grep sbtest2
-rw-r-----. 1 mysql mysql 6,7G янв 8 06:40 sbtest2.ibd

After taking backup:

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

General Tablespace file 'sbtest2.ibd' is missing from backup directory:

[root@mysql-57 ~]# ls /home/backup_dir/full/
backup-my.cnf bck dbtest ib_buffer_pool ibdata1 mysql performance_schema sys xtrabackup_checkpoints xtrabackup_info xtrabackup_logfile

And the SAME thing is with outside general tablespace:

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

mysql> alter table sbtest2 tablespace = sbtest3;
Query OK, 0 rows affected (3 min 23,50 sec)
Records: 0 Duplicates: 0 Warnings: 0

# dropping old tablespace
mysql> drop tablespace sbtest2;
Query OK, 0 rows affected (0,13 sec)

There should be sbtest3.isl file in MySQL datadir:

[root@mysql-57 mysql]# ls | grep sbtest3.isl
sbtest3.isl

Also:

[root@mysql-57 ~]# ls -lh /var/lib/mysql_tablespaces/
total 6,7G
-rw-r-----. 1 mysql mysql 6,7G янв 8 07:03 sbtest3.ibd

Taking backup again and checking for those files:

[root@mysql-57 ~]# ls /home/backup_dir/full/
backup-my.cnf bck dbtest ib_buffer_pool ibdata1 mysql performance_schema sys xtrabackup_checkpoints xtrabackup_info xtrabackup_logfile

as well as sbtest3.ibd file missing too.

[Sergei Glushchenko (sergei.glushchenko)]

Please test with latest branch

[Shahriyar Rzayev (rzayev-sehriyar)]

Confirming that now after taking full & incremental backup there is .ibd (tablespace) file in full/inc directories:

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

mysql> alter table sbtest1 tablespace=ts1;
Query OK, 0 rows affected (35,93 sec)
Records: 0 Duplicates: 0 Warnings: 0

[root@mysql-57 inc]# ls inc1/
backup-my.cnf ib_buffer_pool ibdata1.meta performance_schema ts1.ibd.delta ts2.ibd.delta xtrabackup_binlog_info xtrabackup_info
dbtest ibdata1.delta mysql sys ts1.ibd.meta ts2.ibd.meta xtrabackup_checkpoints xtrabackup_logfile
[root@mysql-57 inc]# ls inc2/
backup-my.cnf ib_buffer_pool ibdata1.meta performance_schema ts1.ibd.delta ts2.ibd.delta xtrabackup_binlog_info xtrabackup_info
dbtest ibdata1.delta mysql sys ts1.ibd.meta ts2.ibd.meta xtrabackup_checkpoints xtrabackup_logfile
[root@mysql-57 inc]# ls ../full/
backup-my.cnf ib_buffer_pool mysql sys ts2.ibd xtrabackup_checkpoints xtrabackup_logfile
dbtest ibdata1 performance_schema ts1.ibd xtrabackup_binlog_info xtrabackup_info

[Shahriyar Rzayev (rzayev-sehriyar)]

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