Return to cleanup_database.pl CVS log

Up to [LON-CAPA] / loncom / metadata_database

- Lifetime of temporary md5_* tables (used to speed up access to student
  performance data, e.g., for Chart or Statistics), can be set as domain
  default (based on course type), and be overridden by a DC in a course.

    1: #!/usr/bin/perl
    2: # The LearningOnline Network
    3: # cleanup_database.pl Remove stale temporary search results.
    4: #
    5: # $Id: cleanup_database.pl,v 1.8 2016/08/15 18:01:14 raeburn Exp $
    6: #
    7: # Copyright Michigan State University Board of Trustees
    8: #
    9: # This file is part of the LearningOnline Network with CAPA (LON-CAPA).
   10: #
   11: # LON-CAPA is free software; you can redistribute it and/or modify
   12: # it under the terms of the GNU General Public License as published by
   13: # the Free Software Foundation; either version 2 of the License, or
   14: # (at your option) any later version.
   15: #
   16: # LON-CAPA is distributed in the hope that it will be useful,
   17: # but WITHOUT ANY WARRANTY; without even the implied warranty of
   18: # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
   19: # GNU General Public License for more details.
   20: #
   21: # You should have received a copy of the GNU General Public License
   22: # along with LON-CAPA; if not, write to the Free Software
   23: # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
   24: #
   25: # /home/httpd/html/adm/gpl.txt
   26: #
   27: # http://www.lon-capa.org/
   28: #
   29: #################################################
   30: 
   31: =pod
   32: 
   33: =head1 NAME
   34: 
   35: cleanup_database.pl - Remove temporary tables from the LON-CAPA MySQL database.
   36: 
   37: =head1 SYNOPSIS
   38: 
   39: cleanup_database.pl drops tables from the LON-CAPA MySQL database if their 
   40: comment is 'temporary' and they have not been modified in a given number 
   41: of seconds.
   42: 
   43: =head1 DESCRIPTION
   44: 
   45: There are two command line arguements possible.  
   46: 
   47: =over 4
   48: 
   49: =item help 
   50: 
   51: Display a brief help message.
   52: 
   53: =item killtime <time>
   54: 
   55: The time in seconds that must have passed since the last update of a table
   56: before it will be dropped.
   57: 
   58: =back
   59: 
   60: The following invocation will drop all tables without updates in the past
   61: two days (the default).
   62: 
   63:  cleanup_database.pl --killtime 172800
   64: 
   65: If you desire the immediate cleanup of temporary tables, use the following:
   66: 
   67:  cleanup_database.pl --killtime 0
   68: 
   69: Depending on permissions, you may have to run this script as root.
   70: 
   71: =cut
   72: 
   73: #################################################
   74: 
   75: use strict;
   76: use lib '/home/httpd/lib/perl/';
   77: use LONCAPA::Configuration;
   78: use Apache::lonnet;
   79: use Getopt::Long;
   80: use Time::Local;
   81: use DBI;
   82: use Digest::MD5();
   83: 
   84: my $help = 0;
   85: my $killtime;
   86: GetOptions( "killtime=s" => \$killtime, 
   87:             "help"       => \$help );
   88: if ($help) {
   89:     print <<ENDHELP;
   90: cleanup_database.pl     Cleans up the LON-CAPA MySQL database by removing 
   91:                         temporary tables.
   92: Command line arguements
   93:    --killtime  <number>     The number of seconds a temporary table is allowed
   94:                             to live. On a library server, specifying this argument
   95:                             also overrides any course-specific or domain-specific
   96:                             lifetimes which apply to the various md5_hash_* tables
   97:                             which contain student data for a course, for which the
   98:                             current server is the homeserver.
   99:    --help                   Print out this help message.
  100: 
  101: Examples:
  102: 
  103: cleanup_database.pl --killtime 0
  104: cleanup_database.pl --killtime 86400
  105: 
  106: Note:  You will probably need to execute this script as root.
  107: 
  108: ENDHELP
  109:     exit;
  110: }
  111: 
  112: # ---------------  Read loncapa_apache.conf and loncapa.conf and get variables
  113: my %perlvar = %{&LONCAPA::Configuration::read_conf('loncapa.conf')};
  114: delete $perlvar{'lonReceipt'}; # remove since sensitive and not needed
  115: 
  116: my $dbh;
  117: # ------------------------------------- Make sure that database can be accessed
  118: unless ($dbh = DBI->connect("DBI:mysql:loncapa","www",
  119:                             $perlvar{'lonSqlAccess'},
  120:                             {RaiseError=>0,PrintError=>0}
  121:                             )
  122:         ) {
  123:     print "Cannot connect to database!\n";
  124:     exit;
  125: }
  126: 
  127: my $sth = $dbh->prepare("SHOW TABLE STATUS");
  128: $sth->execute();
  129: my $results = $sth->fetchall_hashref('Name');
  130: 
  131: my ($nokilltime,$gotconf,%coursetypes,%md5hashes,%domcrsdefs,%gotcourseenv,%crssetting);
  132: if ($killtime eq '') {
  133:     $killtime = 86400*2;
  134:     if ($perlvar{'lonRole'} eq 'library') {
  135:         $nokilltime = 1; 
  136:     }
  137: }
  138: 
  139: foreach my $name (keys(%$results)) {
  140:     next if ($results->{$name}{Comment} ne 'temporary');
  141:     my $tabletime = $results->{$name}{Update_time};
  142:     # Times are like: 2002-07-25 10:17:08
  143:     my ($year,$month,$day,$hour,$min,$sec)= 
  144:         ($tabletime =~ /(\d+)-(\d+)-(\d+) (\d+):(\d+):(\d+)/);
  145:     my $epoch_seconds = timelocal($sec,$min,$hour,$day,$month-1,$year-1900);
  146:     my $currkilltime = $killtime;
  147:     if ($nokilltime) {
  148:         if ($name =~ /^md5_(\w+)_(?:\w+)$/) {
  149:             my $hashid = $1;
  150:             unless ($gotconf) {
  151:                 &get_config(\%coursetypes,\%md5hashes,\%domcrsdefs);
  152:                 $gotconf = 1;
  153:             }
  154:             if (exists($md5hashes{$hashid})) {
  155:                 my ($cdom,$cnum) = split(/_/,$md5hashes{$hashid});  
  156:                 unless ($gotcourseenv{$md5hashes{$hashid}}) {
  157:                     my %envhash = &Apache::lonnet::dump('environment',$cdom,$cnum);
  158:                     $gotcourseenv{$md5hashes{$hashid}} = 1;
  159:                     if ($coursetypes{$md5hashes{$hashid}} eq 'unofficial') {
  160:                          if ($envhash{'internal.textbook'}) {
  161:                              $coursetypes{$md5hashes{$hashid}} = 'textbook';
  162:                          }
  163:                     }
  164:                     $crssetting{$md5hashes{$hashid}} = $envhash{'internal.mysqltables'};
  165:                 }
  166:                 if (($crssetting{$md5hashes{$hashid}} ne '') && ($crssetting{$md5hashes{$hashid}} !~ /^\D/)) {
  167:                     $currkilltime = $crssetting{$md5hashes{$hashid}};
  168:                 } elsif (ref($domcrsdefs{$cdom}) eq 'HASH') {
  169:                     if (($domcrsdefs{$cdom}{$coursetypes{$md5hashes{$hashid}}} ne '') &&
  170:                         ($domcrsdefs{$cdom}{$coursetypes{$md5hashes{$hashid}}} !~ /^\D/)) {
  171:                         $currkilltime = $domcrsdefs{$cdom}{$coursetypes{$md5hashes{$hashid}}};
  172:                     }
  173:                 }
  174:             }
  175:         }
  176:     }
  177:     if ((time - $epoch_seconds) > $currkilltime) {
  178:         $dbh->do('DROP TABLE '.$name);
  179:     }
  180: }
  181: $sth->finish();
  182: 
  183: # --------------------------------------------------- Close database connection
  184: $dbh->disconnect;
  185: 
  186: sub get_config {
  187:     my ($coursetypes,$md5hashref,$domsettings) = @_;
  188:     my @domains = sort(&Apache::lonnet::current_machine_domains());
  189:     my @ids=&Apache::lonnet::current_machine_ids();
  190:     foreach my $dom (@domains) {
  191:         my %domconfig = &Apache::lonnet::get_dom('configuration',['coursedefaults'],$dom);
  192:         if (ref($domconfig{'coursedefaults'}) eq 'HASH') {
  193:             if (ref($domconfig{'coursedefaults'}{'mysqltables'}) eq 'HASH') {
  194:                 $domsettings->{$dom} = $domconfig{'coursedefaults'}{'mysqltables'};
  195:             }
  196:         }
  197:         my %currhash = &Apache::lonnet::courseiddump($dom,'.',1,'.','.','.',1,\@ids,'.');
  198:         if (keys(%currhash)) {
  199:             foreach my $key (keys(%currhash)) {
  200:                 if ($key ne '') {
  201:                     if (ref($currhash{$key}) eq 'HASH') {
  202:                         my $digest = &Digest::MD5::md5_hex($key);
  203:                         $md5hashref->{$digest} = $key;
  204:                         my $crstype = $currhash{$key}{'type'}; 
  205:                         my $longcrstype = 'unofficial';
  206:                         if ($crstype eq 'Community') {
  207:                             $longcrstype = 'community';
  208:                         } elsif ($crstype eq 'Placement') {
  209:                             $longcrstype = 'placement';
  210:                         } elsif ($currhash{$key}{'inst_code'}) {
  211:                             $longcrstype = 'official';
  212:                         }
  213:                         $coursetypes->{$key} = $longcrstype;
  214:                     }
  215:                 }
  216:             }
  217:         }
  218:     }
  219:     return;
  220: }
  221: