MySQL Bugs: #39063: Online Backup: Backup behavior changes with the case used for the database name

Bug #39063	Online Backup: Backup behavior changes with the case used for the database name
Submitted:	27 Aug 2008 5:35	Modified:	3 Apr 2009 3:05
Reporter:	Priyesh Narayanan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S3 (Non-critical)
Version:	6.0.6-alpha-debug	OS:	Windows
Assigned to:	Jørgen Løland	CPU Architecture:	Any

Description:
Happened to notice this bug when I was trying out online backup. Kinda strange, but here it is:
What I did:
Backed up a database named 'test' using two commands:

mysql> backup database test to "c:\\test_bak";
mysql> backup database TEST to "c:\\TEST1_bak";

What I expected:
Expected the same behavior for both commands - that of backing up the database successfully.

What I get:
The backup completed with no errors in both cases, however for the second command, the resultant backup file was effectively empty (only header info I guess). No data got backed up for the second. Restoring the second backup gives me an empty database.

Note that my 'show databases' command returns 'test' and not 'TEST'.

How to repeat:
Here's what I did:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 116
Server version: 6.0.6-alpha-debug mysql-6.0-backup-myisam clone

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
+----------------+
1 row in set (0.01 sec)

mysql> select * from t1;
+------+
| age  |
+------+
|   30 |
+------+
1 row in set (0.00 sec)

mysql> backup database test to "c:\\test_bak";
+-----------+
| backup_id |
+-----------+
| 290       |
+-----------+
1 row in set, 1 warning (0.09 sec)

mysql> backup database TEST to "c:\\TEST1_bak";
+-----------+
| backup_id |
+-----------+
| 291       |
+-----------+
1 row in set, 1 warning (0.06 sec)

mysql> use mysql;
Database changed
mysql> select backup_id, command, backup_file, backup_state, error_num, num_objects, total_bytes from online_backup where backup_id >=290;
+-----------+-----------------------------------------+--------------+--------------+-----------+-------------+-------------+
| backup_id | command                                 | backup_file  | backup_state | error_num | num_objects | total_bytes |
+-----------+-----------------------------------------+--------------+--------------+-----------+-------------+-------------+
|       290 | backup database test to "c:\\test_bak"  | c:\test_bak  | complete     |         0 |           1 |        1033 |
|       291 | backup database TEST to "c:\\TEST1_bak" | c:\TEST1_bak | complete     |         0 |           0 |           0 |
+-----------+-----------------------------------------+--------------+--------------+-----------+-------------+-------------+
2 rows in set (0.00 sec)

mysql>

Thank you for a bug report. Verified just as described. BACKUP statement treats database name as case-sensitive to some extent on Windows.

Repeatable on Linux if the server is started with --lower_case_table_names=1

The problem is in si_objects.cc:get_db_tables:

  s_stream <<
    "SELECT '" << db_name << "', table_name "
    "FROM INFORMATION_SCHEMA.TABLES "
    "WHERE table_schema = '" << db_name << "' AND "
    "table_type = 'BASE TABLE'";

The where clause "table_schema='X'" is case sensitive, but the database is stored in lower case I_S.TABLES:

mysql> select table_schema, table_name from INFORMATION_SCHEMA.TABLES WHERE table_schema = 'X' AND table_type = 'BASE TABLE';
Empty set (0.01 sec)

mysql> select table_schema, table_name from INFORMATION_SCHEMA.TABLES WHERE table_schema like 'X' AND table_type = 'BASE TABLE';
+--------------+------------+
| table_schema | table_name |
+--------------+------------+
| x            | t          | 
+--------------+------------+
1 row in set (0.01 sec)

Numerous places in the code have case-sensitive SELECTS like the one above. The best way to solve this seems to be to use database names in correct case in the first place.

Suggested solution: If BACKUP is executed in a case-insensitive environment, the database names are converted to lower case before adding them to the backup catalog. Case sensitivity can be checked with global variable lower_case_table_names != 0.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/68192

2784 Jorgen Loland	2009-03-04
      Bug#39063 - Online Backup: Backup behavior changes with the case used for the database name
      
      Before, the database list for BACKUP was case sensitive. This caused problems in case insensitive servers because database 'X' and 'x' is considered the same database while backup would fail if the wrong case was used.
      
      With this patch, the database names are converted to lower case when BACKUP is executed on a case insensitive server.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/68363

