Solved

How can I run a MySQL routine periodically?

Posted on 2012-04-04
14
381 Views
Last Modified: 2012-04-06
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.
0
Comment
Question by:thomaszhwang
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
14 Comments
 
LVL 24

Accepted Solution

by:
johanntagle earned 500 total points
ID: 37809434
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
 
LVL 21

Expert Comment

by:theGhost_k8
ID: 37809742
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
 

Author Comment

by:thomaszhwang
ID: 37814902
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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 24

Assisted Solution

by:johanntagle
johanntagle earned 500 total points
ID: 37814910
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
 

Author Comment

by:thomaszhwang
ID: 37815070
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
 
LVL 24

Assisted Solution

by:johanntagle
johanntagle earned 500 total points
ID: 37815074
Can you paste the output of show events or your actual CREATE EVENT command?
0
 

Author Comment

by:thomaszhwang
ID: 37815076
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
 

Author Comment

by:thomaszhwang
ID: 37815078
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
 
LVL 24

Assisted Solution

by:johanntagle
johanntagle earned 500 total points
ID: 37815104
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
 
LVL 24

Assisted Solution

by:johanntagle
johanntagle earned 500 total points
ID: 37815764
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
 

Author Comment

by:thomaszhwang
ID: 37816839
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
 

Author Comment

by:thomaszhwang
ID: 37816920
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
 

Author Comment

by:thomaszhwang
ID: 37817058
OK, found the error.  In MySQL, there is no GETDATE() function.
0
 

Author Closing Comment

by:thomaszhwang
ID: 37817192
Thanks.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MySQL Error Code 2 31
Display images from mysql blob type (Not working) 9 55
two ways encryption with php 3 50
Where on a calculated field 1 36
More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question