With Oracle $query->batch produce fetch out of sequence tips, fine in mySQL

[vinpel]

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

[vinpel]

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}

[vinpel]

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++;
    }

[arieslee]

Yes, I have the same bug

[realmrv]

The bug is relevant.

[laxity7]

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)

[wilkolazki]

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...

[fidi87]

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