yiisoft
With Oracle $query->batch produce fetch out of sequence tips, fine in mySQL
What steps will reproduce the problem?
Connexion string from basic app : (oracle version at the end of the report)
return [
'class' => 'yii\db\Connection',
'dsn'=>'oci:dbname=//localhost:1521/test;charset=WE8MSWIN1252',
Working code (note the limit <= batch size) :
$query = new \yii\db\Query();
$query->select('toto')
->from(Model::tableName())
->limit(101);
foreach ($query->batch(101) as $rows) {}
Non working code :
$query = new \yii\db\Query();
$query->select('toto')
->from(Model::tableName())
->limit(101);
foreach ($query->batch(100) as $rows) {}
What is the expected result?
No crash
What do you get instead?
Exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1002 OCIStmtFetch: ORA-01002: fetch out of sequence tips
(/tmp/oci/PDO_OCI-1.0/oci_statement.c:446)'
in /var/www/myWebSite/vendor/yiisoft/yii2/db/DataReader.php:111
Stack trace:
#0 /var/www/myWebSite/vendor/yiisoft/yii2/db/DataReader.php(111): PDOStatement->fetch()
#1 /var/www/myWebSite/vendor/yiisoft/yii2/db/BatchQueryResult.php(143): yii\db\DataReader->read()
#2 /var/www/myWebSite/vendor/yiisoft/yii2/db/BatchQueryResult.php(112): yii\db\BatchQueryResult->fetchData()
#3 /var/www/myWebSite/myPhpPage.php(17370): yii\db\BatchQueryResult->next()
#6 /var/www/myWebSite/vendor/yiisoft/yii2/base/InlineAction.php(55): call_user_func_array(Array, Array)
#7 /var/www/myWebSite/vendor/yiisoft/yii2/base/Controller.php(154): yii\base\InlineAction->runWithParams(Array)
#8 /var/www/myWebSite/vendor/yiisoft/yii2/console/Controller.php(119): yii\base\Controller->runAction('lance', Array)
#9 /var/www/myWebSite/vendor/yiisoft/yii2/base/Module.php(454): yii\console\Controller->runAction('lance', Array)
#10 /var/www/myWebSite/vendor/yiisoft/yii2/console/Application.php(180): yii\base\Module->runAction('ordonnateur/tac...', Array)
#11 /var/www/myWebSite/vendor/yiisoft/yii2/console/Application.php(147): yii\console\Application->runAction('ordonnateur/tac...', Array)
#12 /var/www/myWebSite/vendor/yiisoft/yii2/base/Application.php(375): yii\console\Application->handleRequest(Object(yii\console\Request))
#13 /var/www/myWebSite/yii(20): yii\base\Application->run()
#14 {main}
Additional info
| Q | A |
|---|---|
| Oracle version | Oracle Database 11g Release 11.2.0.4.0 - 64bit Production |
| Yii version | 2.0.9 |
| PHP version | PHP 5.4.40 (cli) (built: Jun 10 2015 06:44:21) |
| Operating system | RHEL 6 |
it seem to be specific to each / batch when the batch size is different from the expected result.
with this code :
$command=\Yii::$app->db->createCommand('select * FROM TABLE');
$reader = $command->query();
$nb=0;
while ($row = $reader->read()) {
$nb++;
}
print 'OK :'.$nb."\n";
print "-----------------------------------------------------------\n";
$nb=0;
$test=(new \yii\db\Query());
$test->from('TABLE');
try{
foreach ($test->each(1000) as $rows) {
$nb++;
}
}
catch (\PDOException $e){
print "Crash at : ".$nb." iteration \n".print_r($e->getMessage(),true)."\n";
}
print 'OK :'.$nb."\n";
print "-----------------------------------------------------------\n";
i get this output :
OK :37284
Crash at : 37284 iteration SQLSTATE[HY000]: General error: 1002 OCIStmtFetch: ORA-01002: extraction hors séquence (/tmp/oci/PDO_OCI-1.0/oci_statement.c:446)
OK :37284
i have 37 284 row is the table
without try/catch the output is :
Exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1002 OCIStmtFetch: ORA-01002: extraction hors séquence
(/tmp/oci/PDO_OCI-1.0/oci_statement.c:446)'
in /var/www/myWebSite/vendor/yiisoft/yii2/db/DataReader.php:111
Stack trace:
#0 /var/www/myWebSite/vendor/yiisoft/yii2/db/DataReader.php(111): PDOStatement->fetch()
#1 /var/www/myWebSite/vendor/yiisoft/yii2/db/BatchQueryResult.php(143): yii\db\DataReader->read()
#2 /var/www/myWebSite/vendor/yiisoft/yii2/db/BatchQueryResult.php(112): yii\db\BatchQueryResult->fetchData()
#3 /var/www/myWebSite/myPhpPage.php(156): yii\db\BatchQueryResult->next()
#6 /var/www/myWebSite/vendor/yiisoft/yii2/base/InlineAction.php(55): call_user_func_array(Array, Array)
#7 /var/www/myWebSite/vendor/yiisoft/yii2/base/Controller.php(154): yii\base\InlineAction->runWithParams(Array)
#8 /var/www/myWebSite/vendor/yiisoft/yii2/console/Controller.php(119): yii\base\Controller->runAction('test', Array)
#9 /var/www/myWebSite/vendor/yiisoft/yii2/base/Module.php(454): yii\console\Controller->runAction('test', Array)
#10 /var/www/myWebSite/vendor/yiisoft/yii2/console/Application.php(180): yii\base\Module->runAction('user/test', Array)
#11 /var/www/myWebSite/vendor/yiisoft/yii2/console/Application.php(147): yii\console\Application->runAction('user/test', Array)
#12 /var/www/myWebSite/vendor/yiisoft/yii2/base/Application.php(375): yii\console\Application->handleRequest(Object(yii\console\Request))
#13 /var/www/myWebSite/yii(20): yii\base\Application->run()
#14 {main}
A working exemple with active query :
$test=(new \yii\db\Query());
$test->from('TABLE');
$reader=$test->prepare(\Yii::$app->db->queryBuilder)->createCommand()->query();
while ($row = $reader->read()) {
$nb++;
}
I found a simple solution here https://github.com/bcit-ci/CodeIgniter/issues/1701
It is necessary to drown the error and everything will work.
@oci_fetch_assoc($id)
I have the same problem with Yii 2.0.14 and php 7.4.4.
Unfortunatelly the fix from codeigniter will not help, because Yii is not using oci_fetch_assoc but rather PDO directly, and the PDO is throwing exception from deep inside the PDO_OCI extension.
Seems that I'll have to just ignore this exception and trust that I have all the rows...
I had the same problem This appears when the number of records is greater than batchSize and is not divisible by the batchSize. I.e. if there are 106 records in the table and batch Size = 100, throws the error. But all records will be processed, i.e. 106 records will be processed. in my case I used try catch to ignore exception
