(From Manual)
The global
event_scheduler
system variable determines whether the Event Scheduler is enabled and running on the server. Version Introduced 5.1.6
Command Line Format --event-scheduler[=value]
Config File Format event-scheduler
Option Sets Variable Yes, event_scheduler
Variable Name event_scheduler
Variable Scope Global
Dynamic Variable Yes
Permitted Values
Type enumeration
Default OFF
Valid Values ON, OFF, DISABLED
Command Line Format --event-scheduler[=value]
Config File Format event-scheduler
Option Sets Variable Yes, event_scheduler
Variable Name event_scheduler
Variable Scope Global
Dynamic Variable Yes
Permitted Values
Type enumeration
Default OFF
Valid Values ON, OFF, DISABLED
From the mysql prompt you can enable/disable using any one of the following statements
mysql>SET GLOBAL event_scheduler = ON; mysql>SET @@global.event_scheduler = ON; mysql>SET GLOBAL event_scheduler = 1; mysql>SET @@global.event_scheduler = 1;
Also, In the server configuration file (
my.cnf
, or my.ini
on Windows systems), include the line where it will be read by the server (for example, in a [mysqld]
section): event_scheduler=ON|OFF|DISABLED
So now we know that what needs to be done at the server level in order to use the feature. Okay the platform is ready for event_scheduler and can demonstrate you with a tiny example.
Just ensuring that event_scheduler is enabled or not
So now we know that what needs to be done at the server level in order to use the feature. Okay the platform is ready for event_scheduler and can demonstrate you with a tiny example.
Just ensuring that event_scheduler is enabled or not
show global variables like 'event_scheduler';
event_scheduler ON
event_scheduler ON
The result of the SQL command ensures me that "event_scheduler" is enabled now and I can use the feature(but this settings should be made permanent by adding it to config file ).
Syntax for CREATE EVENT
CREATE EVENT [ IF NOT EXISTS ] event_name
ON SCHEDULE schedule
[ ON COMPLETION [ NOT ] PRESERVE ]
[ ENABLED | DISABLED ]
[ COMMENT 'comment' ]
DO sql_statement;
In which - the "event_name" must be a valid identifier of up to 64 characters (e.g me_ushastry, me_trainer etc ushastry1978 etc), with delimiters allowed (`Something To Do`), possibly qualified (database1.event1). Events are database objects, so they are stored within a database and event names must be unique within that database. When checking for uniqueness, MySQL uses case-insensitive comparisons.
The "schedule" can be a timestamp in the future, a recurring interval, or a combination of recurring intervals and timestamps.
The possibilities are:
- AT timestamp [+ interval integer_value time_keyword ]
- EVERY interval
- EVERY interval STARTS timestamp
- EVERY interval ENDS timestamp
- EVERY interval STARTS timestamp ENDS timestamp
"AT timestamp" means "Do this once at the specified time". The timestamp must contain both date and time (that is, it must be a DATETIME or TIMESTAMP value) and must be in the future -- you cannot make an event which is supposed to have already occurred. To specify an exact time, you can also add an interval to the timestamp (using + INTERVAL, a positive integer and one of YEAR, MONTH, WEEK, DAY, HOUR, MINUTE, or SECOND); this makes sense only when you're using the CURRENT_TIMESTAMP function. Here are two examples:
Scenario is like we have a table named `tbl_users` and we have to delete the records from the table every 5 minutes and we want to automate this process so that no one has to sit and run the piece of SQL code manually.
# Checking "event_scheduler"
create database mysqlscheduler;
use mysqlscheduler;
CREATE TABLE IF NOT EXISTS `tbl_users`
(
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(100) DEFAULT NULL,
`email` varchar(100) DEFAULT NULL,
`promo_emails` enum('YES','NO') DEFAULT 'NO',
PRIMARY KEY (`id`)
) ENGINE=MyISAM;
# Add couple of records for testing
INSERT INTO mysqlscheduler.tbl_users VALUES('','Umesh','ushastry@gmail.com','NO'),('','Umesh','ushastry_duplicate@gmail.com','YES'),('','Umesh','ushastry_dummy@gmail.com','NO');
SELECT * FROM mysqlscheduler.tbl_users;
1 Umesh ushastry@gmail.com NO
2 Umesh ushastry_duplicate@gmail.com YES
3 Umesh ushastry_dummy@gmail.com NO
\N \N \N NO
Now time to create EVENT which would take care of deleting the records
CREATE EVENT drop_dummy_user
ON SCHEDULE EVERY 15 MINUTE
STARTS TIMESTAMP '2009-01-01 00:00:00'
ENDS TIMESTAMP '2009-12-31 23:59:00'
DO DELETE FROM mysqlscheduler.tbl_users WHERE promo_emails='YES';
C:\ushastry\MySQL_Com\mysql-5.1.34-win32\bin>mysql -uroot -p
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 26
Server version: 5.1.34-community MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
Your MySQL connection id is 26
Server version: 5.1.34-community MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SELECT * FROM mysql.event\G
*************************** 1. row ***************************
db: mysqlscheduler
name: drop_dummy_user
body: DELETE FROM mysqlscheduler.tbl_users WHERE promo_emails='YES'
definer: root@localhost
execute_at: NULL
interval_value: 15
interval_field: MINUTE
created: 2009-09-02 14:51:45
modified: 2009-09-02 14:51:45
last_executed: NULL
starts: 2009-01-01 00:00:00
ends: 2009-12-31 23:59:00
status: ENABLED
on_completion: DROP
sql_mode:
comment:
originator: 1
time_zone: SYSTEM
character_set_client: utf8
collation_connection: utf8_general_ci
db_collation: latin1_swedish_ci
body_utf8: DELETE FROM mysqlscheduler.tbl_users WHERE promo_emails='YES'
1 row in set (0.00 sec)
mysql>
*************************** 1. row ***************************
db: mysqlscheduler
name: drop_dummy_user
body: DELETE FROM mysqlscheduler.tbl_users WHERE promo_emails='YES'
definer: root@localhost
execute_at: NULL
interval_value: 15
interval_field: MINUTE
created: 2009-09-02 14:51:45
modified: 2009-09-02 14:51:45
last_executed: NULL
starts: 2009-01-01 00:00:00
ends: 2009-12-31 23:59:00
status: ENABLED
on_completion: DROP
sql_mode:
comment:
originator: 1
time_zone: SYSTEM
character_set_client: utf8
collation_connection: utf8_general_ci
db_collation: latin1_swedish_ci
body_utf8: DELETE FROM mysqlscheduler.tbl_users WHERE promo_emails='YES'
1 row in set (0.00 sec)
mysql>
Above command output can be read as
- the db column shows the name of the database that contains the event
- the name column holds the name of the event; recall that it must be unique within the database
- the definer column shows whose privileges will be checked when the event is executed; if the user shown no longer has the proper privileges to execute the event's SQL statement, an error will be returned
- the execute_at column shows when the event should be executed; this is always NULL unless the ON SCHEDULE clause uses the AT timestamp option
- the interval_value column shows the numeric value of how often the event will be executed; this value will be NULL if the ON SCHEDULE clause uses the AT timestamp option
- the interval_field columns shows the datetime value of how often the event will be executed; this value will be NULL if the ON SCHEDULE clause uses the AT timestamp option
- the created column shows the timestamp when the event was created
- the modified column shows the timestamp when the event was last modified; this will be the same as the created column if ALTER EVENT has never changed the event
- the last_executed column shows the timestamp of the last time the event was executed; in this example, it is NULL because the newly-created event hasn't been executed yet
- the starts column shows the timestamp when the event will start executing; this value will be NULL if the ON SCHEDULE clause uses the AT timestamp option
- the ends column shows the timestamp when the event will stop executing; this value will be NULL if the ON SCHEDULE clause uses the AT timestamp option
- the status column shows whether the event is currently enabled or disabled; in this example, it shows ENABLED, the default value
- the on_completion column shows whether the event will be preserved or dropped when it is finished; in this example, it shows that the event will be preserved, as specified in the CREATE EVENT statement
- the comment column shows the comment included in the CREATE EVENT statement
SELECT * FROM mysqlscheduler.tbl_users;
1 Umesh ushastry@gmail.com NO
3 Umesh ushastry_dummy@gmail.com NO
\N \N \N NO
Also, can be verified from the event meta data that when it was executed last time
mysql> SELECT * FROM mysql.event\G
*************************** 1. row ***************************
db: mysqlscheduler
name: drop_dummy_user
body: DELETE FROM mysqlscheduler.tbl_users WHERE promo_emails='YES'
definer: root@localhost
execute_at: NULL
interval_value: 15
interval_field: MINUTE
created: 2009-09-02 14:51:45
modified: 2009-09-02 14:51:45
last_executed: 2009-09-02 14:00:00
starts: 2009-01-01 00:00:00
ends: 2009-12-31 23:59:00
status: ENABLED
on_completion: DROP
sql_mode:
comment:
originator: 1
time_zone: SYSTEM
character_set_client: utf8
collation_connection: utf8_general_ci
db_collation: latin1_swedish_ci
body_utf8: DELETE FROM mysqlscheduler.tbl_users WHERE promo_emails='YES'
1 row in set (0.00 sec)
More details can be found here - http://dev.mysql.com/doc/refman/5.1/en/events.html
3 comments:
very gud example
Only one problem. The name of the event must be unique and it must be a literal. So I can't create anything to dynamically schedule events.
Hi,
Nice article. I have one question.
If the scheduled event is failed, how to retry the event automatically after failure.
And also how to check the status of the event is success or failure..??
Post a Comment