Scheduler Graph Template

Owned by Atul Dattatray Bhujbal
24/08/2017

This custom graph template makes it possible to schedule one or more graphs to be processed at a certain time by specifying the next time a graph needs to run in a database table. 

Package contents

  • Graph: 
    • Scheduler
    • scheduledGraphs (subgraph)
  • Database Listener for table: 
    • scheduleControl
  • Nodes: 
    • Scripts: 
      • getCurrentTime - gets the time the scheduler runs
      • updateSchedulerParams - updates the parameters and prepares them for insert
    • DB Update: 
      • updateSchedulerProcesses - updates the scheduleControl table with new values
  • Schema Location: 
    • transaction.scheduler
  • Connection: 
    • Database connection
  • Required:
    • Database


If there are multiple graphs that need the scheduler they can be added below the first graph inserted. Then add 'equals' branches from the get graphName to the additional graphs with appropriate equals conditions. 

Setup 

  • Create a database table called scheduleControl like this (MySQL): 
CREATE TABLE `scheduleControl` (
  `graphName` varchar(50) NOT NULL,
  `currentState` varchar(50) NOT NULL DEFAULT 'notRunning' COMMENT 'notRunning, running, failed',
  `runCount` int(11) DEFAULT '0',
  `nextRunTs` datetime NOT NULL DEFAULT '2099-12-31 23:59:59',
  `lastRunTs` datetime DEFAULT '1900-01-01 00:00:00',
  `lastRunEndTs` datetime DEFAULT '1900-01-01 00:00:00',
  `secondLastRunTs` datetime DEFAULT '1900-01-01 00:00:00',
  `secondLastRunEndTs` datetime DEFAULT '1900-01-01 00:00:00',
  `millisecondsToAddOn` int(11) DEFAULT '0',
  `secondsToAddOn` int(11) DEFAULT '0',
  `minutesToAddOn` int(11) DEFAULT '0',
  `hoursToAddOn` int(11) DEFAULT '0',
  `weeksToAddOn` int(11) DEFAULT '0',
  `daysToAddOn` int(11) DEFAULT '0',
  `monthsToAddOn` smallint(6) DEFAULT '0',
  `timeToSet` varchar(12) DEFAULT NULL COMMENT 'Set next run ts to a particular time.',
  `secondsToSet` int(11) DEFAULT NULL,
  `minutesToSet` int(11) DEFAULT NULL,
  `hoursToSet` int(11) DEFAULT NULL,
  `dayOfWeekToSet` smallint(6) DEFAULT NULL COMMENT 'day of week. Sunday = 0. ',
  `dayOfMonthToSet` smallint(6) DEFAULT NULL COMMENT 'Optional - Set next run to a particular day of the month (consider 29-31st does not apply to every month)',
  `incrementTsFromNow` smallint(6) DEFAULT '0' COMMENT 'Set to 1 to increment the nextRunTs from now. This stops multiple transactions if nextRunTs is set more than 1 period in the past. Con - nextRunTs will creep forwards due to graph run time - use one of the ''timeToSet'' fields to combat.',
  PRIMARY KEY (`graphName`),
  UNIQUE KEY `graphName_UNIQUE` (`graphName`),
  KEY `idx_stateNextRunTs` (`nextRunTs`,`currentState`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='This table is used to schedule graphs. The DB listener queries select process from scheduleControl where nextRunTs <= current_timestamp and currentState=notRunning';
  • Create/update the database connection to the database containing the scheduleControl table
  • Create the scheduler graph template
  • Add the graphs that you want to schedule to the scheduler graph - add a branch for each graph name
  • Add a record to the scheduleControl table for each graph as such:

Using  

The scheduler graph is designed to schedule any number of processes with the ability to set next run date and time using at least 1 of the following:

  • millisecondsToAddOn to add x milliseconds to the current (transaction completion) UTC date and time
  • secondsToAddOn to add x seconds to the current (transaction completion) UTC date and time
  • minutesToAddOn to add x minutes to the current (transaction completion) UTC date and time
  • hoursToAddOn to add x hours to the current (transaction completion) UTC date and time
  • daysToAddOn to add x days to the current (transaction completion) UTC date and time
  • weeksToAddOn to add x weeks to the current (transaction completion) UTC date and time
  • monthsToAddOn to add x months to the current (transaction completion) UTC date and time

These will add up cumulatively and will roll over into the next higher unit of time (e.g. adding 40 hours will add 1 day and 16 hours total). 

You can configure whether to add the time to the value of 'nextRunTs' that triggered the current transaction. Alternatively you can set it to add the time to the current timestamp (at time of processing).

Use the following field to set this:

  • incrementTsFromNow = 0: add the time to the 'nextRunTs' that triggered the transactions
    • pros: The time between transaction runs will remain constant
    • cons: If you set the nextRunTs far in the past such that after adding the incremental time the nextRunTs is still in the past, then the transaction will fail
      • nextRunTs cannot be in the past
      • This is to protect users from starting a scheduler and it runs many times to catch up to the present
  • incrementTsFromNow = 1: add the time to the current timestamp
    • pros: You can set the nextRunTs in the past, it will resume service as normal
      • This is advantageous when a scheduled graph has been interrupted and is started again.
      • You don't need to think too much about the nextRunTs you put in to start the scheduled process.
    • cons: This suffers from nextRunTs creep between transactions
      • When calculating the nextRunTs, you are effectively increasing the incremental time to (incremental time)+(current timestamp - time the transaction was started)
      • This can be avoided by using secondsToSet or minutesToSet
        • e.g. add 1 minute to every transaction will add 1minute plus some milliseconds. If you add 1 minute and set the seconds to 0 then the next transaction will always be on the next minute.


Optionally, it is possible to set a specific time/day for the scheduler to run.

  • timeToSet to set the next run time to a specific time of day (use format 'hh:mm:ss'). 
  • secondsToSet to set the value of seconds
  • minutesToSet to set the value of minutes
  • hoursToSet to set the value of hours
  • dayOfWeekToSet to set the next run day to a particular day (e.g. run on a weekday Sunday = 0, Monday = 1, ...)
  • dayOfMonthToSet to set the next run time to a specific day of the month 1..28 (29-31 not supported, if entered will revert to 28)

Setting timeToSet to '02:00:00' will always set the time to 2am no matter if you have hoursToAddOn or other previous things enabled), timeToSet will override hours, minutes, seconds, and milliseconds ToAddOn, and dayOfWeekToSet or dayOfMonthToSet will override days, weeks, and months ToAddOn.

However, if the options above are used with high enough values that it rolls over to the next level (hours to days, days to weeks, or weeks to months), the ToSet options will set the time or day for that next block (e.g. if monthsToAddOn is set to 1 and dayOfMonthToSet is set to 1 it will run on the first of every month, similarly if timeToSet is set to 02:00:00 and hoursToAddOn is set to 23, it will run every day at 2am). 

For example:

  • A scheduler is set to run at 09:00:00.000 every month on a Tuesday 
    • monthsToAddOn=1:
    • timeToSet=09:00:00.000
    • dayOfWeek=2
  • Assume the current date today is '2017-06-06 10:00:00.000' and the nextRunTs='2017-06-06 09:00:00.000'. Once deployed, the scheduler will pick up this record.
  • The nextRunTs will be set as follows:
    • Add 1 month to current datetime = '2017-07-06 10:00:00.000'
    • Set time to 09:00:00.000  =  '2017-07-06 09:00:00.000'
    • Set dayOfWeek to 2 (Tues) = '2017-07-04 09:00:00.000'

Examples 

To schedule a graph named daily at 2AM every morning the record would be: 

INSERT INTO scheduleControl(graphName,daysToAddOn,timeToSet) VALUES ('GraphName_DailyRun',1,'02:00:00')

To schedule a graph to run on the first day of each month at 6 AM: 

INSERT INTO scheduleControl(graphName,monthsToAddOn,timeToSet,dayOfMonthToSet) VALUES ('GraphName_MonthlyRun',1,'06:00:00',1)

The nextRunTs is by default set to '2099-12-31 23:59:59' to prevent the the graph being triggered immediately on insert. Set the nextRunTs manually to activate the entry.

To schedule a graph to run on every minute:

INSERT INTO scheduleControl (graphName, minutesToAddOn, secondsToSet, incrementTsFromNow) VALUES ('GraphName_RunOnEveryMinute', '1', '0', '1');

Possible Issues

If the scheduler stops and has an error message instead of 'running' or 'notRunning' in the currentState field of the scheduleControl table it will be one of these three issues:

  1. 'failed - incorrect graph name'
    This means that the graph name put into the graphName field of scheduleControl does not match any of the branches in the graph.
    To fix this make sure the graph name on the link coming out of the get graphName node to your logic in the graph contains the correct graph name and that the scheduleControl contains the exact same graph name in the graphName field.
  2. 'failed - your graph failed'
    This means that the logic/graphs inserted into the scheduler to be ran returned an error.
    To fix this make sure all logic and/or graphs inserted into the scheduler are running properly and not returning errors.
  3. 'stopped - nextRunTs is earlier than lastRunTs'
    This means that the next run time is earlier than the last time it ran. This could cause issues as the scheduler will run many times in an attempt to catch up with the time in between the nextRunTs and now. This might happen if the scheduler goes offline for a period of time and then comes online again and starts to run from where it left off. 
    To fix this set nextRunTs to a future time when the scheduler should start to run again. Additionally make sure if the scheduler goes offline that the nextRunTs is set to a future time again before bringing it online. 





Privacy Policy
© 2022 CSG International, Inc.