List:Commits« Previous MessageNext Message »
From:Jorgen Loland Date:March 20 2009 2:00pm
Subject:bzr commit into mysql-6.0-backup branch (jorgen.loland:2803) Bug#43363
View as plain text  
#At file:///localhome/jl208045/mysql/mysql-6.0-backup-43363/

 2803 Jorgen Loland	2009-03-20
      Bug#43363 - Backup: Backup on case sensitive server restore on case insensitive server fails
      
      Before, RESTORE on a case insensitive server would fail if the backup image contained databases or tables with upper case names. 
      
      Now, RESTORE is able to handle this case by converting the names to lowercase in the restore catalog as long as there are no duplicate names after the conversion. The duplicate name problem is logged as bug 43596.
added:
  mysql-test/suite/backup/r/backup_namecase.result
  mysql-test/suite/backup/t/backup_namecase-master.opt
  mysql-test/suite/backup/t/backup_namecase.test
modified:
  sql/backup/image_info.cc
  sql/backup/kernel.cc

per-file messages:
  mysql-test/suite/backup/r/backup_namecase.result
    Test that RESTORE on a case insensitive server works even if the database names are added to the catalog in upper case.
  mysql-test/suite/backup/t/backup_namecase-master.opt
    Make backup_namecase run on case insensitive server.
  mysql-test/suite/backup/t/backup_namecase.test
    Test that RESTORE on a case insensitive server works even if the database names are added to the catalog in upper case.
  sql/backup/image_info.cc
    If restored on a case insensitive server, the code now checks if the lower case database name is in the backup image before restoring a grant. This is needed because the catalog stores names in lower case in this case.
  sql/backup/kernel.cc
    Store database names and table names in lower case in the catalog if RESTORE is performed on a case insensitive server.
=== added file 'mysql-test/suite/backup/r/backup_namecase.result'
--- a/mysql-test/suite/backup/r/backup_namecase.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup/r/backup_namecase.result	2009-03-20 14:00:02 +0000
@@ -0,0 +1,162 @@
+
+# Create database with tables and views
+
+CREATE DATABASE changecase;
+CREATE TABLE changecase.t1 (i int);
+INSERT INTO changecase.t1 VALUES (1);
+CREATE TABLE changecase.t2 (text varchar(20));
+CREATE VIEW changecase.v1 AS SELECT * FROM changecase.t1;
+
+# Add trigger and procedure
+
+CREATE PROCEDURE changecase.trgmsg(a int)
+BEGIN
+INSERT INTO changecase.t2(text) VALUES ('Db trigger fired!');
+END;
+||
+CREATE TRIGGER changecase.afterins AFTER INSERT ON changecase.t1 FOR EACH ROW 
+CALL trgmsg(1);
+||
+CREATE EVENT changecase.ev ON SCHEDULE EVERY 10 second DO 
+BEGIN
+INSERT INTO changecase.t2(text) VALUES ('Db event fired!');
+END;
+||
+
+# Add user with privileges
+
+CREATE USER user_with_grants;
+default: Grant user rights to run backup. Revoke SUPER from one user.
+GRANT SELECT ON changecase.t1 TO 'user_with_grants'@'%';
+GRANT SELECT ON changecase.v1 TO 'user_with_grants'@'%';
+GRANT UPDATE ON changecase.* TO 'user_with_grants'@'%';
+GRANT INSERT ON changecase.* TO 'user_with_grants'@'%';
+GRANT SUPER ON *.* TO 'user_with_grants'@'%';
+FLUSH PRIVILEGES;
+
+# Show procedure, trigger and event
+
+SHOW CREATE PROCEDURE changecase.trgmsg;;
+Procedure	trgmsg
+sql_mode	
+Create Procedure	CREATE DEFINER=`root`@`localhost` PROCEDURE `trgmsg`(a int)
+BEGIN
+INSERT INTO changecase.t2(text) VALUES ('Db trigger fired!');
+END
+character_set_client	latin1
+collation_connection	latin1_swedish_ci
+Database Collation	latin1_swedish_ci
+
+SHOW TRIGGERS FROM changecase;;
+Trigger	afterins
+Event	INSERT
+Table	t1
+Statement	CALL trgmsg(1)
+Timing	AFTER
+Created	NULL
+sql_mode	
+Definer	root@localhost
+character_set_client	latin1
+collation_connection	latin1_swedish_ci
+Database Collation	latin1_swedish_ci
+
+SHOW EVENTS IN changecase;;
+Db	changecase
+Name	ev
+Definer	root@localhost
+Time zone	SYSTEM
+Type	RECURRING
+Execute at	NULL
+Interval value	10
+Interval field	SECOND
+Starts	#
+Ends	NULL
+Status	ENABLED
+Originator	1
+character_set_client	latin1
+collation_connection	latin1_swedish_ci
+Database Collation	latin1_swedish_ci
+
+# BACKUP, drop database and user
+
+BACKUP DATABASE changecase TO 'uppercase.bup';
+backup_id
+#
+DROP DATABASE changecase;
+REVOKE ALL ON *.* FROM 'user_with_grants'@'%';
+
+# RESTORE database
+
+SET SESSION DEBUG='+d,restore_catalog_uppercase_names';
+RESTORE FROM 'uppercase.bup';
+backup_id
+#
+SET SESSION DEBUG='-d';
+
+# Check contents of restored database
+
+
+SHOW TABLES IN changecase;
+Tables_in_changecase
+t1
+t2
+v1
+
+SELECT * FROM changecase.t1;
+i
+1
+
+# Show procedure, trigger and event
+
+SHOW CREATE PROCEDURE changecase.trgmsg;;
+Procedure	trgmsg
+sql_mode	
+Create Procedure	CREATE DEFINER=`root`@`localhost` PROCEDURE `trgmsg`(a int)
+BEGIN
+INSERT INTO changecase.t2(text) VALUES ('Db trigger fired!');
+END
+character_set_client	latin1
+collation_connection	latin1_swedish_ci
+Database Collation	latin1_swedish_ci
+
+SHOW TRIGGERS FROM changecase;;
+Trigger	afterins
+Event	INSERT
+Table	t1
+Statement	CALL trgmsg(1)
+Timing	AFTER
+Created	NULL
+sql_mode	
+Definer	root@localhost
+character_set_client	latin1
+collation_connection	latin1_swedish_ci
+Database Collation	latin1_swedish_ci
+
+SHOW EVENTS IN changecase;;
+Db	changecase
+Name	ev
+Definer	root@localhost
+Time zone	SYSTEM
+Type	RECURRING
+Execute at	NULL
+Interval value	10
+Interval field	SECOND
+Starts	#
+Ends	NULL
+Status	ENABLED
+Originator	1
+character_set_client	latin1
+collation_connection	latin1_swedish_ci
+Database Collation	latin1_swedish_ci
+
+SHOW GRANTS FOR 'user_with_grants'@'%';
+Grants for user_with_grants@%
+GRANT USAGE ON *.* TO 'user_with_grants'@'%'
+GRANT INSERT, UPDATE ON `changecase`.* TO 'user_with_grants'@'%'
+GRANT SELECT ON `changecase`.`v1` TO 'user_with_grants'@'%'
+GRANT SELECT ON `changecase`.`t1` TO 'user_with_grants'@'%'
+
+# Cleanup
+
+DROP DATABASE changecase;
+DROP USER user_with_grants;

