Propel ORM: PDOException " Can't create more than max_prepared_stmt_count statements"

Question

I have large MySQL database. I want to fetch rows by 100 at once until all DB is processed.

protected function doEcho($msg){
    static $time, $start;
    if (!$start) $start = time();

    if (time() - $time > 0) {
        echo $msg . "\n\n";
        $time = time();
    }
}

// ZF2 action
public function stuffAction() {
    $request = $this->getRequest();
    if (!$request instanceof ConsoleRequest){
        throw new \RuntimeException('You can only use this action from a console!');
    }

    // Propel ORM generated Model UserQuery
    $q = \UserQuery::create()->limit(100)->filterByProcessed(0);
    $users = $q->find();        

    while ($users->count() ) {
        foreach ($users as $user) {
            $id = $user->getId();
            $email = $user->getEmail();
            $password = $user->getPassword();
            $this->doEcho("$id - $email - $password");
            // do stuff and set processed to 1
        }

        $q = \UserQuery::create()->limit(100)->filterByProcessed(0);
        $users = $q->find();
    }
}

I get the following exception raised:

======================================================================

The application has thrown an exception!

Propel\Runtime\Exception\PropelException

Unable to execute SELECT statement [SELECT user.ID, user.EMAIL, user.PASSWORD, user.PHONE, user.IP, user.PROCESSED, user.WHEN FROM `user` WHERE user.PROCESSED=:p1 LIMIT 100]

// debug backtrace

======================================================================

Previous Exception(s):

PDOException

SQLSTATE[42000]: Syntax error or access violation: 1461 Can't create more than max_prepared_stmt_count statements (current value: 16382)

// debug backtrace

Obviously, the reason is that:

An application connecting to the database was preparing sql statements, executing them, then not closing them.

How do I make Propel to close statements ? I do use Propel 2: "propel/propel": "2.0.*@dev"

I'm not a Propel user, so I'm writing this as a comment. Can you store \UserQuery::create() to a member such as $this->q = \UserQuery::create() and then, instead of constantly creating a new statement, just alter the limit and offset by using $this->q->setLimit(100)->setOffset(100), thus shifting through data set? It might not work, but until a proper answer arrives - you might give it a shot. — N.B.
Actually, this was the first what I tried when faced this issue. But nothing changed: after 21 sec working, script dies throwing mentioned exceptions. ideone.com/ZFgyiy — iVenGO
I bybassed it by resorting to old good deprecated mysql-underscore functions )))) — iVenGO
Why don't you fix it by using PDO directly? You'll end up with less code compared to mysql_* and you'll use supported interface instead of obsolete one. — N.B.
I am more familiar with it. I was needing to solve problem fast. — iVenGO

iVenGO iVenGO · Accepted Answer · 2014-09-14T13:23:27

Solved:

use Propel\Runtime\Propel;
use Propel\Runtime\Connection\ConnectionWrapper;

// ...

Propel::getServiceContainer()
    // connection_name - connection from propel config
    ->getReadConnection('connection_name')
    ->setAttribute(ConnectionWrapper::PROPEL_ATTR_CACHE_PREPARES, true);