How to Use Scheduled Events with Mysql 8.0

Updated on July 29, 2021

Introduction

MySQL scheduled events are time-based SQL statements executed by the scheduler thread at a specific time, usually repetitively. These tasks are like cron jobs in Linux.

In a real-life scenario, you would use a MySQL scheduler to automate business logic. For instance, when designing a bank application, you can use a scheduler to post daily savings interests into a database table or schedule a job to apply a roll-over fee to loan defaulters.

This guide explains creating a sample database and implementing a per-minute billing application using a MySQL scheduled event.

Prerequisites

To complete this tutorial, make sure you have:

1. Create a Sample Database

Log in to MySQL as root.
```
 $ sudo mysql - u root -p
```
MySQL uses a thread to run scheduled events in the background. This behavior is controlled by a global variable named event_scheduler

To check if the feature is enabled, run the command below.

 mysql> SELECT @@event_scheduler;

The feature is ON or OFF.

 +-------------------+
 | @@event_scheduler |
 +-------------------+
 | ON                |
 +-------------------+
 1 row in set (0.00 sec)

If the feature is turned OFF, enable it by executing the command below.

 mysql> SET GLOBAL event_scheduler = 1;

After the scheduler is running, create a sample_db database.
```
 mysql> CREATE DATABASE sample_db;
```
Select your new sample_db database.
```
 mysql> USE sample_db;
```
Define a products table.

This table holds information about the different plans offered in your hypothetical company. Each plan has a unique product_id and a distinguishable product_name going for a specific price which is stored in a billing_per_minute column. Use the DECIMAL data type for this column to take advantage of its precision since you're dealing with very low floating values which need accuracy.

Create the products table by running the following command.
```
 mysql> CREATE TABLE products (
          product_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
          product_name VARCHAR(50),
          billing_per_minute  DECIMAL(17, 6)
        ) ENGINE = InnoDB;
```
Insert some items into the products table.

For demonstration purposes, enter two products. This guide assumes you're offering tutorials-as-a-service and you're billing your customers on a per-minute basis. Customers can subscribe to your PYTHON TUTORIAL at a rate of $0.003472 per minute($5 per day) or the C# TUTORIAL that goes for $0.006944 per minute($10 per day).
```
 mysql> INSERT INTO products (product_name, billing_per_minute) VALUES('PYTHON TUTORIAL', '0.003472');
        INSERT INTO products (product_name, billing_per_minute) VALUES('C# TUTORIAL', '0.006944');
```
Create a customers table.

This table holds information about your clients. Each customer has a unique primary key defined by the customer_id column. Also, capture the customers' full names using the first_name and last_name fields.
```
 mysql> CREATE TABLE customers (
          customer_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
          first_name VARCHAR(50),
          last_name  VARCHAR(50)
        ) ENGINE = InnoDB;
```

Add a few clients to the customers table.

 mysql> INSERT INTO customers (first_name, last_name) VALUES('JOHN', 'DOE');
        INSERT INTO customers (first_name, last_name) VALUES('SMITH', 'JANE');
        INSERT INTO customers (first_name, last_name) VALUES('MARY', 'MIKE');

Define a subscriptions table.

This table provides a many-to-many relationship to the customers and the products tables, because a single customer can subscribe to many different products, and a single product can have multiple subscribers.

You'll identify each unique subscription with a subscription_id which is the primary key in this case. Next, you'll insert customer_id's and the subscribed product_id's depending on what each client has subscribed to. Finally, because customers might cancel their subscriptions at any time, include an is_active field which you can either activate or deactivate at any time using the flags Y and N.
```
 mysql> CREATE TABLE subscriptions (
          subscription_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
          customer_id BIGINT NOT NULL,
          product_id BIGINT NOT NULL,
          is_active CHAR(1)
        ) ENGINE = InnoDB;
```

Enter some data into the subscriptions table.

 mysql> INSERT INTO subscriptions (customer_id, product_id, is_active) VALUES(1, 1, 'Y');
        INSERT INTO subscriptions (customer_id, product_id, is_active) VALUES(2, 2, 'Y');
        INSERT INTO subscriptions (customer_id, product_id, is_active) VALUES(3, 1, 'Y');

Confirm the customers' subscriptions by executing the SQL statement below, which joins three tables to retrieve the customer names and associated product names to which they subscribe.

 mysql> SELECT
        customers.customer_id,
        customers.first_name,
        customers.last_name,
        products.product_name,
        products.billing_per_minute
        FROM subscriptions
        LEFT JOIN customers
        ON subscriptions.customer_id = customers.customer_id
        LEFT JOIN products
        ON subscriptions.product_id = products.product_id
        ORDER BY customers.customer_id;

