tokubackup will not take InnoDB tablespace file if it is created outside default datadir

[Shahriyar Rzayev]

Hi,

If you try to create InnoDB tablespace file outside default datadir:

mysql> CREATE TABLE t2 (c1 INT PRIMARY KEY) DATA DIRECTORY = '/home/datadir';

TokuBackup will not consider t2.ibd file while taking backup:

root@percona-XPS-15:~# ls /home/tokubackupdir/mysql_data_dir/
auto.cnf ib_logfile1 mysql_upgrade_info tc.log tokudb.environment __tokudb_lock_dont_delete_me_logs tokudb.rollback
ibdata1 log000000000001.tokulog28 performance_schema test __tokudb_lock_dont_delete_me_data __tokudb_lock_dont_delete_me_recovery xtrabackup_info
ib_logfile0 mysql t1 tokudb.directory __tokudb_lock_dont_delete_me_environment __tokudb_lock_dont_delete_me_temp
root@percona-XPS-15:~# ls /home/tokubackupdir/mysql_data_dir/t1/
db.opt t1.frm t1.ibd t1.isl t2.frm t2.isl

And definitely in recovery process it will crash.

for XtraBackup it will take the .ibd file to default datadir and after recovery process, datafile path will be changed:

2015-11-05 12:15:55 7f52c7bdf7c0 InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
2015-11-05 12:15:55 16709 [ERROR] InnoDB: A link file was found named './t1/t1.isl' but the linked tablespace '/home/datadir/t1/t1.ibd' could not be opened.
2015-11-05 12:15:55 16709 [Note] InnoDB: The InnoDB data dictionary table SYS_DATAFILES for tablespace ID 7 was updated to use file ./t1/t1.ibd.

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_DATAFILES;
+-------+----------------------------------+
| SPACE | PATH |
+-------+----------------------------------+
| 1 | ./mysql/innodb_table_stats.ibd |
| 2 | ./mysql/innodb_index_stats.ibd |
| 3 | ./mysql/slave_relay_log_info.ibd |
| 4 | ./mysql/slave_master_info.ibd |
| 5 | ./mysql/slave_worker_info.ibd |
| 7 | ./t1/t1.ibd |
+-------+----------------------------------+
6 rows in set (0.00 sec)

[Roel Van de Paar (roel11)]

Summary: Tablespace outside datadir? It won't be backed up + corrupt backup!

[Shahriyar Rzayev (rzayev-sehriyar)]

Marking as invalid because could not reproduce with PS 5.6.28 and PS 5.7.10-rc1,
Maybe already fixed or I have opened this one without listing related folder(mistakenly)

mysql> select @@version;
+-----------------+
| @@version |
+-----------------+
| 5.6.28-76.1-log |
+-----------------+
1 row in set (0.00 sec)

mysql> create table t1(id int not null) data directory='/var/lib/mysql_tablespaces';
Query OK, 0 rows affected (0.03 sec)

mysql> set tokudb_backup_dir='/home/tokubackupdir';
Query OK, 0 rows affected (3.17 sec)

root@percona-XPS-15:/home/tokubackupdir# ls mysql_data_dir_tablespaces/
t1
root@percona-XPS-15:/home/tokubackupdir# ls mysql_data_dir_tablespaces/t1/
t1.ibd

mysql> select @@version;
+-------------+
| @@version |
+-------------+
| 5.7.10-1rc1 |
+-------------+
1 row in set (0.01 sec)

mysql> create tablespace t1 add datafile '/var/lib/mysql_general_tablespaces/t1.ibd' engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> set tokudb_backup_dir='/home/tokubackupdir';
Query OK, 0 rows affected (37.42 sec)

[root@ps-5 tokubackupdir]# ls mysql_data_dir_tablespaces/t1
t1.ibd

[Shahriyar Rzayev (rzayev-sehriyar)]

Reopened Issue with weird situation:

When Table is create in '/var/lib/mysql_tablespaces':

mysql> CREATE TABLE `t2` (
    -> `c1` int(11) NOT NULL,
    -> PRIMARY KEY (`c1`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='/var/lib/mysql_tablespaces';
Query OK, 0 rows affected (0.02 sec)

mysql> set tokudb_backup_dir='/home/tokubackupdir';
Query OK, 0 rows affected (2.57 sec)

The backup will copy t2.ibd:

root@percona-XPS-15:/home/tokubackupdir# ls mysql_data_dir_tablespaces/t1/
t1.ibd t2.ibd

But if you create table as:

CREATE TABLE `t2` (
  `c1` int(11) NOT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='/home/datadir/'

Backup will missed t2.ibd.

[Roel Van de Paar (roel11)]

It may be that /var/lib/ location is covered in TB while any other location is not. OK, so the original bug stands.

[George Ormond Lorch III (gl-az)]

https://tokutek.atlassian.net/browse/BACKUP-114

[Shahriyar Rzayev (rzayev-sehriyar)]

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