60

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
Suresh Kamrushi
  • 15,627
  • 13
  • 75
  • 90
user455318
  • 3,280
  • 12
  • 41
  • 66
  • 2
    Do you really need to run it **each 5 second**? – zerkms Jul 03 '11 at 01:53
  • 10
    nop, with interval of 6 hours is fine. But to test is more easy with seconds :) – user455318 Jul 03 '11 at 01:56
  • 2
    Running anything automatically every 5 seconds is probably a really bad idea. Try running it once a day, or once an hour, so you don't completely kill your performance. – Jonathan Hall Jul 03 '11 at 01:56
  • I also wouldn't bother re-creating the stored procedure every time you run it. Then again, I wouldn't even bother with a stored procedure. It's a simple DELETE statement... just execute it using whichever scheduling method you prefer. – Jonathan Hall Jul 03 '11 at 02:02

4 Answers4

88

You can use mysql scheduler to run it each 5 seconds. You can find samples at http://dev.mysql.com/doc/refman/5.1/en/create-event.html

Never used it but I hope this would work:

CREATE EVENT myevent
    ON SCHEDULE EVERY 5 SECOND
    DO
      CALL delete_rows_links();
Ross Rogers
  • 23,523
  • 27
  • 108
  • 164
zerkms
  • 249,484
  • 69
  • 436
  • 539
  • i only replace delete_rows_links with this, but apparently doesn't work. DELETE FROM `database_charts`.`activation_link_password_reset` WHERE TIMESTAMPDIFF(DAY, `time`, NOW()) < 1; – user455318 Jul 03 '11 at 02:13
  • 1
    @user455318: "doesn't work" --- isn't an explanation. What mysql version do you use? What error did you get? – zerkms Jul 03 '11 at 03:17
  • 1
    i cant enable the SET GLOBAL event_scheduler = ON; give me an error of connection – user773961 Jul 03 '11 at 03:20
  • @user773961: uhm, php?!?!? perform **everything** directly in mysql console. – zerkms Jul 03 '11 at 03:22
  • 1
    What mysql version do you use? scheduler is available only in mysql 5.1 – zerkms Jul 03 '11 at 03:22
  • ok. point of situation. the SHOW PROCESSLIST\G show the process event_scheduler, but any row is deleted in the interval of 5 seconds – user773961 Jul 03 '11 at 03:24
  • oh ... 5.0.51a. probably is the problem – user773961 Jul 03 '11 at 03:25
  • 9
    I think this should be `DO CALL delete_rows_links();` – joedborg Apr 17 '12 at 10:36
  • [I have a similar question](http://stackoverflow.com/questions/38029868/how-dichotomous-quantity-intervals-work-in-an-event). If you have some free time, please take a look at it. – stack Jun 25 '16 at 15:19
  • Make sure to also turn on the mysql scheduler: `SET GLOBAL event_scheduler = ON;` – mrmashal Nov 24 '22 at 11:08
15

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();
Robin Daugherty
  • 7,115
  • 4
  • 45
  • 59
Nero
  • 153
  • 1
  • 6
11

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 for more information.

     DROP EVENT IF EXISTS EVENT_NAME;
  CREATE EVENT EVENT_NAME
 ON SCHEDULE EVERY 10 SECOND/minute/hour
 DO
 CALL PROCEDURE_NAME();
radgex
  • 123
  • 1
  • 6
Rishi Gautam
  • 1,948
  • 3
  • 21
  • 31
4

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.

dee-see
  • 23,668
  • 5
  • 58
  • 91