If you want an action to happen at a specific time or at regular intervals, events in MySql is the way to go. Triggers are only good as a reaction to something that happens to the tables in your database, eg a new column added. In my case, I needed to clear off session data and I decided to create an event as follows:
CREATE EVENT SessionRemover ON SCHEDULE EVERY 5 MINUTE DO DELETE FROM Session WHERE Expires < NOW();
The statement failed for me because I did not have the event privilege. So I had to grant that to the user I was using for connecting to the database:
GRANT EVENT ON *.* TO someone@"%" IDENTIFIED BY ‘password’ WITH GRANT OPTION;
The event was created but it never ran. When I investigated the problem, it turned out that the event scheduler was not running. So I had to run this sql as well:
SET GLOBAL event_scheduler = ON;
Everything worked as expected until the server got restarted and my session count got up to 8000!!! I checked whether the event scheduler was running with this sql:
SHOW PROCESSLIST
But I did not find a user with the name ‘event_scheduler’ in the list. To make the event scheduler run event when the server restarts, you have to add it to the configuration file. I’m using Windows 2008 and I had to add the following line to ini file located at YourMySqlInstallationDir/MySql Server 5.1/my.ini
event_scheduler=ON
I couldn’t find any entry for this statement, so I added it to the end of the my.ini file.