include/master-slave.inc
Warnings:
Note	####	Sending passwords in plain text without SSL/TLS is extremely insecure.
Note	####	Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
[connection master]
SET @old_event_scheduler = @@global.event_scheduler;
set global event_scheduler=1;
DROP EVENT IF EXISTS test.justonce;
drop table if exists t1,t2;
CREATE TABLE `t1` (
`id` INT(10) UNSIGNED NOT NULL,
`c` VARCHAR(50) NOT NULL,
`ts` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO t1 (id, c) VALUES (1, 'manually');
"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');
"Checking event is active on master"
SELECT db, name, status, originator FROM mysql.event WHERE db = 'test' AND name = 'justonce';
db	name	status	originator
test	justonce	ENABLED	1
"Checking event data on the master"
ONE
1
"Checking event data on the slave"
ZERO
0
"Checking event is inactive on slave"
SELECT db, name, status, originator FROM mysql.event WHERE db = 'test' AND name = 'justonce';
db	name	status	originator
test	justonce	SLAVESIDE_DISABLED	1
"Dropping event test.slave_once on the slave"
DROP EVENT IF EXISTS test.slave_once;
CREATE EVENT test.slave_once ON SCHEDULE EVERY 5 MINUTE STARTS CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO 
INSERT IGNORE INTO t1(id, c) VALUES (3, 'from slave_once');
"Checking event status on the slave for originator value = slave's server_id"
SELECT db, name, status, originator FROM mysql.event WHERE db = 'test' AND name = 'slave_once';
db	name	status	originator
test	slave_once	ENABLED	2
"Dropping event test.slave_once on the slave"
DROP EVENT IF EXISTS test.slave_once;
"Dropping event test.justonce on the master"
DROP EVENT IF EXISTS test.justonce;
"Creating event test.er on the master"
CREATE EVENT test.er ON SCHEDULE EVERY 3 SECOND STARTS CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO 
INSERT IGNORE INTO t1(id, c) VALUES (4, 'from er');
"Checking event status on the master"
SELECT db, name, status, originator, body FROM mysql.event WHERE db = 'test' AND name = 'er';
db	name	status	originator	body
test	er	ENABLED	1	INSERT IGNORE INTO t1(id, c) VALUES (4, 'from er')
"Checking event status on the slave"
SELECT db, name, status, originator, body FROM mysql.event WHERE db = 'test' AND name = 'er';
db	name	status	originator	body
test	er	SLAVESIDE_DISABLED	1	INSERT IGNORE INTO t1(id, c) VALUES (4, 'from er')
"Altering event test.er on the master"
ALTER EVENT test.er ON SCHEDULE EVERY 5 SECOND STARTS CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO 
INSERT IGNORE INTO t1(id, c) VALUES (5, 'from alter er');
"Checking event status on the master"
SELECT db, name, status, originator, body FROM mysql.event WHERE db = 'test' AND name = 'er';
db	name	status	originator	body
test	er	ENABLED	1	INSERT IGNORE INTO t1(id, c) VALUES (5, 'from alter er')
"Checking event status on the slave"
SELECT db, name, status, originator, body FROM mysql.event WHERE db = 'test' AND name = 'er';
db	name	status	originator	body
test	er	SLAVESIDE_DISABLED	1	INSERT IGNORE INTO t1(id, c) VALUES (5, 'from alter er')
"Dropping event test.er on the master"
DROP EVENT test.er;
"Checking event status on the master"
SELECT db, name, status, originator FROM mysql.event WHERE db = 'test';
db	name	status	originator
"Checking event status on the slave"
SELECT db, name, status, originator FROM mysql.event WHERE db = 'test';
db	name	status	originator
"Creating event test.slave_terminate on the slave"
CREATE EVENT test.slave_terminate ON SCHEDULE EVERY 3 SECOND STARTS CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO 
INSERT IGNORE INTO t1(id, c) VALUES (6, 'from slave_terminate');
"Checking event status on the slave"
SELECT db, name, status, originator FROM mysql.event WHERE db = 'test' AND name = 'slave_terminate';
db	name	status	originator
test	slave_terminate	ENABLED	2
"Dropping event test.slave_terminate on the slave"
DROP EVENT test.slave_terminate;
"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');
"Checking event status on the slave"
SELECT db, name, status, originator FROM mysql.event WHERE db = 'test' AND name = 'slave_terminate';
db	name	status	originator
test	slave_terminate	SLAVESIDE_DISABLED	2
"Dropping event test.slave_terminate on the slave"
DROP EVENT test.slave_terminate;
"Cleanup"
DROP TABLE t1;
CREATE TABLE t28953 (a INT);
CREATE EVENT event1 ON SCHEDULE EVERY 1 YEAR
DO BEGIN
select * from t28953;
END;|
ALTER EVENT event1 RENAME TO event2;
include/sync_slave_sql_with_master.inc
DROP EVENT event2;
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');
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'|
ABC
ABC
123
123
#on master 
select EVENT_SCHEMA, EVENT_NAME, DEFINER from information_schema.events
where EVENT_NAME='event44331_1';
EVENT_SCHEMA	EVENT_NAME	DEFINER
test	event44331_1	root@localhost
select EVENT_SCHEMA, EVENT_NAME, DEFINER from information_schema.events
where EVENT_NAME='event44331_2';
EVENT_SCHEMA	EVENT_NAME	DEFINER
test	event44331_2	root@localhost
select EVENT_SCHEMA, EVENT_NAME, DEFINER from information_schema.events
where EVENT_NAME='event44331_3';
EVENT_SCHEMA	EVENT_NAME	DEFINER
test	event44331_3	root@localhost
select EVENT_SCHEMA, EVENT_NAME, DEFINER from information_schema.events
where EVENT_NAME='event44331_4';
EVENT_SCHEMA	EVENT_NAME	DEFINER
test	event44331_4	user44331@%
include/sync_slave_sql_with_master.inc
#on slave
select EVENT_SCHEMA, EVENT_NAME, DEFINER from information_schema.events
where EVENT_NAME='event44331_1';
EVENT_SCHEMA	EVENT_NAME	DEFINER
test	event44331_1	root@localhost
select EVENT_SCHEMA, EVENT_NAME, DEFINER from information_schema.events
where EVENT_NAME='event44331_2';
EVENT_SCHEMA	EVENT_NAME	DEFINER
test	event44331_2	root@localhost
select EVENT_SCHEMA, EVENT_NAME, DEFINER from information_schema.events
where EVENT_NAME='event44331_3';
EVENT_SCHEMA	EVENT_NAME	DEFINER
test	event44331_3	root@localhost
select EVENT_SCHEMA, EVENT_NAME, DEFINER from information_schema.events
where EVENT_NAME='event44331_4';
EVENT_SCHEMA	EVENT_NAME	DEFINER
test	event44331_4	user44331@%
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;
include/sync_slave_sql_with_master.inc
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\\
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 \\
CALL proc1();
include/sync_slave_sql_with_master.inc
DROP EVENT event1;
DROP PROCEDURE proc1;
call mtr.add_suppression("Unsafe statement written to the binary log");
set @secs=100;
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 |
include/rpl_end.inc
