What Is MySQL Events?
It was very difficult to complete some periodical database operational tasks and you can perform only through the operating system such as Windows scheduled task, or Linux contab. MySQL community finally added a very distinctive features from MySQL5.1.0 onwards and Event Scheduler is the part of those features.
Event Scheduler can be used regularly to perform certain tasks. It can be seen as time-based triggers. MySQL Event Scheduler is the group of queries to execute on the specified time. You can set the time when create an Event in MySQL.
MySQL Event Scheduler function can be implemented directly in the background at the specific time. MySQL Event Scheduler is a database operation performed as per predefined time. It is one kind of “time trigger.” As Event Schedular supported from the MySQL version 5.1 so if your version is lower than 5.1, you will have to get the upgraded version of it.
How to enable MySQL Events?
You have to enable the Event Scheduler in MySQL. By default Event Scheduler is off. Let’s understand to enable MySQL events.
First of all, Check the status Events in your MySQL server before trying to enable it. To check the status, Open MySQL Query Browser and run the following Query.
1 2 3 | SHOW VARIABLES LIKE "EVENT_SCHEDULER"; |
OUTPUT:
Variable_name Value
event_scheduler OFF
Here, In my server, event_scheduler value is the OFF. If output of above query is ON, no need to start the Event scheduler again. You can skip this step and continue to create event.
event_scheduler is the global variable used to on or off the scheduled task, the variable has the following three values:
- OFF: The scheduled task is stopped, event scheduler thread does not run. It is the default value of the event_scheduler.
- ON: The scheduled task is running, event scheduler thread starts, and perform all the scheduled tasks.
- DISABLED: This value will cause the Scheduled Tasks can not run
You can check the executed event scheduler thread through the following command.
1 2 3 | SHOW PROCESSLIST |
When you execute above query and ge the event_scheduler variable in user field, Your server’s MySQL event is enabled.As here the Output is OFF, I have to enable the MySQL Event so let’s understand the following query to enable MySQL events:
1 2 3 4 5 | SET GLOBAL event_scheduler=ON; Or SET GLOBAL event_scheduler = 1; |
You can set ON or 1 to enable the MySQL events. Also you can enable it from my.ini file.For that open your my.ini file and search for keyword event_scheduler:
1 2 3 | event_scheduler = 1 |
Set the event_scheduler as 1 in that file. That’s it. Then again execute SHOW VARIABLES LIKE ‘EVENT_SCHEDULER’; command to make sure you have enabled the MySQL events. If you get the value ON then you have successfully enabled MySQL Events on your server.
How to create an Event in MySQL?
Event Scheduler is work as a database object so to create an Event is similar to the DDL statements and other functionalities like triggers, store procedure and user defined function in MySQL. It has DELIMITER, BEGIN, DO and END keywords as well.A stored procedure is only executed when it is invoked directly, a trigger is executed when an event associated with a table such as an insert, update, or delete event occurs, while an event can be executed at once or more regular intervals.
To create and schedule a new event, you use the CREATE EVENT statement as follows:
1 2 3 4 5 6 7 8 9 | CREATE EVENT [IF NOT EXIST] event_name ON SCHEDULE schedule [ON COMPLETION [NOT] PRESERVE] [ENABLE | DISABLE | DISABLE ON SLAVE] [COMMENT 'comment'] DO event_body |
Let me explain each and every point one by one:
1. This is the keyword “Create” to specify Event creation.
2. To create only If the database object with the same name does not exist
3. The name of the scheduled task. This should be a unique name to make identification as a database object
4. Schedule the planned program. There are two keys to set the task execution plan:
AT and EVERY:
The AT specifies a one-time program, followed by a time stamp, subsequent sql statement will be executed once at the specified time.
EVERY specifies a cyclical program, in this clause can be specified from the point during a certain time to a certain point in time, every once in a cycle to perform the task.
5. The completion of whole task and the plan task object is still retained in the database. The default is not retained.
6. You can create an event but prevent it from being active using the DISABLE keyword. Default set value is Enable.
7. You can add a comment for an event using a COMMENT clause. comment can be any string of up to 64 characters that you wish to use for describing the event. The comment text, being a string literal, must be surrounded by quotation marks.
8. You place the SQL statements after the DO keyword and perform real SQL statement. It is important that you can call a stored procedure inside the body of the event. In case you have compound SQL statements, you can wrap them in a BEGIN END block.
After understanding of syntax, let’s create a basic event by using the CREATE EVENT statement which will execute after 3 minutes of creating event.
1 2 3 4 5 6 7 8 9 10 | DELIMITER $$ CREATE EVENT first_event ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 3 MINUTE DO BEGIN INSERT into table_name (field_name) VALUES (values); END; $$; |
You can use the following command to cross verify that event is created or not. This command list out all the events of your MySQL server.
1 2 3 | SHOW EVENTS; |
You can use the BEGIN and END identifier for compound MySQL operations or multiple SQL queries with the block of Event.You need to end with semi-colon(;) if you have multiple queries in the event.This is the basic one-time event that is executed after its creation time 3 minute only once at a scheduled time and after the execution is completed , the event will be deleted from the server. IF you want to change this behavior, you can use the ON COMPLETION PRESERVE clause
Let’s create an event which will not delete after completion.
1 2 3 4 5 6 7 8 9 10 11 | DELIMITER $$ CREATE EVENT first_event ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 3 MINUTE ON COMPLETION PRESERVE DO BEGIN UPDATE table_name SET field_name = field_name + 1; END; $$; |
If you execute the SHOW EVENTS statement again, you can see the event is exists because of the effect of the ON COMPLETION PRESERVE clause.
Edit the Exiting Events
You can edit events normally with the ALTER EVENT clause. Let’s understand the following syntax which will show the editing of the existing event in MySQL.
1 2 3 4 5 6 7 8 9 10 | ALTER EVENT EVENT_NAME ON SCHEDULE schedule [RENAME TO new_event_name] [ON COMPLETION [NOT] PRESERVE] [ENABLE | DISABLE] [COMMENT 'comment'] DO sql_statement; |
Example:
1 2 3 4 5 6 7 8 9 10 11 | DELIMITER $$ ALTER EVENT first_event ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 2 MINUTE ON COMPLETION PRESERVE DO BEGIN UPDATE table_name SET field_name = field_name + 1; END; $$; |
After executing above query your event will be set to execute after 2 minute from current time.
Rename Events in MySQL
Renaming event is very easy here. Have a look at below query you will get all things yourself, i think no need of explanation over here.
1 2 3 | ALTER EVENT first_event RENAME TO first_event_edited; |
Delete Events in MySQL
To remove an existing event, you use the DROP EVENT statement as follows:
1 2 3 | DROP EVENT [IF EXIST] event_name; |
That’s it, I am ending this article here. Have you ever used MySQL event schedular? or Will you use the MySQL Scheular in your project? Let me know in the comments!
Don’t forget to share this post!
