bzr commit into mysql-6.0-backup branch (Rafal.Somla:2879) Bug#47386

[Rafal Somla]

#At file:///ext/mysql/bzr/backup/bug47386/ based on revid:thavamuni.alagu@stripped

 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.

    added:
      mysql-test/suite/backup/r/backup_dbname_case.result
      mysql-test/suite/backup/t/backup_dbname_case.test
    modified:
      sql/si_objects.cc
=== added file 'mysql-test/suite/backup/r/backup_dbname_case.result'
--- a/mysql-test/suite/backup/r/backup_dbname_case.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup/r/backup_dbname_case.result	2009-10-09 09:55:50 +0000
@@ -0,0 +1,63 @@
+#
+# Creating databases and user.
+# 
+CREATE DATABASE db1;
+CREATE DATABASE DB1;
+CREATE USER joe;
+#
+# Creating objects.
+# 
+USE db1;
+CREATE TABLE t1 (a int);
+CREATE VIEW v1 AS SELECT * FROM t1;
+CREATE PROCEDURE p1() SET @foo=1;
+CREATE FUNCTION f1() RETURNS int RETURN 1;
+CREATE TRIGGER r1 AFTER INSERT ON t1 FOR EACH ROW SET @foo=1;
+CREATE EVENT e1 ON SCHEDULE EVERY 1 YEAR DO SET @foo=1;
+GRANT  INSERT ON t1 TO joe;
+#
+# Backing-up and restoring databases.
+#
+BACKUP DATABASE db1, DB1 TO 'databases.bkp';
+backup_id
+#
+RESTORE FROM 'databases.bkp' OVERWRITE;
+backup_id
+#
+USE information_schema;
+#
+# Only db1 should contain any objects.
+#
+SELECT table_schema, table_name, table_type  
+FROM tables WHERE table_schema LIKE 'DB%';
+table_schema	table_name	table_type
+db1	t1	BASE TABLE
+db1	v1	VIEW
+--
+SELECT routine_schema, routine_name, routine_type  
+FROM routines WHERE routine_schema LIKE 'DB%';
+routine_schema	routine_name	routine_type
+db1	f1	FUNCTION
+db1	p1	PROCEDURE
+--
+SELECT trigger_schema, trigger_name
+FROM triggers WHERE trigger_schema LIKE 'DB%';
+trigger_schema	trigger_name
+db1	r1
+--
+SELECT event_schema, event_name
+FROM events WHERE event_schema LIKE 'DB%';
+event_schema	event_name
+db1	e1
+--
+SHOW GRANTS FOR joe;
+Grants for joe@%
+GRANT USAGE ON *.* TO 'joe'@'%'
+GRANT INSERT ON `db1`.`t1` TO 'joe'@'%'
+--
+#
+# Cleanup.
+#
+DROP DATABASE DB1;
+DROP DATABASE db1;
+DROP USER joe;

=== added file 'mysql-test/suite/backup/t/backup_dbname_case.test'
--- a/mysql-test/suite/backup/t/backup_dbname_case.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup/t/backup_dbname_case.test	2009-10-09 09:55:50 +0000
@@ -0,0 +1,71 @@
+#
+# Test scenario for BUG#47386. Check that objects which exist
+# in database db1 are not wrongly treated as objects of database DB1
+# as well.
+#
+# Note: This test can be run only on case-sensitive system because
+# it creates databases db1 and DB1.
+#
+--source include/not_embedded.inc
+--source include/have_case_sensitive_file_system.inc
+
+let $BDIR=`select @@backupdir`;
+
+--echo #
+--echo # Creating databases and user.
+--echo # 
+CREATE DATABASE db1;
+CREATE DATABASE DB1;
+CREATE USER joe;
+
+--echo #
+--echo # Creating objects.
+--echo # 
+USE db1;
+
+CREATE TABLE t1 (a int);
+CREATE VIEW v1 AS SELECT * FROM t1;
+CREATE PROCEDURE p1() SET @foo=1;
+CREATE FUNCTION f1() RETURNS int RETURN 1;
+CREATE TRIGGER r1 AFTER INSERT ON t1 FOR EACH ROW SET @foo=1;
+CREATE EVENT e1 ON SCHEDULE EVERY 1 YEAR DO SET @foo=1;
+GRANT  INSERT ON t1 TO joe;
+
+# Note that database DB1 is empty
+
+--echo #
+--echo # Backing-up and restoring databases.
+--echo #
+
+--replace_column 1 #
+BACKUP DATABASE db1, DB1 TO 'databases.bkp';
+--replace_column 1 #
+RESTORE FROM 'databases.bkp' OVERWRITE;
+
+USE information_schema;
+
+--echo #
+--echo # Only db1 should contain any objects.
+--echo #
+SELECT table_schema, table_name, table_type  
+  FROM tables WHERE table_schema LIKE 'DB%';
+--echo --
+SELECT routine_schema, routine_name, routine_type  
+  FROM routines WHERE routine_schema LIKE 'DB%';
+--echo --
+SELECT trigger_schema, trigger_name
+  FROM triggers WHERE trigger_schema LIKE 'DB%';
+--echo --
+SELECT event_schema, event_name
+  FROM events WHERE event_schema LIKE 'DB%';
+--echo --
+SHOW GRANTS FOR joe;
+--echo --
+--echo #
+--echo # Cleanup.
+--echo #
+
+DROP DATABASE DB1;
+DROP DATABASE db1;
+DROP USER joe;
+--remove_file $BDIR/databases.bkp

