php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login

go to bug id or search bugs for

Bug #56671 Invalid cursor while fetching a ref cursor
Submitted: 2005-11-24 09:49 UTC Modified: 2006-07-07 05:11 UTC
From: sgattas at fibertel dot com dot ar Assigned:
Status: No Feedback Package: oci8 (PECL)
PHP Version: 5.0.4 OS: Win 2000 / IIS 5.0 / Apache 1.33
Private report: No CVE-ID: None
View Developer Edit
Welcome back! If you're the original bug submitter, here's where you can edit the bug or add additional notes.
If you forgot your password, you can retrieve your password here.
Password:
Status:
Package:
Bug Type:
Summary:
From: sgattas at fibertel dot com dot ar
New email:
PHP Version: OS:

 

 [2005-11-24 09:49 UTC] sgattas at fibertel dot com dot ar 
Description:
------------
Related to php bug_id: 31693.
On that thread, it was suggested to use the pecl version of php_oci8.dll to fix the issue with ref cursors. We installed it (PHP Version (branch): php-5.0.5 (5_0), Last build: 2005-11-08 15:11:43), made no modifications in the php.ini but when we tested it it starts adding new conections and not closing the inactive ones. So out database, when reaching more than 800 inactive conns, starts suffering.
So, we added the following in the php.ini:
oci8.persistent_timeout = 900
Restarted and after it, we don't see new conections but the process is giving the invalid cursor error again. 

Reproduce code:
---------------
<?
session_start();
set_time_limit(0);
$con1=OCILogon("XXX", "XXX" ,"XXX") ;
if ( ! $con1 ) { $arrError = OCIError(); 
     $msg='There is an error on the database side and SMD is not able to connect. Please, inform your DBA about the following error: '.$arrError['message']; 
     echo $msg; 
  }

unset($_SESSION['data']);
$df = "ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY'";
$a=OCIParse($con1, $df);
OCIExecute($a);
$as = "ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,'";
$b=OCIParse($con1, $as);
OCIExecute($b);


$out=OCINewCursor($con1);
$s=OCIParse($con1, "begin rels_6.prc_rel1_daily(:b1,:b2,:b3,:b4,:b5,:b6,to_date(:b7,'dd/mm/yyyy'),to_date(:b8,'dd/mm/yyyy'),:b9); end;");
	OCIBindByName($s, ":b1", &$uf,-1);
	OCIBindByName($s, ":b2", &$site,-1);
	OCIBindByName($s, ":b3", &$acc,-1);
	OCIBindByName($s, ":b4", &$ope,-1);
	OCIBindByName($s, ":b5", &$equ,-1);
	OCIBindByName($s, ":b6", &$ate,-1);
	OCIBindByName($s, ":b7", &$from,-1);	
	OCIBindByName($s, ":b8", &$to,-1);
	OCIBindByName($s, ":b9", $out,-1, OCI_B_CURSOR);
OCIExecute($s, OCI_DEFAULT);
OCIExecute($out);
$_SESSION['data'] = array();
$_SESSION['data'][0][1]='V1';  	
$_SESSION['data'][0][2]='V2';
$_SESSION['data'][0][3]='V3';
$_SESSION['data'][0][4]='V4';
$_SESSION['data'][0][5]='V5';
$_SESSION['data'][0][6]='V6';
$_SESSION['data'][0][7]='V7';
$_SESSION['data'][0][8]='V8';
$_SESSION['data'][0][9]=  $_POST['cname']; 
$_SESSION['data'][0][10]='V9';
$_SESSION['data'][0][11]='V10';
$_SESSION['data'][0][12]='V11';
$_SESSION['data'][0][13]='V12';
$_SESSION['data'][0][14]='V13';
$_SESSION['data'][0][15]='V14';           
$_SESSION['data'][0][16]='V15';
$_SESSION['data'][0][17]='V16';

while (OCIFetchInto($out,&$data)) {
	for ($n=0; $n<18 ; $n++) {
		if ($_POST['qfiltro']==$n) {
				if ($data[6]==1 && $data[7]==1)  {$_SESSION['data'][$ii][9]='Totais';} else {$_SESSION['data'][$ii][9]=$data[$n];}
			}
		if ($n>=9) {
		    switch ($n) {
			case 12: $valor=number_format((($data[11]+$data[10])/$data[9])*100,1,',','.'); break;
			case 15: $valor=number_format((($data[14]+$data[13])/$data[9])*100,1,',','.'); break;
			case 16: $valor=number_format((($data[11]+$data[10]+$data[12]+$data[13])/$data[9])*100,1,',','.'); break;
			default: $valor=$data[$n]; break;
			}
			if ($n==12 or $n==15 or $n==16) {$_SESSION['data'][$ii][$n+1]=$valor;} else { 
				$_SESSION['data'][$ii][$n+1]=TimeFormatting($valor); }
			}
	} 
    $ii++;	
}

OCIFreeCursor($s);
OCIFreeStatement($out);
OCILogOff($con1);
?>


Expected result:
----------------
Expected result: both queries returning all rows.


Actual result:
--------------
[24-Nov-2005 11:51:32] PHP Warning:  ocifetchinto() [<a href='function.ocifetchinto'>function.ocifetchinto</a>]: OCIFetchInto: ORA-01001: invalid cursor
 in E:\...\...\...\php\smd_rel1_proc.php on line 59

