2846 Hema Sridharan 2009-07-17
backup_events.test is added in backup suite
added:
mysql-test/suite/backup/r/backup_events.result
mysql-test/suite/backup/t/backup_events.test
2845 Chuck Bell 2009-07-15 [merge]
BUG#45889 : BACKUP DATABASE command should include all objects
PROBLEM: The code currently silently succeeds if a user is not able to read
all of the objects and their metadata in the database and, in the case of
BACKUP DATABASE *, is not able to read all of the databases.
This is an error because the BACKUP DATABASE command has been clarified
to error if a user cannot read all of the objects and their metadata
all all of the databases listed.
This patch implements a solution to produce an error if either of the
following are true:
* The user cannot read (SELECT) all of the databases when issuing a
BACKUP DATABASE * command.
* The user cannot read (SELECT) all of the objects in a database
and read their metadata when issuing a BACKUP DATABASE <list> command.
added:
mysql-test/suite/backup/r/backup_security_check.result
mysql-test/suite/backup/t/backup_security_check.test
modified:
mysql-test/suite/backup/r/backup_security.result
mysql-test/suite/backup/t/backup_security.test
sql/backup/backup_info.cc
sql/share/errmsg-utf8.txt
sql/share/errmsg.txt
sql/si_objects.cc
sql/si_objects.h
=== added file 'mysql-test/suite/backup/r/backup_events.result'
--- a/mysql-test/suite/backup/r/backup_events.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup/r/backup_events.result 2009-07-17 04:43:41 +0000
@@ -0,0 +1,362 @@
+
+# Starting the test for backup
+
+DROP DATABASE IF EXISTS events;
+# 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');
+#
+# Test 1: Verify that recurring event fires in to tables properly
+# before backup and after restore.
+#
+# 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;
+5
+SELECT * FROM events.t1 ORDER BY id;
+id
+100
+# Perform backup
+BACKUP DATABASE events TO 'events.bak';
+backup_id
+#
+# Drop database and perform restore
+DROP DATABASE events;
+RESTORE FROM 'events.bak';
+backup_id
+#
+7
+# Verify the result
+# If the count values in t3 before backup and after restore is not same
+# then final result will be 0. This means that event fires succesfully
+# before backup and after restore
+# The final result is '0'
+# Check the metadata and table contents.
+SHOW CREATE EVENT events.e1;;
+Event e1
+sql_mode
+time_zone SYSTEM
+Create Event #
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+SHOW CREATE EVENT events.ee;;
+Event ee
+sql_mode
+time_zone SYSTEM
+Create Event #
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+SELECT * FROM events.t1 ORDER BY id;
+id
+100
+
+# Test 2: Rename the event and perform backup and restore.
+
+SET GLOBAL EVENT_SCHEDULER=ON;
+CREATE EVENT events.e3 ON SCHEDULE EVERY 1 SECOND DO
+UPDATE events.t2 SET a='kk' WHERE a='c';
+# We include the SELECT COUNT(*) > 6 FROM events.t3 which will allow the events to fire
+# in table t2 by replacing a='c' with 'kk'
+SELECT * FROM events.t2 ORDER BY a;
+a
+a
+a
+b
+kk
+kk
+
+# Perform backup
+BACKUP DATABASE events to 'events.bak';
+backup_id
+#
+
+# Rename the event and take backup
+ALTER EVENT events.e3 RENAME TO events.e_rename;
+
+# Perform backup again after renaming the events
+BACKUP DATABASE events to 'events1.bak';
+backup_id
+#
+# Drop database and perform restore
+DROP DATABASE events;
+RESTORE FROM 'events.bak';
+backup_id
+#
+
+# Check the data contents and metadata
+SHOW CREATE EVENT events.e3;
+Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation
+e3 SYSTEM # latin1 latin1_swedish_ci latin1_swedish_ci
+INSERT INTO events.t2 VALUES('q'),('c');
+# Include wait_condition and sleep so that events are executed properly
+SELECT * FROM events.t2 ORDER BY a;
+a
+a
+a
+b
+kk
+kk
+kk
+q
+
+# Perform restore again from events1.bak
+RESTORE FROM 'events1.bak' OVERWRITE;
+backup_id
+#
+SHOW EVENTS;;
+Db events
+Name e1
+Definer root@localhost
+Time zone SYSTEM
+Type ONE TIME
+Execute at #
+Interval value NULL
+Interval field NULL
+Starts #
+Ends #
+Status DISABLED
+Originator 1
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+Db events
+Name ee
+Definer root@localhost
+Time zone SYSTEM
+Type RECURRING
+Execute at #
+Interval value 2
+Interval field SECOND
+Starts #
+Ends #
+Status ENABLED
+Originator 1
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+Db events
+Name e_rename
+Definer root@localhost
+Time zone SYSTEM
+Type RECURRING
+Execute at #
+Interval value 1
+Interval field SECOND
+Starts #
+Ends #
+Status ENABLED
+Originator 1
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+INSERT INTO events.t2 VALUES('d'),('p'),('c'),('c');
+# Include wait_condition and sleep so that events are executed properly
+SELECT * FROM events.t2 ORDER BY a;
+a
+a
+a
+b
+d
+kk
+kk
+kk
+kk
+p
+# Rename the event in different database and verify event functions
+# properly after backup and restore in different database.
+DROP DATABASE IF EXISTS events1;
+Warnings:
+Note 1008 Can't drop database 'events1'; database doesn't exist
+CREATE DATABASE events1;
+ALTER EVENT events.e_rename RENAME TO events1.e1_rename;
+INSERT INTO events.t2 VALUES('c'),('c');
+# Include wait_condition and sleep so that events are executed properly
+SELECT * FROM events.t2 ORDER BY a;
+a
+a
+a
+b
+d
+kk
+kk
+kk
+kk
+kk
+kk
+p
+# Perform Backup
+BACKUP DATABASE events1 TO 'events1.bak';
+backup_id
+#
+
+# Drop database and perform restore
+DROP DATABASE events1;
+RESTORE FROM 'events1.bak';
+backup_id
+#
+
+# Check the data contents and metadata
+SHOW CREATE EVENT events1.e1_rename;
+Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation
+e1_rename SYSTEM # latin1 latin1_swedish_ci latin1_swedish_ci
+INSERT INTO events.t2 VALUES('mm'),('c');
+# Include wait_condition and sleep so that events are executed properly
+SELECT * FROM events.t2 ORDER BY a;
+a
+a
+a
+b
+d
+kk
+kk
+kk
+kk
+kk
+kk
+kk
+mm
+p
+
+# Test 3: Create event with definer clause.
+
+# Table log will contain entries added by firing event
+CREATE TABLE events.log(pos INT UNIQUE AUTO_INCREMENT, msg CHAR(32));
+# 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;
+CREATE PROCEDURE events.fire(msg char(32))
+BEGIN
+UPDATE events.msg SET m=msg;
+ALTER DEFINER=tom@'%' EVENT events.e4 ENABLE;
+END;||
+CALL events.fire('user1 created');
+# Include wait_condition and sleep so that events are executed properly
+SELECT * FROM events.log;
+pos msg
+1 user1 created
+
+# Perform backup
+BACKUP DATABASE events TO 'events.bak';
+backup_id
+#
+DROP USER tom@'%';
+
+# Perform backup again after dropping the user
+BACKUP DATABASE events TO 'events1.bak';
+backup_id
+#
+# Drop database and perform restore
+DROP DATABASE events;
+RESTORE FROM 'events1.bak';
+backup_id
+#
+Warnings:
+# # The user specified as a definer ('tom'@'%') does not exist
+# Exercise objects
+SHOW CREATE EVENT events.e4;
+Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation
+e4 SYSTEM # latin1 latin1_swedish_ci latin1_swedish_ci
+CALL events.fire('no user');
+Warnings:
+Note # The user specified as a definer ('tom'@'%') does not exist
+# Include wait_condition so that events are executed properly
+SELECT * FROM events.log;
+pos msg
+1 user1 created
+CREATE USER tom@'%';
+GRANT ALL ON *.* TO tom;
+RESTORE FROM 'events.bak' OVERWRITE;
+backup_id
+#
+CALL events.fire('user2 created');
+# Include wait_condition and sleep so that events are executed properly
+SELECT * FROM events.log;
+pos msg
+1 user1 created
+2 user2 created
+
+# Test 4: Change the sql_mode, cset and coll before restore and verify
+# event functions properly.
+
+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';
+SELECT * FROM events.t4 ORDER BY id, a;
+id a
+1 a
+3 c
+4 d
+# Checking the cset, coll and sql_mode of events.e5
+SHOW CREATE EVENT events.e5;
+Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation
+e5 SYSTEM # latin1 latin1_swedish_ci latin1_swedish_ci
+# Perform Backup
+BACKUP DATABASE events TO 'events.bak';
+backup_id
+#
+# 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;
+SHOW CREATE EVENT events.e5;
+Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation
+e5 SYSTEM # latin1 latin1_swedish_ci latin1_swedish_ci
+# Perform backup again after changing sql_mode, cset and coll
+BACKUP DATABASE events TO 'events1.bak';
+backup_id
+#
+# Drop database and perform restore
+DROP DATABASE events;
+RESTORE FROM 'events1.bak';
+backup_id
+#
+# Check the cset, coll and sql_mode of events.e5
+SHOW CREATE EVENT events.e5;
+Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation
+e5 SYSTEM # latin1 latin1_swedish_ci latin1_swedish_ci
+# Execute events
+INSERT INTO events.t4 VALUES (10,'b'),(20,'p'),(30,'b');
+SELECT * FROM events.t4 ORDER BY id, a;
+id a
+1 a
+3 c
+4 d
+20 p
+# Perform restore again
+RESTORE FROM 'events.bak' OVERWRITE;
+backup_id
+#
+# Check the cset, coll and sql_mode of events.e5
+SHOW CREATE EVENT events.e5;
+Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation
+e5 SYSTEM # latin1 latin1_swedish_ci latin1_swedish_ci
+# Execute events
+INSERT INTO events.t4 VALUES (90,'b'),(100,'o');
+# Include wait_condition and sleep so that events are executed properly
+SELECT * FROM events.t4 ORDER BY id, a;
+id a
+1 a
+3 c
+4 d
+100 o
+# Clean-up section
+DROP USER tom@'%';
+DROP DATABASE events;
+DROP DATABASE events1;
+SET GLOBAL EVENT_SCHEDULER=OFF;
=== added file 'mysql-test/suite/backup/t/backup_events.test'
--- a/mysql-test/suite/backup/t/backup_events.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup/t/backup_events.test 2009-07-17 04:43:41 +0000
@@ -0,0 +1,369 @@
+#
+# 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`;
+--echo $result_before_bkup
+
+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 $result_after_res
+
+--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
+
+
+
| Thread |
|---|
| • bzr push into mysql-5.4 branch (hema:2845 to 2846) | Hema Sridharan | 17 Jul |
