Recipe 16.7. Using Events to Schedule Database Actions

Problem

You want to set up a database operation that runs periodically without user intervention.

Solution

Create an event that executes according to a schedule.

Discussion

As of MySQL 5.1, one of the capabilities available to you is an event scheduler that enables you to set up database operations that run at times that you define. This section describes what you must do to use events, beginning with a simple event that writes a row to a table at regular intervals. Why bother creating such an event? One reason is that the rows serve as a log of continuous server operation, similar to the MARK line that some Unix syslogd servers write to the system log periodically so that you know they're alive.

Begin with a table to hold the mark records. It contains a TIMESTAMP column (which MySQL will initialize automatically) and a column to store a message:

mysql> CREATE TABLE mark_log (ts TIMESTAMP, message VARCHAR(100));             

Our logging event will write a string to a new row. To set it up, use a CREATE EVENT statement:

mysql> CREATE EVENT mark_insert     -> ON SCHEDULE EVERY 5 MINUTE     -> DO INSERT INTO mark_log (message) VALUES('-- MARK --');             

The mark_insert event causes the message '-- MARK --' to be logged to the mark_log table every five minutes. Use a different interval for more or less frequent logging.

This event is simple and its body contains only a single SQL statement. For an event body that needs to execute multiple statements, use BEGIN ... END compound-statement syntax. In that case, if you use mysql to create the event, you need to change the statement delimiter while you're defining the event, as discussed in Section 16.1.

At this point, you should wait a few minutes and then select the contents of the mark_log table to verify that new rows are being written on schedule. However, if this is the first event that you've set up, you might find that the table remains empty no matter how long you wait:

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

If that's the case, very likely the event scheduler isn't running (which is its default state until you enable it). Check the scheduler status by examining the value of the event_scheduler system variable:

mysql> SHOW VARIABLES LIKE 'event_scheduler'; +-----------------+-------+ | Variable_name   | Value | +-----------------+-------+ | event_scheduler | 0     | +-----------------+-------+ 

To enable the scheduler interactively if it is not running, execute the following statement (which requires the SUPER privilege):

mysql> SET GLOBAL event_scheduler = 1;             

That statement enables the scheduler, but only until the server shuts down. To make sure that the scheduler runs each time the server starts, set the system variable to 1 in your my.cnf option file:

[mysqld] event_scheduler=1 

When the event scheduler is enabled, the mark_insert event eventually will create many rows in the table. There are several ways that you can affect event execution to prevent the table from growing forever:

• Drop the event:

  mysql> DROP EVENT mark_insert;                   

  
  

  This is the simplest way to stop an event from occurring. But if you want it to resume later, you must re-create it.

• Suspend execution for the event:

  mysql> ALTER EVENT mark_insert DISABLE;                   

  
  

  Disabling an event leaves it in place but causes it not to run until you reactivate it:

  mysql> ALTER EVENT mark_insert ENABLE;                   

  
  

• Let the event continue to run, but set up another event that "expires" old mark_log rows. This second event need not run so frequently (perhaps once a day). Its body should contain a DELETE statement that removes rows older than a given threshold. The following definition creates an event that deletes rows that are more than two days old:

  mysql> CREATE EVENT mark_expire     -> ON SCHEDULE EVERY 1 DAY     -> DO DELETE FROM mark_log WHERE ts < NOW() - INTERVAL 2 DAY;                   

  
  

  If you adopt this strategy, you have cooperating events, such that one event adds rows to the mark_log table and the other removes them. They act together to maintain a log that contains recent records but does not become too large.

To check on event activity, look in the server's error log, where it records information about which events it executes and when.