Running Scheduled Tasks from MySQL Database

Question

I have an application I am building using CakePHP with a MySQL database. Without getting into too much of the details, it's a video surveillance as a service (VSaaS) application. I have built part of a feature that would allow a user to schedule automated snapshots. Users can choose hour and minutes in five minute increments as well as days of the week that the snapshot should reoccur. What I need to do now is build the a service or daemon that will actually execute the routine(s) necessary to fetch the snapshot, save it and write the necessary data to the database based on the interval chosen by the user which is stored in the database table described below.

Database Table Structure

 CREATE TABLE `snapshots_schedules` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `device_id` int(11) DEFAULT '0',
  `name` varchar(255) DEFAULT NULL,
  `resolution` varchar(16) DEFAULT '320x240',
  `sunday` tinyint(1) DEFAULT '0',
  `monday` tinyint(1) DEFAULT '0',
  `tuesday` tinyint(1) DEFAULT '0',
  `wednesday` tinyint(1) DEFAULT '0',
  `thursday` tinyint(1) DEFAULT '0',
  `friday` tinyint(1) DEFAULT '0',
  `saturday` tinyint(1) DEFAULT '0',
  `time` time DEFAULT '00:00:00',
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
        PRIMARY KEY (`id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

Now, I could write a PHP script and run it as a daemon however, from past experience, this is not a secure nor scalable solution. I have searched wide and far on solutions: CakeResque, CakePHP SchedulerShell, Beanstalkd, ZeroMQ, RabbitMQ, etc. Unfortunately, these all fall short of what I am after. These services require me to store the information in their proprietary systems which would just add confusion the an application that already has many components for processing live feeds, handling camera connections and communications and more.

In a nut shell, I am wondering what the best way is and / or best language to write a continuously running service / daemon / script that will query the database and check entries in the table to see if any of those entries need to be acted upon based on the current day of the week and time.

I am definitely open to suggestions however, there obviously has to be a way to store the scheduled data somewhere retrievable so the user can see what is scheduled and remove any schedules they want.

Note: I want to stay away from cron jobs. If your suggestion involves a cron job, please do not bother replying. They are not scalable or easily manageable across a network of servers.

UPDATE - Example

A user creates a scheduled snapshot for a device on Monday's at 6:00PM. The daemon or service, would be continually checking the database for scheduled snapshots that need to be created. For example, every five minutes the daemon or service would run a SELECT query on the database table described above. If it is Monday at 6:00PM, a URL could be executed (http://domain.tld/snapshots/create/device_id) that would actual perform the task of getting the snapshot from the device, saving it, etc.

Thank you

It's still not clear what actually you want to do. Please explain with example. So that we can easily catch your requirement. Thank you — 1000111
Updated with example. The concept isn't that difficult. User creates a scheduled snapshot for Monday's at 6:00PM. I need suggestions on what to use to write a service that would continually check for and perform the needed routine(s) if a snapshot is scheduled at that time and on that day. — recoilnetworks
You can use MySQL event scheduler for this periodic checking. — 1000111
I like your suggestion unfortunately, it looks to me like the MySQL Event Scheduler is specifically for executing another query or set of queries at a specific time. This will not work. I would need to execute a URL to actually create the snapshot programmatically. The snapshot doesn't already exist and just need a database entry. The reason I mentioned the database is because that is where the time, device information to act on and day(s) of the week the snapshot should be taken exists. — recoilnetworks
I edited the original post again hoping it makes more sense this time. What I am after shouldn't be difficult and I am definitely not reinventing the wheel. I am merely looking for suggestions on how to accomplish the task at hand. If it's easier to think of this way, how about sending out automated text messages. For example: at 5:00PM every Monday, send a text message to USER. This entry would be stored in a database such as Redis, Mongo, MySQL, etc. and a daemon would be running checking to see if any notifications need to be sent then executing the routine(s) to send those messages. — recoilnetworks

recoilnetworks recoilnetworks · Accepted Answer · 2016-03-16T04:24:40

After doing some more research, this time specifically using Node.js, it appears that there has been a plugin written for Node.js called node-cron that would allow me to do exactly what I am after. If anyone else that needs to schedule tasks finds this post and doesn't want to use cron and PHP, checkout node-cron for Node.js.

https://github.com/ncb000gt/node-cron

Running Scheduled Tasks from MySQL Database

1 Answers