2784 Jorgen Loland	2009-03-05
      Bug#39063 - Online Backup: Backup behavior changes with the case used for the database name
            
      Before, the database list for BACKUP was case sensitive. This caused problems in case insensitive servers because database 'X' and 'x' is considered the same database while backup would fail if the wrong case was used.
            
      With this patch, the database names are converted to lower case when BACKUP is executed on a case insensitive server.

The latest patch normalizes the database name on case insensitive servers by selecting it from I_S.SCHEMATA.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/68469

2791 Jorgen Loland	2009-03-06
      Bug#39063 - Online Backup: Backup behavior changes with the case used for the
      database name
                  
      Before, the database list for BACKUP was case sensitive. This caused problems in
      case insensitive servers because database 'X' and 'x' is considered the same database
      while backup would fail if the wrong case was used.
                  
      With this patch, the database names are converted to lower case when BACKUP is
      executed on a case insensitive server.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/68744

2792 Jorgen Loland	2009-03-10
      Bug#39063 - Online Backup: Backup behavior changes with the case used for the
      database name
                        
      Before, the database list for BACKUP was case sensitive. This caused problems in
      case insensitive servers because database 'X' and 'x' is considered the same database
      while backup would fail if the wrong case was used.
                        
      With this patch, the database names are converted to lower case when BACKUP is
      executed on a case insensitive server.

Good to push.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/68846

2793 Jorgen Loland	2009-03-11 [merge]
      Local merge to push bug#39063

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/70153

2792 Jorgen Loland	2009-03-24
      Bug#39063 - Online Backup: Backup behavior changes with the case used for the database name
      
      Followup patch: 
       * rename backup_dbname_notwin to backup_dbname_lctn0 for uniformity
       * make backup_dbname_lctn0 execute on case sensitive file systems only
       * make backup_dbname_lctn2 execute on case insensitive file systems only
     @ mysql-test/suite/backup/r/backup_dbname_lctn0.result
        Rename test file
     @ mysql-test/suite/backup/t/backup_dbname_lctn0-master.opt
        Explicitly execute backup_dbname_lctn0.test with system variable lower_case_table_names=0
     @ mysql-test/suite/backup/t/backup_dbname_lctn0.test
        Rename test file and make it run on servers with case sensitive file systems only
     @ mysql-test/suite/backup/t/backup_dbname_lctn2.test
        Make test run on servers with case insensitive file systems only

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/70230

2786 Jorgen Loland	2009-03-24
      Bug#39063 - Online Backup: Backup behavior changes with the case used for the database name
            
      Followup patch: 
       * rename backup_dbname_notwin to backup_dbname_lctn0 for uniformity
       * make backup_dbname_lctn0 execute on case sensitive file systems only
       * make backup_dbname_lctn2 execute on case insensitive file systems only
     @ mysql-test/suite/backup/r/backup_dbname_lctn0.result
        Rename test file
     @ mysql-test/suite/backup/t/backup_dbname_lctn0-master.opt
        Explicitly execute backup_dbname_lctn0.test with system variable lower_case_table_names=0
     @ mysql-test/suite/backup/t/backup_dbname_lctn0.test
        Rename test file and make it run on servers with case sensitive file systems only
     @ mysql-test/suite/backup/t/backup_dbname_lctn2.test
        Make test run on servers with case insensitive file systems only

Pushed into 6.0.11-alpha (revid:alik@sun.com-20090326121822-pt84kzxxayzho4mn) (version source revid:jorgen.loland@sun.com-20090324154454-5ck7m84c24lmi176) (merge vers: 6.0.11-alpha) (pib:6)

Noted in 6.0.11 changelog.

BACKUP DATABASE treated the database list in case-sensitive fashion,
even on case-insensitive file systems.

Pushed into 6.0.11-alpha (revid:alik@sun.com-20090423070920-e5lq3vrrqi016z2c) (version source revid:alik@sun.com-20090423070920-e5lq3vrrqi016z2c) (merge vers: 6.0.11-alpha) (pib:6)