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