OutOfMemoryException in PDOConnection using Doctrine iterate result

query.php

<?php
// 7041 rows with content being BLOB content - total table size ~ 2.4 GB
$instructionRepository = $this->getDoctrine()->getRepository('PlusquamContractBundle:Instruction');
$instructionQueryBuilder = $instructionRepository->createQueryBuilder('i')
    ->where('i.content IS NOT NULL')
    ->orderBy('i.id', 'desc');

$instructions = $instructionQueryBuilder->getQuery()->iterate();
?>

My hope was that using itereate would not load the objects (especially BLOB content) into memory. But calling iterate already throws:

OutOfMemoryException in PDOConnection.php line 104:
Error: Allowed memory size of 536870912 bytes exhausted (tried to allocate 462848 bytes)

Suggestions?

Came from:

• https://twitter.com/webdevilopers/status/831086537509105664

Do not use ORM for reporting?

Another way fixing it could be use of: http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/partial-objects.html

Or even save the blob in filesystem and just reference it?

Unfortunately using PARTIALs - also suggested by @maglnet - doesn't help @mablae:

• https://twitter.com/MaGlNet/status/831088564066717696

I need this script for a one-time database migration. It has to loop over the results beacuse I need to create new objects with a different repository inside the loop.

I'm already saving the file to filesystem inside that loop.

The memory issue is created BEFORE I'm actually running the loop at all. Just by calling the iterate method @mablae.
I was surprised too. Doctrine will at least load some things into memory. And 2.5 GB seem to be too much even if the full object is not loaded into memory.

I tried to use raw SQL too:

        $sql = "SELECT i.id, i.content FROM instructions AS i";

        $em = $this->getDoctrine()->getManager();
        $stmt = $em->getConnection()->prepare($sql);
        $stmt->execute();

Againg: memory exhausted!

Maybe this helps: http://stackoverflow.com/a/27186091/693546

You made my day @mablae!

Indeed changing the USE_BUFFERED_QUERY attribute on PDO prevents loading anything into memory:

$pdo = $this->getDoctrine()->getManager()->getConnection()->getWrappedConnection();
$pdo->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

$sql = "SELECT i.id FROM instructions AS i WHERE i.content IS NOT NULL";

$em = $this->getDoctrine()->getManager();
$stmt = $em->getConnection()->prepare($sql);
$stmt->execute();
$instructionIds = $stmt->fetchAll();

I also tried using the repository too. But it looks like the Manager then does not recognize my attribute change then?

$pdo = $this->getDoctrine()->getManager()->getConnection()->getWrappedConnection();
$pdo->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

$instructionQueryBuilder = $instructionRepository->createQueryBuilder('i')
    ->where('i.content IS NOT NULL')
    ->orderBy('i.id', 'desc');

Nice!