rpl_events.inc 5.27 KB
Newer Older
1 2 3 4 5 6 7 8
##################################################################
# Author: Giuseppe, Chuck Bell                                   #
# Date: 17-January-2007                                          #
# Purpose: To test that event effects are replicated             #
# in both row based and statement based format                   #
##################################################################

--disable_warnings
9
DROP EVENT IF EXISTS test.justonce;
10 11 12 13 14 15
drop table if exists t1,t2;
--enable_warnings

# first, we need a table to record something from an event

eval 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 20 21
  PRIMARY KEY (`id`)
) ENGINE=$engine_type DEFAULT CHARSET=utf8;

22
INSERT INTO t1 (id, c) VALUES (1, 'manually');
23

24 25 26 27 28 29 30
# We create the event so that it inserts exactly 1 row in the table
# A recuring event is used so that we can be sure the event will
# fire regardless of timing delays on the server. Otherwise, it is
# possible for the event to timeout before it has inserted a row.
--echo "Creating event test.justonce on the master"
CREATE EVENT test.justonce ON SCHEDULE EVERY 2 SECOND DO 
  INSERT IGNORE INTO t1 (id, c) VALUES (2, 'from justonce');
31

32 33
# Show the event is alive and present on master
--echo "Checking event is active on master"
34 35
SELECT db, name, status, originator FROM mysql.event WHERE db = 'test' AND name = 'justonce';

36 37 38 39 40
# Wait until event has fired. We know this because t1 will contain 
# the row from the event.
let $wait_condition=
  SELECT COUNT(*) = 1 FROM t1 WHERE c = 'from justonce';
--source include/wait_condition.inc
41 42

# check that table t1 contains something
43
--echo "Checking event data on the master"
44 45 46 47
let $events_done=`SELECT count(*)  FROM t1 id`;
--disable_query_log
eval SELECT $events_done > 0 as ONE;
--enable_query_log
48 49 50

sync_slave_with_master;

51
--echo "Checking event data on the slave"
52 53 54
--disable_query_log
eval SELECT count(*) - $events_done as ZERO FROM t1 id;
--enable_query_log
55

56
--echo "Checking event is inactive on slave"
57 58
SELECT db, name, status, originator FROM mysql.event WHERE db = 'test' AND name = 'justonce';

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 62 63 64 65
--disable_warnings
DROP EVENT IF EXISTS test.slave_once;
--enable_warnings

CREATE EVENT test.slave_once ON SCHEDULE EVERY 5 MINUTE DO 
66 67 68
  INSERT IGNORE INTO t1(id, c) VALUES (3, 'from slave_once');

--echo "Checking event status on the slave for originator value = slave's server_id"
69
SELECT db, name, status, originator FROM mysql.event WHERE db = 'test' AND name = 'slave_once';
70

71
--echo "Dropping event test.slave_once on the slave"
72 73 74 75 76 77 78
--disable_warnings
DROP EVENT IF EXISTS test.slave_once;
--enable_warnings

connection master;

# BUG#20384 - disable events on slave
79
--echo "Dropping event test.justonce on the master"
80
--disable_warnings
81
DROP EVENT IF EXISTS test.justonce;
82 83
--enable_warnings

84
--echo "Creating event test.er on the master"
85
CREATE EVENT test.er ON SCHEDULE EVERY 3 SECOND DO 
86 87 88
  INSERT IGNORE INTO t1(id, c) VALUES (4, 'from er');

--echo "Checking event status on the master"
89
SELECT db, name, status, originator, body FROM mysql.event WHERE db = 'test' AND name = 'er';
90 91

sync_slave_with_master;
92

93
--echo "Checking event status on the slave"
94
SELECT db, name, status, originator, body FROM mysql.event WHERE db = 'test' AND name = 'er';
95 96

connection master;
97 98 99 100 101
--echo "Altering event test.er on the master"
ALTER EVENT test.er ON SCHEDULE EVERY 5 SECOND DO 
  INSERT IGNORE INTO t1(id, c) VALUES (5, 'from alter er');

--echo "Checking event status on the master"
102
SELECT db, name, status, originator, body FROM mysql.event WHERE db = 'test' AND name = 'er';
103 104

sync_slave_with_master;
105

106
--echo "Checking event status on the slave"
107
SELECT db, name, status, originator, body FROM mysql.event WHERE db = 'test' AND name = 'er';
108 109

connection master;
110
--echo "Dropping event test.er on the master"
111
DROP EVENT test.er;
112 113

--echo "Checking event status on the master"
114
SELECT db, name, status, originator FROM mysql.event WHERE db = 'test';
115 116 117 118

--disable_info

sync_slave_with_master;
119

120
--echo "Checking event status on the slave"
121
SELECT db, name, status, originator FROM mysql.event WHERE db = 'test';
122

123 124 125
# test the DISABLE ON SLAVE for setting event SLAVESIDE_DISABLED as status
# on CREATE EVENT

126 127 128 129 130
--echo "Creating event test.slave_terminate on the slave"
CREATE EVENT test.slave_terminate ON SCHEDULE EVERY 3 SECOND DO 
  INSERT IGNORE INTO t1(id, c) VALUES (6, 'from slave_terminate');

--echo "Checking event status on the slave"
131 132
SELECT db, name, status, originator FROM mysql.event WHERE db = 'test' AND name = 'slave_terminate';

133
--echo "Dropping event test.slave_terminate on the slave"
134 135
DROP EVENT test.slave_terminate;

136 137 138 139 140
--echo "Creating event test.slave_terminate with DISABLE ON SLAVE on the slave"
CREATE EVENT test.slave_terminate ON SCHEDULE EVERY 3 SECOND DISABLE ON SLAVE DO 
  INSERT IGNORE INTO t1(c) VALUES (7, 'from slave_terminate');

--echo "Checking event status on the slave"
141 142
SELECT db, name, status, originator FROM mysql.event WHERE db = 'test' AND name = 'slave_terminate';

143
--echo "Dropping event test.slave_terminate on the slave"
144 145
DROP EVENT test.slave_terminate;

146
--echo "Cleanup"
147 148
connection master;
DROP TABLE t1;
unknown's avatar
unknown committed
149 150
sync_slave_with_master;
connection master;
151