Re: Simple MySQL sample code runs out of memory

On Thu, Oct 27, 2011 at 8:56 PM, tamouse mailing lists <tamouse.lists@gmail.com> wrote: > On Thu, Oct 27, 2011 at 6:59 PM,  <php@umpquanet.com> wrote: >> Running PHP 5.3.5 on FreeBSD 8.2 connecting to a MySQL 5.1.55 >> server. >> >> Why does this code (below) run out of memory?  It queries >> test_table for all rows, all fields and sorts them by the numeric >> 'contract' field.  It then iterates through the rows, and tallies >> the number of rows per contract.  That's not the end goal of the >> processing, but the processing does not require storing multiple >> rows in memory (except in the MySQL result buffer).  One row at a >> time in memory is all I need. >> >> The schema is not huge: >> >> +--------------+-----------------+------+-----+---------+-------+ >> | Field        | Type            | Null | Key | Default | Extra | >> +--------------+-----------------+------+-----+---------+-------+ >> | contract     | int(11)         | YES  |     | NULL    |       | >> | A            | int(8) unsigned | NO   |     | 0       |       | >> | B            | datetime        | YES  |     | NULL    |       | >> | C            | int(8) unsigned | YES  |     | 0       |       | >> | D            | char(8)         | YES  |     | NULL    |       | >> | E            | char(8)         | YES  |     | 0000    |       | >> | F            | int(4)          | YES  |     | 0       |       | >> | G            | int(1)          | YES  |     | 0       |       | >> | H            | char(8)         | YES  |     | 00:00   |       | >> | I            | varchar(100)    | YES  |     | XXX     |       | >> +--------------+-----------------+------+-----+---------+-------+ >> >> >> <?php >> >> require_once( '../include/mysql_funcs.php' ); >> >> $db_host = $test_db_host; >> $db_user = $test_db_user; >> $db_name = $test_db_name; >> $db_pwd  = $test_db_pwd; >> >> if (!($db_conn = mysql_connect( $db_host, $db_user, $db_pwd ))) >>        die( "Can't connect to MySQL server\n" ); >> >> if (!mysql_select_db( $db_name, $db_conn )) >>        die( "Can't connect to database $db_name\n" ); >> >> $qry = "select * from test_table order by contract"; >> >> if ($result = mysql_query( $qry, $db_conn )) { >> >>        $row = mysql_fetch_array( $result ); >>        while ($row) { >>                $c = $row[ 'contract' ]; >>                $n = 0; >>                while ($row && ($c == $row[ 'contract' ])) { >>                        $n++; >>                        $row = mysql_fetch_array( $result ); >>                } >>                echo sprintf( "|%13d |%7d |\n", $c, $n ); >>        } // while >> >> } else { >> >>        die( mysql_error() . "\n" ); >> >> } >> >> ?> >> >> >> The output ends with: >> ... >> |        39582 |    518 | >> |        39583 |    384 | >> |        39584 |    429 | >> |        39585 |    433 | >> |        39586 |    359 | >> PHP Fatal error:  Allowed memory size of 134217728 bytes exhausted (tried to allocate 20 >> bytes) in xx2.php on line 26 >> >> Line 26 seems to be the innermost "mysql_fetch_array" call. >> >> The sum of the right-hand column for all the output produced >> prior to running out of memory is 274547.  There are 295287 total >> rows in the table. >> >> What can I change to prevent this code from running out of memory >> even with an arbitrarily large number of rows returned from the >> 'select' query? >> >> Thank you for your time! >> >> >> >> -- >> PHP Database Mailing List (http://www.php.net/) >> To unsubscribe, visit: http://www.php.net/unsub.php >> >> > > I don't use the mysql functions, but rather the mysqli functions, > where you have to free resources (query results) when you're done with > them. Do you have to do the same thing with msql ? > No, sorry, forget that, I think I see what's going on now. Your inner loop is unnecessary. Instead, try: $contracts = array(); while ($row = mysql_fetch_assoc($result) { $contracts[$row['contract']]++; } foreach ($contracts as $contract => $count) { echo sprintf( "|%13d |%7d |\n", $contract, $count ); } That said, I think there must be a way to do this in SQL.