STATUS
------
Approved.
COMMENT
-------
Thanks for taking into account my suggestions.
Jorgen Loland wrote:
> #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);
>
>
>
> ------------------------------------------------------------------------
>
>
