fail to restore (copy-back) tables haveing partitions with their own tablespace location

Bug #1322658 reported by Frederic Descamps
8
This bug affects 1 person
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/fred/sales_figures#P#US_DATA.ibd
        -rw-rw---- 1 mysql mysql 98304 May 23 16:19 /tmp/tb1/fred/sales_figures#P#US_DATA.ibd

        [root@imac2 mysql]# ls -l /tmp/tb2/fred/sales_figures#P#EU_DATA.ibd
        -rw-rw---- 1 mysql mysql 98304 May 23 16:19 /tmp/tb2/fred/sales_figures#P#EU_DATA.ibd

Then the backup:

         40523 16:36:08 innobackupex: Starting ibbackup with command: xtrabackup_56 --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/tmp/2014-05-23_16-36-08 --tmpdir=/tmp
innobackupex: Waiting for ibbackup (pid=2571) to suspend
innobackupex: Suspend file '/tmp/2014-05-23_16-36-08/xtrabackup_suspended_2'

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_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 50331648
>> log scanned up to (1636174)
[01] Copying ./ibdata1 to /tmp/2014-05-23_16-36-08/ibdata1
[01] ...done
[01] Copying /tmp/tb2/fred/sales_figures#P#EU_DATA.ibd to /tmp/2014-05-23_16-36-08/fred/sales_figures#P#EU_DATA.ibd
[01] ...done
[01] Copying /tmp/tb1/fred/sales_figures#P#US_DATA.ibd to /tmp/2014-05-23_16-36-08/fred/sales_figures#P#US_DATA.ibd
[01] ...done
[01] Copying ./mysql/slave_master_info.ibd to /tmp/2014-05-23_16-36-08/mysql/slave_master_info.ibd
[01] ...done
[01] Copying ./mysql/slave_relay_log_info.ibd to /tmp/2014-05-23_16-36-08/mysql/slave_relay_log_info.ibd
[01] ...done
[01] Copying ./mysql/innodb_table_stats.ibd to /tmp/2014-05-23_16-36-08/mysql/innodb_table_stats.ibd
[01] ...done
[01] Copying ./mysql/innodb_index_stats.ibd to /tmp/2014-05-23_16-36-08/mysql/innodb_index_stats.ibd
[01] ...done
[01] Copying ./mysql/slave_worker_info.ibd to /tmp/2014-05-23_16-36-08/mysql/slave_worker_info.ibd
[01] ...done
>> log scanned up to (1636174)
xtrabackup: Creating suspend file '/tmp/2014-05-23_16-36-08/xtrabackup_suspended_2' with pid '2571'

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/mysql/sys/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (64 files)
innobackupex: Backing up file '/var/lib/mysql/fred/sales_figures.frm'
innobackupex: Backing up file '/var/lib/mysql/fred/db.opt'
innobackupex: Backing up file '/var/lib/mysql/fred/sales_figures#P#EU_DATA.isl'
innobackupex: Backing up file '/var/lib/mysql/fred/sales_figures.par'
innobackupex: Backing up file '/var/lib/mysql/fred/sales_figures#P#US_DATA.isl'
innobackupex: Backing up files '/var/lib/mysql/mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (74 files)
innobackupex: Backing up files '/var/lib/mysql/performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (53 files)
>> 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-05-23_16-36-08/xtrabackup_log_copied' with pid '2571'
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-05-23_16-36-08'
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/mysql/fred'
innobackupex: Copying '/tmp/2014-05-23_16-36-08/fred/sales_figures#P#US_DATA.isl' to '/var/lib/mysql/fred/sales_figures#P#US_DATA.isl'
innobackupex: Copying '/tmp/2014-05-23_16-36-08/fred/sales_figures.par' to '/var/lib/mysql/fred/sales_figures.par'
innobackupex: Copying '/tmp/2014-05-23_16-36-08/fred/sales_figures#P#EU_DATA.isl' to '/var/lib/mysql/fred/sales_figures#P#EU_DATA.isl'
innobackupex: Copying '/tmp/2014-05-23_16-36-08/fred/db.opt' to '/var/lib/mysql/fred/db.opt'
innobackupex: Copying '/tmp/2014-05-23_16-36-08/fred/sales_figures.frm' to '/var/lib/mysql/fred/sales_figures.frm'
Found an .isl file for /tmp/2014-05-23_16-36-08/fred/sales_figures#P#US_DATA.ibd
Using /tmp/tb1/fred/sales_figures#P#US_DATA.ibd as the destination path
innobackupex: Copying '/tmp/2014-05-23_16-36-08/fred/sales_figures#P#US_DATA.ibd' to '/tmp/tb1/fred/sales_figures#P#US_DATA.ibd'
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

