MySQL runs automated scripts

Question

I have a MySQL database with two tables: drivers and devices

The drivers table has a field called expiration date. The devices table has a field called status.

My goal is to create an event that will:

• Get the current date
• Compare drivers (expiration date) with current date
• If the expiration date has passed, you need to change the status of the device to 'EXP'

Is such a thing possible? Like an expiration check

Answer 1

This is the query your event needs to execute:

UPDATE devices
SET `status` = 'EXP'
WHERE EXISTS
(
    SELECT 1
    FROM drivers
    JOIN device_drivers
    ON drivers.id = device_drivers.driver_id AND
       device_drivers.device_id = devices.id
    WHERE drivers.`expiration date` < now()
);

As for event creation, you have several options, some of which are listed here:

• Event Scheduler (Thanks @Akina)
• cron job
• Heartbeat Mode
• There could even be some UI or other events that trigger a call to this

Regardless, you may want to wrap your UPDATE in a stored procedure, and if there are more writes, you may also want to call this event when Wrap a transaction.

Answer 2

Okay, consider the following MySQL table structure:

CREATE TABLE Devices (
    device_id INT PRIMARY KEY,
    status ENUM('ACTIVE', 'EXPIRED')
);

CREATE TABLE Drivers (
    driver_id INT PRIMARY KEY,
    device_id INT,
    expiration_date DATE,
    FOREIGN KEY (device_id) REFERENCES Devices(device_id)
);

You need to go through each driver to see if it has expired and update the device correctly.

First, you need to enable EVENTS in the database:

SET GLOBAL event_scheduler = ON;

Next, you can create an event that runs daily, checking all active devices for expired drivers and updating them appropriately:

DELIMITER //
CREATE EVENT UpdateDeviceStatus
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_DATE
DO
BEGIN
    UPDATE Devices d
    SET d.status = 'EXPIRED'
    WHERE EXISTS (
        SELECT 1 FROM Drivers dr
        WHERE dr.device_id = d.device_id
        AND dr.expiration_date < CURRENT_DATE
    ) AND d.status = 'ACTIVE';
END;
//    
DELIMITER ;