Modifying database after a set time limit

[dannyb785]

I'm building a site where a user has a timer after they've accepted a job. That timer could be a range from 1 hr to 2 days and if the user hasn't submitted their work within that period of time, I'd like it to automatically mark that job as late. Similarly, their job has to be approved by the boss within 2 days of submission or their work would be automatically approved.

 

I know I could do a cron job every x minutes, but I'd like it to be instant, where as soon as the job late(based on the due date) it marks it in the database as late and sends an email to the user letting them know. Is there any other way other than running a cron job every 5 seconds?

[xyph]

If you want an event to trigger an action, you have to have some sort of listener checking for that event.

 

Since the database can't actively trigger anything, you'll need something that checks for the event, and triggers the action. In this case, your easiest solution is probably a CRON, or a custom application that always runs in the background.

[dannyb785]

thank you for your quick reply. I was worried that the only solution would be either:

 

a) a cron job script every x sec/min/hours or

 

b) running the script(which checks jobs) any time a user accesses any page(to simulate a cron job, but it'd only work when at least 1 user is browsing the site).

 

I know that with a busy site, the script could be run every few seconds(using solution b) but if the site is dead then it could go hours without being run.

 

Which solutions is better?

[xyph]

The more reliable one. You server shouldn't notice a 5 second CRON that runs a tiny PHP script.

 

If you want to reliably send an email within seconds of the project being 'late,' that's your easiest and most reliable solution.

[gizmola]

More elaborate/scalable solutions to this problem usually involve a message broker, typically that implements publish/subscribe.  Often used message brokers in the open source world are RabbitMQ and ActiveMQ. 

 

Short of that, as others have advised, the single server/KISS solution to this is indeed a command line script that only handles this particular job, as efficiently as possible, run in a cron job that executes every few seconds or at whatever frequency is acceptable to you.  Cron will let you run a script every second, however, in practical use, a script like this that does not involve a queue of some sort, must query the database repeatedly with a date range, and there is of course overhead in this activity.  You should insure that query is optimized and utilizes an index and returns in milliseconds.

 

One thing you typically want to do however, is protect against multiple versions of the script running at the same time, which could lead to race conditions or database deadlocking, or any of a number of other issues.  Fortunately, there is a real simple solution available on linux servers, of putting your call inside a bash script that uses the flock program to set a filesystem lock (see http://linux.die.net/man/1/flock)

 

 

 

 

[dannyb785]

More elaborate/scalable solutions to this problem usually involve a message broker, typically that implements publish/subscribe.  Often used message brokers in the open source world are RabbitMQ and ActiveMQ. 

 

Short of that, as others have advised, the single server/KISS solution to this is indeed a command line script that only handles this particular job, as efficiently as possible, run in a cron job that executes every few seconds or at whatever frequency is acceptable to you.  Cron will let you run a script every second, however, in practical use, a script like this that does not involve a queue of some sort, must query the database repeatedly with a date range, and there is of course overhead in this activity.  You should insure that query is optimized and utilizes an index and returns in milliseconds.

 

One thing you typically want to do however, is protect against multiple versions of the script running at the same time, which could lead to race conditions or database deadlocking, or any of a number of other issues.  Fortunately, there is a real simple solution available on linux servers, of putting your call inside a bash script that uses the flock program to set a filesystem lock (see http://linux.die.net/man/1/flock)

 

wtf man, does it make you feel smart by throwing out all those references that I obviously won't recognize. I'm here asking about an efficient way to run a script consistently and you're talking about crap like a "message broker", something that "implements publish/subscribe", RabbitMQ, ActiveMQ, race conditions, flock program, setting a filesystem lock... seriously? Do you honestly think you're helping with that?

[ManiacDan]

If you don't understand a word in a post, you could google for it, read its wikipedia entry, or ask for more information.  If you're completely out of your depth on a problem, you need to understand that some of the solution might be unknown to you.

[Pikachu2000]

Gizmola took time out of his life to provide you with an accurate response in an effort to help you and that's how you respond? Really?

[gizmola]

wtf man, does it make you feel smart by throwing out all those references that I obviously won't recognize. I'm here asking about an efficient way to run a script consistently and you're talking about crap like a "message broker", something that "implements publish/subscribe", RabbitMQ, ActiveMQ, race conditions, flock program, setting a filesystem lock... seriously? Do you honestly think you're helping with that?

 

Let's start with something simple:

 

You write your update script in this manner:

 

- SELECT all rows that are late AND haven't been alerted

---  foreach through the results

----- send an email

-------- update the status of the row to indicate they were alerted

 

Now you put that in a cron job that runs every second

 

What do you think might happen if 2 of those scripts were running at right about the same time?  Do you suppose that people might get 1-2-3 or more emails? 

 

If you actually looked at the link to the flock manual I provided you would have seen this:

 

(
flock -s 200
# ... commands executed under lock ...
) 200>/var/lock/mylockfile 

 

That example isn't what you would want -- you would want an exclusive lock, so a slight adjustment would need to be made:

 

 

#!/bin/sh
#runjob.sh
cd /location/of/yourscript/
(
flock -x 
/usr/bin/php -f yourscript.php

) 200>/var/lock/mylockfile 

 

You then setup cron to run the script every few seconds, and you are off and running, knowing that even if the execution time of the script exceeds the cron interval, a second copy of the script will not be run simultaneously.

 

As for queueing systems, I think I was pretty clear that a scalable solution to your problem will require a more complicated solution.  I really have no idea what your skill level or aptitude is, and it's pretty unreasonable to think I would, however, beyond that, you have a mistaken impression of the purpose of our replies.  If this was an email thread, I'd be in essence working for you for free.  I have no interest in that, nor does anyone else who helps out here.  Almost all of us make our living as professional programmers.  We post here, because we are helping the OP, but also, are contributing to a conversation which gets archived and not infrequently indexed in google, and when other people face similar problems, they get pointed here.

 

However more directly to your question, yes I'm helping you directly as well, because I've given you a heads up, that if you build some sort of effective business on top of this, it will become a bottleneck, and can't be effectively deployed on anything other than a single server.  Since I do most of my work in the cloud, using clusters of servers, this is something I deal with every day.  When I get into an area that is new to me, I have to spend time reading and doing research and even at times prototyping or doing POC applications.  I simply pointed you in that direction, with no particular expectation that it would be of value or not.  I can't teach you a thing, nor can anyone else.  However, what we can do, is point you in a direction, and the rest is up to you.  This place works best when people do their own research and come back with focused questions.

 

I don't take offense easily, so the fact that you chose to respond in such a combative fashion, doesn't bother me personally.  We could go back, and find probably hundreds of threads where I replied to someone, and they then marked their thread solved.  And I could also provide anecdotal evidence, in rough accounting of people who thanked me in their threads, or sent me an email.  I have no doubt that I've helped a lot of people over the years, so your assertion that I simply reply to people in order to try and make them feel  inadequate or stupid, is humorous.  One of the reasons I have been active on this forum for over a decade, is that there are lots of other developers who bring up libraries, frameworks and servers that they use, just as I brought up RabbitMQ and ActiveMQ in this thread.  Rather than curse them for talking about something I wasn't familiar with, I simply put those things on a list to investigate when I have free time.  It's a fairly common practice for most experienced deverlopers, in my experience.