Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
OutOfMemoryException in PDOConnection using Doctrine iterate result
<?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();
?>
@webdevilopers
Copy link
Author

webdevilopers commented Feb 13, 2017

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:

@mablae
Copy link

mablae commented Feb 13, 2017

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?

@webdevilopers
Copy link
Author

webdevilopers commented Feb 13, 2017

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

@webdevilopers
Copy link
Author

webdevilopers commented Feb 13, 2017

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.

@webdevilopers
Copy link
Author

webdevilopers commented Feb 13, 2017

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!

@mablae
Copy link

mablae commented Feb 13, 2017

@webdevilopers
Copy link
Author

webdevilopers commented Feb 13, 2017

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');

@mablae
Copy link

mablae commented Feb 13, 2017

Nice!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment