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