Skip to content

event_scheduler

Ever wanted to run queries in a cronjob? Where to safely put the database credentials? MySQL / MariaDB can help out with that.

Enable the event_scheduler System

You must have the event_scheduler enabled, this can be done by running the following query:

1
SET GLOBAL event_scheduler = ON;

Note

It is highly recommended to use the config file(s) of your MySQL / MariaDB server to enable the event_scheduler feature.
Another (hacky) way is to use the MySQL server init_file option which runs a SQL script on server startup.

Create an Event to run a SQL Query

The CREATE EVENT query below would run the query after the DO every day at 02:00 in the exampledb database.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
CREATE EVENT `exampledb`.`my_cool_table_reset_userOption45` ON
SCHEDULE EVERY 1 DAY STARTS CURRENT_DATE + INTERVAL 1 DAY + INTERVAL 2 HOUR DO
UPDATE
    `exampledb`.`my_cool_table`
SET
    userOption45 = ''
WHERE
    userOption45 != ''
    AND STR_TO_DATE(userOption45,
    '%Y-%m-%d') <= NOW();

Show existing Events

Note

exampledb is the database name.

1
SHOW EVENTS FROM `exampledb`;

References