Google
  Web www.spinics.net

Simple MySQL sample code runs out of memory

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]


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



[PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Plagiarism Notes]     [Postgresql]     [Yosemite News]

Powered by Linux