Solved

How can I run a MySQL routine periodically?

Posted on 2012-04-04
14
376 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MySQL  on Tomcat 8 70
AWS EC2 & RDS Instance 5 51
Creating Functions in phpMyAdmin 8 26
Giant ibd file for our biggest table on mysql 2 18
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 …
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…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

828 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