Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I am using an update function, where I insert some 40,000 rows to a mysql database. While making that array, I am getting out of memory error (tried to allocate 41 bytes).

The final function is like this:

function Tright($area) {
   foreach ($area as $a1=>&$a2) {
       mysql_query('INSERT INTO 0_right SET section=\''.$a2['sec_id'].'\', user_id=\''.$a2['user_id'].'\', rank_id=\''.$a2['rank_id'].'\', menu_id=\''.$a2['menu_id'].'\', droit = 1;');
   }
}

I have two questions. Is it natural that this above work load becomes too much for php to handle?

If no, can anyone suggest where should I check? And if yes, is there a way to break that $area array to subarrays and execute the function, maybe that way I won't get the out of memory issue. Any other workaround?

Thanks guys.

Edit: @halfdan, @Patrick Fisher, both of you have spoken about making a single multi insert query. How do you do that, in this example please.

share|improve this question
1  
What version of PHP are you using? Looping over SQL statements is generally very slow, you should probably to a multi insert. How much memory do you give your PHP instance? –  halfdan Mar 9 '11 at 23:01
 
Are you speaking about this one > electrictoolbox.com/mysql-insert-multiple-records –  Jeremy Roy Mar 9 '11 at 23:13
 
I know this is long closed, but the problem was the foreach ($area as $a1=>&$a2) where the & will cause php to remember all $a2 arrays. And you don't even use the & –  BazzPsychoNut Nov 6 '12 at 15:51

3 Answers

up vote 3 down vote accepted

First, you should combine all the values into a single INSERT statement, instead of 40,000 different queries.

Second, yes, it is quite natural that you are running out of memory. You can increase this limit at runtime with ini_set(), e.g. ini_set('memory_limit', '16M');

To insert multiple values at once, your SQL should look something like this:

INSERT INTO 0_right (section, user_id, rank_id, menu_id, droit) VALUES
(1,1,1,1,1),
(1,2,1,1,1),
(1,3,1,1,1)

You can build the query like so:

$values = '';
foreach ($area as $a){
    if ($values != ''){
        $values .= ',';
    }
    $values .= "('{$a['sec_id']}', '{$a['user_id']}', '{$a['rank_id']}', '{$a['menu_id']}',  1)";
}
$sql = "INSERT INTO 0_right (section, user_id, rank_id, menu_id, droit) VALUES $values";
share|improve this answer
 
@atrick Fisher: How do I combine all the values into a single INSERT statement in this example, please? –  Jeremy Roy Mar 9 '11 at 23:09
 
Well @Patrick Fisher, on doing mysql_query, I'm getting Got a packet bigger than 'max_allowed_packet' bytes –  Jeremy Roy Mar 9 '11 at 23:41
 
Solved with the server guys. ;) –  Jeremy Roy Mar 10 '11 at 0:31
up vote 1 down vote

These are all nice answers to get around the problem. If this is a one time script, just bump up the RAM and make sure the script doesn't time out (max_execution_time in the php.ini file) and you should be fine.

It may run faster if it was one big insert statement, but then you'd pay the cost of constructing the huge query on the PHP side (so the out of memory issue will still be there and will be even worse with the string concatenation). But honestly, who cares if you're just running this once?

However, if you're to perform this operation all the time (e.g. on a webpage), I'd recommend other approaches... like restricting the size of the area, cutting the feature or storing the data differently.

share|improve this answer
 
Thanks @Saurav, I'll be using that a few times, moving one site to a new location, for the coding, debugging and testing phase. My new error is now Got a packet bigger than 'max_allowed_packet' bytes –  Jeremy Roy Mar 9 '11 at 23:42
up vote 0 down vote

PHP has a built-in (configurable) memory limit to prevent a single script that goes the wrong way from bogging down the whole machine. Depending on your version, that limit defaults to 8MB (pre-5.2.0), 16MB (5.2.0), or 128MB (5.3.0).

You can change the limit either via ini_set or in the php.ini.

share|improve this answer
 
wow did the 5.3 set to default at 128MB O_O why that –  llnk Mar 9 '11 at 23:04
 
It's 5.2.9 in my server. memory_limit: 128M. –  Jeremy Roy Mar 9 '11 at 23:07
 
@yes123: No idea, probably because they figured that RAM gets cheaper, applications deal with more data, and machines have abundant RAM because it's cheap. Personally I've never run into a memory limit, but then again my scripts deal with working sets of a dozen or so rows at most, never 40,000 at a time. –  Damon Mar 9 '11 at 23:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.