When you submit the same query from two different sessions, the first one gets the error and the second one -the one that "steals" the first session conn- finishes succesfully.
The first onw gets the error described up supra.

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2005-11-24 10:01 UTC] tony2001 at phpclub dot net 
What version of OCI8 are you using?
 [2005-11-24 10:29 UTC] sgattas at fibertel dot com dot ar 
Hi, good morning. Thanks your quick reply:

PHP Version
(branch): php-5.0.5 (5_0), Last build: 2005-11-08 15:11:43
 [2005-11-24 10:40 UTC] tony2001 at phpclub dot net 
Okay, I got your PHP version.
But I'm asking about OCI8, not PHP.
Did you install OCI8 from PECL (http://pecl4win.php.net/ext.php/php_oci8.dll)?
 [2005-11-24 14:32 UTC] sgattas at fibertel dot com dot ar 
Hi again.
As you noticed, PHP version is 5.0.4.

I downloaded php_oci8.dll from http://pecl4win.php.net/ext.php/php_oci8.dll
This is the information about the dll:
# 4 
File name: php_oci8.dll
Php Version (Branch): php-5.0.5 (5_0)
Size: 84 KB
Last build: 2005-11-08 15:11:43.

Let me state my problem again cause it seems that it's really another one:
this dll seems to be working and no invalid cursor errors encountered.
However I'm checking v$session in the database and no connections are closed after being used. As I said before I added this to the php.ini:
oci8.persistent_timeout = 900
I expect that after being used the connection would disappear but they are all there in the v$session dynamic view in the database. It's really crashing the database.

This is the query I'm running in the database to check this:

select sid, program, command, module, action, status, logon_time, sysdate-(LAST_CALL_ET/86400) last_time
from v$session
where logon_time>=trunc(sysdate)
order by sid
 [2005-11-24 14:38 UTC] tony2001 at phpclub dot net 
I can't reproduce it on Linux and Solaris with PHP 5.0.5 and 5.1.
Please, wait for the next build of php_oci8.dll (there were several bugfixes since 2005-11-08) and try the same with PHP 5.1.
 [2005-11-24 15:32 UTC] sgattas at fibertel dot com dot ar 
I cannot reproduce even on Xp/ apache. However, I need to use Windows 2003/IIS. Just in order it's clear for me and then I would see what I can do (I'm afraid I need to find a solution asap):
Do you have any suggestion in order to reuse conns? I cannot think of anything to explain why it keeps adding new connections.
I'm using OCILogon so it should use already open conns and not open a new one.

As always thanks a lot for your time.
 [2005-11-24 15:36 UTC] tony2001 at phpclub dot net 
The only thing I can propose is to try newer PHP.
I don't know of an easy way to debug it under IIS, so no more ideas, sorry.
 [2005-11-25 05:20 UTC] tony2001 at phpclub dot net 
Hopefully fixed in CVS.
Please wait for the next OCI8 binary build and confirm.
Thanks.
 [2005-11-25 15:23 UTC] sgattas at fibertel dot com dot ar 
Sure, I would try. Thanks a lot for the followup.
When are you planning next build?

Best regards.
 [2005-11-28 19:13 UTC] sgattas at fibertel dot com dot ar 
Hi just to keep you updated, tried with last build and with php 5.1.0. Same error on versions post 5.0.4 (open connection and not closing them). Reproducible on Apache (I stated earlier that apache was working but was a mistaken, some problem in the configuration didn't let me see that) on Win XP pro.
The error is not encountered on PHP 4.3.9. Guess we are going all the way back to that version.
 [2005-11-28 19:14 UTC] sgattas at fibertel dot com dot ar 
By the way, just le me know if there's something I can post here to give you additional data.

Best regards.
 [2005-12-01 10:07 UTC] tony2001 at phpclub dot net 
Please try with the next OCI8 dll build (whenever it happens).
I've fixed something similar and I suspect the patch should have solved you problem too.
 [2005-12-01 10:32 UTC] sgattas at fibertel dot com dot ar 
Tony, thanks a lot for the followup.
Q: checking the cvs, I see that last mod was made on php 5.1. So, would this fix even work on 5.0.4? or should I migrate instead to 5.1.0 anyway?

Kind regards.
 [2005-12-01 10:45 UTC] tony2001 at phpclub dot net 
What fix do you mean?
I committed new OCI8 code to the 5_1 branch recently, so new OCI8 will apear in 5.1.2 and its snapshots (so you don't have to grab dlls from pecl4win repository).
No, it won't work with 5.0.4 (obviously).
But you still can use pecl4win to get the DLL for 5.0.4.
 [2005-12-02 07:20 UTC] tony2001 at phpclub dot net 
Ok, there is a new build of OCI8 for 5.0.x.
See http://pecl4win.php.net/ext.php/php_oci8.dll
 [2005-12-13 10:08 UTC] sgattas at fibertel dot com dot ar 
Good morning. Tested it with the dll for 5.0.4 just downloaded. The issue is still there.

Information about the configuration:
SO: Win XP (5.1.2600) / Apache 1.33 / Oracle 10.1.0.2.0

Same error as before (open new connections, not closing them). It's not just happening with ref cursors, even with a simple "select * from dual" it can be replicated. 

Thanks for your time. Hope it can be fixed.
 [2006-03-21 10:30 UTC] tony2001 at phpclub dot net 
Still not reproducible.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved. 		Last updated: Sun Dec 22 01:01:30 2024 UTC