Table names containing chars other than [_a-zA-Z0-9] are skipped

[Vasco da Gama]

If a table name contains characters other than [_a-zA-Z0-9] the table is skipped. I asume that this is because the table file name does not match the table name any more. (See http://dev.mysql.com/doc/refman/5.1/en/identifier-mapping.html)

How to reproduce:
1. Create a table with a character that is not from [_a-zA-Z0-9] in its name, e.g. CREATE TABLE `test-table` (id int) engine=innodb; (The table data file names are then test@002dtable.* which seems to be the problem.
2. Put stuff in the table
3. Try to backup it using innobackupex

[Jervin R (revin)]

Vasco,

I am not able to reproduce this on Percona Server 5.5.25a, XtraBackup 2.0.1 - do you can you share your versions, my.cnf and specific procedure - a transcript like mine below can help.

[revin@forge msb_5_5_250]$ ./use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.25a-rel27.1 Percona Server with XtraDB (GPL), Release rel27.1, Revision 277

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql [localhost] {msandbox} (test) > drop table `test-ab`;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} (test) > set global innodb_file_per_table=1;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} (test) > create table `test-ab` (id int) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql [localhost] {msandbox} (test) > insert into `test-ab` values(100);
Query OK, 1 row affected (0.00 sec)

mysql [localhost] {msandbox} (test) > \q
Bye
[revin@forge msb_5_5_250]$ ls -alh data/test/
total 144K
drwx------. 2 revin revin 50 Aug 11 22:34 .
drwx------. 5 revin revin 4.0K Aug 11 22:32 ..
-rw-rw----. 1 revin revin 8.4K Aug 11 22:34 [e-mail]
-rw-rw----. 1 revin revin 96K Aug 11 22:35 [e-mail]
[revin@forge msb_5_5_250]$ innobackupex --defaults-file=/ssd/msb/msb_5_5_250/my.sandbox.cnf /ssd/msb/msb_5_5_250/bkp/

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona Inc 2009-2012. All Rights Reserved.

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

120811 22:35:22 innobackupex: Starting mysql with options: --defaults-file='/ssd/msb/msb_5_5_250/my.sandbox.cnf' --unbuffered --
120811 22:35:22 innobackupex: Connected to database with mysql child process (pid=14750)
120811 22:35:28 innobackupex: Connection to database server closed
IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".

innobackupex: Using mysql Ver 14.14 Distrib 5.5.27, for Linux (x86_64) using readline 5.1
innobackupex: Using mysql server version Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

innobackupex: Created backup directory /ssd/msb/msb_5_5_250/bkp/2012-08-11_22-35-28
120811 22:35:28 innobackupex: Starting mysql with options: --defaults-file='/ssd/msb/msb_5_5_250/my.sandbox.cnf' --unbuffered --
120811 22:35:28 innobackupex: Connected to database with mysql child process (pid=14778)
120811 22:35:30 innobackupex: Connection to database server closed

120811 22:35:30 innobackupex: Starting ibbackup with command: xtrabackup_55 --defaults-file="/ssd/msb/msb_5_5_250/my.sandbox.cnf" --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/ssd/msb/msb_5_5_250/bkp/2012-08...

[Vasco da Gama (iqb)]

Hello Jervin,

thanks for the fast response. The problem only occures when using a list of tables (not backing up everything).

I have put some information below that should help you reproduce:

test01 /var/lib/mysql/test # mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 233
Server version: 5.1.63-0+squeeze1 (Debian)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test-table |
| test_table |
+----------------+
2 rows in set (0.00 sec)

mysql>

test01 /var/lib/mysql/test # ls -la
insgesamt 385160
drwx------ 2 mysql mysql 4096 12. Aug 05:06 .
drwx------ 4 mysql mysql 4096 12. Aug 05:05 ..
-rw-rw---- 1 mysql mysql 65 9. Aug 20:05 db.opt
-rw-rw---- 1 mysql mysql 9094 12. Aug 03:32 [e-mail]
-rw-rw---- 1 mysql mysql 98304 12. Aug 03:49 [e-mail]
-rw-rw---- 1 mysql mysql 9094 9. Aug 20:06 test_table.frm
-rw-rw---- 1 mysql mysql 394264576 9. Aug 20:32 test_table.ibd

test01 / # innobackupex --defaults-file=/etc/mysql/my.cnf --no-timestamp --extra-lsndir=/backup --tables-file=/tmp/tables --no-lock --safe-slave --safe-slave-backup --stream=xbstream --tmpdir=/backup --target-dir=/backup /backup
innobackupex: test.test-table will be registerd to the list
innobackupex: test.test_table will be registerd to the list
... (the full log is attached)
>> log scanned up to (0 999456199)
[01] Streaming ./ibdata1
[01] ...done
[01] Streaming ./test/test_table.ibd
>> log scanned up to (0 999456199)
>> log scanned up to (0 999456199)
>> log scanned up to (0 999456199)
[01] ...done
[01] Skipping ./test/[e-mail]

test01 / # cat /tmp/tables
test.test_table
test.test-table

I hope this information helps you to pin down the problem.
Thanks,
Vasco

[Jervin R (revin)]

Vasco,

Can you try with /tmp/tables like this:

test.test_table
test.test@002dtable

[Vasco da Gama (iqb)]

Hello Jervin,

using test.test@002dtable in the tables file works. This is a little bit awkward as the tables file then does not hold the tables in $database.$table format but in $directory.$basename_of_tablefile format.

Why i came to this problem (and why I used the tables file at first instead of just create a complete backup): i only want to backup InnoDB tables. It would be nice to have a switch to innobackupex like --innodbonly to skip all non-innodb/xtradb tables or a way to exclude some tables.

Greets,
Vasco

[Shahriyar Rzayev (rzayev-sehriyar)]

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