Why is the "Allowed memory size exhausted"?

Question

I am writing a script package and getting an error Allowed memory size of 134217728 bytes exhausted .

I do not understand why the memory is full. I tried to disable the $row variable, but that didn't change anything. Here is my code:

 // ... (sql connection) $result = mysql_query("SELECT * FROM large_table"); while ($row = mysql_fetch_array($result)) { echo $row['id'] . PHP_EOL; unset($row); } 

(simplified code)

Why is the memory full and how can I avoid it?

Note: this is a script package. It is normal that I should process such data (go through 1 million lines).

Update: out of memory occurs around the 400,000th line, so should it be something in the loop? I would like to avoid the need for paging, if possible.

Answer 1

Try using http://www.php.net/manual/en/function.mysql-unbuffered-query.php (mysql_unbuffered_query ()) to prevent the entire table from loading into memory, but still avoiding pagination.

Answer 2

Limit your query, for example, to 1k, and execute it again (with an offset) until you go through the whole table. Your current reset does not matter, since $ row is overwritten with each one at repetition, so you can skip it.

 $chunk_size = 1000; $done = 0; $keep_asking_for_data = true; do{ $result = mysql_query("SELECT * FROM `large_table` LIMIT {$done}, {$chunk_size}"); $num_rows = mysql_num_rows($result); if($num_rows){ $done += $num_rows; while($row = mysql_fetch_assoc($result)){ echo "{$row['id']}\n"; } } else { $keep_asking_for_data = false; } mysql_free_result($result); }while($keep_asking_for_data); 

Just compiled on my head, hope it works = D

Answer 3

If you use MySQL, provide your results so that you do not run out of available memory. MySQL itself takes this memory using your database dataset. Take a look at the following link, in particular the syntax for LIMIT offset, limit SELECT :

http://dev.mysql.com/doc/refman/5.0/en/select.html

Answer 4

I had the same problem with a large database. I ran out of memory, despite the fact that it did not change the $ row variable by about 400,000 records, but an unbuffered request fixed it.

Just for reference to others (and me when I do it again!), Some unbuffered request code:

 $sql = "SELECT SEQ, RECTYPE, ROSTERGRP, EMPNM, EMPNUM, DT, RDUTYCAT, ADUTYCAT FROM " . $tblRosters . " ORDER BY EMPNUM,DT"; $result = mysql_unbuffered_query( $sql, $dbConn ); $svRow = array(); while ( $row = mysql_fetch_array( $result ) ) { // your processing code here } // Unset, close db etc. if you are finished goes here.