Mysql Database Event and Procedure(1)MySQL Event

Mysql Database Event and Procedure(1)MySQL Event

Check Event Switch
>show variables like '%scheduler%';
event_scheduler OFF

or

event_scheduler ON

Start the event
>SET GLOBAL event_scheduler = 1;

Need to set my.cnf as follow, otherwise, after reboot the mysql database, the settings will be gone.
event_scheduler=ON

Event Grammer - Create Event
Call Procedure Every 9 days
CREAT EVENT EVENT1

ON SCHEDULE EVERY 9 DAY STARTS NOW()
ON COMPLETION PRESERVE ENABLE
DO
BEGIN
CALL TOTAL();
END

Grammer
CREATE
EVENT
[IF NOT EXISTS]
event_name
ON SCHEDULE scheudle
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT ‘comment']
DO event_body;

schedule grammar:
AT timestamp [+INTERVAL interval] ..
| EVERY interval
[STARTS timestamp [+ INTERVAL interval] … ]
[ENDS timestamp [+ INTERVAL interval] … ]

interval grammar:
quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE | WEEK
| SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE | DAY_SECOND
| HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

ON COMPLETION PRESERVE execute many times forever

Event Grammar - Change the Event
ALTER
EVENT event_name
[ON SCHEDULE schedule]
[ON COMPLETION [NOT] PRESERVE]
[RENAME TO new_event_name]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT ‘comment']
[DO event_body]

Event Grammer - Delete the Event
DROP EVENT [IF EXISTS] event_name

Some Samples - Event and SQL in Body
-- check switch
show variables like '%scheduler%';

-- enable switch
SET GLOBAL event_scheduler = 1;

-- test table
CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`t1` datetime DEFAULT NULL,
`id2` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=106 DEFAULT CHARSET=utf8

-- insert 1 record to that table every 3 seconds
CREATE EVENT IF NOT EXISTS test ON SCHEDULE EVERY 3 SECOND
ON COMPLETION PRESERVE
DO INSERT INTO test(id,t1) VALUES(NULL,NOW());

-- check event
SHOW CREATE EVENT test;

-- alter the event
ALTER EVENT test ON SCHEDULE EVERY 3 SECOND
ON COMPLETION PRESERVE
DO INSERT INTO test(id,t1) VALUES(NULL,NOW());

-- clean the things after 10 minutes
CREATE EVENT IF NOT EXISTS test2
ON SCHEDULE
AT CURRENT_TIMESTAMP + INTERVAL 10 MINUTE
DO TRUNCATE TABLE test;

DROP EVENT test;

-- check event
SHOW CREATE EVENT test2;

Some Samples - Event Call Procedure
define a store procedure and call the procedure after CALL
procedure name test_add();

CREATE EVENT test ON SCHEDULE EVERY 1 DAY
STARTS ‘2016-12-01 00:00:00'
ENDS ‘2016-12-01 00:00:00’ + INTERVAL 40 DAY
ON COMPLETION PRESERVE DO
CALL test_add();

Start and Disable Event
-- disable the event
ALTER EVENT test DISABLE;

-- enable the event
ALTER EVENT test ENABLE;

References:
https://blog.tankywoo.com/2015/04/01/mysql-stored-procedure.html
http://blog.chinaunix.net/uid-20639775-id-3323098.html