Mysql Events 5.6

MySQL Events are tasks that run according to a schedule. Therefore, we sometimes refer to them as scheduled events. statements to be executed at one or more regular intervals Scheduled tasks of this type are also sometimes known as “temporal triggers”,

In MySQL, an event is uniquely identified by its name and the schema to which it is assigned.

Events can be overlapping each other

If a repeating event does not terminate within its scheduling interval, the result may be multiple instances of the event executing simultaneously. If this is undesirable, you should institute a mechanism to prevent simultaneous instances. For example, you could use the GET_LOCK() function, or row or table locking.

Privilege

A user may include statements in an event's action which require privileges that the user does not actually have. The event creation or modification statement succeeds but the event's action fails.

Enable the event scheduler process

The events are executed by a special event scheduler thread; and it is very possible that event scheduler thread was not started. Check it using this query -

SELECT @@global.event_scheduler; The result should be - ON. Otherwise, execute this query to start event scheduler -

SET @@global.event_scheduler = 1;

Create/Alter/Drop Event

https://dev.mysql.com/doc/refman/5.6/en/create-event.html

DROP EVENT UPDATET; delimiter | CREATE DEFINER = CURRENTUSER EVENT IF NOT EXISTS UPDATET ON SCHEDULE EVERY 1 SECOND ON COMPLETION PRESERVE DO BEGIN DECLARE t VARCHAR(25); DECLARE i INTEGER; select currenttimestamp(2), round(rand()*100000) INTO t,i; INSERT INTO dmp.heartbeat (ts, serverid, file, position, relaymasterlogfile, execmasterlog_pos) VALUES (t,i, 'mysql-bin-changelog.099999', '111716011', NULL, NULL); END |

delimiter ;

Error messages

sudo vi /var/log/mysql/error.log

2017-06-02 16:21:16 953 [Note] Event Scheduler: [scala@localhost].[dmp.UPDATE_T] event execution failed.
2017-06-02 16:21:17 953 [ERROR] Event Scheduler: [scala@localhost][dmp.UPDATE_T] Duplicate entry '77776655' for key 'PRIMARY'

Privilege

CREATE EVENT requires the EVENT privilege for the schema in which the event is to be created.

On SCHEDULE

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

AT timestamp is used for a one-time event.

CREATE EVENT e_totals
->     ON SCHEDULE AT '2006-02-10 23:59:00'
->     DO INSERT INTO test.totals VALUES (NOW());

To repeat actions at a regular interval, use an EVERY clause.

An EVERY clause may contain an optional STARTS clause.

STARTS is followed by a timestamp value that indicates when the action should begin repeating,

EVERY 3 MONTH STARTS CURRENT_TIMESTAMP + INTERVAL 1 WEEK means “every three months, beginning one week from now”.
EVERY 2 WEEK STARTS CURRENT_TIMESTAMP + INTERVAL '6:15' HOUR_MINUTE means  “every two weeks, beginning six hours and fifteen minutes from now” 

An EVERY clause may contain an optional ENDS clause.

Preservation event after expire

Normally, once an event has expired, it is immediately dropped. You can override this behavior by specifying ON COMPLETION PRESERVE. Using ON COMPLETION NOT PRESERVE merely makes the default nonpersistent behavior explicit.

 [ON COMPLETION [NOT] PRESERVE]

Other Examples:

CREATE EVENT e_call_myproc
    ON SCHEDULE
      AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
    DO CALL myproc(5, 27);

delimiter |

CREATE EVENT e
    ON SCHEDULE
      EVERY 5 SECOND
    DO
      BEGIN
    DECLARE v INTEGER;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;

    SET v = 0;

    WHILE v < 5 DO
      INSERT INTO t1 VALUES (0);
      UPDATE t2 SET s1 = s1 + 1;
      SET v = v + 1;
    END WHILE;
    END |

delimiter ;

My osservation

An event can replace the heartbeat process.

Mysql 5.6 Events Documentations