Unexpectedly hitting PHP memory limit with a single PDO query?

Question

I have a really simple query that looks something like:

$result = $pdo->query('SELECT * FROM my_table');

foreach($result as $r) {
    // do some stuff
}

But when I run this I get the following error:

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 32 bytes) in /path/to/myfile.php on line 15

"Line 15" being the $pdo->query line.

If I put die() after the query, I still get the same error.

I thought this was only supposed to fetch one row at at time; why is it using so much memory?

@WesleyMurch: Hrm? How's that relevant? I only work with $r which is a single record. Why would that cause it to fetch extra data? ...unless there's a memory leak in there and the extra iterations are what cause the problem...fine, I'll post it. Why the downvote? — mpen
– mpen, Commented Dec 5, 2012 at 17:16
Getting the actual memory limit error would also be helpful (the actual wording used, plesae), so would actually checking memory use immediately before the query. You may already be at the limit before the query. Also, how many records are returned by the query? You're doing a SELECT * with no LIMIT. Returning a million rows is gonna use just a bit of memory. — Charles
– Charles, Commented Dec 5, 2012 at 17:26
Dude, it's allocating 32 bytes and you're hitting a 128M limit? You really need to check memory use before the query. Also, what PHP version is this? — Charles
– Charles, Commented Dec 5, 2012 at 17:29
@Charles: Memory usage is 635384 bytes before calling query. I'm guessing query allocates in chunks, for each record. Doesn't prove much. It's PHP 5.3.17. — mpen
– mpen, Commented Dec 5, 2012 at 17:31
MySQL, right? Are you using PDO::MYSQL_ATTR_USE_BUFFERED_QUERY? It's enabled by default. — Charles
– Charles, Commented Dec 5, 2012 at 17:36

Charles · Accepted Answer · 2012-12-05 18:15:19Z

43

Memory usage is 635384 bytes before calling query. I'm guessing query allocates in chunks, for each record.

Ding ding ding!

When connecting to MySQL, PHP likes using buffered queries. This is true regardless of what method you're using to connect. When using buffered queries, the entire resultset is fetched immediately instead of being fetched when you ask. This is usually good for performance, as there are fewer round-trips.

But like everything in PHP, there's a gotcha. As noted on the buffering page:

When using libmysql as library PHP's memory limit won't count the memory used for result sets unless the data is fetched into PHP variables. With mysqlnd the memory accounted for will include the full result set.

You're using PHP 5.3, which means that there's a good chance that you're using mysqlnd.

You'll want to turn off buffered queries here. It's done differently in every PHP interface to MySQL:

For PDO, you'll need to set the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute to false.
For mysqli, you need to pass the MYSQLI_USE_RESULT constant to the query method.
For mysql, you need to call mysql_unbuffered_query instead of mysql_query.

Full details and examples are on the page.

Big fat unbuffered query gotcha!

You must properly close the statement handle and free the result set before issuing another query:

In PDO, this means calling closeCursor on the statement handle.
In mysqli, this means calling free_result on the statement handle or free on the result handle, depending on what you're working with.
In mysql, this means calling mysql_free_result

Failure to do this will result in an error.

answered Dec 5, 2012 at 18:15

Charles

51.5k13 gold badges107 silver badges146 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

mpen Over a year ago

I don't think you need to call closeCursor if you use fetchAll or iterate over the entire result set; only when you have unfetched rows left. Anyway, great answer! Thanks again.

Charles Over a year ago

Honestly I've never tried, as combining unbuffered and fetchAll sounds amazingly silly.

mpen Over a year ago

It does, unless there's a handful of cases where you need the full result set and you don't want to flip the setting on and off.

PaulV Over a year ago

I landed here for the same error, however my problem turned out to be an non-existing field name. Causing it to hit the memory limit.

Marco Marsala Over a year ago

finally no more memory_limit=x GB

Collectives™ on Stack Overflow

Unexpectedly hitting PHP memory limit with a single PDO query?

1 Answer 1

Big fat unbuffered query gotcha!

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Big fat unbuffered query gotcha!

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related