MySQL client got out of memory

[slighltywhacked]

?? what the??....


MySQL client got out of memory


anyone have any ideas on this error...??
i ran it through the serach at mysql.com and it came up with no results. I have no clue what it means.. its sounds like a database is full or something...


any ideas?

thanx

[qslack]

The mysql client needs more memory. You can kill other apps or you can buy more memory.

[slighltywhacked]

this is what my server dude said

"As for the out of memory, you are using too many variables in your PHP script for the server to handle. There is an 8MB Memory allowance for each instance of a script, it will cause workload issues on the server if we allow any more memmory, so we can not increase the limit."

Im not the server admin, so I dont have access to shut down any programs or add memory. However my code does not have verymany variables...

What it does is open up a csv file and takes the records and then inserts them into a database, depending if its in there already.. it also links it to a category which has previously been entered into the database.. heres the code

<?php

include "vars.php3";

$db =mysql_pconnect("localhost", "$mysql_username", "$mysql_password")
        or die("Unable to connect".mysql_error());

mysql_select_db("$database",$db)
    or die("lINE 6".mysql_error());


    
    
    
$fp = fopen("upload.csv", "r")
or die("error");



while (!feof($fp))
{
    
    $fp2 = fgetcsv($fp, 200, ",");
    $uid++;
    $name = $fp2[0];
    $address = $fp2[1];
    $phone = $fp2[2];
    
    $subcat[1]= $fp2[4];
    $subcat[2]= $fp2[5];
    $subcat[3]= $fp2[6];
    $subcat[4]= $fp2[7];
    


    $result=mysql_query("select * from users where name= '$name' AND address='$address'")
        or die ("error occured line 120, the last name enttered was $name".mysql_error());

    $test = mysql_affected_rows();

    if ($test == 0)
    {
        $result=mysql_query("insert into users values ($uid, '$name','$phone','$address','','','0','','')")
                    or die(mysql_error());
        echo"$name ($uid) <br>";
    }
    $y=0;
    while ($y < 5)
    {
        $y++;
        $result=mysql_query("select * from users, category, lookup WHERE users.uid = lookup.uid AND category.cid=lookup.cid AND users.name='$name' AND category.value='$subcat[$y]'");
            
        $test=mysql_affected_rows();
        if ($test == 0)
        {
            $result = mysql_query("select * from category where value='$subcat[$y]'");
            
            $row=mysql_fetch_array($result);
            $cid=$row[cid];
            $result=mysql_query("insert into lookup values ($uid, $cid)");
            

        }
            
        
            
    }
    
}

3 tables are involved

users, which contains information like names, phone numbers and such
category wich has to collums cid and value

then lookup, whcih links the two primary keys together

lookup(uid, cid) //user id and category id


is my code really unefficient... its only getting through about 100 or so rows in hte csv before it dies on line

$result=mysql_query("select * from users where name= '$name' AND address='$address'")
        or die ("error occured line 120, the last name enttered was $name".mysql_error()); 

[freddydoesphp]

Yes its a lot of queries, but I think the reason you are having problems is you are in some sort of infinite loop. Because you run a select statement first then use mysql_affected_rows(). Then you test that number to see if its equal to 0. Its always gonna be 0 whether or not a record is found in the first select statement. mysql_affected_rows() only works against UPDATE, DELETE and INSERT statements, you need to use mysql_num_rows() when counting rows from a select statement. That goes for all three times you used it in here.

Thats part of the problem.

[slighltywhacked]

I changed the mysql_affected_rows and im still not getting much farther... , I think I got an extra 2 records in before it died again

I also dont see how that can cause an infinite loop seen as how im using the eof as the check thingy. the number of rows has no effect on how many times the loop is repeated

[slighltywhacked]

the admin guys said it isnt running because there isnt enough memory. Is each query stored and seen as how im doing so many queries its eating up the memory to fast?
if this is the case (taking a stab in the dark)
is there a way to clear the memory every 100 entreries or so?

[freddydoesphp]

I think you need to rethink your process here instead of running all those queries. Let me see what I can come up with.

[freddydoesphp]

Okay so I though of a couple things. Are all the names in the users table unique, if so you coul load them into an array first then see if the name from the csv file is in the array. This would cut out a bunch of queries. Now instead of running that select for every line in the file you only run it once at the beginning. Also on the query that you run that joins three tables, you could just grab a count(*) value instead of all the records. it will be faster.

DISCLAIMER: I DID NOT TEST THIS, BUT IT SHOULD WORK IN THEORY.

<?php

include "vars.php3";

$db =mysql_pconnect("localhost", "$mysql_username", "$mysql_password")
        or die("Unable to connect".mysql_error());

mysql_select_db("$database",$db)
    or die("lINE 6".mysql_error());

//Grab all current names and stick them in an array
$result=mysql_query("select * from users where name= '$name' AND address='$address'")
        or die (mysql_error());
while($row = mysql_fetch_array($result)) {
    $names[] = $row['name'];
    }    
    

$fp = fopen("upload.csv", "r")
or die("error");

while (!feof($fp))
{
    
    $fp2 = fgetcsv($fp, 200, ",");
    $uid++;
    $name = $fp2[0];
    $address = $fp2[1];
    $phone = $fp2[2];
    
    $subcat[1]= $fp2[4];
    $subcat[2]= $fp2[5];
    $subcat[3]= $fp2[6];
    $subcat[4]= $fp2[7];
    


    if (!in_array($name, $names))
    {
        $result2=mysql_query("insert into users values ($uid, '$name','$phone','$address','','','0','','')")
                    or die(mysql_error());
        echo"$name ($uid) <br>";
    }
    $y=1;
    while ($y < 5)
    {

        $result3=mysql_query("select COUNT(*) as totalnum from users, category, lookup WHERE users.uid = lookup.uid AND category.cid=lookup.cid AND users.name='$name' AND category.value='$subcat[$y]'");
        $test=mysql_result($result3,0);
        if ($test == 0)
        {
            $result4 = mysql_query("select * from category where value='$subcat[$y]'");
            $row=mysql_fetch_array($result4);
            $cid=$row[cid];
            $result5=mysql_query("insert into lookup values ($uid, $cid)");
            

        }
            
    $y++;        
            
    }
    
}

?>

[slighltywhacked]

there is in an error

Fatal error: Call to unsupported or undefined function in_array() in /www/welcometokelowna/directory/admin/upload.php3 on line 191

hers the code where it died

if (!in_array($name, $names))
    {
        $result2=mysql_query("insert into users values ($uid, '$name','$phone','$address','','','0','','')")
                    or die(mysql_error());
        echo"$name ($uid) <br>";
    } 

[freddydoesphp]

Wow, is your version of php considered an artifact in_array() is a very useful function that is new in PHP4. I would see what your host thinks about updating PHP on your server.

[slighltywhacked]

Hey, I ended up compiling the script on my own home machine, and it got all the entries into my home database.. now I just outputed the database to a .sql file... it just has like 40,000 insert statements.. no logic.. just straight sql

Ususaly when I have files like this, I use phpMyAdmin, but the file is to big (over 2 megs of text) and phpmyadmin is just screwin up

I know there is another way to do this, but I dont know how. I know its done a the command prompt though


any help would be great


thank you

[freddydoesphp]

Upload it to your server with FTP.

Login with telnet or SSH.

issue the following command where /path/to/yourfile.sql is the correct path and name of the file and dbname is the name of your database and username is your username.


$> mysql -u username -p dbname < /path/to/yourfile.sql