#At file:///C:/Work/mysql_checkouts/mysql-6.0-backup-02/ based on revid:thavamuni.alagu@stripped20091117201850-eh76ps0bxd7okeml
2894 Sanjay Manwani 2009-11-18
BUG#33354 Backup: restore changes current database to null
If there is a database set as a default database by
the SQL command "use <Database>"before a
restore; this default database gets reset to blank
after the restore.
The si context now also saves the default database,
and restores it .
@ si_objects.cc
Added a variable m_db_saved_string to
Si_session_context to save the default
database.
Extended the restore_si_ctx and save_si_ctx to
save and restore the default database into/from
this variable
@ backup_use_db_restore.test
New test to ensure the 'USE' database is still default
ensures using an keeping as default of ascii and
utf8 character databases.
@ backup_use_db_restore.result
Added the results of above test.
@ backup_objects.result
Changed this result file since objects are now
reported without the database. Changed the
object in the from e.g. db1.object to simply object.
@ backup_view.result
Same change as objects. Views are changed
to simply view1 instead of say db1.view1.
@ backup_events.test
An event was not visible since the test assumed
that the backed up database would be the default
database after restore. Added an explicit 'USE events'
to be able to view the event.
@ backup_events.result
Changed to reflect the change in the test.
added:
mysql-test/suite/backup/r/backup_use_db_restore.result
mysql-test/suite/backup/t/backup_use_db_restore.test
modified:
mysql-test/suite/backup/r/backup_events.result
mysql-test/suite/backup/r/backup_objects.result
mysql-test/suite/backup/r/backup_views.result
mysql-test/suite/backup/t/backup_events.test
sql/si_objects.cc
=== modified file 'mysql-test/suite/backup/r/backup_events.result'
--- a/mysql-test/suite/backup/r/backup_events.result 2009-07-21 15:45:20 +0000
+++ b/mysql-test/suite/backup/r/backup_events.result 2009-11-18 17:33:26 +0000
@@ -109,6 +109,7 @@ q
RESTORE FROM 'events1.bak' OVERWRITE;
backup_id
#
+USE events;
SHOW EVENTS;;
Db events
Name e1
=== modified file 'mysql-test/suite/backup/r/backup_objects.result'
--- a/mysql-test/suite/backup/r/backup_objects.result 2009-10-12 09:08:34 +0000
+++ b/mysql-test/suite/backup/r/backup_objects.result 2009-11-18 17:33:26 +0000
@@ -229,7 +229,7 @@ character_set_client latin2
collation_connection latin1_swedish_ci
SHOW CREATE VIEW db2.v21;;
View v21
-Create View CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v21` AS select `x`.`b` AS `b`,`db2`.`f21`(`y`.`a`) AS `a` from (`db1`.`v11` `x` join `db2`.`v22` `y`) where (`x`.`a` = `y`.`b`)
+Create View CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `db2`.`v21` AS select `x`.`b` AS `b`,`db2`.`f21`(`y`.`a`) AS `a` from (`db1`.`v11` `x` join `db2`.`v22` `y`) where (`x`.`a` = `y`.`b`)
character_set_client latin2
collation_connection latin1_swedish_ci
SHOW CREATE PROCEDURE db1.p11;;
=== added file 'mysql-test/suite/backup/r/backup_use_db_restore.result'
--- a/mysql-test/suite/backup/r/backup_use_db_restore.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup/r/backup_use_db_restore.result 2009-11-18 17:33:26 +0000
@@ -0,0 +1,64 @@
+#
+# This test ensures that if there is a database in use before
+# a "restore" command, then the same database remains the
+# default database even after restore.
+#
+# for details see : Bug#33354 : Backup: restore changes current
+# database to null
+#
+DROP DATABASE IF EXISTS db1;
+USE test;
+SELECT DATABASE();
+DATABASE()
+test
+CREATE DATABASE db1;
+CREATE TABLE db1.t (i int, j int) engine=myisam;
+INSERT INTO db1.t values(10, 11);
+BACKUP DATABASE db1 to 'image1.bak';
+backup_id
+#
+RESTORE FROM 'image1.bak' overwrite;
+backup_id
+#
+SELECT DATABASE();
+DATABASE()
+test
+#
+# Try again now using the database which is being restored
+#
+USE db1;
+SELECT DATABASE();
+DATABASE()
+db1
+BACKUP DATABASE db1 to 'image2.bak';
+backup_id
+#
+RESTORE FROM 'image2.bak' overwrite;
+backup_id
+#
+SELECT DATABASE();
+DATABASE()
+db1
+DROP DATABASE IF EXISTS `ﾆﾎracter_set_database = utf8;
+CREATE DATABASE `ﾎ�ｺﾞ`;
+SELECT DATABASE();
+DATABASE()
+ﾆﾎ�ｺﾞ
+BACKUP DATABASE db1 to 'image3.bak';
+backup_id
+#
+RESTORE FROM 'image3.bak' overwrite;
+backup_id
+#
+SELECT DATABASE();
+DATABASE()
+ﾆﾎ�ｺﾞ
+#
+# Cleanup
+#
+DROP TABLE db1.t;
+DROP DATABASE db1;
+DROP DATABASE `ﾆﾎ�ｺﾞ`;
=== modified file 'mysql-test/suite/backup/r/backup_views.result'
--- a/mysql-test/suite/backup/r/backup_views.result 2009-10-12 09:08:34 +0000
+++ b/mysql-test/suite/backup/r/backup_views.result 2009-11-18 17:33:26 +0000
@@ -317,17 +317,17 @@ Tables_in_bup_db2 vv
Table_type VIEW
SHOW CREATE VIEW bup_db1.v1;;
View v1
-Create View CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `bup_db1`.`v1` AS select `bup_db1`.`t1`.`id` AS `id`,`bup_db1`.`t1`.`name` AS `name`,`bup_db1`.`t1`.`city` AS `city` from `bup_db1`.`t1`
+Create View CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`id` AS `id`,`t1`.`name` AS `name`,`t1`.`city` AS `city` from `t1`
character_set_client latin1
collation_connection latin1_swedish_ci
SHOW CREATE VIEW bup_db1.vcomb;;
View vcomb
-Create View CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `bup_db1`.`vcomb` AS select `bup_db1`.`t1`.`name` AS `name`,`bup_db1`.`t1`.`city` AS `city`,`bup_db1`.`t3`.`ccode` AS `ccode` from (`bup_db1`.`t1` join `bup_db1`.`t3`) where (`bup_db1`.`t1`.`id` = `bup_db1`.`t3`.`scode`)
+Create View CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vcomb` AS select `t1`.`name` AS `name`,`t1`.`city` AS `city`,`t3`.`ccode` AS `ccode` from (`t1` join `t3`) where (`t1`.`id` = `t3`.`scode`)
character_set_client latin1
collation_connection latin1_swedish_ci
SHOW CREATE VIEW bup_db2.v3;;
View v3
-Create View CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select `bup_db1`.`t1`.`name` AS `name`,`bup_db2`.`t2`.`age` AS `age`,`bup_db2`.`t2`.`education` AS `education` from (`bup_db1`.`t1` join `bup_db2`.`t2`) where (`bup_db1`.`t1`.`id` = `bup_db2`.`t2`.`idno`)
+Create View CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `bup_db2`.`v3` AS select `bup_db1`.`t1`.`name` AS `name`,`bup_db2`.`t2`.`age` AS `age`,`bup_db2`.`t2`.`education` AS `education` from (`bup_db1`.`t1` join `bup_db2`.`t2`) where (`bup_db1`.`t1`.`id` = `bup_db2`.`t2`.`idno`)
character_set_client latin1
collation_connection latin1_swedish_ci
****check for view contents after Restore*****
=== modified file 'mysql-test/suite/backup/t/backup_events.test'
--- a/mysql-test/suite/backup/t/backup_events.test 2009-07-21 15:45:20 +0000
+++ b/mysql-test/suite/backup/t/backup_events.test 2009-11-18 17:33:26 +0000
@@ -1,367 +1,368 @@
-#
-# This test is created to verify that object events works properly with backup
-# and restore. We will be including simple tests to test the functionality.
-# There are other complicated tests in backup suite that executes triggers and
-# events together with BACKUP and RESTORE features which uses debug
-# synchronization points.
-#
-# We will be executing following test cases:
-# 1. Create simple recurring event and check if event fires in tables properly
-# before backup and after restore.
-# 2. Rename the event and perform backup / restore
-# 3. Create event with definer clause
-# 4. Change the sql_mode, cset and coll before restore and verify
-# event functions properly.
-#
-
---source include/not_embedded.inc
-
---echo
---echo # Starting the test for backup
---echo
-
-let $bdir= `select @@backupdir`;
---error 0,1
-remove_file $MYSQLD_BACKUPDIR/events.bak;
-
---disable_warnings
-DROP DATABASE IF EXISTS events;
---enable_warnings
-
---echo # Create database and tables.
-
-CREATE DATABASE events;
-CREATE TABLE events.t1(id INT);
-CREATE TABLE events.t3(id INT);
-CREATE TABLE events.t2(a CHAR(20));
-INSERT INTO events.t2 VALUES ('a'),('b'),('c'),('a'),('c');
-
---echo #
---echo # Test 1: Verify that recurring event fires in to tables properly
---echo # before backup and after restore.
---echo #
-
---echo # Create event in the database
-
-CREATE EVENT events.e1 ON SCHEDULE AT NOW() ON COMPLETION PRESERVE
-DO INSERT INTO events.t1 VALUES(100);
-
-CREATE EVENT events.ee ON SCHEDULE EVERY 2 SECOND DO
-INSERT INTO events.t3 VALUES(200);
-
-SET GLOBAL EVENT_SCHEDULER=ON;
-
-let $wait_condition=SELECT COUNT(*) > 4 FROM events.t3;
---source include/wait_condition.inc
-LET $result_before_bkup=`SELECT COUNT(*) FROM events.t3`;
-
-let $wait_condition = SELECT LAST_EXECUTED FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME = 'e1' AND LAST_EXECUTED IS NOT NULL;
---source include/wait_condition.inc
-SELECT * FROM events.t1 ORDER BY id;
-
---echo # Perform backup
---replace_column 1 #
-BACKUP DATABASE events TO 'events.bak';
-
---echo # Drop database and perform restore
-DROP DATABASE events;
-
---replace_column 1 #
-RESTORE FROM 'events.bak';
-
-let $wait_condition=SELECT COUNT(*) > 6 FROM events.t3;
---source include/wait_condition.inc
-LET $result_after_res=`SELECT COUNT(*) FROM events.t3`;
-
---echo # Verify the result
-LET $result=`SELECT $result_before_bkup = $result_after_res AS are_identical`;
-
---echo # If the count values in t3 before backup and after restore is not same
---echo # then final result will be 0. This means that event fires succesfully
---echo # before backup and after restore
---echo # The final result is '$result'
-
-# From the above we can see that events executes properly before backup and
-# after restore.
-
---remove_file $bdir/events.bak
-
---echo # Check the metadata and table contents.
---replace_column 4 #
---query_vertical SHOW CREATE EVENT events.e1;
---replace_column 4 #
---query_vertical SHOW CREATE EVENT events.ee;
-SELECT * FROM events.t1 ORDER BY id;
-
---echo
---echo # Test 2: Rename the event and perform backup and restore.
---echo
-
-SET GLOBAL EVENT_SCHEDULER=ON;
-CREATE EVENT events.e3 ON SCHEDULE EVERY 1 SECOND DO
-UPDATE events.t2 SET a='kk' WHERE a='c';
-
---echo # We include the $wait_condition which will allow the events to fire
---echo # in table t2 by replacing a='c' with 'kk'
-
-sleep 2;
-let $wait_condition = SELECT LAST_EXECUTED FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME = 'e3' AND LAST_EXECUTED IS NOT NULL;
---source include/wait_condition.inc
-
-SELECT * FROM events.t2 ORDER BY a;
-# Note that table will contain columns with a='kk'
-
---echo
---echo # Perform backup
---replace_column 1 #
-BACKUP DATABASE events to 'events.bak';
-
---echo
---echo # Rename the event and take backup
-ALTER EVENT events.e3 RENAME TO events.e_rename;
-
---echo
---echo # Perform backup again after renaming the events
---replace_column 1 #
-BACKUP DATABASE events to 'events1.bak';
-
---echo # Drop database and perform restore
-DROP DATABASE events;
---replace_column 1 #
-RESTORE FROM 'events.bak';
-
---echo
---echo # Check the data contents and metadata
---replace_column 4 #
-SHOW CREATE EVENT events.e3;
-INSERT INTO events.t2 VALUES('q'),('c');
-
---echo # Include wait_condition and sleep so that events are executed properly
-sleep 2;
-let $wait_condition = SELECT LAST_EXECUTED FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME = 'e3' AND LAST_EXECUTED IS NOT NULL;
---source include/wait_condition.inc
-
-SELECT * FROM events.t2 ORDER BY a;
-
---echo
---echo # Perform restore again from events1.bak
---replace_column 1 #
-RESTORE FROM 'events1.bak' OVERWRITE;
-
---replace_column 6 # 9 # 10 #
---query_vertical SHOW EVENTS;
-INSERT INTO events.t2 VALUES('d'),('p'),('c'),('c');
-
---echo # Include wait_condition and sleep so that events are executed properly
-sleep 2;
-
-let $wait_condition = SELECT LAST_EXECUTED FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME = 'e_rename' AND LAST_EXECUTED IS NOT NULL;
---source include/wait_condition.inc
-
-SELECT * FROM events.t2 ORDER BY a;
-
---remove_file $bdir/events.bak
---remove_file $bdir/events1.bak
-
---echo # Rename the event in different database and verify event functions
---echo # properly after backup and restore in different database.
-
-DROP DATABASE IF EXISTS events1;
-CREATE DATABASE events1;
-ALTER EVENT events.e_rename RENAME TO events1.e1_rename;
-
-INSERT INTO events.t2 VALUES('c'),('c');
-
---echo # Include wait_condition and sleep so that events are executed properly
-sleep 2;
-let $wait_condition = SELECT LAST_EXECUTED FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME = 'e1_rename' AND LAST_EXECUTED IS NOT NULL;
---source include/wait_condition.inc
-SELECT * FROM events.t2 ORDER BY a;
-
---echo # Perform Backup
---replace_column 1 #
-BACKUP DATABASE events1 TO 'events1.bak';
-
---echo
---echo # Drop database and perform restore
-DROP DATABASE events1;
---replace_column 1 #
-RESTORE FROM 'events1.bak';
-
---echo
---echo # Check the data contents and metadata
---replace_column 4 #
-SHOW CREATE EVENT events1.e1_rename;
-INSERT INTO events.t2 VALUES('mm'),('c');
-
---echo # Include wait_condition and sleep so that events are executed properly
-sleep 2;
-let $wait_condition = SELECT LAST_EXECUTED FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME = 'e1_rename' AND LAST_EXECUTED IS NOT NULL;
---source include/wait_condition.inc
-SELECT * FROM events.t2 ORDER BY a;
-
---remove_file $bdir/events1.bak
-
---echo
---echo # Test 3: Create event with definer clause.
---echo
-
---echo # Table log will contain entries added by firing event
-CREATE TABLE events.log(pos INT UNIQUE AUTO_INCREMENT, msg CHAR(32));
---echo # Table msg contains a message to be inserted into log by the event.
-CREATE TABLE events.msg(m CHAR(32)) AS SELECT (NULL);
-
-CREATE USER tom@'%';
-GRANT ALL ON *.* TO tom;
-CREATE DEFINER=tom@'%' EVENT events.e4 ON SCHEDULE AT CURRENT_TIMESTAMP
- ON COMPLETION PRESERVE DISABLE
- DO INSERT INTO events.log(msg) SELECT m FROM msg LIMIT 1;
-
-DELIMITER ||;
-CREATE PROCEDURE events.fire(msg char(32))
-BEGIN
- UPDATE events.msg SET m=msg;
- ALTER DEFINER=tom@'%' EVENT events.e4 ENABLE;
-END;||
-DELIMITER ;||
-CALL events.fire('user1 created');
-
---echo # Include wait_condition and sleep so that events are executed properly
-sleep 2;
-let $wait_condition = SELECT LAST_EXECUTED FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME = 'e4' AND LAST_EXECUTED IS NOT NULL;
---source include/wait_condition.inc
-
-SELECT * FROM events.log;
-
---echo
---echo # Perform backup
---replace_column 1 #
-BACKUP DATABASE events TO 'events.bak';
-
-DROP USER tom@'%';
-
---echo
---echo # Perform backup again after dropping the user
---replace_column 1 #
-BACKUP DATABASE events TO 'events1.bak';
-
---echo # Drop database and perform restore
-DROP DATABASE events;
-
---replace_column 1 # 2 #
-RESTORE FROM 'events1.bak';
-
---echo # Exercise objects
---replace_column 4 #
-SHOW CREATE EVENT events.e4;
---replace_column 2 #
-CALL events.fire('no user');
-
---echo # Include wait_condition so that events are executed properly
-let $wait_condition = SELECT LAST_EXECUTED FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME = 'e4' AND LAST_EXECUTED IS NOT NULL;
---source include/wait_condition.inc
-SELECT * FROM events.log;
-
-CREATE USER tom@'%';
-GRANT ALL ON *.* TO tom;
-
---replace_column 1 #
-RESTORE FROM 'events.bak' OVERWRITE;
-CALL events.fire('user2 created');
-
---echo # Include wait_condition and sleep so that events are executed properly
-sleep 2;
-let $wait_condition = SELECT LAST_EXECUTED FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME = 'e4' AND LAST_EXECUTED IS NOT NULL;
---source include/wait_condition.inc
-SELECT * FROM events.log;
-
---remove_file $bdir/events.bak
---remove_file $bdir/events1.bak
-
---echo
---echo # Test 4: Change the sql_mode, cset and coll before restore and verify
---echo # event functions properly.
---echo
-
-CREATE TABLE events.t4(id INT, a CHAR(5));
-INSERT INTO events.t4 VALUES(1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'b');
-CREATE EVENT events.e5 ON SCHEDULE EVERY 1 SECOND
-DO DELETE FROM events.t4 WHERE a='b';
-
-sleep 2;
-let $wait_condition = SELECT LAST_EXECUTED FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME = 'e5' AND LAST_EXECUTED IS NOT NULL;
---source include/wait_condition.inc
-
-SELECT * FROM events.t4 ORDER BY id, a;
-
---echo # Checking the cset, coll and sql_mode of events.e5
---replace_column 4 #
-SHOW CREATE EVENT events.e5;
-
---echo # Perform Backup
---replace_column 1 #
-BACKUP DATABASE events TO 'events.bak';
-
---echo # Change the cset, coll and sql_mode of the server
-SET SQL_MODE='TRADITIONAL';
-SET NAMES big5 COLLATE big5_chinese_ci;
-SET CHARACTER_SET_DATABASE=big5;
-SET COLLATION_DATABASE=big5_chinese_ci;
-SET CHARACTER_SET_SERVER=big5;
-SET COLLATION_SERVER=big5_chinese_ci;
-
---replace_column 4 #
-SHOW CREATE EVENT events.e5;
-
---echo # Perform backup again after changing sql_mode, cset and coll
---replace_column 1 #
-BACKUP DATABASE events TO 'events1.bak';
-
---echo # Drop database and perform restore
-DROP DATABASE events;
-
---replace_column 1 #
-RESTORE FROM 'events1.bak';
-
---echo # Check the cset, coll and sql_mode of events.e5
---replace_column 4 #
-SHOW CREATE EVENT events.e5;
-
---echo # Execute events
-INSERT INTO events.t4 VALUES (10,'b'),(20,'p'),(30,'b');
-
-sleep 2;
-let $wait_condition = SELECT LAST_EXECUTED FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME = 'e5' AND LAST_EXECUTED IS NOT NULL;
---source include/wait_condition.inc
-
-SELECT * FROM events.t4 ORDER BY id, a;
-
---echo # Perform restore again
---replace_column 1 #
-RESTORE FROM 'events.bak' OVERWRITE;
-
---echo # Check the cset, coll and sql_mode of events.e5
---replace_column 4 #
-SHOW CREATE EVENT events.e5;
-
---echo # Execute events
-INSERT INTO events.t4 VALUES (90,'b'),(100,'o');
-
---echo # Include wait_condition and sleep so that events are executed properly
-sleep 2;
-let $wait_condition = SELECT LAST_EXECUTED FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME = 'e5' AND LAST_EXECUTED IS NOT NULL;
---source include/wait_condition.inc
-
-SELECT * FROM events.t4 ORDER BY id, a;
-
---echo # Clean-up section
-DROP USER tom@'%';
-DROP DATABASE events;
-DROP DATABASE events1;
-SET GLOBAL EVENT_SCHEDULER=OFF;
-
---remove_file $bdir/events.bak
---remove_file $bdir/events1.bak
-
-
-
+#
+# This test is created to verify that object events works properly with backup
+# and restore. We will be including simple tests to test the functionality.
+# There are other complicated tests in backup suite that executes triggers and
+# events together with BACKUP and RESTORE features which uses debug
+# synchronization points.
+#
+# We will be executing following test cases:
+# 1. Create simple recurring event and check if event fires in tables properly
+# before backup and after restore.
+# 2. Rename the event and perform backup / restore
+# 3. Create event with definer clause
+# 4. Change the sql_mode, cset and coll before restore and verify
+# event functions properly.
+#
+
+--source include/not_embedded.inc
+
+--echo
+--echo # Starting the test for backup
+--echo
+
+let $bdir= `select @@backupdir`;
+--error 0,1
+remove_file $MYSQLD_BACKUPDIR/events.bak;
+
+--disable_warnings
+DROP DATABASE IF EXISTS events;
+--enable_warnings
+
+--echo # Create database and tables.
+
+CREATE DATABASE events;
+CREATE TABLE events.t1(id INT);
+CREATE TABLE events.t3(id INT);
+CREATE TABLE events.t2(a CHAR(20));
+INSERT INTO events.t2 VALUES ('a'),('b'),('c'),('a'),('c');
+
+--echo #
+--echo # Test 1: Verify that recurring event fires in to tables properly
+--echo # before backup and after restore.
+--echo #
+
+--echo # Create event in the database
+
+CREATE EVENT events.e1 ON SCHEDULE AT NOW() ON COMPLETION PRESERVE
+DO INSERT INTO events.t1 VALUES(100);
+
+CREATE EVENT events.ee ON SCHEDULE EVERY 2 SECOND DO
+INSERT INTO events.t3 VALUES(200);
+
+SET GLOBAL EVENT_SCHEDULER=ON;
+
+let $wait_condition=SELECT COUNT(*) > 4 FROM events.t3;
+--source include/wait_condition.inc
+LET $result_before_bkup=`SELECT COUNT(*) FROM events.t3`;
+
+let $wait_condition = SELECT LAST_EXECUTED FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME = 'e1' AND LAST_EXECUTED IS NOT NULL;
+--source include/wait_condition.inc
+SELECT * FROM events.t1 ORDER BY id;
+
+--echo # Perform backup
+--replace_column 1 #
+BACKUP DATABASE events TO 'events.bak';
+
+--echo # Drop database and perform restore
+DROP DATABASE events;
+
+--replace_column 1 #
+RESTORE FROM 'events.bak';
+
+let $wait_condition=SELECT COUNT(*) > 6 FROM events.t3;
+--source include/wait_condition.inc
+LET $result_after_res=`SELECT COUNT(*) FROM events.t3`;
+
+--echo # Verify the result
+LET $result=`SELECT $result_before_bkup = $result_after_res AS are_identical`;
+
+--echo # If the count values in t3 before backup and after restore is not same
+--echo # then final result will be 0. This means that event fires succesfully
+--echo # before backup and after restore
+--echo # The final result is '$result'
+
+# From the above we can see that events executes properly before backup and
+# after restore.
+
+--remove_file $bdir/events.bak
+
+--echo # Check the metadata and table contents.
+--replace_column 4 #
+--query_vertical SHOW CREATE EVENT events.e1;
+--replace_column 4 #
+--query_vertical SHOW CREATE EVENT events.ee;
+SELECT * FROM events.t1 ORDER BY id;
+
+--echo
+--echo # Test 2: Rename the event and perform backup and restore.
+--echo
+
+SET GLOBAL EVENT_SCHEDULER=ON;
+CREATE EVENT events.e3 ON SCHEDULE EVERY 1 SECOND DO
+UPDATE events.t2 SET a='kk' WHERE a='c';
+
+--echo # We include the $wait_condition which will allow the events to fire
+--echo # in table t2 by replacing a='c' with 'kk'
+
+sleep 2;
+let $wait_condition = SELECT LAST_EXECUTED FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME = 'e3' AND LAST_EXECUTED IS NOT NULL;
+--source include/wait_condition.inc
+
+SELECT * FROM events.t2 ORDER BY a;
+# Note that table will contain columns with a='kk'
+
+--echo
+--echo # Perform backup
+--replace_column 1 #
+BACKUP DATABASE events to 'events.bak';
+
+--echo
+--echo # Rename the event and take backup
+ALTER EVENT events.e3 RENAME TO events.e_rename;
+
+--echo
+--echo # Perform backup again after renaming the events
+--replace_column 1 #
+BACKUP DATABASE events to 'events1.bak';
+
+--echo # Drop database and perform restore
+DROP DATABASE events;
+--replace_column 1 #
+RESTORE FROM 'events.bak';
+
+--echo
+--echo # Check the data contents and metadata
+--replace_column 4 #
+SHOW CREATE EVENT events.e3;
+INSERT INTO events.t2 VALUES('q'),('c');
+
+--echo # Include wait_condition and sleep so that events are executed properly
+sleep 2;
+let $wait_condition = SELECT LAST_EXECUTED FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME = 'e3' AND LAST_EXECUTED IS NOT NULL;
+--source include/wait_condition.inc
+
+SELECT * FROM events.t2 ORDER BY a;
+
+--echo
+--echo # Perform restore again from events1.bak
+--replace_column 1 #
+RESTORE FROM 'events1.bak' OVERWRITE;
+
+USE events;
+--replace_column 6 # 9 # 10 #
+--query_vertical SHOW EVENTS;
+INSERT INTO events.t2 VALUES('d'),('p'),('c'),('c');
+
+--echo # Include wait_condition and sleep so that events are executed properly
+sleep 2;
+
+let $wait_condition = SELECT LAST_EXECUTED FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME = 'e_rename' AND LAST_EXECUTED IS NOT NULL;
+--source include/wait_condition.inc
+
+SELECT * FROM events.t2 ORDER BY a;
+
+--remove_file $bdir/events.bak
+--remove_file $bdir/events1.bak
+
+--echo # Rename the event in different database and verify event functions
+--echo # properly after backup and restore in different database.
+
+DROP DATABASE IF EXISTS events1;
+CREATE DATABASE events1;
+ALTER EVENT events.e_rename RENAME TO events1.e1_rename;
+
+INSERT INTO events.t2 VALUES('c'),('c');
+
+--echo # Include wait_condition and sleep so that events are executed properly
+sleep 2;
+let $wait_condition = SELECT LAST_EXECUTED FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME = 'e1_rename' AND LAST_EXECUTED IS NOT NULL;
+--source include/wait_condition.inc
+SELECT * FROM events.t2 ORDER BY a;
+
+--echo # Perform Backup
+--replace_column 1 #
+BACKUP DATABASE events1 TO 'events1.bak';
+
+--echo
+--echo # Drop database and perform restore
+DROP DATABASE events1;
+--replace_column 1 #
+RESTORE FROM 'events1.bak';
+
+--echo
+--echo # Check the data contents and metadata
+--replace_column 4 #
+SHOW CREATE EVENT events1.e1_rename;
+INSERT INTO events.t2 VALUES('mm'),('c');
+
+--echo # Include wait_condition and sleep so that events are executed properly
+sleep 2;
+let $wait_condition = SELECT LAST_EXECUTED FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME = 'e1_rename' AND LAST_EXECUTED IS NOT NULL;
+--source include/wait_condition.inc
+SELECT * FROM events.t2 ORDER BY a;
+
+--remove_file $bdir/events1.bak
+
+--echo
+--echo # Test 3: Create event with definer clause.
+--echo
+
+--echo # Table log will contain entries added by firing event
+CREATE TABLE events.log(pos INT UNIQUE AUTO_INCREMENT, msg CHAR(32));
+--echo # Table msg contains a message to be inserted into log by the event.
+CREATE TABLE events.msg(m CHAR(32)) AS SELECT (NULL);
+
+CREATE USER tom@'%';
+GRANT ALL ON *.* TO tom;
+CREATE DEFINER=tom@'%' EVENT events.e4 ON SCHEDULE AT CURRENT_TIMESTAMP
+ ON COMPLETION PRESERVE DISABLE
+ DO INSERT INTO events.log(msg) SELECT m FROM msg LIMIT 1;
+
+DELIMITER ||;
+CREATE PROCEDURE events.fire(msg char(32))
+BEGIN
+ UPDATE events.msg SET m=msg;
+ ALTER DEFINER=tom@'%' EVENT events.e4 ENABLE;
+END;||
+DELIMITER ;||
+CALL events.fire('user1 created');
+
+--echo # Include wait_condition and sleep so that events are executed properly
+sleep 2;
+let $wait_condition = SELECT LAST_EXECUTED FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME = 'e4' AND LAST_EXECUTED IS NOT NULL;
+--source include/wait_condition.inc
+
+SELECT * FROM events.log;
+
+--echo
+--echo # Perform backup
+--replace_column 1 #
+BACKUP DATABASE events TO 'events.bak';
+
+DROP USER tom@'%';
+
+--echo
+--echo # Perform backup again after dropping the user
+--replace_column 1 #
+BACKUP DATABASE events TO 'events1.bak';
+
+--echo # Drop database and perform restore
+DROP DATABASE events;
+
+--replace_column 1 # 2 #
+RESTORE FROM 'events1.bak';
+
+--echo # Exercise objects
+--replace_column 4 #
+SHOW CREATE EVENT events.e4;
+--replace_column 2 #
+CALL events.fire('no user');
+
+--echo # Include wait_condition so that events are executed properly
+let $wait_condition = SELECT LAST_EXECUTED FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME = 'e4' AND LAST_EXECUTED IS NOT NULL;
+--source include/wait_condition.inc
+SELECT * FROM events.log;
+
+CREATE USER tom@'%';
+GRANT ALL ON *.* TO tom;
+
+--replace_column 1 #
+RESTORE FROM 'events.bak' OVERWRITE;
+CALL events.fire('user2 created');
+
+--echo # Include wait_condition and sleep so that events are executed properly
+sleep 2;
+let $wait_condition = SELECT LAST_EXECUTED FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME = 'e4' AND LAST_EXECUTED IS NOT NULL;
+--source include/wait_condition.inc
+SELECT * FROM events.log;
+
+--remove_file $bdir/events.bak
+--remove_file $bdir/events1.bak
+
+--echo
+--echo # Test 4: Change the sql_mode, cset and coll before restore and verify
+--echo # event functions properly.
+--echo
+
+CREATE TABLE events.t4(id INT, a CHAR(5));
+INSERT INTO events.t4 VALUES(1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'b');
+CREATE EVENT events.e5 ON SCHEDULE EVERY 1 SECOND
+DO DELETE FROM events.t4 WHERE a='b';
+
+sleep 2;
+let $wait_condition = SELECT LAST_EXECUTED FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME = 'e5' AND LAST_EXECUTED IS NOT NULL;
+--source include/wait_condition.inc
+
+SELECT * FROM events.t4 ORDER BY id, a;
+
+--echo # Checking the cset, coll and sql_mode of events.e5
+--replace_column 4 #
+SHOW CREATE EVENT events.e5;
+
+--echo # Perform Backup
+--replace_column 1 #
+BACKUP DATABASE events TO 'events.bak';
+
+--echo # Change the cset, coll and sql_mode of the server
+SET SQL_MODE='TRADITIONAL';
+SET NAMES big5 COLLATE big5_chinese_ci;
+SET CHARACTER_SET_DATABASE=big5;
+SET COLLATION_DATABASE=big5_chinese_ci;
+SET CHARACTER_SET_SERVER=big5;
+SET COLLATION_SERVER=big5_chinese_ci;
+
+--replace_column 4 #
+SHOW CREATE EVENT events.e5;
+
+--echo # Perform backup again after changing sql_mode, cset and coll
+--replace_column 1 #
+BACKUP DATABASE events TO 'events1.bak';
+
+--echo # Drop database and perform restore
+DROP DATABASE events;
+
+--replace_column 1 #
+RESTORE FROM 'events1.bak';
+
+--echo # Check the cset, coll and sql_mode of events.e5
+--replace_column 4 #
+SHOW CREATE EVENT events.e5;
+
+--echo # Execute events
+INSERT INTO events.t4 VALUES (10,'b'),(20,'p'),(30,'b');
+
+sleep 2;
+let $wait_condition = SELECT LAST_EXECUTED FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME = 'e5' AND LAST_EXECUTED IS NOT NULL;
+--source include/wait_condition.inc
+
+SELECT * FROM events.t4 ORDER BY id, a;
+
+--echo # Perform restore again
+--replace_column 1 #
+RESTORE FROM 'events.bak' OVERWRITE;
+
+--echo # Check the cset, coll and sql_mode of events.e5
+--replace_column 4 #
+SHOW CREATE EVENT events.e5;
+
+--echo # Execute events
+INSERT INTO events.t4 VALUES (90,'b'),(100,'o');
+
+--echo # Include wait_condition and sleep so that events are executed properly
+sleep 2;
+let $wait_condition = SELECT LAST_EXECUTED FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME = 'e5' AND LAST_EXECUTED IS NOT NULL;
+--source include/wait_condition.inc
+
+SELECT * FROM events.t4 ORDER BY id, a;
+
+--echo # Clean-up section
+DROP USER tom@'%';
+DROP DATABASE events;
+DROP DATABASE events1;
+SET GLOBAL EVENT_SCHEDULER=OFF;
+
+--remove_file $bdir/events.bak
+--remove_file $bdir/events1.bak
+
+
+
=== added file 'mysql-test/suite/backup/t/backup_use_db_restore.test'
--- a/mysql-test/suite/backup/t/backup_use_db_restore.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup/t/backup_use_db_restore.test 2009-11-18 17:33:26 +0000
@@ -0,0 +1,84 @@
+--echo #
+--echo # This test ensures that if there is a database in use before
+--echo # a "restore" command, then the same database remains the
+--echo # default database even after restore.
+--echo #
+--echo # for details see : Bug#33354 : Backup: restore changes current
+--echo # database to null
+--echo #
+
+
+--disable_warnings
+DROP DATABASE IF EXISTS db1;
+--enable_warnings
+
+# Use the test database
+USE test;
+SELECT DATABASE();
+
+# Now create a database abd backup and restore it
+CREATE DATABASE db1;
+CREATE TABLE db1.t (i int, j int) engine=myisam;
+INSERT INTO db1.t values(10, 11);
+
+--replace_column 1 #
+BACKUP DATABASE db1 to 'image1.bak';
+
+--replace_column 1 #
+RESTORE FROM 'image1.bak' overwrite;
+
+# The database 'test' should still be the default database
+SELECT DATABASE();
+
+--echo #
+--echo # Try again now using the database which is being restored
+--echo #
+
+USE db1;
+SELECT DATABASE();
+
+--replace_column 1 #
+BACKUP DATABASE db1 to 'image2.bak';
+
+--replace_column 1 #
+RESTORE FROM 'image2.bak' overwrite;
+
+# The restored database should be the default database
+SELECT DATABASE();
+
+# Now lets try a UTF8 character set database
+
+--disable_warnings
+DROP DATABASE IF EXISTS `ﾆﾎ�ｺﾞ`;
+--enable_warnings
+
+SET NAMES utf8;
+SET character_set_database = utf8;
+
+CREATE DATABASE `ï¾¾Žï¾�ｺﾞ`;
+
+# Ensure that our international database is indeed the default
+SELECT DATABASE();
+
+--replace_column 1 #
+BACKUP DATABASE db1 to 'image3.bak';
+
+--replace_column 1 #
+RESTORE FROM 'image3.bak' overwrite;
+
+# After restore the default database should be the international database
+SELECT DATABASE();
+
+--echo #
+--echo # Cleanup
+--echo #
+
+DROP TABLE db1.t;
+DROP DATABASE db1;
+DROP DATABASE `ﾆﾎ�ｺﾞ`;
+let $MYSQLD_BACKUPDIR= `select @@backupdir`;
+remove_file $MYSQLD_BACKUPDIR/image1.bak;
+remove_file $MYSQLD_BACKUPDIR/image2.bak;
+remove_file $MYSQLD_BACKUPDIR/image3.bak;
=== modified file 'sql/si_objects.cc'
--- a/sql/si_objects.cc 2009-11-17 20:18:50 +0000
+++ b/sql/si_objects.cc 2009-11-18 17:33:26 +0000
@@ -75,6 +75,7 @@ private:
Time_zone *m_tz_saved;
TABLE *m_tmp_tables_saved;
bool m_engage_general_log;
+ String m_db_saved_string;
private:
Si_session_context(const Si_session_context &);
@@ -104,6 +105,21 @@ void Si_session_context::save_si_ctx(THD
m_tz_saved= thd->variables.time_zone;
m_tmp_tables_saved= thd->temporary_tables;
m_old_db_collation= thd->variables.collation_database;
+
+ /* Saving the default database */
+ if (thd->db)
+ m_db_saved_string.copy(thd->db, thd->db_length, thd->db_charset);
+ /*
+ Clear up and old data in the saved database since there is no default
+ database set.
+ */
+ else
+ if (!m_db_saved_string.is_empty())
+ m_db_saved_string= 0;
+ /* clean up database info from thd context */
+ thd->db= 0;
+ thd->db_length= 0;
+
DBUG_VOID_RETURN;
}
@@ -154,8 +170,21 @@ void Si_session_context::reset_si_ctx(TH
void Si_session_context::restore_si_ctx(THD *thd)
{
+
+ String query ("USE `",m_db_saved_string.charset());
+ Ed_connection ed_connection(thd);
+ LEX_STRING lex_query;
+
DBUG_ENTER("Si_session_context::restore_si_ctx");
+ query.append(m_db_saved_string);
+ query.append("`");
+ lex_query= query.lex_string();
+
+ /* Restoring the default database */
+ if (m_db_saved_string != 0)
+ ed_connection.execute_direct(lex_query);
+
thd->variables.sql_mode= m_sql_mode_saved;
thd->variables.time_zone= m_tz_saved;
Attachment: [text/bzr-bundle] bzr/sanjay.manwani@sun.com-20091118173326-dy72bu4qn8gtqemc.bundle
