SitePoint Sponsor

User Tag List

Results 1 to 12 of 12

Hybrid View

  1. #1
    SitePoint Guru
    Join Date
    Apr 2001
    Location
    BC, Canada
    Posts
    630
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL client got out of memory

    ?? 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

  2. #2
    SitePoint Wizard
    Join Date
    Jul 1999
    Location
    Chicago
    Posts
    2,629
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The mysql client needs more memory. You can kill other apps or you can buy more memory.

  3. #3
    SitePoint Guru
    Join Date
    Apr 2001
    Location
    BC, Canada
    Posts
    630
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 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($fp200",");
        
    $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
    PHP Code:
    $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()); 

  4. #4
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  5. #5
    SitePoint Guru
    Join Date
    Apr 2001
    Location
    BC, Canada
    Posts
    630
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Last edited by slighltywhacked; Aug 28, 2001 at 14:53.

  6. #6
    SitePoint Guru
    Join Date
    Apr 2001
    Location
    BC, Canada
    Posts
    630
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  7. #7
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think you need to rethink your process here instead of running all those queries. Let me see what I can come up with.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  8. #8
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 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());

    //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($fp200",");
        
    $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++;        
                
        }
        
    }

    ?>
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  9. #9
    SitePoint Guru
    Join Date
    Apr 2001
    Location
    BC, Canada
    Posts
    630
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

    PHP Code:
    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>";
        } 

  10. #10
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  11. #11
    SitePoint Guru
    Join Date
    Apr 2001
    Location
    BC, Canada
    Posts
    630
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Last edited by slighltywhacked; Aug 29, 2001 at 16:28.

  12. #12
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Please don't PM me with questions.
    Use the forums, that is what they are here for.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •