MySQL - Event Scheduler

MySQL Events are tasks that run according to a schedule. Therefore, we sometimes refer to them as scheduled events. Basically an event is a named database object containing one or more SQL statements to be executed at one or more regular intervals, beginning and ending at a specific date and time. Conceptually, this is similar to the idea of the Unix crontab (also known as a “cron job”) or the Windows Task Scheduler.
Here is the summary of Event object features:
- An event is uniquely identified by its name and the schema to which it is assigned
- An event performs a specific action according to a schedule. This action consists of an SQL statement, which can be a compound statement in BEGIN .. END;
- An event's timing can be either one-time or recurrent. A one-time event executes one time only. A recurrent event repeats its action at a regular interval.
For further details, visit :http://dev.mysql.com/doc/refman/5.1/en/events-overview.html
Let's create an Event object to increment 'counter' column of event_test table every minute:

mysql> CREATE EVENT my_event
ON SCHEDULE EVERY 1 MINUTE
DO UPDATE event_test set counter=counter+1;
Query OK, 0 rows affected (0.00 sec)

Metadata about events can be obtained as follows:

mysql> SHOW CREATE EVENT my_event\G
*************************** 1. row ***************************
               Event: my_event
            sql_mode:
           time_zone: SYSTEM
        Create Event: CREATE EVENT `my_event` 
ON SCHEDULE EVERY 1 MINUTE STARTS '2012-03-06 16:46:01' 
ON COMPLETION NOT PRESERVE ENABLE 
DO update event_test set counter=counter+1
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

Since this is a repeating event and may not terminate within its scheduling interval, the result may be multiple instances of the event executing simultaneously. Which is undesirable,  use GET_LOCK() function or LOCK TABLE to avoid this.

Check if the 'my_event' has actually worked

mysql> SELECT SLEEP(60); SELECT * FROM event_test;
+-----------+
| SLEEP(60) |
+-----------+
|         0 |
+-----------+
1 row in set (1 min 0.01 sec)

+---------+
| counter |
+---------+
|       0 |
+---------+
1 row in set (0.00 sec)

No it has not worked, because  event scheduler thread is not ON. The global event_scheduler system variable determines whether the Event Scheduler is enabled and running on the server.

mysql> select @@GLOBAL.event_scheduler ;
+--------------------------+
| @@GLOBAL.event_scheduler |
+--------------------------+
| OFF                      |
+--------------------------+
1 row in set (0.00 sec)

To enable Event Scheduler use this sql:

mysql> set GLOBAL event_scheduler =ON;
Query OK, 0 rows affected (0.00 sec)

For more information about event scheduler, see http://dev.mysql.com/doc/refman/5.1/en/events-configuration.html
When the Event Scheduler is ON, the event scheduler thread is listed in the output of SHOW PROCESSLIST as a daemon process, and its state is represented as shown here:

mysql> SHOW PROCESSLIST;

| 43 | event_scheduler | localhost | NULL | Daemon  |   35 |
 Waiting for next activation | NULL             |
+----+-----------------+-----------+------+---------+------+

Lets check the status of 'counter' after truning event Scheduler ON

mysql> SELECT SLEEP(60); SELECT * FROM event_test;
+-----------+
| SLEEP(60) |
+-----------+
|         0 |
+-----------+
1 row in set (1 min 0.00 sec)

+---------+
| counter |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

mysql> SELECT SLEEP(60); SELECT * FROM event_test;
+-----------+
| SLEEP(60) |
+-----------+
|         0 |
+-----------+
1 row in set (1 min 0.01 sec)

+---------+
| counter |
+---------+
|       2 |
+---------+
1 row in set (0.00 sec)

To disable my_event, use this ALTER EVENT statement:

ALTER EVENT my_event
    DISABLE;

It was just a simple example to show,

that how to automate database task inside MySQL server.