Solved

How can I run a MySQL routine periodically?

Posted on 2012-04-04
14
367 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
  • 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
 
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

760 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now