I have large MySQL database. I want to fetch rows by 100 at once until all DB is processed.
I get the following exception raised:
======================================================================
<h1> The application has thrown an exception!</h1>
Propel\Runtime\Exception\PropelException
<h2> Unable to execute SELECT statement [SELECT user.ID, user.EMAIL, user.PASSWORD, user.PHONE, user.IP, user.PROCESSED, user.WHEN FROM
WHERE user.PROCESSED=
1 LIMIT 100]</h2>
// debug backtrace
======================================================================
<h1> Previous Exception(s):</h1>
PDOException
<h2> SQLSTATE[42000]: Syntax error or access violation: 1461 Can't create more than max_prepared_stmt_count statements (current value: 16382)</h2>
// debug backtrace
<strong>Obviously, the reason is that:</strong>
<blockquote>
<a href="http://iammysql.wordpress.com/2012/05/04/cant-create-more-than-max_prepared_stmt_count-statements/" rel="nofollow">An application con</a>necting to the database was preparing sql
statements, executing them, then not closing them.
</blockquote>
How do I make Propel to close statements ? I do use Propel 2: "propel/propel": "2.0.*@dev"
Code:
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:
======================================================================
<h1> The application has thrown an exception!</h1>
Propel\Runtime\Exception\PropelException
<h2> Unable to execute SELECT statement [SELECT user.ID, user.EMAIL, user.PASSWORD, user.PHONE, user.IP, user.PROCESSED, user.WHEN FROM
Code:
user
// debug backtrace
======================================================================
<h1> Previous Exception(s):</h1>
PDOException
<h2> SQLSTATE[42000]: Syntax error or access violation: 1461 Can't create more than max_prepared_stmt_count statements (current value: 16382)</h2>
// debug backtrace
<strong>Obviously, the reason is that:</strong>
<blockquote>
<a href="http://iammysql.wordpress.com/2012/05/04/cant-create-more-than-max_prepared_stmt_count-statements/" rel="nofollow">An application con</a>necting to the database was preparing sql
statements, executing them, then not closing them.
</blockquote>
How do I make Propel to close statements ? I do use Propel 2: "propel/propel": "2.0.*@dev"