1################################################################## 2# Author: Giuseppe # 3# Date: 2006-12-20 # 4# Purpose: To test that event effects are replicated. # 5################################################################## 6 7--source include/master-slave.inc 8 9SET @old_event_scheduler = @@global.event_scheduler; 10set global event_scheduler=1; 11 12# first, we need a table to record something from an event 13 14eval CREATE TABLE `t1` ( 15 `id` INT(10) UNSIGNED NOT NULL, 16 `c` VARCHAR(50) NOT NULL, 17 `ts` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 18 PRIMARY KEY (`id`) 19) DEFAULT CHARSET=utf8; 20 21INSERT INTO t1 (id, c) VALUES (1, 'manually'); 22 23# We create the event so that it inserts exactly 1 row in the table 24# A recuring event is used so that we can be sure the event will 25# fire regardless of timing delays on the server. Otherwise, it is 26# possible for the event to timeout before it has inserted a row. 27--echo "Creating event test.justonce on the master" 28CREATE EVENT test.justonce ON SCHEDULE EVERY 2 SECOND DO 29 INSERT IGNORE INTO t1 (id, c) VALUES (2, 'from justonce'); 30 31# Show the event is alive and present on master 32--echo "Checking event is active on master" 33SELECT db, name, status, originator FROM mysql.event WHERE db = 'test' AND name = 'justonce'; 34 35# Wait until event has fired. We know this because t1 will contain 36# the row from the event. 37let $wait_condition= 38 SELECT COUNT(*) = 1 FROM t1 WHERE c = 'from justonce'; 39--source include/wait_condition.inc 40 41# check that table t1 contains something 42--echo "Checking event data on the master" 43let $events_done=`SELECT count(*) FROM t1 id`; 44--disable_query_log 45eval SELECT $events_done > 0 as ONE; 46--enable_query_log 47 48sync_slave_with_master; 49 50--echo "Checking event data on the slave" 51--disable_query_log 52eval SELECT count(*) - $events_done as ZERO FROM t1 id; 53--enable_query_log 54 55--echo "Checking event is inactive on slave" 56SELECT db, name, status, originator FROM mysql.event WHERE db = 'test' AND name = 'justonce'; 57 58# Create an event on the slave and check to see what the originator is. 59--echo "Dropping event test.slave_once on the slave" 60--disable_warnings 61DROP EVENT IF EXISTS test.slave_once; 62--enable_warnings 63 64# Create an event on slave and check its state. An event shouldn't be executed 65# so set start time in 1 hour. 66CREATE EVENT test.slave_once ON SCHEDULE EVERY 5 MINUTE STARTS CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO 67 INSERT IGNORE INTO t1(id, c) VALUES (3, 'from slave_once'); 68 69--echo "Checking event status on the slave for originator value = slave's server_id" 70SELECT db, name, status, originator FROM mysql.event WHERE db = 'test' AND name = 'slave_once'; 71 72--echo "Dropping event test.slave_once on the slave" 73--disable_warnings 74DROP EVENT IF EXISTS test.slave_once; 75--enable_warnings 76 77connection master; 78 79# BUG#20384 - disable events on slave 80--echo "Dropping event test.justonce on the master" 81--disable_warnings 82DROP EVENT IF EXISTS test.justonce; 83--enable_warnings 84 85# Create an event on master and check its state on slave. An event shouldn't be executed 86# so set start time in 1 hour. Check that changes of event statement replicated to slave 87 88--echo "Creating event test.er on the master" 89CREATE EVENT test.er ON SCHEDULE EVERY 3 SECOND STARTS CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO 90 INSERT IGNORE INTO t1(id, c) VALUES (4, 'from er'); 91 92--echo "Checking event status on the master" 93SELECT db, name, status, originator, body FROM mysql.event WHERE db = 'test' AND name = 'er'; 94 95sync_slave_with_master; 96 97--echo "Checking event status on the slave" 98SELECT db, name, status, originator, body FROM mysql.event WHERE db = 'test' AND name = 'er'; 99 100connection master; 101--echo "Altering event test.er on the master" 102ALTER EVENT test.er ON SCHEDULE EVERY 5 SECOND STARTS CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO 103 INSERT IGNORE INTO t1(id, c) VALUES (5, 'from alter er'); 104 105--echo "Checking event status on the master" 106SELECT db, name, status, originator, body FROM mysql.event WHERE db = 'test' AND name = 'er'; 107 108sync_slave_with_master; 109 110--echo "Checking event status on the slave" 111SELECT db, name, status, originator, body FROM mysql.event WHERE db = 'test' AND name = 'er'; 112 113connection master; 114--echo "Dropping event test.er on the master" 115DROP EVENT test.er; 116 117--echo "Checking event status on the master" 118SELECT db, name, status, originator FROM mysql.event WHERE db = 'test'; 119 120--disable_info 121 122sync_slave_with_master; 123 124--echo "Checking event status on the slave" 125SELECT db, name, status, originator FROM mysql.event WHERE db = 'test'; 126 127# test the DISABLE ON SLAVE for setting event SLAVESIDE_DISABLED as status 128# on CREATE EVENT 129 130# Create an event on slave and check its status. An event shouldn't be executed 131# so set start time in 1 hour. 132 133--echo "Creating event test.slave_terminate on the slave" 134CREATE EVENT test.slave_terminate ON SCHEDULE EVERY 3 SECOND STARTS CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO 135 INSERT IGNORE INTO t1(id, c) VALUES (6, 'from slave_terminate'); 136 137--echo "Checking event status on the slave" 138SELECT db, name, status, originator FROM mysql.event WHERE db = 'test' AND name = 'slave_terminate'; 139 140--echo "Dropping event test.slave_terminate on the slave" 141DROP EVENT test.slave_terminate; 142 143--echo "Creating event test.slave_terminate with DISABLE ON SLAVE on the slave" 144CREATE EVENT test.slave_terminate ON SCHEDULE EVERY 3 SECOND DISABLE ON SLAVE DO 145 INSERT IGNORE INTO t1(c) VALUES (7, 'from slave_terminate'); 146 147--echo "Checking event status on the slave" 148SELECT db, name, status, originator FROM mysql.event WHERE db = 'test' AND name = 'slave_terminate'; 149 150--echo "Dropping event test.slave_terminate on the slave" 151DROP EVENT test.slave_terminate; 152 153--echo "Cleanup" 154 155connection master; 156DROP TABLE t1; 157sync_slave_with_master; 158connection master; 159 160# 161# Bug #28953 Using events in a replication let the slave crash. 162# 163 164CREATE TABLE t28953 (a INT); 165 166DELIMITER |; 167CREATE EVENT event1 ON SCHEDULE EVERY 1 YEAR 168DO BEGIN 169 select * from t28953; 170END;| 171DELIMITER ;| 172 173ALTER EVENT event1 RENAME TO event2; 174 175sync_slave_with_master; 176 177connection master; 178 179DROP EVENT event2; 180 181# 182# BUG#44331 183# This test verifies if the definer is consistent between master and slave, 184# when the event is created without the DEFINER clause set explicitly or the 185# DEFINER is set to CURRENT_USER 186# 187CREATE TABLE test.t1(details CHAR(30)); 188 189CREATE EVENT /*!50000 event44331_1 */ 190 ON SCHEDULE AT CURRENT_TIMESTAMP 191 ON COMPLETION PRESERVE DISABLE 192 DO INSERT INTO test.t1 VALUES('event event44331_1 fired - no definer'); 193 194CREATE DEFINER=CURRENT_USER /*!50000 EVENT event44331_2 */ 195 ON SCHEDULE AT CURRENT_TIMESTAMP 196 ON COMPLETION PRESERVE DISABLE 197 DO INSERT INTO test.t1 VALUES('event event44331_2 fired - DEFINER=CURRENT_USER'); 198 199CREATE DEFINER=CURRENT_USER() EVENT event44331_3 200 ON SCHEDULE AT CURRENT_TIMESTAMP 201 ON COMPLETION PRESERVE DISABLE 202 DO INSERT INTO test.t1 VALUES('event event44331_3 fired - DEFINER=CURRENT_USER() function'); 203 204DELIMITER |; 205CREATE /*!50000 DEFINER='user44331' */ EVENT event44331_4 206 ON SCHEDULE AT CURRENT_TIMESTAMP 207 ON COMPLETION PRESERVE DISABLE 208 DO INSERT INTO test.t1 VALUES('event event44331_4 fired - DEFINER=user1'); 209# Test for bug#50095 Multi-statement including CREATE EVENT causes rotten 210# binlog entry 211 SELECT 'ABC'; 212 SELECT '123'| 213DELIMITER ;| 214 215select EVENT_SCHEMA, EVENT_NAME, DEFINER from information_schema.events 216 where EVENT_NAME='event44331_1'; 217select EVENT_SCHEMA, EVENT_NAME, DEFINER from information_schema.events 218 where EVENT_NAME='event44331_2'; 219select EVENT_SCHEMA, EVENT_NAME, DEFINER from information_schema.events 220 where EVENT_NAME='event44331_3'; 221select EVENT_SCHEMA, EVENT_NAME, DEFINER from information_schema.events 222 where EVENT_NAME='event44331_4'; 223 224sync_slave_with_master; 225connection slave; 226select EVENT_SCHEMA, EVENT_NAME, DEFINER from information_schema.events 227 where EVENT_NAME='event44331_1'; 228select EVENT_SCHEMA, EVENT_NAME, DEFINER from information_schema.events 229 where EVENT_NAME='event44331_2'; 230select EVENT_SCHEMA, EVENT_NAME, DEFINER from information_schema.events 231 where EVENT_NAME='event44331_3'; 232select EVENT_SCHEMA, EVENT_NAME, DEFINER from information_schema.events 233 where EVENT_NAME='event44331_4'; 234 235connection master; 236SET @@global.event_scheduler= @old_event_scheduler; 237DROP TABLE t28953; 238DROP TABLE t1; 239DROP EVENT event44331_1; 240DROP EVENT event44331_2; 241DROP EVENT event44331_3; 242DROP EVENT event44331_4; 243sync_slave_with_master; 244--source include/rpl_end.inc 245