DBD::mysql::st execute failed: MySQL server has gone away at

Question

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";"

Answer 1

Just make sure dbh is still connected (ping) before any DB operations and reconnect (or clone)

while (<FIFO>)
{
    if ( ! $dbh->ping ) {
        $dbh = $dbh->clone() or die "cannot connect to db";
    }

...

} #end FIFO

See also http://www.perlmonks.org/?node_id=497849

Answer 2

As an alternative you can use the mysql auto reconnect feature.

$dbh->{mysql_auto_reconnect} = 1;