=== added file 'mysql-test/suite/backup/t/backup_namecase-master.opt'
--- a/mysql-test/suite/backup/t/backup_namecase-master.opt	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup/t/backup_namecase-master.opt	2009-03-20 14:00:02 +0000
@@ -0,0 +1 @@
+--lower_case_table_names=1
\ No newline at end of file

=== added file 'mysql-test/suite/backup/t/backup_namecase.test'
--- a/mysql-test/suite/backup/t/backup_namecase.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup/t/backup_namecase.test	2009-03-20 14:00:02 +0000
@@ -0,0 +1,116 @@
+--source include/have_debug_sync.inc
+--source include/not_embedded.inc
+
+--echo
+--echo # Create database with tables and views
+--echo
+
+CREATE DATABASE changecase;
+
+CREATE TABLE changecase.t1 (i int);
+INSERT INTO changecase.t1 VALUES (1);
+
+CREATE TABLE changecase.t2 (text varchar(20));
+
+CREATE VIEW changecase.v1 AS SELECT * FROM changecase.t1;
+
+
+--echo
+--echo # Add trigger and procedure
+--echo
+
+DELIMITER ||;
+
+CREATE PROCEDURE changecase.trgmsg(a int)
+BEGIN
+    INSERT INTO changecase.t2(text) VALUES ('Db trigger fired!');
+END;
+||
+
+CREATE TRIGGER changecase.afterins AFTER INSERT ON changecase.t1 FOR EACH ROW 
+CALL trgmsg(1);
+||
+
+CREATE EVENT changecase.ev ON SCHEDULE EVERY 10 second DO 
+BEGIN
+  INSERT INTO changecase.t2(text) VALUES ('Db event fired!');
+END;
+||
+
+DELIMITER ;||
+
+--echo
+--echo # Add user with privileges
+--echo
+
+CREATE USER user_with_grants;
+
+--echo default: Grant user rights to run backup. Revoke SUPER from one user.
+GRANT SELECT ON changecase.t1 TO 'user_with_grants'@'%';
+GRANT SELECT ON changecase.v1 TO 'user_with_grants'@'%';
+GRANT UPDATE ON changecase.* TO 'user_with_grants'@'%';
+GRANT INSERT ON changecase.* TO 'user_with_grants'@'%';
+GRANT SUPER ON *.* TO 'user_with_grants'@'%';
+FLUSH PRIVILEGES;
+
+--echo
+--echo # Show procedure, trigger and event
+--echo
+
+--query_vertical SHOW CREATE PROCEDURE changecase.trgmsg;
+--echo
+--query_vertical SHOW TRIGGERS FROM changecase;
+--echo
+--replace_column 9 #
+--query_vertical SHOW EVENTS IN changecase;
+
+
+--echo
+--echo # BACKUP, drop database and user
+--echo
+
+--replace_column 1 #
+BACKUP DATABASE changecase TO 'uppercase.bup';
+
+DROP DATABASE changecase;
+REVOKE ALL ON *.* FROM 'user_with_grants'@'%';
+
+--echo
+--echo # RESTORE database
+--echo
+
+# Activate debug hook that makes all names upper-case
+SET SESSION DEBUG='+d,restore_catalog_uppercase_names';
+--replace_column 1 #
+RESTORE FROM 'uppercase.bup';
+SET SESSION DEBUG='-d';
+
+--echo
+--echo # Check contents of restored database
+--echo
+
+--echo
+SHOW TABLES IN changecase;
+
+--echo
+SELECT * FROM changecase.t1;
+
+--echo
+--echo # Show procedure, trigger and event
+--echo
+
+--query_vertical SHOW CREATE PROCEDURE changecase.trgmsg;
+--echo
+--query_vertical SHOW TRIGGERS FROM changecase;
+--echo
+--replace_column 9 #
+--query_vertical SHOW EVENTS IN changecase;
+
+--echo
+SHOW GRANTS FOR 'user_with_grants'@'%';
+
+--echo
+--echo # Cleanup
+--echo
+DROP DATABASE changecase;
+DROP USER user_with_grants;

