+- 0Day Forums (https://0day.red)
+-- Forum: Coding (https://0day.red/Forum-Coding)
+--- Forum: Database (https://0day.red/Forum-Database)
+--- Thread: How to schedule a stored procedure in MySQL (/Thread-How-to-schedule-a-stored-procedure-in-MySQL)
How to schedule a stored procedure in MySQL - vivifydg - 07-20-2023
I have this stored procedure. How can I run this for example with intervals of 5 seconds? Like a routine for eliminate data with a time-stamp older than one day?
DROP PROCEDURE IF EXISTS `delete_rows_links`
GO
CREATE PROCEDURE delete_rows_links
BEGIN
DELETE activation_link
FROM activation_link_password_reset
WHERE TIMESTAMPDIFF(DAY, `time`, NOW()) < 1 ;
END
GO
RE: How to schedule a stored procedure in MySQL - briefs554842 - 07-20-2023
If you're open to out-of-the-DB solution: You could set up a cron job that runs a script that will itself call the procedure.
RE: How to schedule a stored procedure in MySQL - intercolonizations147406 - 07-20-2023
You can use mysql scheduler to run it each 5 seconds. You can find samples at
[To see links please register here]
Never used it but I hope this would work:
CREATE EVENT myevent
ON SCHEDULE EVERY 5 SECOND
DO
CALL delete_rows_links();
RE: How to schedule a stored procedure in MySQL - hoarding266278 - 07-20-2023
I used this query and it worked for me:
CREATE EVENT `exec`
ON SCHEDULE EVERY 5 SECOND
STARTS '2013-02-10 00:00:00'
ENDS '2015-02-28 00:00:00'
ON COMPLETION NOT PRESERVE ENABLE
DO
call delete_rows_links();
RE: How to schedule a stored procedure in MySQL - idealogies528041 - 07-20-2023
In order to create a cronjob, follow these steps:
1. run this command : SET GLOBAL event_scheduler = ON;
2. If ERROR 1229 (HY000): Variable 'event_scheduler' is a GLOBAL
variable and should be set with SET GLOBAL:
mportant
It is possible to set the Event Scheduler to DISABLED only at server startup. If event_scheduler is ON or OFF, you cannot set it to DISABLED at runtime. Also, if the Event Scheduler is set to DISABLED at startup, you cannot change the value of event_scheduler at runtime.
To disable the event scheduler, use one of the following two methods:
1. As a command-line option when starting the server:
--event-scheduler=DISABLED
2. In the server configuration file (my.cnf, or my.ini on Windows systems):
include the line where it will be read by the server (for example, in a [mysqld] section):
event_scheduler=DISABLED
Read MySQL [documentation](
[To see links please register here]
) for more information.DROP EVENT IF EXISTS EVENT_NAME;
CREATE EVENT EVENT_NAME
ON SCHEDULE EVERY 10 SECOND/minute/hour
DO
CALL PROCEDURE_NAME();