#At file:///Users/cbell/source/bzr/mysql-6.0-bug-37445/ based on revid:ritheesh.vedire@stripped
2889 Chuck Bell 2009-11-10
BUG#37445 : Do not automatically enable restored events
This bug is a feature request to disable events on restore.
This patch implements a mechanism to disable all events on
restore and communicate to the user which events were
disabled. This list is written to the console, the warning
stack, and the backup_progress log.
The variable (option, global, sesson) restore_disables_events
is ON by default but may be turned off. When turned OFF,
events are not disabled on restore and they retain their
original state from backup.
Note: A number of option files were created to allow the
older tests to run correctly and suppress the disabling of
events on restore.
@ mysql-test/collections/default.experimental
Removed test from experimental status.
@ mysql-test/extra/scripts/monitor_disabled_tests/comments.txt
Removed comment - no longer relevant.
@ mysql-test/suite/backup/r/backup_events.result
Corrected result file.
@ mysql-test/suite/backup/r/backup_namecase.result
Corrected result file.
@ mysql-test/suite/backup/t/backup_bml_not_falcon-master.opt
New option file to turn OFF restore_disables_events.
@ mysql-test/suite/backup/t/backup_compression-master.opt
New option file to turn OFF restore_disables_events.
@ mysql-test/suite/backup/t/backup_errors_debug_1-master.opt
Added option to turn OFF restore_disables_events.
@ mysql-test/suite/backup/t/backup_errors_debug_2-master.opt
New option file to turn OFF restore_disables_events.
@ mysql-test/suite/backup/t/backup_errors_debug_3-master.opt
New option file to turn OFF restore_disables_events.
@ mysql-test/suite/backup/t/backup_events.test
Replaced failing test with new test cases to test disabling
of events on restore.
@ mysql-test/suite/backup/t/backup_logs-master.opt
New option file to turn OFF restore_disables_events.
@ mysql-test/suite/backup/t/backup_namecase.test
Changed test to suppress warnings and allow restore to
disable events on restore.
@ mysql-test/suite/backup/t/backup_objects-master.opt
New option file to turn OFF restore_disables_events.
@ mysql-test/suite/backup/t/backup_security-master.opt
New option file to turn OFF restore_disables_events.
@ mysql-test/suite/backup/t/backup_triggers_and_events-master.opt
New option file to turn OFF restore_disables_events.
@ sql/backup/kernel.cc
Added code to detect when restore should disable events.
Specifically, if either the session or global variable
is set.
@ sql/backup/logger.cc
Added method to report which events are disabled as a
warning.
@ sql/backup/logger.h
Added method declaration for reporting which events were
disabled.
@ sql/mysqld.cc
Added startup option handler.
@ sql/set_var.cc
Added new variable handler.
@ sql/share/errmsg-utf8.txt
New error/warning message.
@ sql/share/errmsg.txt
New error/warning message.
@ sql/si_objects.cc
Added method to disable an event. Note: It was necessary
to use a direct update statement here to avoid changing
the modified by and other columns. Only the state is
changed.
@ sql/si_objects.h
Added method declaration to disable an event.
@ sql/sql_class.h
Added system variable.
added:
mysql-test/suite/backup/t/backup_bml_not_falcon-master.opt
mysql-test/suite/backup/t/backup_compression-master.opt
mysql-test/suite/backup/t/backup_errors_debug_2-master.opt
mysql-test/suite/backup/t/backup_errors_debug_3-master.opt
mysql-test/suite/backup/t/backup_logs-master.opt
mysql-test/suite/backup/t/backup_objects-master.opt
mysql-test/suite/backup/t/backup_security-master.opt
mysql-test/suite/backup/t/backup_triggers_and_events-master.opt
modified:
mysql-test/collections/default.experimental
mysql-test/extra/scripts/monitor_disabled_tests/comments.txt
mysql-test/suite/backup/r/backup_events.result
mysql-test/suite/backup/r/backup_namecase.result
mysql-test/suite/backup/t/backup_errors_debug_1-master.opt
mysql-test/suite/backup/t/backup_events.test
mysql-test/suite/backup/t/backup_namecase.test
sql/backup/kernel.cc
sql/backup/logger.cc
sql/backup/logger.h
sql/mysqld.cc
sql/set_var.cc
sql/share/errmsg-utf8.txt
sql/share/errmsg.txt
sql/si_objects.cc
sql/si_objects.h
sql/sql_class.h
=== modified file 'mysql-test/collections/default.experimental'
--- a/mysql-test/collections/default.experimental 2009-11-02 14:05:19 +0000
+++ b/mysql-test/collections/default.experimental 2009-11-10 21:19:56 +0000
@@ -2,7 +2,6 @@
# in alphabetical order. This also helps with merge conflict resolution.
backup.backup_dbname_lctn2 @windows @darwin # Bug#46933 2009-08-26 alik backup_*_lctn2 tests fail on windows
-backup.backup_events # Bug#47211 2009-09-25 alik backup_events.test fails randomly
backup.backup_external_non_win* # BUG#45877
backup.backup_logs # Bug#47698 2009-09-29 alik backup.backup_logs fails under valgrind
backup.backup_xpfm_compat_backup_lctn2 @windows # Bug#46933 2009-08-26 alik backup_*_lctn2 tests fail on windows
=== modified file 'mysql-test/extra/scripts/monitor_disabled_tests/comments.txt'
--- a/mysql-test/extra/scripts/monitor_disabled_tests/comments.txt 2009-10-26 14:47:30 +0000
+++ b/mysql-test/extra/scripts/monitor_disabled_tests/comments.txt 2009-11-10 21:19:56 +0000
@@ -60464,24 +60464,6 @@ suite/backup/t/backup_xpfm_compat_restor
suite/backup/t/backup_xpfm_compat_restore_lctn1.test:#
suite/backup/t/backup_xpfm_compat_restore_lctn1.test:#
suite/backup/t/backup_xpfm_compat_restore_lctn1.test:#
-suite/backup/t/backup_events.test:#
-suite/backup/t/backup_events.test:# This test is created to verify that object events works properly with backup
-suite/backup/t/backup_events.test:# and restore. We will be including simple tests to test the functionality.
-suite/backup/t/backup_events.test:# There are other complicated tests in backup suite that executes triggers and
-suite/backup/t/backup_events.test:# events together with BACKUP and RESTORE features which uses debug
-suite/backup/t/backup_events.test:# synchronization points.
-suite/backup/t/backup_events.test:#
-suite/backup/t/backup_events.test:# We will be executing following test cases:
-suite/backup/t/backup_events.test:# 1. Create simple recurring event and check if event fires in tables properly
-suite/backup/t/backup_events.test:# before backup and after restore.
-suite/backup/t/backup_events.test:# 2. Rename the event and perform backup / restore
-suite/backup/t/backup_events.test:# 3. Create event with definer clause
-suite/backup/t/backup_events.test:# 4. Change the sql_mode, cset and coll before restore and verify
-suite/backup/t/backup_events.test:# event functions properly.
-suite/backup/t/backup_events.test:#
-suite/backup/t/backup_events.test:# From the above we can see that events executes properly before backup and
-suite/backup/t/backup_events.test:# after restore.
-suite/backup/t/backup_events.test:# Note that table will contain columns with a='kk'
suite/backup/t/backup_nodata_driver.test:#
suite/backup/t/backup_nodata_driver.test:# This test tests the backup using no data engines.
suite/backup/t/backup_nodata_driver.test:# These are engines which tables don't contain data.
=== 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-10 21:19:56 +0000
@@ -1,360 +1,201 @@
-# Starting the test for backup
+# Create database with tables and events.
-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;
-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
+DROP DATABASE IF EXISTS bup_events;
+CREATE DATABASE bup_events;
+CREATE TABLE bup_events.t1 (a int);
+CREATE TABLE bup_events.t2 (b int);
+INSERT INTO bup_events.t1 VALUES (1), (2), (3);
+INSERT INTO bup_events.t2 VALUES (4), (5), (6);
+CREATE EVENT bup_events.e1 ON SCHEDULE EVERY 3 HOUR ENABLE DO
+INSERT INTO bup_events.t1 VALUES (100);
+CREATE EVENT bup_events.e2 ON SCHEDULE EVERY 3 HOUR DO
+INSERT INTO bup_events.t2 VALUES (200);
+CREATE EVENT bup_events.e3 ON SCHEDULE EVERY 3 HOUR DISABLE DO
+INSERT INTO bup_events.t1 VALUES (300);
+CREATE EVENT bup_events.e4 ON SCHEDULE EVERY 3 HOUR DISABLE DO
+INSERT INTO bup_events.t2 VALUES (400);
+ALTER EVENT bup_events.e2 DISABLE;
+ALTER EVENT bup_events.e4 ENABLE;
+#
+# Show the events.
+#
+SHOW EVENTS FROM bup_events;
+Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation
+bup_events e1 root@localhost SYSTEM RECURRING NULL 3 HOUR # # ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci
+bup_events e2 root@localhost SYSTEM RECURRING NULL 3 HOUR # # DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci
+bup_events e3 root@localhost SYSTEM RECURRING NULL 3 HOUR # # DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci
+bup_events e4 root@localhost SYSTEM RECURRING NULL 3 HOUR # # ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci
+#
+# Perform backup for later use.
+#
+BACKUP DATABASE bup_events TO 'bup_events.bak';
+backup_id
+#
+#
+# Show the events.
+#
+SHOW EVENTS FROM bup_events;
+Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation
+bup_events e1 root@localhost SYSTEM RECURRING NULL 3 HOUR # # ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci
+bup_events e2 root@localhost SYSTEM RECURRING NULL 3 HOUR # # DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci
+bup_events e3 root@localhost SYSTEM RECURRING NULL 3 HOUR # # DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci
+bup_events e4 root@localhost SYSTEM RECURRING NULL 3 HOUR # # ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci
+#
+# Test Case 1 : The variable restore_disables_events is ON by default.
+#
+SHOW VARIABLES LIKE 'restore_disables_events';
+Variable_name Value
+restore_disables_events ON
+SHOW GLOBAL VARIABLES LIKE 'restore_disables_events';
+Variable_name Value
+restore_disables_events ON
+#
+# Test Case 2 : The variable restore_disables_events obeys session and
+# global behavior.
+#
+SET restore_disables_events = OFF;
+SHOW VARIABLES LIKE 'restore_disables_events';
+Variable_name Value
+restore_disables_events OFF
+SHOW GLOBAL VARIABLES LIKE 'restore_disables_events';
+Variable_name Value
+restore_disables_events ON
+SET @@global.restore_disables_events = OFF;
+SHOW VARIABLES LIKE 'restore_disables_events';
+Variable_name Value
+restore_disables_events OFF
+SHOW GLOBAL VARIABLES LIKE 'restore_disables_events';
+Variable_name Value
+restore_disables_events OFF
+SET restore_disables_events = ON;
+SET @@global.restore_disables_events = ON;
+SHOW VARIABLES LIKE 'restore_disables_events';
+Variable_name Value
+restore_disables_events ON
+SHOW GLOBAL VARIABLES LIKE 'restore_disables_events';
+Variable_name Value
+restore_disables_events ON
+#
+# Test Case 3 : Ensure all events are disabled on restore when
+# restore_disables_events = ON.
+# While session variable set.
+#
+#
+# Setup variables.
+#
+DROP DATABASE bup_events;
+SET restore_disables_events = ON;
+SET @@global.restore_disables_events = OFF;
+SHOW VARIABLES LIKE 'restore_disables_events';
+Variable_name Value
+restore_disables_events ON
+SHOW GLOBAL VARIABLES LIKE 'restore_disables_events';
+Variable_name Value
+restore_disables_events OFF
#
-# 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
+# Run restore and check events.
#
-
-# 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';
+RESTORE FROM 'bup_events.bak' OVERWRITE;
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
+# # The event 'bup_events.e1' was disabled during restore.
+# # The event 'bup_events.e2' was disabled during restore.
+# # The event 'bup_events.e3' was disabled during restore.
+# # The event 'bup_events.e4' was disabled during restore.
+SHOW WARNINGS;
+Level Code Message
+Warning # The event 'bup_events.e1' was disabled during restore.
+Warning # The event 'bup_events.e2' was disabled during restore.
+Warning # The event 'bup_events.e3' was disabled during restore.
+Warning # The event 'bup_events.e4' was disabled during restore.
+#
+# Show the events.
+#
+SHOW EVENTS FROM bup_events;
+Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation
+bup_events e1 root@localhost SYSTEM RECURRING NULL 3 HOUR # # DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci
+bup_events e2 root@localhost SYSTEM RECURRING NULL 3 HOUR # # DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci
+bup_events e3 root@localhost SYSTEM RECURRING NULL 3 HOUR # # DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci
+bup_events e4 root@localhost SYSTEM RECURRING NULL 3 HOUR # # DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci
+#
+# Test Case 4 : Ensure all events are disabled on restore when
+# restore_disables_events = ON.
+# While global variable set.
+#
+#
+# Setup variables.
+#
+DROP DATABASE bup_events;
+SET restore_disables_events = OFF;
+SET @@global.restore_disables_events = ON;
+SHOW VARIABLES LIKE 'restore_disables_events';
+Variable_name Value
+restore_disables_events OFF
+SHOW GLOBAL VARIABLES LIKE 'restore_disables_events';
+Variable_name Value
+restore_disables_events ON
#
-
-# Drop database and perform restore
-DROP DATABASE events1;
-RESTORE FROM 'events1.bak';
-backup_id
+# Run restore and check events.
#
-
-# 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';
+RESTORE FROM 'bup_events.bak' OVERWRITE;
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;
+# # The event 'bup_events.e1' was disabled during restore.
+# # The event 'bup_events.e2' was disabled during restore.
+# # The event 'bup_events.e3' was disabled during restore.
+# # The event 'bup_events.e4' was disabled during restore.
+SHOW WARNINGS;
+Level Code Message
+Warning # The event 'bup_events.e1' was disabled during restore.
+Warning # The event 'bup_events.e2' was disabled during restore.
+Warning # The event 'bup_events.e3' was disabled during restore.
+Warning # The event 'bup_events.e4' was disabled during restore.
+#
+# Show the events.
+#
+SHOW EVENTS FROM bup_events;
+Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation
+bup_events e1 root@localhost SYSTEM RECURRING NULL 3 HOUR # # DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci
+bup_events e2 root@localhost SYSTEM RECURRING NULL 3 HOUR # # DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci
+bup_events e3 root@localhost SYSTEM RECURRING NULL 3 HOUR # # DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci
+bup_events e4 root@localhost SYSTEM RECURRING NULL 3 HOUR # # DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci
+#
+# Test Case 5 : Ensure events retain their state on restore when
+# restore_disables_events = OFF.
+#
+#
+# Setup variables.
+#
+DROP DATABASE bup_events;
+SET restore_disables_events = OFF;
+SET @@global.restore_disables_events = OFF;
+SHOW VARIABLES LIKE 'restore_disables_events';
+Variable_name Value
+restore_disables_events OFF
+SHOW GLOBAL VARIABLES LIKE 'restore_disables_events';
+Variable_name Value
+restore_disables_events OFF
+#
+# Run restore and check events.
+#
+RESTORE FROM 'bup_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;
+# Show the events.
+#
+SHOW EVENTS FROM bup_events;
+Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation
+bup_events e1 root@localhost SYSTEM RECURRING NULL 3 HOUR # # ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci
+bup_events e2 root@localhost SYSTEM RECURRING NULL 3 HOUR # # DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci
+bup_events e3 root@localhost SYSTEM RECURRING NULL 3 HOUR # # DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci
+bup_events e4 root@localhost SYSTEM RECURRING NULL 3 HOUR # # ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci
+#
+# Cleanup
+#
+SET restore_disables_events = ON;
+SET @@global.restore_disables_events = ON;
+DROP DATABASE bup_events;
=== modified file 'mysql-test/suite/backup/r/backup_namecase.result'
--- a/mysql-test/suite/backup/r/backup_namecase.result 2009-09-03 11:35:02 +0000
+++ b/mysql-test/suite/backup/r/backup_namecase.result 2009-11-10 21:19:56 +0000
@@ -1,4 +1,3 @@
-call mtr.add_suppression("The grant '22 'user_with_grants'@'%");
# Create database with tables and views
@@ -93,7 +92,12 @@ SET SESSION DEBUG='+d,restore_catalog_up
RESTORE FROM 'uppercase.bup';
backup_id
#
+Warnings:
+# # The event 'changecase.EV' was disabled during restore.
SET SESSION DEBUG='-d';
+SHOW WARNINGS;
+Level Code Message
+Warning # The event 'changecase.EV' was disabled during restore.
# Check contents of restored database
@@ -145,7 +149,7 @@ Interval value 10
Interval field SECOND
Starts #
Ends NULL
-Status ENABLED
+Status DISABLED
Originator 1
character_set_client latin1
collation_connection latin1_swedish_ci
=== added file 'mysql-test/suite/backup/t/backup_bml_not_falcon-master.opt'
--- a/mysql-test/suite/backup/t/backup_bml_not_falcon-master.opt 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup/t/backup_bml_not_falcon-master.opt 2009-11-10 21:19:56 +0000
@@ -0,0 +1 @@
+--disable-restore-disables-events
=== added file 'mysql-test/suite/backup/t/backup_compression-master.opt'
--- a/mysql-test/suite/backup/t/backup_compression-master.opt 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup/t/backup_compression-master.opt 2009-11-10 21:19:56 +0000
@@ -0,0 +1 @@
+--disable-restore-disables-events
=== modified file 'mysql-test/suite/backup/t/backup_errors_debug_1-master.opt'
--- a/mysql-test/suite/backup/t/backup_errors_debug_1-master.opt 2009-09-10 16:04:27 +0000
+++ b/mysql-test/suite/backup/t/backup_errors_debug_1-master.opt 2009-11-10 21:19:56 +0000
@@ -1 +1 @@
---log-bin
+--log-bin --disable-restore-disables-events
=== added file 'mysql-test/suite/backup/t/backup_errors_debug_2-master.opt'
--- a/mysql-test/suite/backup/t/backup_errors_debug_2-master.opt 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup/t/backup_errors_debug_2-master.opt 2009-11-10 21:19:56 +0000
@@ -0,0 +1 @@
+--disable-restore-disables-events
=== added file 'mysql-test/suite/backup/t/backup_errors_debug_3-master.opt'
--- a/mysql-test/suite/backup/t/backup_errors_debug_3-master.opt 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup/t/backup_errors_debug_3-master.opt 2009-11-10 21:19:56 +0000
@@ -0,0 +1 @@
+--disable-restore-disables-events
=== 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-10 21:19:56 +0000
@@ -1,367 +1,207 @@
#
-# 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.
+# This test tests the behavior of events during restore. Specifically, that
+# the code obeys the variable restore_disables_events.
#
+# The following test cases are presented.
+# 1 - The variable restore_disables_events is ON by default.
+# 2 - The variable restore_disables_events obeys session and
+# global behavior.
+# 3 - Ensure all events are disabled on restore when
+# restore_disables_events = ON.
+# While session variable set.
+# 4 - Ensure all events are disabled on restore when
+# restore_disables_events = ON.
+# While global variable set.
+# 5 - Ensure events retain their state on restore when
+# restore_disables_events = OFF.
--source include/not_embedded.inc
+let MYSQLD_BACKUPDIR= `select @@backupdir`;
+
+--disable_query_log
+call mtr.add_suppression("Restore: The event");
+--enable_query_log
+
--echo
---echo # Starting the test for backup
+--echo # Create database with tables and events.
--echo
-let $bdir= `select @@backupdir`;
---error 0,1
-remove_file $MYSQLD_BACKUPDIR/events.bak;
-
--disable_warnings
-DROP DATABASE IF EXISTS events;
+DROP DATABASE IF EXISTS bup_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 DATABASE bup_events;
+CREATE TABLE bup_events.t1 (a int);
+CREATE TABLE bup_events.t2 (b int);
-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';
+INSERT INTO bup_events.t1 VALUES (1), (2), (3);
+INSERT INTO bup_events.t2 VALUES (4), (5), (6);
-let $wait_condition=SELECT COUNT(*) > 6 FROM events.t3;
---source include/wait_condition.inc
-LET $result_after_res=`SELECT COUNT(*) FROM events.t3`;
+CREATE EVENT bup_events.e1 ON SCHEDULE EVERY 3 HOUR ENABLE DO
+ INSERT INTO bup_events.t1 VALUES (100);
---echo # Verify the result
-LET $result=`SELECT $result_before_bkup = $result_after_res AS are_identical`;
+CREATE EVENT bup_events.e2 ON SCHEDULE EVERY 3 HOUR DO
+ INSERT INTO bup_events.t2 VALUES (200);
---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'
+CREATE EVENT bup_events.e3 ON SCHEDULE EVERY 3 HOUR DISABLE DO
+ INSERT INTO bup_events.t1 VALUES (300);
-# 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';
+CREATE EVENT bup_events.e4 ON SCHEDULE EVERY 3 HOUR DISABLE DO
+ INSERT INTO bup_events.t2 VALUES (400);
---echo # Drop database and perform restore
-DROP DATABASE events;
---replace_column 1 #
-RESTORE FROM 'events.bak';
+ALTER EVENT bup_events.e2 DISABLE;
+ALTER EVENT bup_events.e4 ENABLE;
---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
+--echo #
+--echo # Show the events.
+--echo #
+--replace_column 9 # 10 #
+SHOW EVENTS FROM bup_events;
-SELECT * FROM events.t2 ORDER BY a;
+--echo #
+--echo # Perform backup for later use.
+--echo #
---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');
+BACKUP DATABASE bup_events TO 'bup_events.bak';
---echo # Include wait_condition and sleep so that events are executed properly
-sleep 2;
+--echo #
+--echo # Show the events.
+--echo #
+--replace_column 9 # 10 #
+SHOW EVENTS FROM bup_events;
-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;
+--echo #
+--echo # Test Case 1 : The variable restore_disables_events is ON by default.
+--echo #
---remove_file $bdir/events.bak
---remove_file $bdir/events1.bak
+SHOW VARIABLES LIKE 'restore_disables_events';
+SHOW GLOBAL VARIABLES LIKE 'restore_disables_events';
---echo # Rename the event in different database and verify event functions
---echo # properly after backup and restore in different database.
+--echo #
+--echo # Test Case 2 : The variable restore_disables_events obeys session and
+--echo # global behavior.
+--echo #
-DROP DATABASE IF EXISTS events1;
-CREATE DATABASE events1;
-ALTER EVENT events.e_rename RENAME TO events1.e1_rename;
+SET restore_disables_events = OFF;
-INSERT INTO events.t2 VALUES('c'),('c');
+SHOW VARIABLES LIKE 'restore_disables_events';
+SHOW GLOBAL VARIABLES LIKE 'restore_disables_events';
---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';
+SET @@global.restore_disables_events = OFF;
---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;
+SHOW VARIABLES LIKE 'restore_disables_events';
+SHOW GLOBAL VARIABLES LIKE 'restore_disables_events';
---remove_file $bdir/events1.bak
+SET restore_disables_events = ON;
+SET @@global.restore_disables_events = ON;
---echo
---echo # Test 3: Create event with definer clause.
---echo
+SHOW VARIABLES LIKE 'restore_disables_events';
+SHOW GLOBAL VARIABLES LIKE 'restore_disables_events';
---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
+--echo #
+--echo # Test Case 3 : Ensure all events are disabled on restore when
+--echo # restore_disables_events = ON.
+--echo # While session variable set.
+--echo #
-SELECT * FROM events.log;
+--echo #
+--echo # Setup variables.
+--echo #
+DROP DATABASE bup_events;
---echo
---echo # Perform backup
---replace_column 1 #
-BACKUP DATABASE events TO 'events.bak';
+SET restore_disables_events = ON;
+SET @@global.restore_disables_events = OFF;
-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;
+SHOW VARIABLES LIKE 'restore_disables_events';
+SHOW GLOBAL VARIABLES LIKE 'restore_disables_events';
+--echo #
+--echo # Run restore and check events.
+--echo #
--replace_column 1 # 2 #
-RESTORE FROM 'events1.bak';
-
---echo # Exercise objects
---replace_column 4 #
-SHOW CREATE EVENT events.e4;
+RESTORE FROM 'bup_events.bak' OVERWRITE;
--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;
+SHOW WARNINGS;
-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
+--echo #
+--echo # Show the events.
+--echo #
+--replace_column 9 # 10 #
+SHOW EVENTS FROM bup_events;
+
+--echo #
+--echo # Test Case 4 : Ensure all events are disabled on restore when
+--echo # restore_disables_events = ON.
+--echo # While global variable set.
+--echo #
+
+--echo #
+--echo # Setup variables.
+--echo #
+DROP DATABASE bup_events;
+
+SET restore_disables_events = OFF;
+SET @@global.restore_disables_events = ON;
+
+SHOW VARIABLES LIKE 'restore_disables_events';
+SHOW GLOBAL VARIABLES LIKE 'restore_disables_events';
+
+--echo #
+--echo # Run restore and check events.
+--echo #
+--replace_column 1 # 2 #
+RESTORE FROM 'bup_events.bak' OVERWRITE;
+--replace_column 2 #
+SHOW WARNINGS;
-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 #
+--echo # Show the events.
+--echo #
+--replace_column 9 # 10 #
+SHOW EVENTS FROM bup_events;
---echo # Perform Backup
---replace_column 1 #
-BACKUP DATABASE events TO 'events.bak';
+--echo #
+--echo # Test Case 5 : Ensure events retain their state on restore when
+--echo # restore_disables_events = OFF.
+--echo #
---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;
+--echo #
+--echo # Setup variables.
+--echo #
+DROP DATABASE bup_events;
---replace_column 4 #
-SHOW CREATE EVENT events.e5;
+SET restore_disables_events = OFF;
+SET @@global.restore_disables_events = OFF;
---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;
+SHOW VARIABLES LIKE 'restore_disables_events';
+SHOW GLOBAL VARIABLES LIKE 'restore_disables_events';
+--echo #
+--echo # Run restore and check events.
+--echo #
--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;
+RESTORE FROM 'bup_events.bak' OVERWRITE;
---echo # Execute events
-INSERT INTO events.t4 VALUES (10,'b'),(20,'p'),(30,'b');
+--echo #
+--echo # Show the events.
+--echo #
+--replace_column 9 # 10 #
+SHOW EVENTS FROM bup_events;
-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 #
+--echo # Cleanup
+--echo #
---echo # Perform restore again
---replace_column 1 #
-RESTORE FROM 'events.bak' OVERWRITE;
+SET restore_disables_events = ON;
+SET @@global.restore_disables_events = ON;
---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;
+DROP DATABASE bup_events;
---remove_file $bdir/events.bak
---remove_file $bdir/events1.bak
+--remove_file $MYSQLD_BACKUPDIR/bup_events.bak
=== added file 'mysql-test/suite/backup/t/backup_logs-master.opt'
--- a/mysql-test/suite/backup/t/backup_logs-master.opt 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup/t/backup_logs-master.opt 2009-11-10 21:19:56 +0000
@@ -0,0 +1 @@
+--disable-restore-disables-events
=== modified file 'mysql-test/suite/backup/t/backup_namecase.test'
--- a/mysql-test/suite/backup/t/backup_namecase.test 2009-09-03 11:35:02 +0000
+++ b/mysql-test/suite/backup/t/backup_namecase.test 2009-11-10 21:19:56 +0000
@@ -16,7 +16,10 @@
--source include/have_debug_sync.inc
--source include/not_embedded.inc
+--disable_query_log
call mtr.add_suppression("The grant '22 'user_with_grants'@'%");
+call mtr.add_suppression("Restore: The event");
+--enable_query_log
--echo
--echo # Create database with tables and views
@@ -98,10 +101,13 @@ REVOKE ALL ON *.* FROM 'user_with_grants
--echo # Activate debug hook that makes all names upper-case. Should work.
SET SESSION DEBUG='+d,restore_catalog_uppercase_names';
---replace_column 1 #
+--replace_column 1 # 2 #
RESTORE FROM 'uppercase.bup';
SET SESSION DEBUG='-d';
+--replace_column 2 #
+SHOW WARNINGS;
+
--echo
--echo # Check contents of restored database
--echo
=== added file 'mysql-test/suite/backup/t/backup_objects-master.opt'
--- a/mysql-test/suite/backup/t/backup_objects-master.opt 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup/t/backup_objects-master.opt 2009-11-10 21:19:56 +0000
@@ -0,0 +1 @@
+--disable-restore-disables-events
=== added file 'mysql-test/suite/backup/t/backup_security-master.opt'
--- a/mysql-test/suite/backup/t/backup_security-master.opt 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup/t/backup_security-master.opt 2009-11-10 21:19:56 +0000
@@ -0,0 +1 @@
+--disable-restore-disables-events
=== added file 'mysql-test/suite/backup/t/backup_triggers_and_events-master.opt'
--- a/mysql-test/suite/backup/t/backup_triggers_and_events-master.opt 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup/t/backup_triggers_and_events-master.opt 2009-11-10 21:19:56 +0000
@@ -0,0 +1 @@
+--disable-restore-disables-events
=== modified file 'sql/backup/kernel.cc'
--- a/sql/backup/kernel.cc 2009-10-23 15:41:56 +0000
+++ b/sql/backup/kernel.cc 2009-11-10 21:19:56 +0000
@@ -1487,6 +1487,20 @@ int Backup_restore_ctx::restore_triggers
while ((ev= it++))
{
int ret= ev->m_obj_ptr->create(m_thd);
+
+ /*
+ If either the global variable restore_disables_events is
+ ON or the session variable by the same name is ON, disable
+ this event.
+ */
+ if (m_thd->variables.restore_disables_events ||
+ global_system_variables.restore_disables_events)
+ {
+ obs::disable_event(m_thd, ev->m_obj_ptr->get_db_name()->ptr(),
+ ev->m_obj_ptr->get_name()->ptr(), TRUE);
+ report_event_disabled(ev->m_obj_ptr->get_db_name()->ptr(),
+ ev->m_obj_ptr->get_name()->ptr());
+ }
DBUG_EXECUTE_IF("ER_BACKUP_CANT_RESTORE_EVENT_1", ret= 1;);
=== modified file 'sql/backup/logger.cc'
--- a/sql/backup/logger.cc 2009-10-23 15:41:56 +0000
+++ b/sql/backup/logger.cc 2009-11-10 21:19:56 +0000
@@ -218,6 +218,34 @@ void Logger::report_stats_post(const Ima
backup_log->size(info.data_size);
}
+/**
+ Report that an event was disabled on restore.
+
+ This method writes a warning message to the console, the warning stack,
+ and the backup progress log to tell the user that a specific event was
+ disabled on restore.
+
+ @param[in] db_name Name of the database for the even.
+ @param[in] event_name Name of event to disable.
+*/
+void Logger::report_event_disabled(const char *db_name, const char *event_name)
+{
+ String ev_full_name;
+ char buff[MYSQL_ERRMSG_SIZE];
+
+ if (!db_name || !event_name)
+ return;
+
+ ev_full_name.length(0);
+ ev_full_name.append(db_name);
+ ev_full_name.append(".");
+ ev_full_name.append(event_name);
+
+ // Report warning and write to backup progress log.
+
+ sprintf(buff, ER(ER_RESTORE_EVENT_DISABLED), ev_full_name.c_ptr());
+ write_message(log_level::WARNING, ER_RESTORE_EVENT_DISABLED, buff);
+}
/**
Indicate if reported errors should be pushed on the warning stack.
=== modified file 'sql/backup/logger.h'
--- a/sql/backup/logger.h 2009-10-21 13:32:24 +0000
+++ b/sql/backup/logger.h 2009-11-10 21:19:56 +0000
@@ -76,6 +76,7 @@ public:
void report_stats_pre(const Image_info&);
void report_stats_post(const Image_info&);
bool report_killed();
+ void report_event_disabled(const char *db_name, const char *event_name);
/// Return the Backup_id of the current operation.
ulonglong get_op_id() const
=== modified file 'sql/mysqld.cc'
--- a/sql/mysqld.cc 2009-10-29 21:33:17 +0000
+++ b/sql/mysqld.cc 2009-11-10 21:19:56 +0000
@@ -6052,7 +6052,8 @@ enum options_mysqld
OPT_BACKUP_HISTORY_LOG_FILE,
OPT_BACKUP_PROGRESS_LOG_FILE,
OPT_MYSQL_BACKUP,
- OPT_IGNORE_BUILTIN_INNODB
+ OPT_IGNORE_BUILTIN_INNODB,
+ OPT_RESTORE_DISABLES_EVENTS
};
@@ -6723,6 +6724,9 @@ Can't be set to 1 if --log-slave-updates
MYSQL_PORT, 0, 0, 0, 0, 0},
{"report-user", OPT_REPORT_USER, "Undocumented.", (uchar**) &report_user,
(uchar**) &report_user, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
+ {"restore-disables-events", OPT_RESTORE_DISABLES_EVENTS,
+ "Enable|disable events on restore", (uchar**) &global_system_variables.restore_disables_events,
+ (uchar**) &global_system_variables.restore_disables_events, 0, GET_BOOL, OPT_ARG, 1, 0, 0, 0, 0, 0},
{"rpl-recovery-rank", OPT_RPL_RECOVERY_RANK, "Undocumented.",
(uchar**) &rpl_recovery_rank, (uchar**) &rpl_recovery_rank, 0, GET_ULONG,
REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
=== modified file 'sql/set_var.cc'
--- a/sql/set_var.cc 2009-10-27 10:03:00 +0000
+++ b/sql/set_var.cc 2009-11-10 21:19:56 +0000
@@ -258,6 +258,10 @@ static sys_var_long_ptr sys_delayed_inse
static sys_var_long_ptr sys_delayed_queue_size(&vars, "delayed_queue_size",
&delayed_queue_size);
+static sys_var_thd_bool sys_restore_disables_events(&vars,
+ "restore_disables_events",
+ &SV::restore_disables_events);
+
#ifdef HAVE_EVENT_SCHEDULER
static sys_var_event_scheduler sys_event_scheduler(&vars, "event_scheduler");
#endif
=== modified file 'sql/share/errmsg-utf8.txt'
--- a/sql/share/errmsg-utf8.txt 2009-10-12 09:08:34 +0000
+++ b/sql/share/errmsg-utf8.txt 2009-11-10 21:19:56 +0000
@@ -6562,3 +6562,5 @@ ER_BACKUP_ACCESS_OBJS_INCOMPLETE
eng "Insufficient privileges. You do not have privileges to backup database '%s'."
ER_WARN_I_S_SKIPPED_TABLE
eng "Table '%s'.'%s' was skipped since its definition is being modified by concurrent DDL statement."
+ER_RESTORE_EVENT_DISABLED
+ eng "The event '%s' was disabled during restore."
=== modified file 'sql/share/errmsg.txt'
--- a/sql/share/errmsg.txt 2009-10-26 14:02:26 +0000
+++ b/sql/share/errmsg.txt 2009-11-10 21:19:56 +0000
@@ -6562,3 +6562,5 @@ ER_BACKUP_ACCESS_OBJS_INCOMPLETE
eng "Insufficient privileges. You do not have privileges to backup database '%s'."
ER_WARN_I_S_SKIPPED_TABLE
eng "Table '%s'.'%s' was skipped since its definition is being modified by concurrent DDL statement."
+ER_RESTORE_EVENT_DISABLED
+ eng "The event '%s' was disabled during restore."
=== modified file 'sql/si_objects.cc'
--- a/sql/si_objects.cc 2009-11-09 10:56:12 +0000
+++ b/sql/si_objects.cc 2009-11-10 21:19:56 +0000
@@ -3674,4 +3674,70 @@ int write_incident_event(THD *thd, incid
///////////////////////////////////////////////////////////////////////////
+/**
+ Disable or enable an event.
+
+ This method can be used to turn off (disable) a specific event.
+
+ @param[IN] thd The current thread
+ @param[IN] db_name Database name for event
+ @param[IN] event_name Name of event to disable
+ @param[IN] disable If TRUE, disable, else enable.
+
+ @retval FALSE on success.
+ @retval TRUE on error.
+*/
+bool disable_event(THD *thd,
+ const char *db_name,
+ const char *event_name,
+ bool disable)
+{
+ int res= FALSE;
+
+ Ed_connection ed_connection(thd);
+ String sql_text;
+ LEX_STRING sql;
+
+ /*
+ We update the mysql table directly to avoid changing character
+ collation or character set, modified by, etc.
+ */
+ sql_text.length(0);
+ sql_text.append("UPDATE mysql.event SET status = ");
+ if (disable)
+ sql_text.append("'DISABLED' ");
+ else
+ sql_text.append("'ENABLED' ");
+ sql_text.append("WHERE db = '");
+ sql_text.append(db_name);
+ sql_text.append("' AND name = '");
+ sql_text.append(event_name);
+ sql_text.append("' ");
+
+ sql.str= sql_text.c_ptr();
+ sql.length= sql_text.length();
+
+ Si_session_context session_context;
+
+ session_context.save_si_ctx(thd);
+ session_context.reset_si_ctx(thd);
+
+ /* Allow to execute DDL operations. */
+ ::obs::bml_exception_on(thd);
+
+ res= ed_connection.execute_direct(sql);
+
+ /* Push warnings on the THD error stack. */
+ thd->warning_info->append_warnings(thd, ed_connection.get_warn_list());
+
+ /* Disable further DDL execution. */
+ ::obs::bml_exception_off(thd);
+
+ session_context.restore_si_ctx(thd);
+
+ return res;
+}
+
+///////////////////////////////////////////////////////////////////////////
+
} // obs namespace
=== modified file 'sql/si_objects.h'
--- a/sql/si_objects.h 2009-11-04 14:18:21 +0000
+++ b/sql/si_objects.h 2009-11-10 21:19:56 +0000
@@ -573,6 +573,14 @@ enum incident_events {
*/
int write_incident_event(THD *thd, incident_events incident_enum);
+/*
+ Disable or enable an event.
+*/
+bool disable_event(THD *thd,
+ const char *db_name,
+ const char *event_name,
+ bool disable);
+
} // obs namespace
#endif // SI_OBJECTS_H_
=== modified file 'sql/sql_class.h'
--- a/sql/sql_class.h 2009-10-28 15:45:46 +0000
+++ b/sql/sql_class.h 2009-11-10 21:19:56 +0000
@@ -448,6 +448,8 @@ struct system_variables
/* deadlock detection */
ulong wt_timeout_short, wt_deadlock_search_depth_short;
ulong wt_timeout_long, wt_deadlock_search_depth_long;
+
+ my_bool restore_disables_events;
};
Attachment: [text/bzr-bundle] bzr/charles.bell@sun.com-20091110211956-w9pa6f1mrht4yln0.bundle
