Bug #54723 MEB: table name to file name translation not done
Submitted: 23 Jun 2010 8:30 Modified: 3 Oct 2014 23:37
Reporter: Ingo Strüwing Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Enterprise Backup Severity:S3 (Non-critical)
Version:3.5 OS:Any
Assigned to: Daniel So CPU Architecture:Any
Triage: Triaged: D3 (Medium)

[23 Jun 2010 8:30] Ingo Strüwing
Description:
Tables, created as
CREATE TABLE `db.2`.`t.2` (c1 INT) ENGINE=InnoDB;
CREATE TABLE `db-3`.`t-3` (c1 INT) ENGINE=InnoDB;
are stored in the file system as
db@002e2/t@002e2.ibd
db@002d3/t@002d3.ibd

To select these tables for partial backup, the pattern to use is
db@002e2\.t@002e2|db@002d3\.t@002d3

That is, the user has to do the table name to file name translation.

The user should be able to use the pattern
db\.2\.t\.2|db-3\.t-3

How to repeat:
I will attach a test case and its result file. These can be run in a branch of mysql-5.1-meb.

Suggested fix:
The server's translation is done in sql/sql_table.cc:build_table_filename().
Reverse translations are done in storage/innodb_plugin/handler/handler0alter.cc (using strconvert(&my_charset_filename, s, system_charset_info, ...)).

ibbackup (and innobackup, where needed) should reverse translate database and table file system names before matching them against the regular expression. If using strconvert(), 'system_charset_info' should be replaced by the character set used for the command line. So probably derived from the LANG environment variable, e.g. with nl_langinfo (*nix only).
[23 Jun 2010 8:31] Ingo Strüwing
Test script

Attachment: special_chars_ibbackup.test (application/octet-stream, text), 3.94 KiB.

[23 Jun 2010 8:32] Ingo Strüwing
Options file required for the test script

Attachment: special_chars_ibbackup-master.opt (application/octet-stream, text), 26 bytes.

[23 Jun 2010 8:32] Ingo Strüwing
Result file

Attachment: special_chars_ibbackup.result (application/octet-stream, text), 2.72 KiB.

[23 Jun 2010 8:38] Ingo Strüwing
Medium defect, an oversight. Backed up tables can be restored with no problems. Just the selection of which tables to back up can be uncomfortable.

Partial workaround is to use file system names for the selection pattern.

Minimal impact. No user ever complained about this, though the tool exists since several years. Probably users don't use names with special characters in them.
[28 Feb 2014 15:56] Daniël van Eeden
If this is still the case then it should be in the documentation:
http://dev.mysql.com/doc/mysql-enterprise-backup/3.9/en/bugs.backup.html
[7 Apr 2014 19:50] Daniel So
The need to use the converted names (db@002e2/t@002e2 and db@002d3/t@002d3) in the argument for selective backup (--include-tables and --exclude-tables) has been added as a limitation of mysqlbackup in the documentation. See  http://dev.mysql.com/doc/mysql-enterprise-backup/3.10/en/bugs.html.
[3 Oct 2014 23:37] Daniel So
With the bug fixed, the "limitation" added for it in the manual was removed. Added the following item to the MySQL Enterprise Backup 3.11 changelog.

"Tables were not filtered correctly for selective backup or restoration when the regular expression provided with the --include-tables or --exclude-tables option contained special characters (for example, literal “.” or “-”). This was because mysqlbackup did not perform the same conversion for special characters as the MySQL server does when it creates the file names for storing the tables, thus it could not work properly with the related table files. With this fix, mysqlbackup is now performing the same conversion, so that the tables are now properly filtered."
[11 Jun 2015 19:00] Daniel So
Posted by developer:
 
Added the following entry to the MySQL Enterprise Backup 3.12.1 changelog:

"When the --include-tables or --exclude-tables option was provided with a regular expression for table names containing special characters, mysqlbackup was handling the special characters incorrectly, and hence table names were not filtered properly and tables were copied incorrectly. With this fix, mysqlbackup handles and filters the table names correctly and takes backups of the matching tables."