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

Bug #1322658 reported by Frederic Descamps on 2014-05-23
8
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona XtraBackup
Medium
Alexey Kopytov
2.1
Medium
Alexey Kopytov
2.2
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

lp:~akopytov/percona-xtrabackup/bug1322658-2.1
Merged into lp:percona-xtrabackup/2.1 at revision 753
Alexey Kopytov: Approve on 2014-07-22
lp:~akopytov/percona-xtrabackup/bug1322658-2.2
Merged into lp:percona-xtrabackup/2.2 at revision 4987
Alexey Kopytov: Approve on 2014-07-22
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.

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!

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
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers