thomaszhwang
asked on
How can I run a MySQL routine periodically?
I have a MySQL routine that accepts two parameters. I want to run it every 5 minutes. What's the easiest way to set this up?
Two things to be aware of
1. The two parameters are datetime type and values need to be retrieved from a query.
2. If the previous instance cannot be finished in 5 minutes, I want the next instance to be skipped.
Thanks.
Two things to be aware of
1. The two parameters are datetime type and values need to be retrieved from a query.
2. If the previous instance cannot be finished in 5 minutes, I want the next instance to be skipped.
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Could you please tell me how I can modify my routine so as to make it automatically runs every 5 minutes?
Thanks.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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;
//
ASKER
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'
'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'
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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;
ASKER
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?
However if I run SHOW PROCESSLIST under the root, I can see the event_scheduler is there.
Any idea?
ASKER
OK, found the error. In MySQL, there is no GETDATE() function.
ASKER
Thanks.
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(parame
rm -f '/tmp/routine.on'