MySQL events

The journey "Fall in love with MySQL (again)" continues from here (triggers) and here (views). Next stop - MySQL events.

A cold shower first - events are not available in versions < 5.1.6. so chances of using them any time soon are pretty slim. You need to upgrade to 5.1. (beta) if you want to play around with them.

OK, so what are the events. Just like cronjobs. Like triggers but instead of firing when something happens with one of the tables, they fire on timely basis. They can be executed only once, on a specific date or they can occur in some defined interval. You have flexibility of setting start date and end date too. A quick example.

To test the events I'll create a table called `log` that has only one field - a timestamp. Then I'll setup an event to log into this table every time it fires.

mysql> CREATE TABLE log (
    -> eventtime DATETIME
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> select NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2006-12-13 22:47:12 |
+---------------------+
1 row in set (0.00 sec)

Let's create the event.

mysql> CREATE EVENT testevent
    -> ON SCHEDULE EVERY 1 MINUTE
    -> STARTS '2006-12-13 22:48:00'
    -> DO
    -> INSERT INTO `log` VALUES (NOW());
Query OK, 0 rows affected (0.00 sec)

To list events, I need SHOW EVENTS statement.

mysql> SHOW EVENTS\G;
*************************** 1. row ***************************
            Db: blog
          Name: testevent
       Definer: root@localhost
          Type: RECURRING
    Execute at: NULL
Interval value: 1
Interval field: MINUTE
        Starts: 2006-12-13 22:48:00
          Ends: NULL
        Status: ENABLED
1 row in set (0.00 sec)

You can see what happened - I set a start date/time for the event, then set an interval 1 minute and finally gave it something to do when the event happens - an INSERT statement.

Let's see if it works.

mysql> SELECT * FROM log;
Empty set (0.00 sec)

Nothing? Hmm, let's try again.

mysql> SELECT * FROM log;
Empty set (0.00 sec)

WTF!? What time is it?

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2006-12-13 22:49:20 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM log;
Empty set (0.00 sec)

Still nothing, although a minute has passed. Ahaaa! I need to enable these events in MySQL! That's as easy as setting a variable.

mysql> SET GLOBAL event_scheduler = 'ON';
Query OK, 0 rows affected (0.01 sec)

Good, now let's see.

mysql> SELECT * FROM log;
+---------------------+
| eventtime           |
+---------------------+
| 2006-12-13 22:50:00 |
+---------------------+
1 row in set (0.00 sec)

Beauty! The event was fired!

Trying again in a few minutes:

mysql> SELECT * FROM log;
+---------------------+
| eventtime           |
+---------------------+
| 2006-12-13 22:50:00 |
| 2006-12-13 22:51:00 |
| 2006-12-13 22:52:00 |
+---------------------+
3 rows in set (0.00 sec)

Great. Let's drop it for now and figure out something more interesting to do with the events (in a follow-up post :D).

mysql> DROP EVENT testevent;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW EVENTS;
Empty set (0.01 sec)

Tell your friends about this post on Facebook and Twitter