X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2Fbin%2Ffreeside-torrus-srvderive;h=f6fc49906ad8f224c418732958db0e8679b12094;hb=5572125d0609b15d697ef6906845911abfdb65f7;hp=90445fab2b9498a07c05d3167cca41bb3e32a46a;hpb=e7f7d3d81c7bfef65f8a60657c91dac77e9d3627;p=freeside.git diff --git a/FS/bin/freeside-torrus-srvderive b/FS/bin/freeside-torrus-srvderive index 90445fab2..f6fc49906 100644 --- a/FS/bin/freeside-torrus-srvderive +++ b/FS/bin/freeside-torrus-srvderive @@ -1,7 +1,9 @@ #!/usr/bin/perl -w use strict; +use Sys::SigAction qw( set_sig_handler ); use Date::Parse; +use Date::Format; use FS::Daemon ':all'; #daemonize1 drop_root daemonize2 myexit logfile sig* use FS::UID qw( adminsuidsetup dbh driver_name ); use FS::Record qw( qsearch str2time_sql str2time_sql_closing concat_sql ); @@ -11,7 +13,7 @@ our $DEBUG = 2; my $user = shift or die &usage; $FS::Daemon::PID_NEWSTYLE = 1; -daemonize1('freeside-torrus-srvderive'); +daemonize1('torrus-srvderive'); drop_root(); @@ -39,45 +41,166 @@ my $other_date = concat_sql([ 'other.srv_date', "' '", 'other.srv_time' ]); $other_date = "CAST( $other_date AS TIMESTAMP )" if driver_name =~ /^Pg/i; $other_date = str2time_sql. $other_date. str2time_sql_closing; +my $in = concat_sql([ '?', "'_IN'" ]); +my $out = concat_sql([ '?', "'_OUT'" ]); + my $sql = " SELECT DISTINCT srv_date, srv_time FROM srvexport WHERE NOT EXISTS ( SELECT 1 FROM srvexport AS other - WHERE other.serviceid IN ( ?||'_IN', ?||'_OUT') + WHERE other.serviceid IN ( $in, $out ) + AND srvexport.srv_date = other.srv_date AND ABS( $_date - $other_date ) <= 60 ) - ORDER BY id "; -while (1) { +my $orderlimit = " + ORDER BY srv_date, srv_time + LIMIT 50 +"; + - #my $found = 0; +MAIN: while (1) { - foreach my $torrus_srvderive ( qsearch('torrus_srvderive', {}) ) { + my $found = 0; + + SERVICEID: foreach my $torrus_srvderive ( qsearch('torrus_srvderive', {}) ) { my $serviceid = $torrus_srvderive->serviceid; - warn $sql if $DEBUG > 1; - my $sth = dbh->prepare($sql) or die $DBI::errstr; #better recovery? - $sth->execute($serviceid, $serviceid) or die $sth->errstr; + my @serviceids = $torrus_srvderive->component_serviceids; + next unless @serviceids; #don't try to search for empty virtual ports + + my @in = (); + for my $dir ('_IN', '_OUT') { + push @in, map dbh->quote("$_$dir"), @serviceids; + } + my $in = join(',', @in); + + if ( ! $torrus_srvderive->last_srv_date ) { + warn "finding initial last_srv_date for $serviceid\n" if $DEBUG; + my $dsql = "SELECT srv_date FROM srvexport WHERE serviceid IN ($in) + ORDER BY srv_date LIMIT 1"; + my $dsth = dbh->prepare($dsql) or die $DBI::errstr; + $dsth->execute or die $dsth->errstr; + my $date = $dsth->fetchrow_arrayref->[0]; + if ( $date ) { + warn "found initial last_srv_date of $date; updating $serviceid\n" + if $DEBUG; + $torrus_srvderive->last_srv_date($date); + my $error = $torrus_srvderive->replace; + die $error if $error; + } else { + warn "no initial last_srv_date for $serviceid; skipping\n" if $DEBUG; + next; + } + } + + my $ssql = " + $sql AND EXISTS ( + SELECT 1 FROM srvexport AS other + WHERE other.serviceid IN ($in) + AND srvexport.srv_date = other.srv_date + AND ABS( $_date - $other_date ) <= 60 + ) + "; + + $ssql .= " AND srv_date >= '". $torrus_srvderive->last_srv_date. "' " + if $torrus_srvderive->last_srv_date; + + $ssql .= $orderlimit; + + warn "searching for times to add $serviceid\n" if $DEBUG; + warn $ssql if $DEBUG > 2; + my $sth = dbh->prepare($ssql) or die $DBI::errstr; #better recovery here? + + eval { + my $h = set_sig_handler( 'ALRM', sub { die "_timeout\n"; } ); + alarm(10*60); #5*60); #$torrus_srvderive->last_srv_date ? 5*60 : 15*60); + $sth->execute($serviceid, $serviceid) or die $sth->errstr; + alarm(0); + }; + alarm(0); + + if ( $@ && $@ eq "_timeout\n" ) { + warn "search timed out; reconnecting and restarting\n"; + dbh->clone()->do("KILL QUERY ". dbh->{"mysql_thread_id"}) + if driver_name eq 'mysql'; + dbh->rollback; #or die dbh->errstr; + adminsuidsetup($user); + next SERVICEID; #MAIN; + } elsif ( $@ ) { + die $@; + } + + warn "search finished; checking results\n" if $DEBUG; + my $prev = 0; while ( my $row = $sth->fetchrow_arrayref ) { + last if sigterm() || sigint(); + my( $srv_date, $srv_time ) = @$row; my $cur = str2time( "$srv_date $srv_time" ); next if $cur-$prev <= 60; - + last if time - $cur <= 300; + warn "no $serviceid for $srv_date $srv_time; adding\n" if $DEBUG; - + $found++; + + for my $dir ('_IN', '_OUT') { + + my $sin = join(',', map dbh->quote("$_$dir"), @serviceids); + + my $sum = " + SELECT COALESCE(SUM(value),0) FROM srvexport AS other + WHERE other.serviceid IN ($sin) + AND ABS( $cur - $other_date ) <= 60 + "; + + my $isql = " + INSERT INTO srvexport ( srv_date, srv_time, serviceid, value, intvl ) + VALUES ( ?, ?, ?, ($sum), ? ) + "; + my @param = ( time2str('%Y-%m-%d', $cur), #srv_date + time2str('%X', $cur), #srv_time + "$serviceid$dir", + 300, #intvl ... + ); + warn $isql. ' with param '. join(',',@param). "\n" + if $DEBUG > 2; + + my $isth = dbh->prepare($isql) or die $DBI::errstr; #better recovery? + + #stupid mysql deadlocks all the time on insert, so we need to recover + unless ( $isth->execute(@param) ) { + warn "Error inserting data for $serviceid$dir (restarting): ". + $isth->errstr; + dbh->rollback; #or die dbh->errstr; + sleep 5; + next SERVICEID; #MAIN; + } + + } + + if ( $srv_date ne $torrus_srvderive->last_srv_date ) { + warn "updating last_srv_date of $serviceid to $srv_date\n" if $DEBUG; + $torrus_srvderive->last_srv_date($srv_date); + my $error = $torrus_srvderive->replace; + die $error if $error; + } + dbh->commit or die dbh->errstr; $prev = $cur; } + warn "done with $serviceid\n" if $DEBUG; - } - + } #foreach my $torrus_srvderive + dbh->commit or die dbh->errstr; myexit() if sigterm() || sigint(); - sleep 60; #unless $found + warn "restarting main loop\n" if $DEBUG > 1; + sleep 60 unless $found; } sub _shouldrun {