?
Solved

How can I run a MySQL routine periodically?

Posted on 2012-04-04
14
Medium Priority
?
400 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 2000 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:K V
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 24

Assisted Solution

by:johanntagle
johanntagle earned 2000 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 2000 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 2000 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 2000 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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month14 days, 16 hours left to enroll

840 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