=== modified file 'sql/backup/image_info.cc'
--- a/sql/backup/image_info.cc	2009-02-16 12:20:31 +0000
+++ b/sql/backup/image_info.cc	2009-03-20 14:00:02 +0000
@@ -215,8 +215,12 @@ int Image_info::add_snapshot(Snapshot_in
  */ 
 bool Image_info::has_db(const String &db_name) const
 {
-  for (uint n=0; n < m_dbs.count() ; ++n)
-    if (m_dbs[n] && m_dbs[n]->name() == db_name)
+  String canonical_name;
+  canonical_name.copy(db_name.ptr(), db_name.length(), system_charset_info);
+  if (lower_case_table_names == 1)
+    my_casedn_str(canonical_name.charset(), canonical_name.c_ptr());
+  for (uint n=0; n < m_dbs.count(); ++n)
+    if (m_dbs[n] && m_dbs[n]->name() == canonical_name)
       return TRUE;
 
   return FALSE;

=== modified file 'sql/backup/kernel.cc'
--- a/sql/backup/kernel.cc	2009-03-16 14:38:05 +0000
+++ b/sql/backup/kernel.cc	2009-03-20 14:00:02 +0000
@@ -1753,6 +1753,9 @@ int bcat_add_item(st_bstream_image_heade
 
   backup::String name_str(item->name.begin, item->name.end);
 
+  DBUG_EXECUTE_IF("restore_catalog_uppercase_names",
+                  my_caseup_str(system_charset_info, name_str.c_ptr());
+                  );
   DBUG_PRINT("restore",("Adding item %s of type %d (pos=%ld)",
                         item->name.begin,
                         item->type,
@@ -1769,6 +1772,9 @@ int bcat_add_item(st_bstream_image_heade
 
   case BSTREAM_IT_DB:
   {
+
+    if (lower_case_table_names == 1)
+      my_casedn_str(system_charset_info, name_str.c_ptr());
     Image_info::Db *db= info->add_db(name_str, item->pos); // reports errors
 
     return db ? BSTREAM_OK : BSTREAM_ERROR;
@@ -1801,6 +1807,8 @@ int bcat_add_item(st_bstream_image_heade
 
     DBUG_PRINT("restore",(" table's database is %s", db->name().ptr()));
 
+    if (lower_case_table_names == 1)
+      my_casedn_str(system_charset_info, name_str.c_ptr());
     Image_info::Table *tbl= info->add_table(*db, name_str, *snap, item->pos); 
                                                              // reports errors
     

Thread
bzr commit into mysql-6.0-backup branch (jorgen.loland:2803) Bug#43363Jorgen Loland20 Mar
  • Re: bzr commit into mysql-6.0-backup branch (jorgen.loland:2803)Bug#43363Chuck Bell30 Mar
  • Re: bzr commit into mysql-6.0-backup branch (jorgen.loland:2803)Bug#43363Rafal Somla31 Mar