fail to restore (copy-back) tables haveing partitions with their own tablespace location
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Percona XtraBackup moved to https://jira.percona.com/projects/PXB |
Fix Released
|
Medium
|
Alexey Kopytov | ||
2.1 |
Fix Released
|
Medium
|
Alexey Kopytov | ||
2.2 |
Fix Released
|
Medium
|
Alexey Kopytov |
Bug Description
If I create a table with partitions and I put the tablespace to another location, the backup succeeds but the copy-back fails because it doesn't create the directory.
Example:
mysql> CREATE TABLE sales_figures (region_id INT, sales_date DATE, amount INT) PARTITION BY LIST (region_id) (
PARTITION US_DATA VALUES IN(100,200,300) DATA DIRECTORY = '/tmp/tb1',
PARTITION EU_DATA VALUES IN(400,500) DATA DIRECTORY = '/tmp/tb2/');
[root@imac2 mysql]# ls -l /tmp/tb1/
-rw-rw---- 1 mysql mysql 98304 May 23 16:19 /tmp/tb1/
[root@imac2 mysql]# ls -l /tmp/tb2/
-rw-rw---- 1 mysql mysql 98304 May 23 16:19 /tmp/tb2/
Then the backup:
40523 16:36:08 innobackupex: Starting ibbackup with command: xtrabackup_56 --defaults-
innobackupex: Waiting for ibbackup (pid=2571) to suspend
innobackupex: Suspend file '/tmp/2014-
xtrabackup_56 version 2.1.9 for MySQL server 5.6.17 Linux (x86_64) (revision id: 744)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_
xtrabackup: innodb_
xtrabackup: innodb_
xtrabackup: innodb_
xtrabackup: innodb_
>> log scanned up to (1636174)
[01] Copying ./ibdata1 to /tmp/2014-
[01] ...done
[01] Copying /tmp/tb2/
[01] ...done
[01] Copying /tmp/tb1/
[01] ...done
[01] Copying ./mysql/
[01] ...done
[01] Copying ./mysql/
[01] ...done
[01] Copying ./mysql/
[01] ...done
[01] Copying ./mysql/
[01] ...done
[01] Copying ./mysql/
[01] ...done
>> log scanned up to (1636174)
xtrabackup: Creating suspend file '/tmp/2014-
140523 16:36:10 innobackupex: Continuing after ibbackup has suspended
140523 16:36:10 innobackupex: Starting to lock all tables...
>> log scanned up to (1636174)
140523 16:36:10 innobackupex: All tables locked and flushed to disk
140523 16:36:10 innobackupex: Starting to backup non-InnoDB tables and files
innobackupex: in subdirectories of '/var/lib/mysql'
innobackupex: Backing up files '/var/lib/
innobackupex: Backing up file '/var/lib/
innobackupex: Backing up file '/var/lib/
innobackupex: Backing up file '/var/lib/
innobackupex: Backing up file '/var/lib/
innobackupex: Backing up file '/var/lib/
innobackupex: Backing up files '/var/lib/
innobackupex: Backing up files '/var/lib/
>> log scanned up to (1636174)
140523 16:36:11 innobackupex: Finished backing up non-InnoDB tables and files
140523 16:36:11 innobackupex: Waiting for log copying to finish
xtrabackup: The latest check point (for incremental): '1636174'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (1636174)
xtrabackup: Creating suspend file '/tmp/2014-
xtrabackup: Transaction log of lsn (1636174) to (1636174) was copied.
140523 16:36:12 innobackupex: All tables unlocked
innobackupex: Backup created in directory '/tmp/2014-
140523 16:36:12 innobackupex: Connection to database server closed
140523 16:36:12 innobackupex: completed OK!
It succeeded !
.isl files are there but during the copy-back:
innobackupex: Creating directory '/var/lib/
innobackupex: Copying '/tmp/2014-
innobackupex: Copying '/tmp/2014-
innobackupex: Copying '/tmp/2014-
innobackupex: Copying '/tmp/2014-
innobackupex: Copying '/tmp/2014-
Found an .isl file for /tmp/2014-
Using /tmp/tb1/
innobackupex: Copying '/tmp/2014-
innobackupex: Error: copy failed: No such file or directory at /bin/innobackupex line 2141.
I need to create /tmp/tb1/fred and /tmp/tb2/fred.
I expected that /tmp/tb1 and /tmp/tb2 were needed to be present, but not the table name.
Related branches
- Alexey Kopytov (community): Approve
-
Diff: 49 lines (+13/-1)2 files modifiedinnobackupex.pl (+11/-0)
test/t/remote_tablespaces.sh (+2/-1)
- Alexey Kopytov (community): Approve
-
Diff: 49 lines (+13/-1)2 files modifiedstorage/innobase/xtrabackup/innobackupex.pl (+11/-0)
storage/innobase/xtrabackup/test/t/remote_tablespaces.sh (+2/-1)
tags: | added: low-hanging-fruit |
I forgot to mention that if I create the table name directory, it works !
innobackupex: Creating directory '/var/lib/ mysql/fred' 05-23_16- 36-08/fred/ sales_figures# P#US_DATA. isl' to '/var/lib/ mysql/fred/ sales_figures# P#US_DATA. isl' 05-23_16- 36-08/fred/ sales_figures. par' to '/var/lib/ mysql/fred/ sales_figures. par' 05-23_16- 36-08/fred/ sales_figures# P#EU_DATA. isl' to '/var/lib/ mysql/fred/ sales_figures# P#EU_DATA. isl' 05-23_16- 36-08/fred/ db.opt' to '/var/lib/ mysql/fred/ db.opt' 05-23_16- 36-08/fred/ sales_figures. frm' to '/var/lib/ mysql/fred/ sales_figures. frm' 05-23_16- 36-08/fred/ sales_figures# P#US_DATA. ibd fred/sales_ figures# P#US_DATA. ibd as the destination path 05-23_16- 36-08/fred/ sales_figures# P#US_DATA. ibd' to '/tmp/tb1/ fred/sales_ figures# P#US_DATA. ibd' 05-23_16- 36-08/fred/ sales_figures# P#EU_DATA. ibd fred/sales_ figures# P#EU_DATA. ibd as the destination path 05-23_16- 36-08/fred/ sales_figures# P#EU_DATA. ibd' to '/tmp/tb2/ fred/sales_ figures# P#EU_DATA. ibd'
innobackupex: Copying '/tmp/2014-
innobackupex: Copying '/tmp/2014-
innobackupex: Copying '/tmp/2014-
innobackupex: Copying '/tmp/2014-
innobackupex: Copying '/tmp/2014-
Found an .isl file for /tmp/2014-
Using /tmp/tb1/
innobackupex: Copying '/tmp/2014-
Found an .isl file for /tmp/2014-
Using /tmp/tb2/
innobackupex: Copying '/tmp/2014-
innobackupex: Starting to copy InnoDB system tablespace 05-23_16- 36-08' 05-23_16- 36-08/ibdata1' to '/var/lib/ mysql/ibdata1'
innobackupex: in '/tmp/2014-
innobackupex: back to original InnoDB data directory '/var/lib/mysql'
innobackupex: Copying '/tmp/2014-
innobackupex: Starting to copy InnoDB undo tablespaces 05-23_16- 36-08'
innobackupex: in '/tmp/2014-
innobackupex: back to '/var/lib/mysql'
innobackupex: Starting to copy InnoDB log files 05-23_16- 36-08' 05-23_16- 36-08/ib_ logfile0' to '/var/lib/ mysql/ib_ logfile0' 05-23_16- 36-08/ib_ logfile1' to '/var/lib/ mysql/ib_ logfile1'
innobackupex: in '/tmp/2014-
innobackupex: back to original InnoDB log directory '/var/lib/mysql'
innobackupex: Copying '/tmp/2014-
innobackupex: Copying '/tmp/2014-
innobackupex: Finished copying back files.