Event Scheduler or Set a Timer in MySQL
![Event Scheduler or Set a Timer in MySQL](/img/MySQL/feature-image---event-scheduler-or-set-a-timer-in-mysql.webp)
This tutorial guides you about creating an EVENT
, also known as the MySQL timer.
We will see how to set a timer in MySQL to let the MySQL manipulate data (for example, UPDATE
/INSERT
/DELETE
data). It will also make a backup on the mentioned date and time (if required).
We will look at MySQL EVENT
considering different scenarios. Also, discuss its importance and benefits. For this article, we are using the MySQL version 8.0.27, but you may get the newer version (if available) from their official website.
MySQL Event
and Its Importance
MySQL EVENT
is a task used to schedule some operations - for example, updating the columns, taking the tables, or database backup.
You can CREATE
EVENT
that will be executed only once or repeat according to the given intervals - for example, every one minute, every hour, month, or year.
There is no fixed number of statements to create an EVENT
. It can have many lines within the EVENT
body.
If the EVENT
has more than one line, the body must be enclosed within the BEGIN
and END
blocks. It is also important to know that scheduled EVENT
commands are stored in the database
and executed on the scheduled DATE
and TIME
.
Creating an event is important for the following benefits:
- Keeps everyone on the same page
- Set your goals
- Manage the schedule and execute the events
- Useful for optimizing tables, updating data
- Generate useful reports for off-peak times
- Let you prioritize your tasks
- Saves your money and time
Event
Scheduler or Set a Timer in MySQL
Delays are very costly to the production team as well as the brands.
Scheduling the EVENT
or setting a timer for specific fields (columns) and then dumping them into a separate file for the project report will save their time. In this way, it will let them focus on other productive tasks.
MySQL uses a thread called event_scheduler
to execute all the events. If the value of event_scheduler
is ON
, only then will the EVENT
be executed; otherwise, it will not.
You have to use the following command to set the value of event_scheduler
equal to ON
.
SET GLOBAL event_scheduler = ON;
Now, if you CREATE
an EVENT
, how would you know it will be executed? Use the following command to see.
SHOW PROCESSLIST;
Let’s see the output before and after setting the value of event_scheduler
to ON
.
In the first output, you can not execute the EVENT
, but in the second output, you can. This is because the event_scheduler
is ON
for the second screenshot.
Output:
You can CREATE
the EVENT
for different purposes - for example, the EVENT
will run only once or every year, or once but within the given intervals. Let’s start understanding with the help of a sample code.
We have a student
table in the person
database, which has the following records at the current time.
Let’s CREATE
the following EVENT
to understand One-Time Event. The example code is given below.
SET GLOBAL event_scheduler = ON;
CREATE EVENT IF NOT EXISTS ONE_TIME_EVENT
ON SCHEDULE AT CURRENT_TIMESTAMP
DO
INSERT INTO person.student (id, FIRST_NAME, LAST_NAME, GENDER, EMAIL)
VALUES
(7, 'Thomas', 'Christoper', 'Male', 'tchristopher@gmail.com');
In the code given above, we set the value of event_scheduler
to ON
to execute the EVENT
.
Then we created an EVENT
, named it as ONE_TIME_EVENT
, and scheduled it on CURRENT_TIMESTAMP
. This EVENT
represents that it will be executed once on the CURRENT_TIMESTAMP
and then expire.
This EVENT
will only be created if it does not exist. The job of this EVENT
is to INSERT
a row in the student
table.
After creating and executing this EVENT
, check the student
table to see the new record (see the following screenshot).
EVENT
but give a unique name. Events’ unique names will make your job easy if you have many events.Output:
Let’s assume that you have to CREATE
an EVENT
that will execute after 20 seconds of the CURRENT_TIMESTAMP
. This event will also be executed only once. Write the following code and understand.
CREATE EVENT IF NOT EXISTS ONE_TIME_EVENT_WITH_INTERVAL
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 20 SECOND
DO
INSERT INTO person.student
VALUES
(8, 'Suzu', 'Aly', 'Fmale', 'aly.suzu@yahoo.com');
Output:
You can see the new record in the student
table after 20 seconds.
Keep in mind that you will not see this new row in the student
table if you check it before 20 seconds. Let the twenty seconds pass, and then check.
Use the following code to see how many events are created and scheduled.
SHOW EVENTS FROM classicmodels;
Output:
Why any of the EVENT
is not listed in the above screenshot? Because all of our events are expired now, according to this article.
You can track all the events, whether they are expired or not, by using the ON COMPLETION PRESERVE
clause as follows.
CREATE EVENT TEST_PRESERVE_CLAUSE
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
ON COMPLETION PRESERVE
DO
INSERT INTO tests(test_name,test_date)
VALUES('Testing Preserve Clause',NOW());
What if you want to CREATE
an EVENT
that starts from the CURRENT_TIMESTAMP
, executes every MINUTE
, and expires after one HOUR
. It is called a recurring Event.
You can create and practice by using the following code.
CREATE EVENT RECURRING_EVENT
ON SCHEDULE EVERY 1 MINUTE
STARTS CURRENT_TIMESTAMP
ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
INSERT INTO Tests(test_name,test_date)
VALUES('This is recurring event test',NOW());
In the code given above, we tell the EVENT
to start and end time using STARTS
and ENDS
. You can also drop the event when you think it is not required now.
Replace the name
with your EVENT
name in the following code.
DROP EVENT [IF EXIST] name;
Let’s take another situation where you have created the EVENT
and handed over the job to another developer. Would it be easy to understand without COMMENT
? Of course not.
CREATE
EVENT
and practice by adding COMMENT
as follows.
CREATE EVENT RECURRING_EVENT
ON SCHEDULE EVERY 1 MINUTE
STARTS CURRENT_TIMESTAMP
ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR
COMMENT 'It will add a new record every minture for one hour'
DO
INSERT INTO Tests(test_name,test_date)
VALUES('This is recurring event test',NOW());
We’ve learned the EVENT
with only one line in the EVENT
body. What if you have multiple lines? Let’s understand it via the following sample code.
delimiter |
CREATE EVENT IF NOT EXISTS EVENT_FOR_UPDATE_COLUMNS_AND_DUMP
ON SCHEDULE AT current_timestamp
DO BEGIN
UPDATE student SET GENDER = 'M' WHERE student.GENDER = 'Male';
UPDATE student SET GENDER = 'F' WHERE student.GENDER = 'Female';
Table student INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/file.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
END |
delimiter ;
You will see three queries in the EVENT
body in the code provided. Whenever you have more than one line in the EVENT
body, enclose them within BEGIN
and END
as given above.
We created an EVENT
that will start from the CURRENT_TIMESTAMP
in this example. It will update the GENDER
column according to the UPDATE
statement and export the data into a .CSV
file.
For exporting, you have to use this path C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/file.csv
(if you have installed MySQL in C Drive). Otherwise, you will get the following error.
Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
If you have installed MySQL on a different location, use EVENT scheduler, then use the following command to find the path where your file will be exported.
SHOW VARIABLES LIKE "secure_file_priv";
Conclusion
We have learned about MySQL EVENT
scheduler in this tutorial.
We learned how to create events that will be executed once, as per provided intervals, only once according to the given interval. We also explored creating an event with many lines, updating the data, and then exporting it into a separate file.