Debian Codegraph @

mysql-test/suite/rpl/t/rpl_events.test

raw
1 ##################################################################
2 # Author: Giuseppe #
3 # Date: 2006-12-20 #
4 # Purpose: To test that event effects are replicated. #
5 ##################################################################
6
7 --source include/not_group_replication_plugin.inc
8 --source include/set_privilege_checks_user_as_system_user.inc
9 --source include/master-slave.inc
10
11 SET @old_event_scheduler = @@global.event_scheduler;
12 set global event_scheduler=1;
13
14 #
15 # Bug #28953 Using events in a replication let the slave crash.
16 #
17
18 connection master;
19
20 CREATE TABLE t28953 (a INT);
21
22 DELIMITER |;
23 CREATE EVENT event1 ON SCHEDULE EVERY 1 YEAR
24 DO BEGIN
25 select * from t28953;
26 END;|
27 DELIMITER ;|
28
29 ALTER EVENT event1 RENAME TO event2;
30
31 --source include/sync_slave_sql_with_master.inc
32
33 connection master;
34
35 DROP EVENT event2;
36
37 #
38 # BUG#44331
39 # This test verifies if the definer is consistent between master and slave,
40 # when the event is created without the DEFINER clause set explicitly or the
41 # DEFINER is set to CURRENT_USER
42 #
43 CREATE TABLE test.t1(details CHAR(30));
44
45 CREATE EVENT /*!50000 event44331_1 */
46 ON SCHEDULE AT CURRENT_TIMESTAMP
47 ON COMPLETION PRESERVE DISABLE
48 DO INSERT INTO test.t1 VALUES('event event44331_1 fired - no definer');
49
50 CREATE DEFINER=CURRENT_USER /*!50000 EVENT event44331_2 */
51 ON SCHEDULE AT CURRENT_TIMESTAMP
52 ON COMPLETION PRESERVE DISABLE
53 DO INSERT INTO test.t1 VALUES('event event44331_2 fired - DEFINER=CURRENT_USER');
54
55 CREATE DEFINER=CURRENT_USER() EVENT event44331_3
56 ON SCHEDULE AT CURRENT_TIMESTAMP
57 ON COMPLETION PRESERVE DISABLE
58 DO INSERT INTO test.t1 VALUES('event event44331_3 fired - DEFINER=CURRENT_USER() function');
59
60 DELIMITER |;
61 CREATE /*!50000 DEFINER='user44331' */ EVENT event44331_4
62 ON SCHEDULE AT CURRENT_TIMESTAMP
63 ON COMPLETION PRESERVE DISABLE
64 DO INSERT INTO test.t1 VALUES('event event44331_4 fired - DEFINER=user1');
65 # Test for bug#50095 Multi-statement including CREATE EVENT causes rotten
66 # binlog entry
67 SELECT 'ABC';
68 SELECT '123'|
69 DELIMITER ;|
70
71 --echo #on master
72 select EVENT_SCHEMA, EVENT_NAME, DEFINER from information_schema.events
73 where EVENT_NAME='event44331_1';
74 select EVENT_SCHEMA, EVENT_NAME, DEFINER from information_schema.events
75 where EVENT_NAME='event44331_2';
76 select EVENT_SCHEMA, EVENT_NAME, DEFINER from information_schema.events
77 where EVENT_NAME='event44331_3';
78 select EVENT_SCHEMA, EVENT_NAME, DEFINER from information_schema.events
79 where EVENT_NAME='event44331_4';
80
81 --source include/sync_slave_sql_with_master.inc
82 connection slave;
83 --echo #on slave
84 select EVENT_SCHEMA, EVENT_NAME, DEFINER from information_schema.events
85 where EVENT_NAME='event44331_1';
86 select EVENT_SCHEMA, EVENT_NAME, DEFINER from information_schema.events
87 where EVENT_NAME='event44331_2';
88 select EVENT_SCHEMA, EVENT_NAME, DEFINER from information_schema.events
89 where EVENT_NAME='event44331_3';
90 select EVENT_SCHEMA, EVENT_NAME, DEFINER from information_schema.events
91 where EVENT_NAME='event44331_4';
92
93 connection master;
94 SET @@global.event_scheduler= @old_event_scheduler;
95 DROP TABLE t28953;
96 DROP TABLE t1;
97 DROP EVENT event44331_1;
98 DROP EVENT event44331_2;
99 DROP EVENT event44331_3;
100 DROP EVENT event44331_4;
101 --source include/sync_slave_sql_with_master.inc
102
103 # Bug#21229951 VARIABLES IN ALTER EVENT NOT REPLICATED PROPERLY
104 connection master;
105 DELIMITER \\;
106 CREATE EVENT event1 ON SCHEDULE EVERY 100 SECOND STARTS '2000-01-01 00:00:00'
107 ON COMPLETION PRESERVE ENABLE DO
108 BEGIN
109 SET @dummy = 100;
110 END\\
111
112 # A procedure that has alter event which uses sp local variable in it
113 CREATE PROCEDURE proc1()
114 BEGIN
115 DECLARE dummy INT UNSIGNED;
116 SET dummy = 100;
117 ALTER EVENT EVENT1 ON SCHEDULE EVERY dummy SECOND
118 STARTS '2000-01-01 00:00:00' ENABLE;
119 END \\
120
121 DELIMITER ;\\
122
123 CALL proc1();
124 --source include/sync_slave_sql_with_master.inc
125
126 # Cleanup
127 connection master;
128 DROP EVENT event1;
129 DROP PROCEDURE proc1;
130
131 #
132 # Bug #19286708 REPLICATION BROKEN AFTER CREATION OF SCHEDULED EVENTS
133 # Testing create/alter event that has sysdate() (which makes statement unsafe)
134 # Testing create/alter event that has local variables
135 #
136 connection master;
137 call mtr.add_suppression("Unsafe statement written to the binary log");
138 --disable_warnings
139 set @secs=100;
140 DELIMITER |;
141 CREATE EVENT EVENT_WITH_SYSDATE_AND_VARIABLES
142 ON SCHEDULE EVERY @secs MINUTE STARTS sysdate()
143 ON COMPLETION PRESERVE
144 DO BEGIN
145 DECLARE EXIT HANDLER FOR SQLEXCEPTION
146 SELECT CONCAT('SAMPLE MSG');
147 END |
148
149 ALTER EVENT EVENT_WITH_SYSDATE_AND_VARIABLES
150 ON SCHEDULE
151 EVERY @secs HOUR
152 STARTS sysdate() |
153
154 DROP EVENT EVENT_WITH_SYSDATE_AND_VARIABLES |
155 --enable_warnings
156 DELIMITER ;|
157 --source include/rpl_end.inc