I want to insert data from a fifo pipe into a mysql table
my script as follows:
#!/usr/bin/perl
#Script to read data out of a named pipe and write to MySQL database.
$| = 1;
use strict;
use DBI();
my $filename;
my $inputline;
my $linenumber;
my @arr;
$filename = "./SEC_fifo";
open(FIFO, "+< $filename") or die "FIFO error on $filename $!";
my $dbh = DBI->connect("DBI:mysql:database=ecdb;host=localhost",
"user", "[pwd]",
{'RaiseError' => 1});
while (<FIFO>)
{
$inputline = $_;
@arr = split(/,/,$inputline);
# Quit read loop when requested.
last if($inputline =~ /quit/i);
chop $inputline;
$linenumber++;
print "Got: [$inputline], ";
my $sql="";
my $sth="";
my @row;
print "output.\n".$arr[0],$arr[1],$arr[2],$arr[3],$arr[4],$arr[5],$arr[6],$arr[7],$arr[8],$arr[9],$arr[10],$arr[11],$arr[12],$arr[13]."\n";
# perl trim function - remove leading and trailing whitespace
my $str = $arr[6] ;
$str =~ s/^\s+//;
$str =~ s/\s+$//;
if($str ne 'Normal')
{
print "arr[6]=".$arr[6]."\n";
$sql = "select Hid from Devices where hostname = '$arr[2]'";
print $sql."\n";
$sth = $dbh->prepare($sql);
$sth->execute || die "Could not execute SQL statement ... maybe invalid? \n\n $sql
\n";
@row=$sth->fetchrow_array;
my $hid1=@row[0];
$sql = "select Hid from Devices where hostname = '$arr[3]'";
$sth = $dbh->prepare($sql);
$sth->execute || die "Could not execute SQL statement ... maybe invalid? \n\n $sql \n";
@row=$sth->fetchrow_array;
my $hid2=@row[0];
$sql = "select Eid from Event where Eventname = '$arr[8]' and severity = '$arr[9]' and Trapoid='$arr[10]'";
print "sql=".$sql."\n";
$sth = $dbh->prepare($sql);
$sth->execute || die "Could not execute SQL statement ... maybe invalid? \n\n $sql \n";
@row=$sth->fetchrow_array;
my $eid1=@row[0];
#print "eid1=".@row[0]."\n";
$sql = "select Eid from Event where Eventname = '$arr[11]' and severity = '$arr[12]' and Trapoid='$arr[13]'";
#print "sql=".$sql."\n";
$sth = $dbh->prepare($sql);
$sth->execute || die "Could not execute SQL statement ... maybe invalid? \n\n $sql \n";
@row=$sth->fetchrow_array;
my $eid2=@row[0];
print "eid1=".$eid1."\n";
print "eid2=".@row[0]."\n";
$sql = "insert into secresult (Hid_1,Hid_2,interface_1,interface_2,ifindex_1,ifindex_2,Eid_1,Eid_2,start_time_1,start_time_2,effect_range,description) values ($hid1,$hid2,'$arr[4]','$arr[5]',$arr[6],$arr[7],$eid1,$eid2,'$arr[0]','$arr[1]','$arr[14]','$arr[15]')";
print $sql."\n";
$dbh->do($sql);
print "inserted it.\n";
}
else
{
$sql = "select Hid from Devices where hostname = '$arr[1]'";
$sth = $dbh->prepare($sql);
$sth->execute || die "Could not execute SQL statement ... maybe invalid? \n\n $sql \n";
@row=$sth->fetchrow_array;
my $hid=@row[0];
$sql = "select Eid from Event where trapoid = '$arr[0]' and severity != 'Normal'";
#print "sql=".$sql."\n";
$sth = $dbh->prepare($sql);
$sth->execute || die "Could not execute SQL statement ... maybe invalid? \n\n $sql \n";
@row=$sth->fetchrow_array;
my $eid=@row[0];
$sql = "select id from (SELECT * FROM secresult WHERE end_time_1 ='' and Hid_1=$hid and interface_1 ='$arr[4]' and ifindex_1=$arr[5] ORDER BY start_time_1 DESC LIMIT 1) result where result.eid_1 = $eid";
print "sql=".$sql."\n";
$sth = $dbh->prepare($sql);
$sth->execute || die "Could not execute SQL statement ... maybe invalid? \n\n $sql \n";
@row=$sth->fetchrow_array;
my $id=@row[0];
if($id !="")
{
$sql = "update secresult set end_time_1 = '$arr[2]' where id = '$id'";
print $sql."\n";
$dbh->do($sql);
print "updated it.\n";
}
else
{
$sql = "select id from (SELECT * FROM secresult WHERE end_time_2 ='' and Hid_1=$hid and interface_2 ='$arr[4]' and ifindex_2=$arr[5] ORDER BY start_time_2 DESC LIMIT 1) result where result.eid_2 = $eid";
print "sql=".$sql."\n";
$sth = $dbh->prepare($sql);
$sth->execute || die "Could not execute SQL statement ... maybe invalid? \n\n $sql \n";
@row=$sth->fetchrow_array;
$id=@row[0];
$sql = "update secresult set end_time_2 = '$arr[2]' where id = '$id'";
print $sql."\n";
$dbh->do($sql);
print "updated it.\n";
}
}
}
my $sth = $dbh->prepare("SELECT * FROM secresult"); $sth->execute(); while (my $ref = $sth->fetchrow_hashref()) {
print "Found a row: id = $ref->{'id'}, line = $ref->{'textline'}\n"; }
$sth->finish();
$dbh->disconnect();
exit;
i can insert into to database by the script,but when i execute the script to listening , after few minute get data from fifo ,the script will be shown the error message "DBD::mysql::st execute failed: MySQL server has gone away at ./Db_code.pl line 66, line 5"
the line 66 is " $sth->execute || die "Could not execute SQL statement ... maybe invalid? \n\n $sql \n";"
