xtrabackup makes invalid backup

[Elbandi]

if table created with DATA DIRECTORY, xtrabackup makes invalid backup.

mysql> use test;
Database changed

mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) DATA DIRECTORY = '/tmp/foo';
Query OK, 0 rows affected (0.42 sec)

mysql> insert into t1 values(1);
Query OK, 1 row affected (0.05 sec)

mysql> insert into t1 values(2);
Query OK, 1 row affected (0.11 sec)

# ls -l /var/lib/mysql/test/
total 20
-rw-rw---- 1 mysql mysql 65 Feb 29 14:49 db.opt
-rw-rw---- 1 mysql mysql 8556 Feb 29 14:49 t1.frm
-rw-rw---- 1 mysql mysql 20 Feb 29 14:49 t1.isl
# cat /var/lib/mysql/test/t1.isl
/tmp/foo/test/t1.ibd
# ls -l /tmp/foo/test/
total 96
-rw-rw---- 1 mysql mysql 98304 Feb 29 14:50 t1.ibd

# innobackupex --user=root --password=xxxx /tmp/backup
160229 14:55:26 innobackupex: Starting the backup operation
...
160229 14:55:54 completed OK!
# innobackupex --user=root --password=xxxx --apply-log /tmp/backup/2016-02-29_14-55-40/
160229 15:00:14 innobackupex: Starting the apply-log operation
...
160229 15:00:20 completed OK!

.isl file is there:
# ls -l /tmp/backup/2016-02-29_14-55-40/test/
total 116
-rw-r----- 1 root root 65 Feb 29 14:55 db.opt
-rw-r----- 1 root root 8556 Feb 29 14:55 t1.frm
-rw-r----- 1 root root 98304 Feb 29 14:55 t1.ibd
-rw-r----- 1 root root 20 Feb 29 14:55 t1.isl

content same:
# cat /tmp/backup/2016-02-29_14-55-40/test/t1.isl
/tmp/foo/test/t1.ibd

tring run mysqld on backup directory:
# mysqld --datadir=/tmp/backup/2016-02-29_14-55-40 -P 3307
...
2016-02-29 15:03:58 3392 [ERROR] InnoDB: A tablespace for test/t1 has been found in multiple places;
2016-02-29 15:03:58 3392 [ERROR] InnoDB: Default location; ./test/t1.ibd, LSN=0, Space ID=6, Flags=1024
2016-02-29 15:03:58 3392 [ERROR] InnoDB: Dictionary location; /tmp/foo/test/t1.ibd, LSN=0, Space ID=6, Flags=1024
2016-02-29 15:03:58 3392 [ERROR] InnoDB: Will not open the tablespace for 'test/t1'
2016-02-29 15:03:58 3392 [ERROR] InnoDB: Tablespace open failed for '"test"."t1"', ignored.
...

if i switch to test database in another client, log gets this:
...
2016-02-29 15:05:23 3420 [ERROR] InnoDB: Failed to find tablespace for table '"test"."t1"' in the cache. Attempting to load the tablespace with space id 6.
2016-02-29 15:05:23 3420 [ERROR] InnoDB: A tablespace for test/t1 has been found in multiple places;
2016-02-29 15:05:23 3420 [ERROR] InnoDB: Default location; ./test/t1.ibd, LSN=0, Space ID=6, Flags=1024
2016-02-29 15:05:23 3420 [ERROR] InnoDB: Dictionary location; /tmp/foo/test/t1.ibd, LSN=0, Space ID=6, Flags=1024
2016-02-29 15:05:23 3420 [ERROR] InnoDB: Will not open the tablespace for 'test/t1'
2016-02-29 15:05:23 7fbf490a4700 InnoDB: cannot calculate statistics for table "test"."t1" because the .ibd file is missing. For help, please refer to http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html
...

[Sergei Glushchenko (sergei.glushchenko)]

Simply remove .isl file and it will fix this specific issue. More interesting question is do we want --copy-back (which you didn't use) to copy tablespace to it's original location as pointed by .isl file? I think in some cases we want, but in other cases we don't. Maybe we should make it optional?

[Elbandi (ea333)]

I demo a "setup a slave for replicaion" based on https://www.percona.com/doc/percona-xtrabackup/2.3/howtos/setting_up_replication.html
this docs doesnt say anything about copy-back...

if i remove the .isl file, mysql wont start too:
...
2016-02-29 17:18:31 3454 [ERROR] InnoDB: Failed to find tablespace for table '"test"."t1"' in the cache. Attempting to load the tablespace with space id 6.
2016-02-29 17:18:31 3454 [ERROR] InnoDB: A tablespace for test/t1 has been found in multiple places;
2016-02-29 17:18:31 3454 [ERROR] InnoDB: Default location; ./test/t1.ibd, LSN=0, Space ID=6, Flags=1024
2016-02-29 17:18:31 3454 [ERROR] InnoDB: Dictionary location; /tmp/foo/test/t1.ibd, LSN=0, Space ID=6, Flags=1024
2016-02-29 17:18:31 3454 [ERROR] InnoDB: Will not open the tablespace for 'test/t1'
2016-02-29 17:18:31 7f4fdc26f700 InnoDB: cannot calculate statistics for table "test"."t1" because the .ibd file is missing. For help, please refer to http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html
...

[Sergei Glushchenko (sergei.glushchenko)]

Well, the problem actually is that MySQL is seeing t1.ibd in two locations. The original one, which is /tmp/foo/t1.ibd and the one in the backup directory, which you are using as new datadir. I think you are using MySQL 5.7 and old path is stored not only in .isl file, bit in the data dictionary as well.

To resolve this conflict I beleive you will need to remove .ibd from one of the locations. Again, I don't think it will be correct if xtrabackup on prepare would "fix" the path in the data dictionary because there are cases when one need to place t1.ibd to the same path either on old server or the new one. This could probably be added as optional step.

[Shahriyar Rzayev (rzayev-sehriyar)]

Just for follow-up, if you remove the .ibd file from /tmp/foo/test/t1.ibd and keep t1.ibd inside mysql datadir(after copy-back):

2016-03-01 17:07:51 7f97ea6e67c0 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.
2016-03-01 17:07:51 22339 [ERROR] InnoDB: A link file was found named './test/t1.isl' but the linked tablespace '/tmp/foo/test/t1.ibd' could not be opened.
2016-03-01 17:07:51 22339 [Note] InnoDB: The InnoDB data dictionary table SYS_DATAFILES for tablespace ID 6 was updated to use file ./test/t1.ibd.

It will refer to new path as specified.

But for the original recovery process t1.ibd file from backup directory should be copied to original path specified in t1.isl, prior to copy-back or as part of copy-back process, with special option as suggested by @Sergei.

[Shahriyar Rzayev (rzayev-sehriyar)]

Follow-up on this:

Upstream:

http://bugs.mysql.com/bug.php?id=80625

Xtrabackup LP:
https://bugs.launchpad.net/percona-xtrabackup/+bug/1553692

[Shahriyar Rzayev (rzayev-sehriyar)]

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