Trigger definitions are ignored for partial backups

[Jervin R]

If I have a trigger defined on t1 and I back this table up only, the TRG file is copied but not the TRN.

[revin@forge msb_5_5_300]$ innobackupex --defaults-file=/ssd/msb/msb_5_5_300/my.sandbox.cnf --tables-file=/ssd/msb/msb_5_5_300/t /ssd/msb/msb_5_5_300/bkp
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona Ireland Ltd 2009-2012. All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

130516 20:35:23 innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/ssd/msb/msb_5_5_300/my.sandbox.cnf;mysql_read_default_group=xtrabackup' (using password: NO).
130516 20:35:23 innobackupex: Connected to MySQL server
IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".

innobackupex: Using mysql server version 5.5.30-rel30.2-log

innobackupex: Created backup directory /ssd/msb/msb_5_5_300/bkp/2013-05-16_20-35-23

130516 20:35:23 innobackupex: Starting ibbackup with command: xtrabackup_55 --defaults-file="/ssd/msb/msb_5_5_300/my.sandbox.cnf" --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/ssd/msb/msb_5_5_300/bkp/2013-05-16_20-35-23 --tmpdir=/ssd/msb/msb_5_5_300/tmp --tables_file='/ssd/msb/msb_5_5_300/t'
innobackupex: Waiting for ibbackup (pid=15692) to suspend
innobackupex: Suspend file '/ssd/msb/msb_5_5_300/bkp/2013-05-16_20-35-23/xtrabackup_suspended_2'

xtrabackup_55 version 2.1.1 for Percona Server 5.5.16 Linux (x86_64) (revision id: 600)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /ssd/msb/msb_5_5_300/data
xtrabackup: Target instance is assumed as followings.
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 = 1073741824
130516 20:35:23 InnoDB: Warning: allocated tablespace 16218, old maximum was 9
>> log scanned up to (151574722451)
[01] Copying ./ibdata1 to /ssd/msb/msb_5_5_300/bkp/2013-05-16_20-35-23/ibdata1
>> log scanned up to (151574722451)
>> log scanned up to (151574722451)
>> log scanned up to (151574722451)
>> log scanned up to (151574722451)
>> log scanned up to (151574722451)
[01] ...done
[01] Copying ./test/t1.ibd to /ssd/msb/msb_5_5_300/bkp/2013-05-16_20-35-23/test/t1.ibd
[01] ...done
>> log scanned up to (151574722451)

130516 20:35:30 innobackupex: Continuing after ibbackup has suspended
130516 20:35:30 innobackupex: Starting to lock all tables...
130516 20:35:30 innobackupex: All tables locked and flushed to disk

130516 20:35:30 innobackupex: Starting to backup non-InnoDB tables and files
innobackupex: in subdirectories of '/ssd/msb/msb_5_5_300/data'
innobackupex: Backing up files '/ssd/msb/msb_5_5_300/data/test/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (32 files)
130516 20:35:30 innobackupex: Finished backing up non-InnoDB tables and files

130516 20:35:30 innobackupex: Waiting for log copying to finish

xtrabackup: The latest check point (for incremental): '151574722451'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (151574722451)

xtrabackup: Transaction log of lsn (151574722451) to (151574722451) was copied.
130516 20:35:31 innobackupex: All tables unlocked

innobackupex: Backup created in directory '/ssd/msb/msb_5_5_300/bkp/2013-05-16_20-35-23'
innobackupex: MySQL binlog position: filename 'mysql-bin.000014', position 11308
130516 20:35:31 innobackupex: Connection to database server closed
130516 20:35:31 innobackupex: completed OK!
[revin@forge msb_5_5_300]$ ls -alh /ssd/msb/msb_5_5_300/bkp/2013-05-16_20-35-23
total 379M
drwxrwxr-x. 3 revin revin 4.0K May 16 20:35 .
drwxrwxr-x. 11 revin revin 4.0K May 16 20:35 ..
-rw-rw-r--. 1 revin revin 263 May 16 20:35 backup-my.cnf
-rw-rw----. 1 revin revin 378M May 16 20:35 ibdata1
drwx------. 2 revin revin 45 May 16 20:35 test
-rw-rw-r--. 1 revin revin 13 May 16 20:35 xtrabackup_binary
-rw-rw-r--. 1 revin revin 25 May 16 20:35 xtrabackup_binlog_info
-rw-rw----. 1 revin revin 99 May 16 20:35 xtrabackup_checkpoints
-rw-rw----. 1 revin revin 2.5K May 16 20:35 xtrabackup_logfile
[revin@forge msb_5_5_300]$ ls -alh /ssd/msb/msb_5_5_300/bkp/2013-05-16_20-35-23/test
total 116K
drwx------. 2 revin revin 45 May 16 20:35 .
drwxrwxr-x. 3 revin revin 4.0K May 16 20:35 ..
-rw-rw----. 1 revin revin 8.4K May 16 19:40 t1.frm
-rw-rw----. 1 revin revin 96K May 16 20:35 t1.ibd
-rw-rw----. 1 revin revin 280 May 16 20:05 t1.TRG
[revin@forge msb_5_5_300]$

[Alexey Kopytov (akopytov)]

Can't repeat. This is what I tried:

CREATE TABLE t1(a INT);
DELIMITER ;;
CREATE TRIGGER t1 AFTER INSERT ON t1 FOR EACH ROW BEGIN
  INSERT INTO t1 VALUES(1);
END;;

cat >$topdir/tables_file <<EOF
test.t1
EOF

innobackupex --no-timestamp --tables-file=$topdir/tables_file $topdir/backup

$ ls var1/backup/test/
t1.TRG t1.TRN t1.frm t1.ibd

Tested on both 2.0 and 2.1.

What was the contents of the tables file?

[Jervin R (revin)]

Alexey,

This happens when the trigger name is different than the table name i.e. in your test instead of:

CREATE TRIGGER t1 AFTER INSERT ON t1 FOR EACH ROW BEGIN

do:

CREATE TRIGGER t1test AFTER INSERT ON t1 FOR EACH ROW BEGIN

[Alexey Kopytov (akopytov)]

OK, I see the problem. So for partial backups XtraBackup should query INFORMATION_SCHEMA.TRIGGERS to get a list of trigger names associated with that table.

A workaround is to include trigger names as table names into a partial backup definition. For example, if the trigger name is 't1test' in your example, the following tables file will copy it as well:

test.t1
test.t1test

[Shahriyar Rzayev (rzayev-sehriyar)]

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