Page 1 of 1

SQL Error: Server gone away

Posted: Fri Dec 04, 2009 2:30 pm
by Katsu Webs
Hi,

I am relatively new to PHP/MySQL and I have run into an issue.

I am uploading a 950 line xml document into my sql database. It's only 4mb large but I am getting php memory errors and MySql Server gone away errors.

I have expanded my max memory in both to 160mb, which surely should be enough.

I think it is probably how I am processing the document.

here is a snippet of the processing code:

Code: Select all

while($reader->read())
            {
       /*get items x position*/
    if($reader->name == "x" && $reader->nodeType == XMLReader::ELEMENT)
            {
            $reader->read();
            $itemposx = $reader->value;
            }
    /*get items Y position*/
    if($reader->name == "y" && $reader->nodeType == XMLReader::ELEMENT)
            {
            $reader->read();
            $itemposy = $reader->value;
            }
    /*get item owner name*/
    if($reader->name == "ownerName" && $reader->nodeType == XMLReader::ELEMENT)
            {
            $reader->read();
            $itemowner = $reader->value;
            }
    /*get standing to faction*/
    if($reader->name == "iffStatus" && $reader->nodeType == XMLReader::ELEMENT)
            {
            $reader->read();
            $standing = $reader->value;
            }
    /*set scannum*/
        $itemclass = "none";
        $scannum = $dateyear.$dateday.$datehour.$dateminutes;
        $cxn = MYSQLI_CONNECT($dbhost,$dbuser,$dbpass,$dbname) or die ('Databade unavailable::001');
        $itemname = MYSQLI_REAL_ESCAPE_STRING($cxn,$itemname);
        $scandata = "INSERT INTO scandata (dateday,dateyear,galposx,galposy,itemname,itemtype,itemposx,itemposy,itemowner,standing,scannum,itemclass) VALUES ('$dateday','$dateyear','$galposx','$galposy','$itemname','$entitytype','$itemposx','$itemposy','$itemowner','$standing','$snamnum','$itemclass')";    
$sendscandata = MYSQLI_QUERY($cxn,$scandata) or die (mysqli_error($cxn));
 
}
I have chopped out a lot of the if statements, there are around 13 all together. As I have never processed such a big file in an iterative way I could do with some advice as to how to speed it up, reduce the memory requirements and make it work!

It uploads 167 records and falls over, either with php max_memory error or with mysql server gone away error.

Please remember I am a new comer to php so may not understand every thing you say.

/*edit*/ I have checked through the data that actually uploaded and it seems the escape string finction is stuck in an infinite loop on one of the variables.

out put looks like this:

AEL Big'n'Speedy
AEL Big\'n\'Speedy
AEL Big\\\'n\\\'Speedy
AEL Big\\\\\\\'n\\\\\\\'Speedy
AEL Big\\\\\\\\\\\\\\\'n\\\\\\\\\\\\\\\'Speedy

And so one.

Not sure why this is happening, does any one have any ideas?

Kind Regards

Katsu

Re: SQL Error: Server gone away

Posted: Sat Dec 05, 2009 4:54 am
by Katsu Webs
Never mind. My newbyness astounds me.

It was just loop logic that was falling over. I forgot to output to the database only when I have received the end tag of the elements I was looking at, resulting in sending output to the database on every tag, rather than only after picking up all the info between two specific tags.