Event scheduler in MySQL 5.1

I just recently used Event scheduler which was the major feature in MySQL 5.1 version. Its very much similar to the linux crontab functionality. MySQL Event is nothing but a bunch of statements which we can run on specific schedule. When you will create any event, its just a database object like table, view or stored procedure.You can also say that its like a trigger but trigger will be fired/executed on specific action and event will be fired/executed on specific time.

Some major properties of MySQL Event Scheduler:

  • An event is uniquely identified by its name and the schema to which it is assigned.
  • An event’s timing will be either one time or recurrent. A one-time event will execute one time only. A recurrent event will repeat its action on a regular interval. Schedule for a recurring event will be assigned a specific start day, time, end day, time, both, or neither.
  • Event properties can be change by “ALTER EVENT” or update the mysql.event table. Here also, DEFINER and INVOKER are comes in the picture. Only DEFINER can change the properties.
  • One important thing is there. “A user may include statements in an event’s action which require privileges that the user does not actually have“. I’m bit confused over here that is it the feature or security breach?

For enabling this feature, you have to set global parameter named “event_scheduler”. There are 3 modes to set this variable.  0 for OFF, 1 for ON and 2 for SUSPENDED. When the event_scheduler will be set to 2, it will be in suspended mode so in this case thread will be running on the server but it will not be able to run any event.  MySQL will also create a separate thread for event scheduler. So, when you’ll enable it you will get extra thread as a daemon process in processlist.

To run the simple statement you can create even like this.

CREATE EVENT event_hourly
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
COMMENT ‘Clears out sessions table each hour.’
DO
DELETE FROM site_activity.sessions;

To run complex queries you can create event like this.

delimiter |

CREATE EVENT event_daily
ON SCHEDULE EVERY 1 DAY
COMMENT ‘Saves number of queries which are running each hour’
DO
BEGIN
INSERT INTO site_activity.queries(time, total) SELECT CURRENT_TIMESTAMP, COUNT(*) FROM information_schema.processlist;
END |

delimiter ;

You can also run any procedure by creating event on specific time like this.

CREATE EVENT event_call_calc_expense
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 30 DAY
DO
CALL calc_expense(‘january’);

You can get more details about events over here.

Reference: MySQL 5.1 Documentation

2 thoughts on “Event scheduler in MySQL 5.1”

  1. Sir Please tell me that how to send email on a particular date which will be match ?
    my problem:-
    I have 500 client so i want to send email when they have near to expiry date of their domain .
    please reply me….

    Atul Kumar Sharma
    Programmer(php)

Leave a Reply

Your email address will not be published. Required fields are marked *