How can I run a MySQL routine periodically?

If you don't want to change your SP to use event or if you're on older version of mysql which don't support events you can easily run a shellscript and put that in a cronjob. Check a sample script below:


LOGFILE="routinewatch.log"
echo "Starting @" `date`>> $LOGFILE

if [ -f  '/tmp/routine.on' ]; then
echo " One instance already on @ " `date` >> $LOGFILE
exit;
fi;
touch '/tmp/routine.on'
mysql -uuser -ppassword -e "CALL your-stored-routine(parameter1, parameter2);"
rm -f  '/tmp/routine.on'

I want to do this in MySQL, so I think the Event Scheduler is my answer.

Could you please tell me how I can modify my routine so as to make it automatically runs every 5 minutes?

Thanks.

OK.  I think I have set it up in a correct way, but it looks like my routine is never fired.

I used SHOW EVENTS to check the status of the event and it looks like it is enabled.

I also enabled the event_schedule by running SET GLOBAL event_scheduler = ON;

Did I miss anything?  Thanks.

Here is my CREATE EVENT

DELIMITER //

CREATE EVENT AFT.event_Count_Users
ON SCHEDULE EVERY 5 MINUTE
DO
BEGIN
    DECLARE LogID BIGINT;
    DECLARE Running BIT;
    DECLARE ActivityTimeUTC DATETIME;
    
    INSERT INTO AFT.ProcessLog (Start_Time, Status) VALUES (GETDATE(), 'Started');
    SELECT MAX(Log_ID) INTO LogID FROM AFT.ProcessLog;
    
    SELECT Event_Count_User_Running INTO Running FROM AFT.Configurations LIMIT 1;
    
    IF Event_Count_User_Running THEN
        UPDATE AFT.ProcessLog SET Status = 'Already Running' WHERE Log_ID = LogId;
    ELSE
        UPDATE AFT.Configurations SET Event_Count_User_Running = TRUE;
    
        SELECT Activity_Time_UTC INTO ActivityTimeUTC FROM AFT.tableEURUSDHourlyPrices WHERE Unique_User_Count_Up IS NULL LIMIT 1;
    
        IF Activity_Time_UTC IS NOT NULL THEN
            UPDATE AFT.ProcessLog SET Status = 'Calling Routine' WHERE Log_ID = LogId;
            CALL AFT.proc_Count_Users(ActivityTimeUTC);
            UPDATE AFT.ProcessLog SET Status = 'Routine Called' WHERE Log_ID = LogId;
        ELSE
            UPDATE AFT.ProcessLog SET Status = 'No Updates' WHERE Log_ID = LogId;
        END IF;
        
        UPDATE AFT.Configurations SET Event_Count_User_Running = FALSE;
    END IF;
    
    UPDATE AFT.ProcessLog SET End_Time = GETDATE() WHERE Log_ID = LogId;
END;

//

Select allOpen in new window

Here is my SHOW EVENT


'AFT', 'event_Count_Users', 'root@localhost', 'SYSTEM', 'RECURRING', NULL, '5', 'MINUTE', '2012-04-06 05:12:46', NULL, 'ENABLED', '0', 'latin1', 'latin1_swedish_ci', 'utf8_bin'

I just test with the following code.  It looks like it is not running.

CREATE TABLE AFT.test
(
    a DATETIME NOT NULL
);

DELIMITER //

CREATE EVENT test_event
ON SCHEDULE EVERY 10 SECOND
DO
BEGIN
    INSERT INTO AFT.test VALUES (GETDATE());
END;

//

SHOW EVENTS;

Select allOpen in new window

If I run SHOW PROCESSLIST under my usual account, it looks like the event_scheduler is not running.

However if I run SHOW PROCESSLIST under the root, I can see the event_scheduler is there.

Any idea?

OK, found the error.  In MySQL, there is no GETDATE() function.

Thanks.