squidalyser 0.2.55对时间戳处理的bug

xjjjk

mysql5、4 和mysql3对时间戳的处理方式问题，前期用squidalyser.pl将squid日志导入到mysql 3中，对于时间戳字段的处理都没有问题，现在mysql server 3升级到5后，发现mysql 5、4对时间戳的处理方式和mysql 3很不同，导入的时间戳都变成了1970-01-01，squidalyser.pl认为导入的数据都是过期数据，导入完成就全部删除了，导致无法分析和查看代理服务器上网日志，在squid官方网站也有人提出此问题，但是也没有解决办法，该如何解决这个问题，请大家帮忙解决相关问题；
以下是导入数据的脚本和删除过期数据的脚本squidalyser.pl，这个程序目前原作者已经停止更新；
#!/usr/bin/perl
use strict;
use DBI;
use Time::ParseDate;
my $t0 = time;
my (%conf, $dbh);
open (CONF, ") {
    chomp($line);
    $line =~ /^\#/ and next;
    $line =~ /^$/ and next;
    my ($varname, $varvalue) = split(/\s+/, $line);
    $conf{$varname} = $varvalue;
}
close CONF;
my $now = localtime();
print qq|
Running $0 at $now
DB Name:    $conf{dbname}
DB Host:    $conf{dbhost}
DB User:    $conf{dbuser}
Squidlog:    $conf{squidlog}
|;
dbconnect($conf{dbhost}, $conf{dbname}, $conf{dbuser}, $conf{dbpass});
my $query = 'SELECT MAX(time) FROM logfile';
my $sth = $dbh->prepare($query);
$sth->execute;
my $lastrun = $sth->fetchrow;
$sth->finish;
$query = qq|
    INSERT INTO logfile (remotehost,rfc931,authuser,request,status,bytes,time)
    VALUES ( ? , ? , ? , ? , ?, ? , ? )
|;
$sth = $dbh->prepare($query);
my $count;
open (LOG, ") {
    my $seconds;
    if ($line =~ /\s+\[(\d{2,2}.*?)\]\s+/) {
        $seconds = parsedate($1);
    } else {
        $line =~ /^(\d{10,10})\.\d{3,3}/;
        $seconds = $1;
    }
    if ($seconds >= $lastrun) {
        my ($remhost, $rfc931, $date, $request, $status, $bytes) = parseline($line);
        insertinlog($remhost, $rfc931, $date, $request, $status, $bytes, $line);
        $lastrun = $seconds;
        $count++;
    }
}
&expire_db;
&dbdisconnect;
my $t1 = time;
my $elapsed = $t1 - $t0;
print qq|Took $elapsed seconds to process $count records.\n|;
exit 0;
sub insertinlog {
    my ($remhost, $rfc931, $date, $request, $status, $bytes, $line) = @_;
    $bytes = 0 unless ($bytes =~ /\d+/);
    $status = 0 unless ($status =~ /\d+/);
    $sth->execute($remhost,$rfc931,'-',$request,$status,$bytes, $date) or do {
        print $line;
        print $query;
        die $!;
    };
    $sth->finish;
    return;
}
sub parseline {
    my $line = shift or return;
    my @f = split(/\s+/, $line);
    if ($line =~ /^(\d{10,}\.\d{3,3})/) {
        # squid logfile format
        my $sec = $1;
        my $sta = $1 if ($line =~ /TCP.*?\/(\d{3,3})/);
        # If we're using NTLM authentication, we have username in DOMAIN\User format,
        # so split domain and user (authuser) at \ and store username back in $f[7].
        # Patch submitted by Neil M. and hacked a bit by me :) I don't use NTLM so if
        # you spot an error with this one let me know.
        if ($f[7] =~ /\\/) {
            my ($domain, $user) = split(/\\/,$f[7]);
            if ($conf{include_domain} eq 'y') {
                $f[7] = qq|$domain:$user|;
            } else {
                $f[7] = $user;
            }
        }
        return ($f[2], $f[7], $sec, $f[6], $sta, $f[4]);
    } else {
        # http logfile format
        my $date = "$f[3] $f[4]";
        $date =~ s/\[|\]//g;
        my $sec = parsedate($date);
        $f[6] =~ s/"//g;
        print $f[0];
        print $f[1];
        
        return ($f[0], $f[1], $sec, $f[6], $f[8], $f[9]);
    }
}
sub expire_db {
    my ($exp_val, $exp_unit) = split(/\_/, $conf{expire});
    unless ($exp_val > 0) {
        print qq|Error in config file: `expire $conf{expire}'.\nNo records expired from database.|;
        return;
    }
    if ($exp_unit eq 'h') {
        $conf{factor} = 3600;
    } elsif ($exp_unit eq 'd') {
        $conf{factor} = 86400
    } elsif ($exp_unit eq 'w') {
        $conf{factor} = 604800;
    }
    my $nowsec = parsedate($now);
    my $expire_before = $nowsec - ($conf{factor} * $exp_val);
    my $query = qq|DELETE FROM logfile WHERE time prepare($query);
    $sth->execute or do {
        print $query;
        die $!;
    };
    my $rows = $sth->rows;
    print qq|\nExpired $rows records from the database.\n|;
}
sub dbconnect {
    my ($dbhost, $dbname, $dbuser, $dbpass) = @_;
    $dbh = DBI->connect("DBI:mysql:$dbname:$dbhost",$dbuser,$dbpass) or die (DBI::errstr);
}
sub dbdisconnect {
    $sth and $sth->finish;
    ($dbh and $dbh->disconnect) or die (DBI::errstr);
}


本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u2/63677/showart_497266.html