As you can confirm from the output below, JOHN DOE and MARY MIKE have subscribed to the PYTHON TUTORIAL while SMITH JANE has opted for the C# tutorial. In all cases, you're using the power of the MySQL JOIN statement to retrieve the cost of each subscription per minute.

 +-------------+------------+-----------+-----------------+--------------------+
 | customer_id | first_name | last_name | product_name    | billing_per_minute |
 +-------------+------------+-----------+-----------------+--------------------+
 |           1 | JOHN       | DOE       | PYTHON TUTORIAL |           0.003472 |
 |           2 | SMITH      | JANE      | C# TUTORIAL     |           0.006944 |
 |           3 | MARY       | MIKE      | PYTHON TUTORIAL |           0.003472 |
 +-------------+------------+-----------+-----------------+--------------------+
 3 rows in set (0.00 sec)

Define a billings table.

You'll use the power of MySQL scheduled events to log billing minutes for each client depending on their subscription in this table.

 mysql> CREATE TABLE billings (
          billing_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
          subscription_id BIGINT NOT NULL,
          time  DATETIME,
          amount DECIMAL(17, 6)
        ) ENGINE = InnoDB;

Your database and all the required tables are now in place. Next, you'll schedule an event to execute your billing logic after each minute.

2. Understand the MySQL Event Syntax

MySQL comes with an elegant syntax for creating scheduled events, as shown below.

mysql> CREATE EVENT IF NOT EXISTS SAMPLE_EVENT_NAME
       ON SCHEDULE
           SAMPLE_SCHEDULE
       DO
           SAMPLE_EVENT_BODY
       ;

Before you create a scheduled event for your billing application, familiarize yourself with the syntax as explained below.

SAMPLE_EVENT_NAME:. This is a name for your event, and it must be unique in your database. For instance, billing_event. See the example below.
```
  CREATE EVENT IF NOT EXISTS billing_event
  ...
```
SAMPLE_SCHEDULE:. This is the exact timing when the event will fire. Use the AT keyword to queue a one-time event, as shown below.
```
  ON SCHEDULE
      AT SAMPLE_TIME_STAMP + INTERVAL SAMPLE_INTERVAL
```
For instance, to execute an event immediately at the current timestamp based on your MySQL server's timezone, use the syntax below.
```
  ...
  ON SCHEDULE
      AT CURRENT_TIMESTAMP
  ...
```
To create a recurring event like in this guide's per-minute billing software, use the EVERY keyword.
```
  ...
  ON SCHEDULE
      EVERY SAMPLE_INTERVAL
  ...
```
For instance, to run an event after every 1 minute, use the syntax below.
```
  ...
  ON SCHEDULE
      EVERY 1 MINUTE
  ...
```
Also, you can specify when a recurring event begins and stops by using the STARTS and ENDS keywords as shown below.
```
  ...
  STARTS
      SAMPLE_TIME_STAMP + INTERVAL SAMPLE_INTERVAL
  ENDS
      SAMPLE_TIME_STAMP + INTERVAL SAMPLE_INTERVAL
  ...
```
For instance, to start a recurring event one hour after the current time and end it after one year, use the syntax below.
```
  ...
  STARTS
      CURRENT_TIMESTAMP + INTERVAL 1 HOUR
  ENDS
      CURRENT_TIMESTAMP + INTERVAL 1 YEAR
  ...
```

Here is a list of all the intervals that you can use to time recurring events.

  YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE | DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND

Some intervals combine two timing periods. For instance, MINUTE_SECOND defines both minutes and seconds. In such a case, a '10 minutes and 4 seconds` interval can be expressed as follows.
```
  + INTERVAL '10:4' MINUTE_SECOND
```

Now that you're familiar with the syntax of creating scheduled events in MySQL, create an event for your billing software in the next step.

3. Schedule a Billing Event

For this demonstration, you'll create a billing software scheduled event that logs per-minute subscription activities into the billings database table.

