I am having a hard time with a logic problem related to scheduling an event
based on data stored in a MySQL table. Below is a sample data set.
The basic concept is a monitoring program that checks a website to see if
its up or down based on info stored in a table.
The way I am doing it requires me to know the last time the site was checked
and how often the site should be checked. I threw in the nc_epoch (next
check epoch seconds) while trying to work through this. That field can be
ignored, unless you see a solution using it.
mc_id = customer id related to another table
url_id = url_id related to another table
chk_freq = how often in seconds the site should be checked
url_timeout = how long to wait for website before considering it down
lc_epoch = last time the site was checked - converted to UNIX epoch in DBI
query
active = is the site supposed to be checked
nc_epoch = next time the site should be checked - converted to UNIX epoch in
DBI query
mc_id url_id chk_freq url_timeout lc_epoch lc_status
active nc_epoch
1 1 60 5 20020409144829 150
1 20020409145310
1 51 5 5 20020409144829 0
1 20020409144210
32 49 5 0 20020409144829 0
1 20020407101341
32 50 3600 15 20020409144829 0
1 20020407221241
select * from table where lc_epoch + chk_freq = now
This will not work because this perl script will run from cron every 60
seconds and it's highly unlikely it will run at the exact second that the
lc_epoch + chk_freq will ever equal $now.
select * from table where nc_epoch = now
This will not work because of the same reason as above
select * from table where lc_epoch + chk_freq >= now
This will not work because if lc_epoch + chk_freq will almost always be
greater than $now
Is there any work or past research in scheduling based on values in a
column?
I have tried numerous select logic and non will work 100% of the time.
Here's my latest failure:
my $select_query = "SELECT url_id, chk_freq, url_timeout,
UNIX_TIMESTAMP(nc_epoch), UNIX_TIMESTAMP(lc
_epoch)
FROM monitor_check
WHERE active = 1
# AND ((UNIX_TIMESTAMP(nc_epoch) + chk_freq) >= ?)";
AND ((UNIX_TIMESTAMP(lc_epoch) + chk_freq) >= ?)";
my $url_list = $dbh->selectall_arrayref($select_query, undef,
\$current_time)
or die "Can't execute statement: $DBI::errstr";
The ideal query in my eyes would be:
Select * from table where lc_epoch + chk_freq is greater than $now but not
greater than $now minus chk_freq.
Does this make any sense?
Thanks
zack
