event_scheduler
Ever wanted to run queries in a cronjob? Where to safely put the database credentials? MySQL/ MariaDB can help out with that.
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:
SET GLOBAL event_scheduler = ON;
Hint: 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.
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
Hint:
exampledb is the database name.SHOW EVENTS FROM `exampledb`;