Revision history for this message
Frederic Descamps (lefred) wrote :

I forgot to mention that if I create the table name directory, it works !

innobackupex: Creating directory '/var/lib/mysql/fred'
innobackupex: Copying '/tmp/2014-05-23_16-36-08/fred/sales_figures#P#US_DATA.isl' to '/var/lib/mysql/fred/sales_figures#P#US_DATA.isl'
innobackupex: Copying '/tmp/2014-05-23_16-36-08/fred/sales_figures.par' to '/var/lib/mysql/fred/sales_figures.par'
innobackupex: Copying '/tmp/2014-05-23_16-36-08/fred/sales_figures#P#EU_DATA.isl' to '/var/lib/mysql/fred/sales_figures#P#EU_DATA.isl'
innobackupex: Copying '/tmp/2014-05-23_16-36-08/fred/db.opt' to '/var/lib/mysql/fred/db.opt'
innobackupex: Copying '/tmp/2014-05-23_16-36-08/fred/sales_figures.frm' to '/var/lib/mysql/fred/sales_figures.frm'
Found an .isl file for /tmp/2014-05-23_16-36-08/fred/sales_figures#P#US_DATA.ibd
Using /tmp/tb1/fred/sales_figures#P#US_DATA.ibd as the destination path
innobackupex: Copying '/tmp/2014-05-23_16-36-08/fred/sales_figures#P#US_DATA.ibd' to '/tmp/tb1/fred/sales_figures#P#US_DATA.ibd'
Found an .isl file for /tmp/2014-05-23_16-36-08/fred/sales_figures#P#EU_DATA.ibd
Using /tmp/tb2/fred/sales_figures#P#EU_DATA.ibd as the destination path
innobackupex: Copying '/tmp/2014-05-23_16-36-08/fred/sales_figures#P#EU_DATA.ibd' to '/tmp/tb2/fred/sales_figures#P#EU_DATA.ibd'

innobackupex: Starting to copy InnoDB system tablespace
innobackupex: in '/tmp/2014-05-23_16-36-08'
innobackupex: back to original InnoDB data directory '/var/lib/mysql'
innobackupex: Copying '/tmp/2014-05-23_16-36-08/ibdata1' to '/var/lib/mysql/ibdata1'

innobackupex: Starting to copy InnoDB undo tablespaces
innobackupex: in '/tmp/2014-05-23_16-36-08'
innobackupex: back to '/var/lib/mysql'

innobackupex: Starting to copy InnoDB log files
innobackupex: in '/tmp/2014-05-23_16-36-08'
innobackupex: back to original InnoDB log directory '/var/lib/mysql'
innobackupex: Copying '/tmp/2014-05-23_16-36-08/ib_logfile0' to '/var/lib/mysql/ib_logfile0'
innobackupex: Copying '/tmp/2014-05-23_16-36-08/ib_logfile1' to '/var/lib/mysql/ib_logfile1'
innobackupex: Finished copying back files.

Revision history for this message
Frederic Descamps (lefred) wrote :

It's the same for a table not being partitioned:

innobackupex: Creating directory '/var/lib/mysql/fred'
innobackupex: Copying '/tmp/2014-05-23_22-27-10/fred/sales_figures.isl' to '/var/lib/mysql/fred/sales_figures.isl'
innobackupex: Copying '/tmp/2014-05-23_22-27-10/fred/db.opt' to '/var/lib/mysql/fred/db.opt'
Found an .isl file for /tmp/2014-05-23_22-27-10/fred/sales_figures.ibd
Using /tmp/tb1/fred/sales_figures.ibd as the destination path
innobackupex: Copying '/tmp/2014-05-23_22-27-10/fred/sales_figures.ibd' to '/tmp/tb1/fred/sales_figures.ibd'
innobackupex: Error: copy failed: No such file or directory at /bin/innobackupex line 2141.

