MySQL Bugs: #54723: MEB: table name to file name translation not done

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

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).

Test script

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

Options file required for the test script

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

Result file

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

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.

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

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.

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."

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."