| 1 |
include/master-slave.inc |
| 2 |
[connection master] |
| 3 |
SET @old_event_scheduler = @@global.event_scheduler; |
| 4 |
set global event_scheduler=1; |
| 5 |
CREATE TABLE `t1` ( |
| 6 |
`id` INT(10) UNSIGNED NOT NULL, |
| 7 |
`c` VARCHAR(50) NOT NULL, |
| 8 |
`ts` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, |
| 9 |
PRIMARY KEY (`id`) |
| 10 |
) DEFAULT CHARSET=utf8; |
| 11 |
INSERT INTO t1 (id, c) VALUES (1, 'manually'); |
| 12 |
"Creating event test.justonce on the master" |
| 13 |
CREATE EVENT test.justonce ON SCHEDULE EVERY 2 SECOND DO |
| 14 |
INSERT IGNORE INTO t1 (id, c) VALUES (2, 'from justonce'); |
| 15 |
"Checking event is active on master" |
| 16 |
SELECT db, name, status, originator FROM mysql.event WHERE db = 'test' AND name = 'justonce'; |
| 17 |
db name status originator |
| 18 |
test justonce ENABLED 1 |
| 19 |
"Checking event data on the master" |
| 20 |
ONE |
| 21 |
1 |
| 22 |
connection slave; |
| 23 |
"Checking event data on the slave" |
| 24 |
ZERO |
| 25 |
0 |
| 26 |
"Checking event is inactive on slave" |
| 27 |
SELECT db, name, status, originator FROM mysql.event WHERE db = 'test' AND name = 'justonce'; |
| 28 |
db name status originator |
| 29 |
test justonce SLAVESIDE_DISABLED 1 |
| 30 |
"Dropping event test.slave_once on the slave" |
| 31 |
DROP EVENT IF EXISTS test.slave_once; |
| 32 |
CREATE EVENT test.slave_once ON SCHEDULE EVERY 5 MINUTE STARTS CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO |
| 33 |
INSERT IGNORE INTO t1(id, c) VALUES (3, 'from slave_once'); |
| 34 |
Warnings: |
| 35 |
Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. |
| 36 |
"Checking event status on the slave for originator value = slave's server_id" |
| 37 |
SELECT db, name, status, originator FROM mysql.event WHERE db = 'test' AND name = 'slave_once'; |
| 38 |
db name status originator |
| 39 |
test slave_once ENABLED 2 |
| 40 |
"Dropping event test.slave_once on the slave" |
| 41 |
DROP EVENT IF EXISTS test.slave_once; |
| 42 |
connection master; |
| 43 |
"Dropping event test.justonce on the master" |
| 44 |
DROP EVENT IF EXISTS test.justonce; |
| 45 |
"Creating event test.er on the master" |
| 46 |
CREATE EVENT test.er ON SCHEDULE EVERY 3 SECOND STARTS CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO |
| 47 |
INSERT IGNORE INTO t1(id, c) VALUES (4, 'from er'); |
| 48 |
"Checking event status on the master" |
| 49 |
SELECT db, name, status, originator, body FROM mysql.event WHERE db = 'test' AND name = 'er'; |
| 50 |
db name status originator body |
| 51 |
test er ENABLED 1 INSERT IGNORE INTO t1(id, c) VALUES (4, 'from er') |
| 52 |
connection slave; |
| 53 |
"Checking event status on the slave" |
| 54 |
SELECT db, name, status, originator, body FROM mysql.event WHERE db = 'test' AND name = 'er'; |
| 55 |
db name status originator body |
| 56 |
test er SLAVESIDE_DISABLED 1 INSERT IGNORE INTO t1(id, c) VALUES (4, 'from er') |
| 57 |
connection master; |
| 58 |
"Altering event test.er on the master" |
| 59 |
ALTER EVENT test.er ON SCHEDULE EVERY 5 SECOND STARTS CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO |
| 60 |
INSERT IGNORE INTO t1(id, c) VALUES (5, 'from alter er'); |
| 61 |
"Checking event status on the master" |
| 62 |
SELECT db, name, status, originator, body FROM mysql.event WHERE db = 'test' AND name = 'er'; |
| 63 |
db name status originator body |
| 64 |
test er ENABLED 1 INSERT IGNORE INTO t1(id, c) VALUES (5, 'from alter er') |
| 65 |
connection slave; |
| 66 |
"Checking event status on the slave" |
| 67 |
SELECT db, name, status, originator, body FROM mysql.event WHERE db = 'test' AND name = 'er'; |
| 68 |
db name status originator body |
| 69 |
test er SLAVESIDE_DISABLED 1 INSERT IGNORE INTO t1(id, c) VALUES (5, 'from alter er') |
| 70 |
connection master; |
| 71 |
"Dropping event test.er on the master" |
| 72 |
DROP EVENT test.er; |
| 73 |
"Checking event status on the master" |
| 74 |
SELECT db, name, status, originator FROM mysql.event WHERE db = 'test'; |
| 75 |
db name status originator |
| 76 |
connection slave; |
| 77 |
"Checking event status on the slave" |
| 78 |
SELECT db, name, status, originator FROM mysql.event WHERE db = 'test'; |
| 79 |
db name status originator |
| 80 |
"Creating event test.slave_terminate on the slave" |
| 81 |
CREATE EVENT test.slave_terminate ON SCHEDULE EVERY 3 SECOND STARTS CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO |
| 82 |
INSERT IGNORE INTO t1(id, c) VALUES (6, 'from slave_terminate'); |
| 83 |
Warnings: |
| 84 |
Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. |
| 85 |
"Checking event status on the slave" |
| 86 |
SELECT db, name, status, originator FROM mysql.event WHERE db = 'test' AND name = 'slave_terminate'; |
| 87 |
db name status originator |
| 88 |
test slave_terminate ENABLED 2 |
| 89 |
"Dropping event test.slave_terminate on the slave" |
| 90 |
DROP EVENT test.slave_terminate; |
| 91 |
"Creating event test.slave_terminate with DISABLE ON SLAVE on the slave" |
| 92 |
CREATE EVENT test.slave_terminate ON SCHEDULE EVERY 3 SECOND DISABLE ON SLAVE DO |
| 93 |
INSERT IGNORE INTO t1(c) VALUES (7, 'from slave_terminate'); |
| 94 |
Warnings: |
| 95 |
Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. |
| 96 |
"Checking event status on the slave" |
| 97 |
SELECT db, name, status, originator FROM mysql.event WHERE db = 'test' AND name = 'slave_terminate'; |
| 98 |
db name status originator |
| 99 |
test slave_terminate SLAVESIDE_DISABLED 2 |
| 100 |
"Dropping event test.slave_terminate on the slave" |
| 101 |
DROP EVENT test.slave_terminate; |
| 102 |
"Cleanup" |
| 103 |
connection master; |
| 104 |
DROP TABLE t1; |
| 105 |
connection slave; |
| 106 |
connection master; |
| 107 |
CREATE TABLE t28953 (a INT); |
| 108 |
CREATE EVENT event1 ON SCHEDULE EVERY 1 YEAR |
| 109 |
DO BEGIN |
| 110 |
select * from t28953; |
| 111 |
END;| |
| 112 |
ALTER EVENT event1 RENAME TO event2; |
| 113 |
connection slave; |
| 114 |
connection master; |
| 115 |
DROP EVENT event2; |
| 116 |
CREATE TABLE test.t1(details CHAR(30)); |
| 117 |
CREATE EVENT /*!50000 event44331_1 */ |
| 118 |
ON SCHEDULE AT CURRENT_TIMESTAMP |
| 119 |
ON COMPLETION PRESERVE DISABLE |
| 120 |
DO INSERT INTO test.t1 VALUES('event event44331_1 fired - no definer'); |
| 121 |
CREATE DEFINER=CURRENT_USER /*!50000 EVENT event44331_2 */ |
| 122 |
ON SCHEDULE AT CURRENT_TIMESTAMP |
| 123 |
ON COMPLETION PRESERVE DISABLE |
| 124 |
DO INSERT INTO test.t1 VALUES('event event44331_2 fired - DEFINER=CURRENT_USER'); |
| 125 |
CREATE DEFINER=CURRENT_USER() EVENT event44331_3 |
| 126 |
ON SCHEDULE AT CURRENT_TIMESTAMP |
| 127 |
ON COMPLETION PRESERVE DISABLE |
| 128 |
DO INSERT INTO test.t1 VALUES('event event44331_3 fired - DEFINER=CURRENT_USER() function'); |
| 129 |
CREATE /*!50000 DEFINER='user44331' */ EVENT event44331_4 |
| 130 |
ON SCHEDULE AT CURRENT_TIMESTAMP |
| 131 |
ON COMPLETION PRESERVE DISABLE |
| 132 |
DO INSERT INTO test.t1 VALUES('event event44331_4 fired - DEFINER=user1'); |
| 133 |
# Test for bug#50095 Multi-statement including CREATE EVENT causes rotten |
| 134 |
# binlog entry |
| 135 |
SELECT 'ABC'; |
| 136 |
SELECT '123'| |
| 137 |
ABC |
| 138 |
ABC |
| 139 |
123 |
| 140 |
123 |
| 141 |
select EVENT_SCHEMA, EVENT_NAME, DEFINER from information_schema.events |
| 142 |
where EVENT_NAME='event44331_1'; |
| 143 |
EVENT_SCHEMA EVENT_NAME DEFINER |
| 144 |
test event44331_1 root@localhost |
| 145 |
select EVENT_SCHEMA, EVENT_NAME, DEFINER from information_schema.events |
| 146 |
where EVENT_NAME='event44331_2'; |
| 147 |
EVENT_SCHEMA EVENT_NAME DEFINER |
| 148 |
test event44331_2 root@localhost |
| 149 |
select EVENT_SCHEMA, EVENT_NAME, DEFINER from information_schema.events |
| 150 |
where EVENT_NAME='event44331_3'; |
| 151 |
EVENT_SCHEMA EVENT_NAME DEFINER |
| 152 |
test event44331_3 root@localhost |
| 153 |
select EVENT_SCHEMA, EVENT_NAME, DEFINER from information_schema.events |
| 154 |
where EVENT_NAME='event44331_4'; |
| 155 |
EVENT_SCHEMA EVENT_NAME DEFINER |
| 156 |
test event44331_4 user44331@% |
| 157 |
connection slave; |
| 158 |
connection slave; |
| 159 |
select EVENT_SCHEMA, EVENT_NAME, DEFINER from information_schema.events |
| 160 |
where EVENT_NAME='event44331_1'; |
| 161 |
EVENT_SCHEMA EVENT_NAME DEFINER |
| 162 |
test event44331_1 root@localhost |
| 163 |
select EVENT_SCHEMA, EVENT_NAME, DEFINER from information_schema.events |
| 164 |
where EVENT_NAME='event44331_2'; |
| 165 |
EVENT_SCHEMA EVENT_NAME DEFINER |
| 166 |
test event44331_2 root@localhost |
| 167 |
select EVENT_SCHEMA, EVENT_NAME, DEFINER from information_schema.events |
| 168 |
where EVENT_NAME='event44331_3'; |
| 169 |
EVENT_SCHEMA EVENT_NAME DEFINER |
| 170 |
test event44331_3 root@localhost |
| 171 |
select EVENT_SCHEMA, EVENT_NAME, DEFINER from information_schema.events |
| 172 |
where EVENT_NAME='event44331_4'; |
| 173 |
EVENT_SCHEMA EVENT_NAME DEFINER |
| 174 |
test event44331_4 user44331@% |
| 175 |
connection master; |
| 176 |
SET @@global.event_scheduler= @old_event_scheduler; |
| 177 |
DROP TABLE t28953; |
| 178 |
DROP TABLE t1; |
| 179 |
DROP EVENT event44331_1; |
| 180 |
DROP EVENT event44331_2; |
| 181 |
DROP EVENT event44331_3; |
| 182 |
DROP EVENT event44331_4; |
| 183 |
connection slave; |
| 184 |
include/rpl_end.inc |