At 13:40 24/02/2009, you wrote:
Message-ID: <>
Date: Mon, 23 Feb 2009 18:16:01 +0000
From: Stewart Duncan <>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Subject: PDO buffered query problem

Hi there,

I'm having some serious problems with the PHP Data Object functions. I'm trying to loop through a sizeable result set (~60k rows, ~1gig) using a buffered query to avoid fetching the whole set. No matter what I do, the script just hangs on the PDO::query() - it seems the query is running unbuffered (why else would the change in result set size 'fix' the issue?).

Just for completeness in understanding - the 60k rows does not correspond to 1Gb data - that's for the full rows right ? If not, that would imply your "ID" field below is ~18000 characters long which seems (cough) extreme !

If your ID field is still of substantial size, you could consider a surrogate key, eg an autoincrement integer to keep sizes small.

IMO, requiring 60000 rows in a result set might indicate that your application is configured to do much of the work otherwise done by the DB. Without any specifics it's hard to say, but be sure you actually ~need~ to fetch all the rows instead of returning a more limited resultset by better specifying the SQL.

As one other poster noted, you *could* be reaching PHP's memory limit : It's not configured that high in default installs - often 2 or 8MB - and the script will terminate silently in that case, unless you have error_reporting(E_ALL) and/or error logging switched on in your application to debug this.

If it's a production server, you'll need to check the error logs, as you won't be displaying the errors to screen ( -right - ? ;-))

Finally - try to make sure you're running the mysqldnd MySQL client driver with a recent (PHP 5.3+) install with MySQL4.1

That tends to use 1/2 the memory as the resultset is only stored in one place rather than 2

Oh - and I imagine that is repro code, but check if you had code in the script prior to those isolated lines which doesn't close resultsets or release statement resources, to make sure you're not consuming and not releasing memory during the script execution.

On the MySQL side, if you're really returning large BLOBs and not the ID you specified below, read

One final top place to search for tips and ideas is always (buy the book if you get the chance, it's excellent)

Cheers - Neil

Here is my code to reproduce the problem:

$Database = new PDO(

$rQuery = $Database->query('SELECT id FROM mytable');

// This is never reached because the result set is too large
echo 'Made it through.';

foreach($rQuery as $aRow) {

If I limit the query with some reasonable number, it works fine:

$rQuery = $Database->query('SELECT id FROM mytable LIMIT 10');

I have tried playing with PDO::MYSQL_ATTR_MAX_BUFFER_SIZE and using the PDO::prepare() and PDO::execute() as well (though there are no parameters in the above query), both to no avail.

Any help would be appreciated,


PHP Database Mailing List (
To unsubscribe, visit:

Reply via email to