php memory limit cannot be modified

Question

I have a php script that loads data from a database. The table I am loading the data from is 50mb big, and the current php memory settings allow for 128mb - still I get the error message

Allowed memory size of 134217728 bytes exhausted (tried to allocate 6 bytes)

Some research has led me to changing the following in php.ini:

memory_limit = 128M

I changed it to -1, which according to the documentation will set it to infinite. To no avail.

Not only does it not makes sense that even if the query returned all of the 50mb, it should not complain since the limit is set to 128mb - strangely, the query I run does not even need much memory: when I echo it out and run it directly in the database, it only returns three rows. The query looks as follows:

SELECT * FROM table WHERE foreign_id=1

Does anyone know how to fix that problem? I would like to set the memory limit to infinite.

EDIT 1

First of all - does it matter that I run the script from my terminal, not from my browser?

Second, here is the php code:

//Connect to database
$user="root";
$databasePassword="password";
$host="127.0.0.1";
$database="primarydata";
$identifier=mysql_connect($host,$user,$databasePassword,true);
$db1=mysql_select_db($database);

//Select * from first table
$query="SELECT id,name,city,country FROM table1 WHERE id>=1 ORDER BY id ASC";
$result=mysql_query($query);
$datas=array();
while($row=mysql_fetch_object($result)) $datas[]=(object) $row;

//Loop through artists
for($i=0;$i<sizeof($datas);$i++){
    for($j=$i+1;$j<sizeof($datas);$j++){
        //This is where the problem occurs
        $query="SELECT * FROM table2 WHERE data_id=".$datas[$i]->id;
    }
}

Third, do I have to use "--enable-memory-limit'"?

This needs more info. Are you on your own server? What does phpinfo() show when you change the memory limit - maybe you are editing the wrong php.ini? — Pekka
– Pekka, Commented Aug 5, 2012 at 10:47
Perhaps you should be using the result instead of the default of storing the result, see MYSQLI_USE_RESULT in php.net/manual/en/mysqli.query.php. This is why it is taking up 50MB for the result set or more, as PHP is loading the entire result set into memory. — Geoffrey
– Geoffrey, Commented Aug 5, 2012 at 10:49
@Pekka I am on my own local server, and I seem to be editing the correct php.ini file. It does change the memory limit in there. Maybe I can do something with "--enable-memory-limit'"? — Dennis Hackethal
– Dennis Hackethal, Commented Aug 5, 2012 at 10:49
@Charles so the php.ini says there is no limit, yet you get the error message above? That's strange. — Pekka
– Pekka, Commented Aug 5, 2012 at 10:52
it would be helpful if you can show the php code, since there can be a situation which is not related to mysql that is causing the memory issues.. e.g. an endless loop.. — Software Guy
– Software Guy, Commented Aug 5, 2012 at 10:52

Chris · Accepted Answer · 2012-08-05 11:41:45Z

2

Try:

ini_set('memory_limit','-1')

When I insert millions of entries into a database (samples), then I had the same problem as you. This one helped.

EDIT: to add, using this is not recommended (only e.g. on localhost for examples as I gave you, entering tons of sample data into a database, etc.) This answer was posted before you posted your php code...

edited Aug 5, 2012 at 11:41

answered Aug 5, 2012 at 10:56

Chris

3,8567 gold badges38 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Geoffrey Over a year ago

This should never be used as a fix for a problem! Find the reason why your using too much ram, in this case it is poor design of the script.

Chris Over a year ago

@Geoffrey: that's true! I did not suggest that it was the best solution, I just posted this as the answer to his question, at that time he hasn't had his php code posted... so I don't see your reason for the downvote, sorry...

Geoffrey Over a year ago

@Chris - down-vote is because you did not state that this is not the proper solution to the actual problem and should only be used as a stop-gap measure at best.

Chris Over a year ago

@Geoffrey: added it to the answer to make it clear, so others won't use it for things it is not intended for...

Geoffrey · Accepted Answer · 2012-08-05 11:28:39Z

Returning objects will use more RAM
why not just write one while inside the other.
the mysql_* methods are being deprecated, you should use the newer mysqli_* methods.
why load your entire first result set into an array?? There is your 100MB, the MySQL client library has already loaded 50MB into its cache, then your caching it again in PHP.

Example:

//Select * from first table
$query  = "SELECT id, name, city, country FROM table1 WHERE id >= 1 ORDER BY id ASC";
$result = mysqli_query($dbc, $query);
while($row = mysqli_fetch_array($result)) {
    //Loop through artists
    $query2  = "SELECT * FROM table2 WHERE data_id = " . $row['id'];
    $result2 = mysqli_query($dbc, $query2);
    while($artist = mysqli_fetch_array($result2)) {
        // Do stuff with $row and $artist
    }
}

Collectives™ on Stack Overflow

php memory limit cannot be modified

EDIT 1

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

EDIT 1

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related