Create the billing_event by running the command below.
```
 mysql> CREATE EVENT IF NOT EXISTS billing_event
        ON SCHEDULE
            EVERY 1 MINUTE
        DO
            INSERT INTO billings(
              subscription_id,
              time,
              amount
            )
            SELECT
              subscriptions.subscription_id,
              NOW(),
              products.billing_per_minute
           FROM subscriptions
           LEFT JOIN products
           ON subscriptions.product_id = products.product_id
           WHERE subscriptions.is_active = 'Y';
```
In the above code, you've instructed MySQL to run the event after every 1 minute using the keyword ON SCHEDULE EVERY 1 MINUTE. Next, you're using the MySQL INSERT INTO SELECT command to copy subscription_id's and the associated billing_per_minute amount from the subscriptions and products tables, respectively. You're also using the NOW() function to record the current timestamp for each billing record. The statement `WHERE subscriptions.is_active = 'Y" ensures that you're only billing active subscriptions.

Ensure your event was successfully created in the database by executing the command below.

 mysql> SHOW EVENTS FROM sample_db;

Your billing_event should be listed in the list as shown below.

 +-----------+---------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
 | Db        | Name          | Definer        | Time zone | Type      | Execute at | Interval value | Interval field | Starts              | Ends | Status  | Originator | character_set_client | collation_connection | Database Collation |
 +-----------+---------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
 | sample_db | billing_event | root@localhost | SYSTEM    | RECURRING | NULL       | 1              | MINUTE         | 2021-07-08 12:41:01 | NULL | ENABLED |          1 | cp850                | cp850_general_ci     | utf8mb4_unicode_ci |
 +-----------+---------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
 1 row in set (0.00 sec)

Your event is now scheduled to run after every single minute. In the next step, you'll confirm whether the logic is working as expected.

4. Confirm the Billing Logic

Wait for a few minutes for the scheduler to insert a few records into the billings table and then run the following SELECT statement against the table.

mysql> SELECT
       billing_id,
       subscription_id
       time,
       amount
       FROM billings;

As time progresses, the list from the billings table grows as follows.

+------------+-----------------+---------------------+----------+
| billing_id | subscription_id | time                | amount   |
+------------+-----------------+---------------------+----------+
|          1 |               1 | 2021-07-08 12:41:01 | 0.003472 |
|          2 |               3 | 2021-07-08 12:41:01 | 0.003472 |
|          3 |               2 | 2021-07-08 12:41:01 | 0.006944 |
|          4 |               1 | 2021-07-08 12:42:01 | 0.003472 |
|          5 |               3 | 2021-07-08 12:42:01 | 0.003472 |
|          6 |               2 | 2021-07-08 12:42:01 | 0.006944 |
|          7 |               1 | 2021-07-08 12:43:01 | 0.003472 |
|          8 |               3 | 2021-07-08 12:43:01 | 0.003472 |
|          9 |               2 | 2021-07-08 12:43:01 | 0.006944 |
...
+------------+-----------------+---------------------+----------+
n rows in set (0.00 sec)

This confirms that the MySQL event scheduler is working as expected. Retrieve the total billed amount for each customer's subscription by executing the command below.

mysql> SELECT
       customers.customer_id,
       CONCAT(customers.first_name, ' ', customers.last_name) as customer_full_name,
       sum(amount) as total_due
       FROM billings
       LEFT JOIN subscriptions
       ON billings.subscription_id = subscriptions.subscription_id
       LEFT JOIN customers
       ON customers.customer_id = subscriptions.customer_id
       GROUP BY billings.subscription_id;

You should now see a detailed report as shown below. Your figures should differ depending on how long your scheduled event has run.

+-------------+--------------------+-----------+
| customer_id | customer_full_name | total_due |
+-------------+--------------------+-----------+
|           1 | JOHN DOE           |  0.010416 |
|           2 | SMITH JANE         |  0.020832 |
|           3 | MARY MIKE          |  0.010416 |
+-------------+--------------------+-----------+
3 rows in set (0.00 sec)

Next, you'll learn how to drop a scheduled event.

5. Drop Events

You can drop a scheduled event in MySQL by executing the command below. Replace SAMPLE_EVENT_NAME with the name of your event.

mysql> DROP EVENT SAMPLE_EVENT_NAME;

For instance, to drop the event billing_event, run the command below.

mysql> DROP EVENT billing_event;

Once you delete an event, it will stop executing immediately.

Conclusion

In this guide, you've set up a sample database and learned the basic syntax for creating and scheduling events in MySQL. You've also experimented with the working process of scheduled jobs using a hypothetical billing application. You can use the knowledge in this guide to create any database application that requires recurring jobs to process some business logic.

How to Use Scheduled Events with Mysql 8.0

Introduction

Prerequisites

1. Create a Sample Database

2. Understand the MySQL Event Syntax

3. Schedule a Billing Event

4. Confirm the Billing Logic

5. Drop Events

Conclusion

Products

Features

Marketplace

Resources

Company