stew has asked for the wisdom of the Perl Monks concerning the following question:

Hi
I am trying to run thru a directory of PDF's, read each one in turn into memory and then store them in MySQL

I get thru about 8 or so when I get the following error 

DBD::mysql::st execute failed: MySQL server has gone away at insert_pd
+f.pl line 76.
de140202.pdf

[download]


My query looks like this

my $sth = $dbh->prepare(q{insert into ee_pdf(id,data) values (?,?)});

I am reading the pdf like this
      open(FILE,"<".$filename) || die ("Could not open FILE");
      @file_arr = <FILE>;
      close(FILE);

      foreach $line(@file_arr) {
      $data .= $line;
      }

[download]


And executing the query thus..

$sth->execute($id,$data);

Any idea why this might be?
Stew

Replies are listed 'Best First'.
Re: MySQL gone Away
by blokhead (Monsignor) on Oct 14, 2002 at 14:06 UTC
    I had this problem as well while putting large BLOBs into the database. The problem turned out to be that the server had set an upper limit on the amount of data sent at once. If I remember correctly, you can change this in the /etc/mysql/my.cnf file like this: 
    set-variable    = max_allowed_packet=256M
# or something else ridiculously large

    [download]

    blokhead

[reply]
[d/l]
Re: MySQL gone Away
by xtype (Deacon) on Oct 14, 2002 at 14:10 UTC
    You are trying to execute a query larger than your max_allowed_packet perhaps?

    And you could be reading your file with read 
    open(FILE,"<".$filename) || die ("Could not open FILE");
my $size = -s $fname;
my $data;
read FILE, $data, $size;   
$sth->execute($pid, $_, $data, $public) if $data;
close(FILE);

    [download]
    After checking and possibly adjusting your max_allowed_packets (else if you cannot adjust it) you could do something along the lines of this: Handling huge BLOB fields with DBI and MySQL

    -xtype
[reply]
[d/l]
Re: MySQL gone Away
by diotalevi (Canon) on Oct 14, 2002 at 13:58 UTC

    From grepping mysql source it's just a db connection issue. I'd guess you just abended the server-side mysql process somehow and the client noticed the disconnection. Consider looking into your MySQL documentation further to see how you're breaking MySQL. Perhaps your data column cannot accept binary data. Perhaps you need to use a different interface to get your data loaded into the db.

    Update: Hmm... apparently I was incorrect. Oh well.

    __SIG__
printf "You are here %08x\n", unpack "L!", unpack "P4", pack "L!", B::
+svref_2object(sub{})->OUTSIDE

    [download]
[reply]
[d/l]
Re: MySQL gone Away
by bronto (Priest) on Oct 14, 2002 at 16:43 UTC

    Since you already had your MySQL replies, I'll concentrate on your Perl :-)

    Without loss of readability, you could compact your code in:

          open(FILE,"<".$filename) || die ("Could not open FILE");
      $data .= $_ foreach <FILE> ; # for example
      close(FILE);

    [download]

    Ciao!
    --bronto

    # Another Perl edition of a song:
# The End, by The Beatles
END {
  $you->take($love) eq $you->make($love) ;
}

[reply]
[d/l]
      Why iterate, when you can get the whole file in one big slurp? 
      {
    local $/ = undef;
    open(FILE,"<".$filename) || die ("Could not open: $!");
    $data = <FILE>;
    close(FILE);
}

      [download]

      /\/\averick
      OmG! They killed tilly! You *bleep*!!

[reply]
[d/l]

        Only for the sake of KISS, nothing more and nothing less.

        Ciao!
        --bronto

        # Another Perl edition of a song:
# The End, by The Beatles
END {
  $you->take($love) eq $you->make($love) ;
}

[reply]

Back to Seekers of Perl Wisdom