#At file:///localhome/jl208045/mysql/mysql-6.0-backup-43363/ based on revid:jorgen.loland@stripped
2809 Jorgen Loland 2009-04-01
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.
@ 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/kernel.cc
Store database names and table names in lower case in the catalog if RESTORE is performed on a case insensitive server.
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/kernel.cc
=== 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-04-01 11:08:48 +0000
@@ -0,0 +1,172 @@
+
+# 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
+
+# Activate debug hook that makes db names upper-case when checking if
+# a grant statement operates on a db that is restored. Should fail
+SET SESSION DEBUG='+d,restore_catalog_uppercase_names_grant';
+RESTORE FROM 'uppercase.bup';
+ERROR HY000: The grant '22 'user_with_grants'@'%
+22 INSERT ON changecase.*
+32 SET chara' failed. Database not included in the backup image.
+SET SESSION DEBUG='-d';
+DROP DATABASE changecase;
+# Activate debug hook that makes all names upper-case. Should work.
+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-04-01 11:08:48 +0000
@@ -0,0 +1 @@
+--lower_case_table_names=1
=== 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-04-01 11:08:48 +0000
@@ -0,0 +1,140 @@
+#
+# Test to ensure that RESTORE works on a server with a case insensitive
+# file system when the BACKUP was made on a server with case sensitive
+# file system.
+#
+# The tests in this file use error injection. A better way to test
+# this scenario is to backup a database with
+# database/table/view/procedure/etc names in upper or camel case on a
+# host with case sensitive file system (e.g., Linux), and then restore
+# on a host with case insensitive file system (e.g., Windows).
+#
+# WL#4771 will add tests as described above. Once WL#4771 is pushed,
+# it should be considered whether or not this test file can be
+# removed.
+
+--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
+
+--echo # Activate debug hook that makes db names upper-case when checking if
+--echo # a grant statement operates on a db that is restored. Should fail
+SET SESSION DEBUG='+d,restore_catalog_uppercase_names_grant';
+--error ER_BACKUP_GRANT_WRONG_DB
+RESTORE FROM 'uppercase.bup';
+SET SESSION DEBUG='-d';
+
+DROP DATABASE changecase;
+
+--echo # Activate debug hook that makes all names upper-case. Should work.
+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/kernel.cc'
--- a/sql/backup/kernel.cc 2009-03-16 14:38:05 +0000
+++ b/sql/backup/kernel.cc 2009-04-01 11:08:48 +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
@@ -2198,6 +2206,14 @@ int bcat_create_item(st_bstream_image_he
db_name.alloc(size);
db_name.length(0);
db_name.append(start, size);
+
+ if (lower_case_table_names == 1)
+ my_casedn_str(system_charset_info, db_name.c_ptr());
+
+ DBUG_EXECUTE_IF("restore_catalog_uppercase_names_grant",
+ my_caseup_str(system_charset_info, db_name.c_ptr());
+ );
+
if (!info->has_db(db_name))
{
log.report_error(ER_BACKUP_GRANT_WRONG_DB, create_stmt);
Attachment: [text/bzr-bundle] bzr/jorgen.loland@sun.com-20090401110848-2yt5nieadit7mqby.bundle
