Create a MySQL timer tutorial - single and recurring

You can create a timer that runs a specific MySQL query at a specific time by creating an event.

A MySQL event is a query task that runs on a determined schedule. This event will be executed by a special Event Scheduler thread

When you install a MySQL database and start the database server, MySQL should also run a separate thread for the event_scheduler user.

You can check if your MySQL server already has a running event scheduler by running the SHOW PROCESSLIST query as shown below:

mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
     Id: 5
   User: event_scheduler
   Host: localhost
     db: NULL
Command: Daemon
   Time: 146
  State: Waiting on empty queue
   Info: NULL
*************************** 2. row ***************************
     Id: 11
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: init
   Info: SHOW PROCESSLIST
2 rows in set (0.00 sec)

Note how the query above returned two rows:

• One process for the event_scheduler
• Another process for the root user, because I’m accessing my server using root

If you don’t see any process for the event_scheduler user in your process list, then you can try turning on the event scheduler system variable manually by executing the following query:

SET GLOBAL event_scheduler = ON; 

Please note that you may need to access your MySQL server with SYSTEM_VARIABLES_ADMIN or SUPER privilege to turn on the event_scheduler variable.

To turn off the event_scheduler, you can set the global system variable to OFF:

SET GLOBAL event_scheduler = OFF; 

When you have the event_scheduler active, it’s time to create the timer by using the CREATE EVENT statement.

The syntax for CREATE EVENT statement is as follows:

CREATE EVENT [event_name]
  ON SCHEDULE [schedule] + [optional interval]
  DO [query]

A minimum event needs to have an event_name, a schedule for when the event will run, and the query that will be executed by the event.

For example, take a look at the following query:

CREATE EVENT new_member
ON SCHEDULE AT '2021-09-01 15:30:00'
DO 
  INSERT INTO heroes.users (first_name, last_name) VALUES ("Peter", "Parker");

The query above will create a new event named new_member scheduled for September 1st, 2021 at 3.30 PM.

The query will insert a new row into the table users in database heroes.

Alternatively, you can set the event to run at a specific time from now by using the INTERVAL keyword.

The following event will run 2 hours after the event has been created:

CREATE EVENT new_member
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 2 HOUR
DO 
  INSERT INTO heroes.users (first_name, last_name) VALUES ("Peter", "Parker");

Once you have created a MySQL event, you can check if the event has been registered by using the SHOW EVENTS statement:

SHOW EVENTS FROM [database_name];

You need to include the FROM [database_name] clause in the statement.

Here’s an example when I run mine:

mysql> SHOW EVENTS FROM heroes\G
*************************** 1. row ***************************
                  Db: heroes
                Name: new_member
             Definer: root@localhost
           Time zone: SYSTEM
                Type: ONE TIME
          Execute at: 2021-09-01 15:30:00
      Interval value: NULL
      Interval field: NULL
              Starts: NULL
                Ends: NULL
              Status: ENABLED
          Originator: 1
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

Alternatively, you can also check the event by querying the events table inside the information_schema database.

Here’s an example of querying the events table:

mysql> SELECT event_name, definer, event_definition, execute_at FROM information_schema.events\G
*************************** 1. row ***************************
      EVENT_NAME: nathan_event
         DEFINER: root@localhost
EVENT_DEFINITION: INSERT INTO heroes.users(first_name,last_name) VALUES ("Peter", "Parker")
      EXECUTE_AT: 2021-09-01 15:30:00
1 row in set (0.00 sec)

Feel free to use the method that you prefer for checking scheduled events.

Create a repeating timer

To create a MySQL timer that repeats periodically, you can replace the SCHEDULE AT keyword with SCHEDULE EVERY.

For example, suppose you have a database for a game, and you may want to have a repeating timer that resets the high score column to zero at the start of each month.

Here’s a query to do so:

CREATE EVENT reset_score
ON SCHEDULE EVERY '1' MONTH
STARTS '2021-10-01 15:30:00'
DO 
  UPDATE heroes.users SET high_score = 0;

The query above will create a new event named reset_score that will be executed every time one month has passed.

The event will run the UPDATE statement inside the DO clause that will reset the high_score column back to 0.

Additionally, you need to include the STARTS clause so that the scheduler knows when to execute the event.

Without the STARTS clause, then MySQL will execute the event one month after the event has been created instead of the beginning of the next month.

Removing an event

To cancel an event that you have created before, you need to use the DROP EVENT statement to drop the event from the information_schema:

The statement syntax is as follows:

DROP EVENT [event_name]

You need to include the event_name right after the DROP EVENT statement.

Once you have executed the query, the event will be removed from the server.

Conclusion

And that’s how you can create and remove a timer in the MySQL database server. For more information, you can visit the following MySQL documentation page:

• CREATE EVENT statement
• Event Scheduler configuration

Thanks for reading this tutorial. I hope it has been useful for you 😉