[root@imac2 tmp]# rm -rf /var/lib/mysql/*
[root@imac2 tmp]# mkdir /tmp/tb1/fred
[root@imac2 tmp]# innobackupex --copy-back 2014-05-23_22-27-10/

innobackupex: Creating directory '/var/lib/mysql/fred'
innobackupex: Copying '/tmp/2014-05-23_22-27-10/fred/sales_figures.isl' to '/var/lib/mysql/fred/sales_figures.isl'
innobackupex: Copying '/tmp/2014-05-23_22-27-10/fred/db.opt' to '/var/lib/mysql/fred/db.opt'
Found an .isl file for /tmp/2014-05-23_22-27-10/fred/sales_figures.ibd
Using /tmp/tb1/fred/sales_figures.ibd as the destination path
innobackupex: Copying '/tmp/2014-05-23_22-27-10/fred/sales_figures.ibd' to '/tmp/tb1/fred/sales_figures.ibd'
innobackupex: Copying '/tmp/2014-05-23_22-27-10/fred/sales_figures.frm' to '/var/lib/mysql/fred/sales_figures.frm'
innobackupex: Creating directory '/var/lib/mysql/sys'
...
innobackupex: Finished copying back files.

140523 22:30:14 innobackupex: completed OK!

Revision history for this message
Nilnandan Joshi (nilnandan-joshi) wrote :

Able to reproduce the same issue with Xtrabackup 2.1.9 and 2.2.3. Surprisingly, Xtrabackup 2.1.9 is not even giving error that dir doesn't exist.

Found an .isl file for /home/nilnandan/backup/2014-07-08_14-56-26/test/sales_figures#P#US_DATA.ibd
Using /tmp/tb1/test/sales_figures#P#US_DATA.ibd as the destination path
innobackupex: Copying '/home/nilnandan/backup/2014-07-08_14-56-26/test/sales_figures#P#US_DATA.ibd' to '/tmp/tb1/test/sales_figures#P#US_DATA.ibd'
innobackupex: Error: copy failed: No such file or directory at /usr/bin/innobackupex line 2191.

And yes, if dir are already there, it copies files.

innobackupex: Copying '/home/nilnandan/backup/2014-07-08_14-56-26/test/nil_test.frm' to '/var/lib/mysql/test/nil_test.frm'
Found an .isl file for /home/nilnandan/backup/2014-07-08_14-56-26/test/sales_figures#P#US_DATA.ibd
Using /tmp/tb1/test/sales_figures#P#US_DATA.ibd as the destination path
innobackupex: Copying '/home/nilnandan/backup/2014-07-08_14-56-26/test/sales_figures#P#US_DATA.ibd' to '/tmp/tb1/test/sales_figures#P#US_DATA.ibd'
Found an .isl file for /home/nilnandan/backup/2014-07-08_14-56-26/test/sales_figures#P#EU_DATA.ibd
Using /tmp/tb2/test/sales_figures#P#EU_DATA.ibd as the destination path
innobackupex: Copying '/home/nilnandan/backup/2014-07-08_14-56-26/test/sales_figures#P#EU_DATA.ibd' to '/tmp/tb2/test/sales_figures#P#EU_DATA.ibd'
innobackupex: Copying '/home/nilnandan/backup/2014-07-08_14-56-26/test/nil_test.ibd' to '/var/lib/mysql/test/nil_test.ibd'
...
innobackupex: Starting to copy InnoDB log files
innobackupex: in '/home/nilnandan/backup/2014-07-08_14-56-26'
innobackupex: back to original InnoDB log directory '/var/lib/mysql'
innobackupex: Finished copying back files.

140708 15:01:51 innobackupex: completed OK!
root@nilnandan-Dell-XPS:/var/lib/mysql#

tags: added: low-hanging-fruit
Revision history for this message
Shahriyar Rzayev (rzayev-sehriyar) wrote :

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

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.