paolo@bimodesign.com | +34 608 61 64 10

DB Relational

        

MySQL and Cron DB (Event Scheduler)

Beginnig of MySQL 5.1, it availables the event feature, that allow to schedule (like a Unix cron) task that you want to perform on your database.
Which are the advantages?
The most important advantages are that you'll write directly on MySQL Server and that it's platform indipendent. Otherwise, you could check the errors in a log file.
There are several options to define a MySQL Events. Here you can find the official manual where all the options.
I'll show in this post the most important option.
This is a basic example that executes every five minutes the procedure called change_state_contest.

CREATE EVENT IF NOT EXISTS event_name
    ON SCHEDULE
      EVERY 5 MINUTE
    COMMENT 'evento de prueba'
    DO
      CALL change_state_contest();    

The scheduling can be of three types:
- AT + timestamp : The event starts at executes one time only at the date and time given by timestamp
- EVERY + interval + SECOND/MINUTE/HOUR/DAY/WEEK/ etc...: The events starts every interval + type interval (for example 5 minutes) and repeat actions at a regular interval.
- START ... END : The event will repeat action at a regular interval until the end date. Attention! If you don't define the end date, the event will repeat indefinitely.

In the DO section you could put the SQL statment directly or call a procedure. It's depend to the complex of action.
For example to simply delete a table, you can write

    CREATE EVENT e_hourly
    ON SCHEDULE
      EVERY 1 HOUR
    COMMENT 'Clears out sessions table each hour.'
    DO
      DELETE FROM site_activity.sessions;

or for example

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    

Those are some advertisement to manage the MySQL events:
1. The global event_scheduler needs to be active. Show that parameter by this sql command

    SET GLOBAL event_scheduler=ON; 

2. To drop an event

    drop event event_name;

3. To show the details events:
show event event_name;

+----------+------------------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| Db       | Name                   | Definer        | Time zone | Type      | Execute at | Interval value | Interval field | Starts              | Ends | Status  | Originator | character_set_client | collation_connection | Database Collation |
+----------+------------------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| concurso | cambio_estado_concurso | root@localhost | SYSTEM    | RECURRING | NULL       | 5              | MINUTE         | 2015-02-26 15:20:01 | NULL | ENABLED |          0 | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
+----------+------------------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+