SQL Error: Server gone away

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Katsu Webs
Forum Newbie
Posts: 5
Joined: Fri Dec 04, 2009 2:20 pm

SQL Error: Server gone away

Post 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
Katsu Webs
Forum Newbie
Posts: 5
Joined: Fri Dec 04, 2009 2:20 pm

Re: SQL Error: Server gone away

Post 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.
Post Reply