1##################################################################
2# Author: Giuseppe, Chuck Bell                                   #
3# Date: 17-January-2007                                          #
4# Purpose: To test that event effects are replicated             #
5# in both row based and statement based format                   #
6##################################################################
7
8--disable_warnings
9DROP EVENT IF EXISTS test.justonce;
10drop table if exists t1,t2;
11--enable_warnings
12
13# first, we need a table to record something from an event
14
15eval CREATE TABLE `t1` (
16  `id` INT(10) UNSIGNED NOT NULL,
17  `c` VARCHAR(50) NOT NULL,
18  `ts` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
19  PRIMARY KEY (`id`)
20) ENGINE=$engine_type DEFAULT CHARSET=utf8;
21
22INSERT INTO t1 (id, c) VALUES (1, 'manually');
23
24# We create the event so that it inserts exactly 1 row in the table
25# A recuring event is used so that we can be sure the event will
26# fire regardless of timing delays on the server. Otherwise, it is
27# possible for the event to timeout before it has inserted a row.
28--echo "Creating event test.justonce on the master"
29CREATE EVENT test.justonce ON SCHEDULE EVERY 2 SECOND DO
30  INSERT IGNORE INTO t1 (id, c) VALUES (2, 'from justonce');
31
32# Show the event is alive and present on master
33--echo "Checking event is active on master"
34SELECT db, name, status, originator FROM mysql.event WHERE db = 'test' AND name = 'justonce';
35
36# Wait until event has fired. We know this because t1 will contain
37# the row from the event.
38let $wait_condition=
39  SELECT COUNT(*) = 1 FROM t1 WHERE c = 'from justonce';
40--source include/wait_condition.inc
41
42# check that table t1 contains something
43--echo "Checking event data on the master"
44let $events_done=`SELECT count(*)  FROM t1 id`;
45--disable_query_log
46eval SELECT $events_done > 0 as ONE;
47--enable_query_log
48
49sync_slave_with_master;
50
51--echo "Checking event data on the slave"
52--disable_query_log
53eval SELECT count(*) - $events_done as ZERO FROM t1 id;
54--enable_query_log
55
56--echo "Checking event is inactive on slave"
57SELECT db, name, status, originator FROM mysql.event WHERE db = 'test' AND name = 'justonce';
58
59# Create an event on the slave and check to see what the originator is.
60--echo "Dropping event test.slave_once on the slave"
61--disable_warnings
62DROP EVENT IF EXISTS test.slave_once;
63--enable_warnings
64
65# Create an event on slave and check its state. An event shouldn't be executed
66# so set start time in 1 hour.
67CREATE EVENT test.slave_once ON SCHEDULE EVERY 5 MINUTE STARTS CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO
68  INSERT IGNORE INTO t1(id, c) VALUES (3, 'from slave_once');
69
70--echo "Checking event status on the slave for originator value = slave's server_id"
71SELECT db, name, status, originator FROM mysql.event WHERE db = 'test' AND name = 'slave_once';
72
73--echo "Dropping event test.slave_once on the slave"
74--disable_warnings
75DROP EVENT IF EXISTS test.slave_once;
76--enable_warnings
77
78connection master;
79
80# BUG#20384 - disable events on slave
81--echo "Dropping event test.justonce on the master"
82--disable_warnings
83DROP EVENT IF EXISTS test.justonce;
84--enable_warnings
85
86# Create an event on master and check its state on slave. An event shouldn't be executed
87# so set start time in 1 hour. Check that changes of event statement replicated to slave
88
89--echo "Creating event test.er on the master"
90CREATE EVENT test.er ON SCHEDULE EVERY 3 SECOND STARTS CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO
91  INSERT IGNORE INTO t1(id, c) VALUES (4, 'from er');
92
93--echo "Checking event status on the master"
94SELECT db, name, status, originator, body FROM mysql.event WHERE db = 'test' AND name = 'er';
95
96sync_slave_with_master;
97
98--echo "Checking event status on the slave"
99SELECT db, name, status, originator, body FROM mysql.event WHERE db = 'test' AND name = 'er';
100
101connection master;
102--echo "Altering event test.er on the master"
103ALTER EVENT test.er ON SCHEDULE EVERY 5 SECOND STARTS CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO
104  INSERT IGNORE INTO t1(id, c) VALUES (5, 'from alter er');
105
106--echo "Checking event status on the master"
107SELECT db, name, status, originator, body FROM mysql.event WHERE db = 'test' AND name = 'er';
108
109sync_slave_with_master;
110
111--echo "Checking event status on the slave"
112SELECT db, name, status, originator, body FROM mysql.event WHERE db = 'test' AND name = 'er';
113
114connection master;
115--echo "Dropping event test.er on the master"
116DROP EVENT test.er;
117
118--echo "Checking event status on the master"
119SELECT db, name, status, originator FROM mysql.event WHERE db = 'test';
120
121--disable_info
122
123sync_slave_with_master;
124
125--echo "Checking event status on the slave"
126SELECT db, name, status, originator FROM mysql.event WHERE db = 'test';
127
128# test the DISABLE ON SLAVE for setting event SLAVESIDE_DISABLED as status
129# on CREATE EVENT
130
131# Create an event on slave and check its status. An event shouldn't be executed
132# so set start time in 1 hour.
133
134--echo "Creating event test.slave_terminate on the slave"
135CREATE EVENT test.slave_terminate ON SCHEDULE EVERY 3 SECOND STARTS CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO
136  INSERT IGNORE INTO t1(id, c) VALUES (6, 'from slave_terminate');
137
138--echo "Checking event status on the slave"
139SELECT db, name, status, originator FROM mysql.event WHERE db = 'test' AND name = 'slave_terminate';
140
141--echo "Dropping event test.slave_terminate on the slave"
142DROP EVENT test.slave_terminate;
143
144--echo "Creating event test.slave_terminate with DISABLE ON SLAVE on the slave"
145CREATE EVENT test.slave_terminate ON SCHEDULE EVERY 3 SECOND DISABLE ON SLAVE DO
146  INSERT IGNORE INTO t1(c) VALUES (7, 'from slave_terminate');
147
148--echo "Checking event status on the slave"
149SELECT db, name, status, originator FROM mysql.event WHERE db = 'test' AND name = 'slave_terminate';
150
151--echo "Dropping event test.slave_terminate on the slave"
152DROP EVENT test.slave_terminate;
153
154--echo "Cleanup"
155connection master;
156DROP TABLE t1;
157sync_slave_with_master;
158connection master;
159
160