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.
thomaszhwangAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johanntagleCommented:
You can use the mysql event scheduler.  Please read an answered question about it at http://www.experts-exchange.com/Database/MySQL/Q_27564109.html.

For the checking if the process is still running, you can create a table where you check first if a value is set (if so, then it is running), set it when you start the main process, then unset it when finished.  On one project where I had a similar requirement for multiple events I had something like:

select active from process_check_table where event_name='event1';

if value of active is 0, then I proceed, updating first the value to 1,  running the routine, then putting back the value to 0 when finished.  If the value returned from the sql above is 1, then I skip the rest of the script.

I will need more info e.g. your actual code if you need a more detailed solution.  Thanks.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
theGhost_k8Database ConsultantCommented:
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'
0
thomaszhwangAuthor Commented:
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.
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

johanntagleCommented:
You don't modify the routine to make it run every 5 minutes.  You make Event Scheduler call it every 5 minutes.  Have you read the link I gave you, along with the mysql doc page it mentioned?  Your answers are all there, so please read it and try it out first.  If you encounter difficulty, post the details here.
0
thomaszhwangAuthor Commented:
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.
0
johanntagleCommented:
Can you paste the output of show events or your actual CREATE EVENT command?
0
thomaszhwangAuthor Commented:
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;

//

Open in new window

0
thomaszhwangAuthor Commented:
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'
0
johanntagleCommented:
Hmmm the create event syntax itself looks sound.  I can't test your actual procedure but this is what I did to test a sample event on sample tables (just changed interval to 10 seconds):

mysql> desc bang;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| a     | datetime | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> desc bookings;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(11)  | NO   | PRI | NULL    | auto_increment |
| from  | datetime | YES  |     | NULL    |                |
| to    | datetime | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> delimiter |
mysql> create event test_event
    -> on schedule every 10 second
    -> do
    -> begin
    ->   insert into bang values (now());
    ->   insert into bookings (`from`, `to`) values (now(), date_add(now(), interval 1 day));
    -> end;|
Query OK, 0 rows affected (0.00 sec)

--after waiting a bit

mysql> select * from bang;
+---------------------+
| a                   |
+---------------------+
| 2010-02-12 08:38:17 |
| 2012-03-29 15:27:56 |
| 2012-04-06 13:24:27 |
| 2012-04-06 13:25:15 |
| 2012-04-06 13:25:25 |
| 2012-04-06 13:25:35 |
| 2012-04-06 13:25:45 |
+---------------------+
7 rows in set (0.00 sec)

mysql> select * from bookings;
+----+---------------------+---------------------+
| id | from                | to                  |
+----+---------------------+---------------------+
|  5 | 2012-04-06 13:24:28 | 2012-04-07 13:24:28 |
|  2 | 2012-03-26 00:00:00 | 2012-03-27 00:00:00 |
|  3 | 2012-03-27 00:00:00 | 2012-03-28 00:00:00 |
|  4 | 2012-03-27 00:00:00 | 2012-03-29 00:00:00 |
|  6 | 2012-04-06 13:25:15 | 2012-04-07 13:25:15 |
|  7 | 2012-04-06 13:25:25 | 2012-04-07 13:25:25 |
|  8 | 2012-04-06 13:25:35 | 2012-04-07 13:25:35 |
|  9 | 2012-04-06 13:25:45 | 2012-04-07 13:25:45 |
| 10 | 2012-04-06 13:25:55 | 2012-04-07 13:25:55 |
+----+---------------------+---------------------+

Open in new window


Your code works standalone, right?  Things you can check:
1.  drop the event, then recreate it.  See if there were any warnings.
2.  Check if the scheduler process is really running:
mysql> show processlist;
+-----+-----------------+-----------+------+---------+------+-----------------------------+------------------+
| Id  | User            | Host      | db   | Command | Time | State                       | Info             |
+-----+-----------------+-----------+------+---------+------+-----------------------------+------------------+
| 270 | root            | localhost | test | Query   |    0 | NULL                        | show processlist |
| 271 | event_scheduler | localhost | NULL | Daemon  |    0 | Waiting for next activation | NULL             |
+-----+-----------------+-----------+------+---------+------+-----------------------------+------------------+

Open in new window

0
johanntagleCommented:
You can also check the mysql error log in case running the procedure terminated with an error.  Also see http://dev.mysql.com/doc/refman/5.1/en/events-status-info.html regarding using SHOW commands or mysqladmin (depending on mysql version) for troubleshooting.
0
thomaszhwangAuthor Commented:
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;

Open in new window

0
thomaszhwangAuthor Commented:
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?
0
thomaszhwangAuthor Commented:
OK, found the error.  In MySQL, there is no GETDATE() function.
0
thomaszhwangAuthor Commented:
Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.