List:General Discussion« Previous MessageNext Message »
From:Rolando Edwards Date:May 22 2008 7:13pm
Subject:RE: Triggering an action every 24 hours
View as plain text  
No. You need to use MySQL 5.1 as that is now a standard feature.

Or, if all your functionality is 100% MySQL, just run a stored procedure in an infinite
loop and check every 60 seconds for

DELIMITER $$

DROP PROCEDURE IF EXISTS `rolando`.`runjob` $$
CREATE PROCEDURE `rolando`.`runjob` (scheduled_hour TINYINT,scheduled_minute TINYINT)
BEGIN

    DECLARE nw DATETIME;
    DECLARE x,hh,mm,ss,time_to_run TINYINT;

    SELECT NOW() INTO nw;
    SELECT HOUR(nw),MINUTE(nw),SECOND(nw) INTO hh,mm,ss;

    WHILE 1=1 DO

        SET time_to_run = 0;
        IF hh = scheduled_hour AND mm = scheduled_minute AND ss = 0 THEN
            SET time_to_run = 1;
        END IF;

        WHILE time_to_run = 0 DO
            SET x = SLEEP(1);
            SELECT NOW() INTO nw;
            SELECT HOUR(nw),MINUTE(nw),SECOND(nw) INTO hh,mm,ss;
            IF hh = scheduled_hour AND mm = scheduled_minute AND ss = 0 THEN
                SET time_to_run = 1;
            END IF;
        END WHILE;

          -- Now that You reached the scheduled time
          -- Run Your SQL Code Here
        -- On completion, the job will restart

       SET x = SLEEP(57);

    END WHILE;


END $$

DELIMITER ;

-----Original Message-----
From: Samuel Vogel [mailto:samy-delux@stripped]
Sent: Thursday, May 22, 2008 2:28 PM
To: MYSQL General List
Subject: Triggering an action every 24 hours

Hey,

I am wondering how I can trigger an Action every 24 hours inside of mysql.
A cron job would also do the job, but it won't work on shared hosts and
it's just another point of failure.

So is there any way to run a specific SQL command every 24 hours inside
of mysql?
I'm using MySQL 5.0.51!

Regards,
Samy

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1

Thread
Triggering an action every 24 hoursSamuel Vogel22 May
  • Re: Triggering an action every 24 hoursDan Nelson22 May
    • Re: Triggering an action every 24 hoursSamuel Vogel22 May
  • RE: Triggering an action every 24 hoursRolando Edwards22 May