Event Scheduler or Set a Timer in MySQL

1. MySQL Event and Its Importance
2. Event Scheduler or Set a Timer in MySQL
3. Conclusion

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).

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.