perl 对时间戳的处理（和mysql5的时间戳兼容性问题）
xjjjk
|
1#
xjjjk 发表于 2008-03-12 13:09
perl 对时间戳的处理（和mysql5的时间戳兼容性问题）
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, "< /usr/local/squidparse/squidalyser.conf") or die $!; while (my $line = <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, "< $conf{squidlog}") or die $!; while (my $line = <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 < $expire_before|; my $sth = $dbh->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); } [[i] 本帖最后由 xjjjk 于 2008-3-15 10:48 编辑 [/i]] |