CODEWITHSUNDEEP
mysqlxmlperl
Welcho
Welcho

Reputation: 87

Perl and MySQL Inserting an XML string causes error 2006 "server gone away"

I am trying to insert a XML string into this table:

CREATE TABLE producto_xml ( Id int(11) unsigned NOT NULL DEFAULT '0', FechaDescarga datetime NOT NULL, XML longtext,
PRIMARY KEY (Id), CONSTRAINT producto_xml_ibfk_1 FOREIGN KEY (Id) REFERENCES producto (Id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED

And this is my script:

#!/usr/bin/perl

use strict;
use DBI;
use XML::LibXML;

my $xmlfilename = 'E:/localhost/erp/XMLs/TPT/BigFile.xml';

my $MyDB = 'XXXXX';
my $MyHOST = 'localhost';
my $MyUSR = 'XXXXX';
my $MyPASS = 'XXXXX';

my $dbh = DBI->connect("DBI:mysql:$MyDB:$MyHOST",$MyUSR,$MyPASS, { AutoCommit => 1, RaiseError => 0, PrintError => 0 } ) or &ErrHandle('MySQL: connecting to DB.');
$dbh->do('SET NAMES utf8');

my $max_allowed_packet = ((1024*1024)*15);#15 mb
$dbh ->do('SET @max_allowed_packet='.$max_allowed_packet); 

my $parser = XML::LibXML->new();
my $doc;
eval { $doc = $parser->parse_file($xmlfilename);};
if ($@){ &ErrHandle('XML Parser: Incorrect XML File');}

print 'File length: ' . length($doc->toString(0)) . "\n";

my $St = 'INSERT INTO producto_xml (XML, FechaDescarga, Id) VALUES (?,NOW(), ?) ON DUPLICATE KEY UPDATE XML=VALUES(XML), FechaDescarga=NOW()';

my $Sth =$dbh->prepare($St);
$Sth->execute(
    $doc->toString(0)
    ,1780
);

if ($dbh->{'mysql_errno'} > 0) { &ErrHandle('MySQL: ' . $dbh->{'mysql_errno'} . ': ' . $dbh->{'mysql_error'}); }

print $dbh->{'mysql_insertid'} ."\n";
print $Sth->rows; 
exit;

sub ErrHandle {
    my $Err = shift;
    print 'ERROR: ' . $Err;
    exit;
}

The file "BigFile.xml" is about 1.2 Mb and I have this error no matter what i try:

File length: 1207951
ERROR: MySQL: 2006: MySQL server has gone away

I have tryed:

I have used the same script to insert files from about 300 Kb. without issues.

I will apreciate any help.

Enrique

Upvotes: 2

Views: 154

Answers (2)

Sathish D
Sathish D

Reputation: 5034

Information for any one how to change of max_allowed_packet

Change in the my.ini/my.cnf file. Include the single line under [mysqld] in your file

max_allowed_packet=500M

now restart the MySQL service once you are done. You can see it's curent value in mysql like this:

SHOW VARIABLES LIKE 'max_allowed_packet'

You can read about it here http://dev.mysql.com/doc/refman/5.1/en/packet-too-large.html

Upvotes: 0

Grrrr
Grrrr

Reputation: 2536

As AKHolland hinted, max_allowed_packet should be set server-side. Moreover, changing this parameter requires mysqld restart, if I remember correctly.

Upvotes: 1

Related Questions