=== modified file 'sql/si_objects.cc'
--- a/sql/si_objects.cc	2009-09-28 09:13:54 +0000
+++ b/sql/si_objects.cc	2009-10-09 09:55:50 +0000
@@ -2573,16 +2573,16 @@ uint get_num_objects(THD *thd, const Str
   s_stream <<
     "SELECT TABLE_NAME "
     "FROM INFORMATION_SCHEMA.TABLES "
-    "WHERE table_schema = '" << db_name << "' UNION " 
+    "WHERE table_schema COLLATE utf8_bin = '" << db_name << "' UNION " 
     "SELECT TRIGGER_NAME "
     "FROM INFORMATION_SCHEMA.TRIGGERS "
-    "WHERE trigger_schema = '" << db_name << "' UNION " 
+    "WHERE trigger_schema COLLATE utf8_bin = '" << db_name << "' UNION " 
     "SELECT ROUTINE_NAME "
     "FROM INFORMATION_SCHEMA.ROUTINES "
-    "WHERE routine_schema = '" << db_name << "' UNION " 
+    "WHERE routine_schema COLLATE utf8_bin = '" << db_name << "' UNION " 
     "SELECT EVENT_NAME "
     "FROM INFORMATION_SCHEMA.EVENTS "
-    "WHERE event_schema = '" << db_name << "'";
+    "WHERE event_schema COLLATE utf8_bin = '" << db_name << "'";
 
   if (run_service_interface_sql(thd, &ed_connection, s_stream.lex_string()))
     /* Query failed with an error */
@@ -2674,7 +2674,7 @@ Obj_iterator *get_db_triggers(THD *thd, 
   s_stream <<
     "SELECT '" << db_name << "', trigger_name "
     "FROM INFORMATION_SCHEMA.TRIGGERS "
-    "WHERE trigger_schema = '" << db_name << "'";
+    "WHERE trigger_schema COLLATE utf8_bin = '" << db_name << "'";
 
   return create_row_set_iterator<Db_trigger_iterator>(thd, s_stream.lex_string());
 }
@@ -2687,7 +2687,7 @@ Obj_iterator *get_db_stored_procedures(T
   s_stream <<
     "SELECT '" << db_name << "', routine_name "
     "FROM INFORMATION_SCHEMA.ROUTINES "
-    "WHERE routine_schema = '" << db_name << "' AND "
+    "WHERE routine_schema COLLATE utf8_bin = '" << db_name << "' AND "
     "routine_type = 'PROCEDURE'";
 
   return create_row_set_iterator<Db_stored_proc_iterator>(thd, s_stream.lex_string());
@@ -2701,7 +2701,7 @@ Obj_iterator *get_db_stored_functions(TH
   s_stream <<
     "SELECT '" << db_name << "', routine_name "
     "FROM INFORMATION_SCHEMA.ROUTINES "
-    "WHERE routine_schema = '" << db_name <<"' AND "
+    "WHERE routine_schema COLLATE utf8_bin = '" << db_name <<"' AND "
     "routine_type = 'FUNCTION'";
 
   return create_row_set_iterator<Db_stored_func_iterator>(thd, s_stream.lex_string());
@@ -2716,7 +2716,7 @@ Obj_iterator *get_db_events(THD *thd, co
   s_stream <<
     "SELECT '" << db_name << "', event_name "
     "FROM INFORMATION_SCHEMA.EVENTS "
-    "WHERE event_schema = '" << db_name <<"'";
+    "WHERE event_schema COLLATE utf8_bin = '" << db_name <<"'";
 
   return create_row_set_iterator<Db_event_iterator>(thd, s_stream.lex_string());
 #else


Attachment: [text/bzr-bundle] bzr/[e-mail]