From 5c4f27bd0df7b7557519660d752eeee3c9fb0c7c Mon Sep 17 00:00:00 2001 From: Marcel de Rooy Date: Thu, 28 Oct 2010 09:05:20 -0400 Subject: [PATCH] Enhancement 5074 (Adding possibility to cleanup_database.pl to purge only older sessions) [UPDATED for master] Currently, the misc/cronjobs script cleanup_database truncates the session table (deleting all records, including active sessions). With an additional parameter sessdays, this behavior could be changed or (perhaps better) extended. If the parameter sessdays is passed along with a number of days, the script only deletes older session records. This is accomplished by examining the values of lasttime, atime or ctime in the record. So, calling the script like: ./cleanup_database.pl -v -sessions -sessdays 7 will only delete sessions records older than 7 days. The "old style" call ./cleanup_database.pl -v -sessions still works too and truncates the table as before. Signed-off-by: Galen Charlton Signed-off-by: Chris Cormack --- misc/cronjobs/cleanup_database.pl | 45 ++++++++++++++++++++++++++++--- 1 file changed, 42 insertions(+), 3 deletions(-) diff --git a/misc/cronjobs/cleanup_database.pl b/misc/cronjobs/cleanup_database.pl index f811b249d4..7d5bcd1341 100755 --- a/misc/cronjobs/cleanup_database.pl +++ b/misc/cronjobs/cleanup_database.pl @@ -37,12 +37,13 @@ use Getopt::Long; sub usage { print STDERR < \$help, 'sessions' => \$sessions, + 'sessdays:i' => \$sess_days, 'v|verbose' => \$verbose, 'm|mail' => \$mail, 'zebraqueue:i' => \$zebraqueue_days, @@ -77,7 +79,7 @@ my $sth; my $sth2; my $count; -if ($sessions) { +if ($sessions && !$sess_days) { #old behavior if ($verbose){ print "Session purge triggered.\n"; $sth = $dbh->prepare("SELECT COUNT(*) FROM sessions"); @@ -91,6 +93,15 @@ if ($sessions) { print "Done with session purge.\n"; } } +elsif($sessions && $sess_days>0) { #new behavior with number of days old + if ($verbose){ + print "Session purge triggered with days>$sess_days.\n"; + } + RemoveOldSessions(); + if ($verbose){ + print "Done with session purge with days>$sess_days.\n"; + } +} if ($zebraqueue_days){ $count = 0; @@ -122,3 +133,31 @@ if ($mail) { print "Done with purging the mail queue.\n" if ($verbose); } exit(0); + +sub RemoveOldSessions { + my ($id, $a_session, $limit, $lasttime); + $limit= time() - 24*3600*$sess_days; + + $sth= $dbh->prepare("SELECT id, a_session FROM sessions"); + $sth->execute or die $dbh->errstr; + $sth->bind_columns(\$id, \$a_session); + $sth2 = $dbh->prepare("DELETE FROM sessions WHERE id=?"); + $count=0; + + while ($sth->fetch) { + $lasttime=0; + if($a_session =~ /lasttime:\s+(\d+)/) { + $lasttime= $1; + } + elsif($a_session =~ /(ATIME|CTIME):\s+(\d+)/ ) { + $lasttime= $2; + } + if($lasttime && $lasttime < $limit) { + $sth2->execute($id) or die $dbh->errstr; + $count++; + } + } + if ($verbose){ + print "$count sessions were deleted.\n"; + } +} -- 2.39.5