Does PHP7's PDO ext read the entire result set into memory?

Question

I have noticed since I upgraded to PHP7 that some SQL statements no longer work and instead run out of memory.

I have this code:

$query = Yii::$app->db->createCommand('select * from tbl_title')->query();
while ($row = $reader->read()) {
    var_dump($row);
    exit();
}

And Yii2's database abstraction is just an extremely thin layer over PDO's and does not do anything extra. query() does nothing extra except add a line to a log file (Yii2's) for profiling and reader->read() just calls the PDO stream's fetch() function.

But it runs out of memory quoting the size (space used) of my table, i.e. trying to allocate 385 MB of process memory:

Allowed memory size of 134217728 bytes exhausted (tried to allocate 385883840 bytes)

As a spanner, if I use a query whose result set fits entirely in the 128 MB limit of the PHP process works.

So, has PHP7 changed and can I change it back?

Answer 1

It is not directly PHP7-related. The issue is due to new mysqlnd driver, so you can experience the the same problem even with PHP 5.x as well. It is actually a bugfix, because even before the memory was still allocated, but it didn't count towards memory_limit.

To avoid a memory issue you have to use unbuffered queries for the large resultsets.

So, for the query that is expecting a large dataset, set the proper setting like this:

$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

For the further reading, I've got a decent explanation in my PDO tutorial, thanks to Nikic, whose critical feedback was invaluable.