| 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 | ||
| Category: | Server: Backup | Severity: | S3 (Non-critical) |
| Version: | 6.0.6-alpha-debug | OS: | Microsoft Windows |
| Assigned to: | Jorgen Loland | Target Version: | 6.0-beta |
| Triage: | Triaged: D2 (Serious) | ||
[27 Aug 2008 5:42]
Valeriy Kravchuk
Thank you for a bug report. Verified just as described. BACKUP statement treats database name as case-sensitive to some extent on Windows.
[27 Feb 2009 14:26]
Jorgen Loland
Repeatable on Linux if the server is started with --lower_case_table_names=1
[2 Mar 2009 10:33]
Jorgen Loland
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)
[3 Mar 2009 8:31]
Jorgen Loland
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.
[4 Mar 2009 10:00]
Bugs System
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.
[5 Mar 2009 11:46]
Bugs System
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.
[5 Mar 2009 11:58]
Jorgen Loland
The latest patch normalizes the database name on case insensitive servers by selecting it from I_S.SCHEMATA.
[6 Mar 2009 10:14]
Bugs System
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.
[10 Mar 2009 10:02]
Bugs System
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.
[10 Mar 2009 15:28]
Rafal Somla
Good to push.
[11 Mar 2009 7:01]
Bugs System
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
[24 Mar 2009 9:42]
Bugs System
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
[24 Mar 2009 15:43]
Bugs System
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
[26 Mar 2009 12:34]
Bugs System
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)
[3 Apr 2009 3:05]
Paul DuBois
Noted in 6.0.11 changelog. BACKUP DATABASE treated the database list in case-sensitive fashion, even on case-insensitive file systems.
[23 Apr 2009 7:18]
Bugs System
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)
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>