Oci8 Driver generating "Fetch out of sequence warning" #3958
Conversation
|
|
|
I experienced the same problem while trying to fetch 600k+ records. Didn't try with less. Thanks for the workaround. -Bart Verstuurd vanaf mijn iPad Op 5 mrt. 2013 om 08:30 heeft Phil Bennett [email protected] het volgende geschreven:
|
|
It's just suppressing warnings but beware that this will also suppress any On Tuesday, March 5, 2013, Bart McLeod wrote:
|
|
A collegue did a similar query with traditional php oci, he did not get the warning, do you know what might be the difference? -Bart Verstuurd vanaf mijn iPad Op 5 mrt. 2013 om 12:08 heeft Phil Bennett [email protected] het volgende geschreven:
|
|
It seems to be the order in which the query is built and committed, I'm On Tuesday, March 5, 2013, Bart McLeod wrote:
|
it exceeded the limit prefetch value? |
|
I don't think that's the issue as running the query in procedural PHP would have the same prefetch limit and that doesn't throw any warnings. |
|
works for me |
|
What does? Changing the prefetch limit? On Wednesday, March 6, 2013, Alexander Bonar wrote:
|
|
Is there a way someone could help me reproduce this? Perhaps with adding some large amount of data to the schema in https://github.com/ralphschindler/Zend_Db-Examples, then a script to reproduce the error that I can run? |
|
I can but it would have to be tomorrow if that's ok? On Wednesday, March 13, 2013, Ralph Schindler wrote:
|
|
That's fine - whenever you can. Thanks, @happyaccidents |
|
Turning on oci_internal_debug(1) in the .php code may help to identify what OCI8 calls are being used. If you can recompile PHP OCI8, the patch below will print out SQL statements and whether the execute mode is autocommit or not: diff --git a/ext/oci8/oci8_statement.c b/ext/oci8/oci8_statement.c
index 89facb0..ef5dc67 100644
--- a/ext/oci8/oci8_statement.c
+++ b/ext/oci8/oci8_statement.c
@@ -52,6 +52,11 @@ php_oci_statement *php_oci_statement_create (php_oci_connection *connection, cha
if (!query_len) {
/* do not allocate stmt handle for refcursors, we'll get it from OCIStmtPrepare2() */
PHP_OCI_CALL(OCIHandleAlloc, (connection->env, (dvoid **)&(statement->stmt), OCI_HTYPE_STMT, 0, NULL));
+ } else {
+ if (OCI_G(debug_mode)) {
+ php_printf ("OCI8 DEBUG SQL: \"%s\" at (%s:%d) \n", query, __FILE__, __LINE__);
+ }
+
}
PHP_OCI_CALL(OCIHandleAlloc, (connection->env, (dvoid **)&(statement->err), OCI_HTYPE_ERROR, 0, NULL));
@@ -421,6 +426,9 @@ int php_oci_statement_execute(php_oci_statement *statement, ub4 mode TSRMLS_DC)
case OCI_DESCRIBE_ONLY:
case OCI_DEFAULT:
/* only these are allowed */
+ if (OCI_G(debug_mode)) {
+ php_printf ("OCI8 DEBUG: Execute mode 0x%x at (%s:%d) \n", mode, __FILE__, __LINE__);
+ }
break;
default:
php_error_docref(NULL TSRMLS_CC, E_WARNING, "Invalid execute mode given: %d", mode);
|
|
@ralphschindler Were you able to reproduce this? |
|
I've not been given a reproduction case yet. ping @happyaccidents |
|
This error may occur if you perform a FETCH on an active cursor after all records have been fetched. The option to resolve this error are: Zend/Db/Adapter/Driver/Oci8/Result.php ( Updated) protected $currentData = null;
public function rewind()
{
if ($this->position > 0) {
throw new Exception\RuntimeException(
'Oci8 results cannot be rewound for multiple iterations');
}
}
public function next()
{
++$this->position;
}
public function valid()
{
if ($this->currentData === false) {
return false;
}
$this->currentData = oci_fetch_assoc($this->resource);
return ($this->currentData !== false);
}
public function current()
{
return $this->currentData;
}
public function key()
{
return $this->position;
} |
|
Been working on this today, came to a similar solution. Will send PR and schema shortly (testing needed first) On Tuesday, May 7, 2013, Alexander Bonar wrote:
|
|
This issue is being caused by 1 too many calls being sent to |
|
You can reproduce this situation with a small set of data ( such as 5 records ) and the set option oci8.default_prefetch=3 The current implementation is too many calls to loadData function. This makes it difficult to read and understand the code. Sorry for my english |
|
@ralphschindler The prefetch limit shouldn't have to be set by the driver though, it's still getting two false returns from |
|
The only problem I see with @hamilok's patch is that it fails a common use case, which is getting a row from the result without iteration (consider this use case): $result = $table->select(['id' => 5]);
if ($result) {
$row = $result->current();
}. In the current Result, the current looks like this: public function current()
{
if ($this->currentComplete == false) {
if ($this->loadData() === false) {
return false;
}
}
return $this->currentData;
}The two primary use cases being: a) just call current to get the current data, or b) iterate using the iterate workflow to then call current to get a row. |
|
I've temporarily changed the check in 5684b17 |
|
@ralphschindler Excuse me, please. In my amendment is not considered a direct function call "current" on the resulting data set. public function current()
{
+ if ($this->currentData === null) {
+ $this->valid();
+ }
return $this->currentData;
}either the solution proposed @happyaccidents either use an explicit calls $result = $table->select(['id' => 5]);
if ($result->valid()) {
$row = $result->current();
} |
|
Can you guys please test the patch in this PR? I believe it was a logic error. This works for me. |
|
To expand on @happyaccidents comment about being within the prefetch Prefetching reduces network traffic and DB interrupts etc by fetching The design premise is that when oci_fetch_assoc() returns false, then The reason the error doesn't always occur is an optimization in the For cases where oci8.default_prefetch is less than the number of rows |
|
@cjbj 👍 well explained |
|
@ralphschindler yes, the fix works fine |
…ch-warnings Oci8 Driver generating "Fetch out of sequence warning"
|
Great this is fixed, works for me too. |
Zend\Db\Adapter\Driver\Oci8\Resultline 151 is generating aoci_fetch_assoc(): ORA-01002: fetch out of sequencewarning for any larger queries. Limiting the results to less than 50 stops the warning appearing but anything larger it appears.Suppressing warnings with
@oci_fetch_assocget's rid of the warning and returns results as expected but obviously this isn't fixing the issue.The warning is usually generated when a fetch is performed after a commit.