From: Jorgen Loland Date: March 20 2009 2:00pm Subject: bzr commit into mysql-6.0-backup branch (jorgen.loland:2803) Bug#43363 List-Archive: http://lists.mysql.com/commits/69920 X-Bug: 43363 Message-Id: <20090320140013.5A692BCC00F@atum06.norway.sun.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit #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