| Bug #47386 | Backup fails to backup objects with case sensitive names on case sensitive OS | ||
|---|---|---|---|
| Submitted: | 16 Sep 2009 21:17 | Modified: | 7 Mar 2010 19:49 |
| Reporter: | Chuck Bell | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Backup | Severity: | S3 (Non-critical) |
| Version: | 5.4.4 | OS: | Linux |
| Assigned to: | Rafal Somla | CPU Architecture: | Any |
[17 Sep 2009 4:54]
Sveta Smirnova
Thank you for the report. Verified as described.
[17 Sep 2009 7:20]
Rafal Somla
Note that BUG#43363 fix concerns only RESTORE operation and only on servers which are case insensitive (lower_case_table_names set to 1). Thus it is unrelated to the reported behaviour which concerns BACKUP operation on a case sensitive server. The problem must lie somewhere else.
[1 Oct 2009 6:04]
Rafal Somla
REFINED PROBLEM DESCRIPTION The problem lies in si_object code for enumerating triggers in a given database. Function get_db_triggers() in si_objects creates an iterator which iterates over results of a query: SELECT '<db_name>', trigger_name FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_schema = '<db_name>'; where <db_name> is the name of the database. In the example given above this will be a query: SELECT 'BACKUP_TEST', trigger_name FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_schema = 'BACKUP_TEST'; Surprisingly, the comparison in WHERE clause is *not* case sensitive. Thus the query returns triggers defined for 'backup_test' database. If this query is added to the provided test script, one can see that it returns: SELECT 'BACKUP_TEST', trigger_name FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_schema = 'BACKUP_TEST'; BACKUP_TEST trigger_name BACKUP_TEST Trg BACKUP_TEST trg This is not what is expected - we expect an empty result set because there are no triggers in the BACKUP_TEST database. Btw, a similar query used to enumerate tables in a given database works as expected.
[1 Oct 2009 6:56]
Rafal Somla
I've reported Bug#47755 about ingnoring case in I_S queries.
[1 Oct 2009 17:45]
Rafal Somla
I did not realize that semantics of '=' on strings depends on the collation of the string. By default, the I_S table columns have a case-insensitive collation and this is why case is ignored (see BUG#34921 for discussion). This might or might not be a bug. However, it is possible to explicitly request a case-sensitive collation in the queries and this is a solution which I'm going to implement. I.e., modify the queries to say: SELECT trigger_schema, trigger_name FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_schema COLLATE utf8_bin = '<db_name>';
[1 Oct 2009 18:51]
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/85428 2876 Rafal Somla 2009-10-01 Bug #47386 - Backup fails to backup objects with case sensitive names on case sensitive OS Before: BACKUP failed if there were 2 databases whose names differ in case only and when one database contains an object which is missing in the other one. After: BACKUP correctly determines what objects are in which database even when database names differ in case only. @ mysql-test/suite/backup/t/backup_dbname_case.test Test script with the scenario from the bug. @ sql/si_objects.cc Add explicit "COLLATE utf8_bin" phrases to the SELECT queries.
[2 Oct 2009 1:20]
Chuck Bell
Patch conditionally accepted based on implementation of required item plus response to two requests made during review.
[7 Oct 2009 5:22]
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/85987 2879 Rafal Somla 2009-10-07 Bug #47386 - Backup fails to backup objects with case sensitive names on case sensitive OS Before: BACKUP failed if there were 2 databases whose names differ in case only and when one database contains an object which is missing in the other one. After: BACKUP correctly determines what objects are in which database even when database names differ in case only. @ mysql-test/suite/backup/t/backup_dbname_case.test Test script with the scenario from the bug. @ sql/si_objects.cc Add explicit "COLLATE utf8_bin" phrases to the SELECT queries. This is not done for table/view iterators because they already correctly distinguish case.
[9 Oct 2009 9:56]
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/86320 2879 Rafal Somla 2009-10-09 Bug #47386 - Backup fails to backup objects with case sensitive names on case sensitive OS Before: BACKUP failed if there were 2 databases whose names differ in case only and when one database contains an object which is missing in the other one. After: BACKUP correctly determines what objects are in which database even when database names differ in case only. @ mysql-test/suite/backup/t/backup_dbname_case.test Test script with the scenario from the bug. @ sql/si_objects.cc Add explicit "COLLATE utf8_bin" phrases to the SELECT queries. This is not done for table/view iterators because they already correctly distinguish case.
[9 Oct 2009 12:22]
Rafal Somla
Pushed into team 6.0-backup tree. revision-id:rafal.somla@sun.com-20091009095550-bb525yyjxr4u891s
[21 Jan 2010 18:58]
Paul DuBois
See http://dev.mysql.com/doc/refman/5.1/en/charset-collation-information-schema.html.
[20 Feb 2010 9:19]
Bugs System
Pushed into 6.0.14-alpha (revid:ingo.struewing@sun.com-20100218152520-s4v1ld76bif06eqn) (version source revid:ingo.struewing@sun.com-20100119103538-wtp5alpz4p2jayl5) (merge vers: 6.0.14-alpha) (pib:16)
[7 Mar 2010 19:49]
Paul DuBois
Noted in 6.0.14 changelog. Previously, BACKUP DATABASE failed if there were two databases whose names differed in lettercase only and when one database contained an object that was missing in the other. Now BACKUP DATABASE correctly determines which objects are in which database even when database names differ only in lettercase.
Description: Backup does not correctly preserve the case sensitivity of objects in the catalog. As a result, searches for metadata fail (see below). How to repeat: Overview 1. create database X 2. create database x 3. create table x.t1 4. create table x.T1 5. create trigger trg on x.t1 6. create trigger Trg on x.T1 7. BACKUP --> fail The following is an actual test case --source include/have_case_sensitive_file_system.inc CREATE DATABASE BACKUP_TEST; CREATE DATABASE backup_test; CREATE TABLE backup_test.t1 (a char(30)) ENGINE=MEMORY; CREATE TABLE backup_test.T1 (a char(30)) ENGINE=MEMORY; CREATE TRIGGER backup_test.trg AFTER INSERT ON backup_test.t1 FOR EACH ROW INSERT INTO backup_test.t1 VALUES('Test objects count'); CREATE TRIGGER backup_test.Trg AFTER INSERT ON backup_test.T1 FOR EACH ROW INSERT INTO backup_test.T1 VALUES('Test objects count'); --replace_column 1 # BACKUP DATABASE backup_test, BACKUP_TEST TO 'b1.bak'; DROP DATABASE BACKUP_TEST; DROP DATABASE backup_test; The result: mysqltest: At line 18: query 'BACKUP DATABASE backup_test, BACKUP_TEST TO 'b1.bak'' failed: 1728: Failed to obtain metadata for trigger `BACKUP_TEST`.`Trg` Notice there *is no* 'Trg' in database 'BACKUP_TEST'. It is in 'backup_test'. Suggested fix: Possibly undo changes made by BUG#43363 (makes everything lower case). Make catalog case sensitive and searches for metadata case sensitive.