php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #42496 cursor is not closed when using 2 clobs in a select query
Submitted: 2007-08-31 15:46 UTC Modified: 2007-12-11 19:45 UTC
Votes:13
Avg. Score:4.6 ± 0.6
Reproduced:9 of 9 (100.0%)
Same Version:6 (66.7%)
Same OS:2 (22.2%)
From: iddekingej at lycos dot com Assigned:
Status: Closed Package: OCI8 related
PHP Version: 5.2.4 OS: win 2000
Private report: No CVE-ID:
 [2007-08-31 15:46 UTC] iddekingej at lycos dot com
Description:
------------
When a query contains 2 clob fields then the cursor is not closed after oci_free_statement.

I run the attached script with the following table
tblDocuments
(id number
,v1 clob
,v2 clob
)

After a while the script gives a ora-01000 (maximum number of open cursors).

When tblDocuments contains only one clob field, the script keeps running fine.

I can monitor opencursors with the following script:

select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#  and s.sid=a.sid
and b.name = 'opened cursors current

When I monitor the script and tblDocuments has 2 clobs, the opencursor    increases until maximum number of cursors is reached and the script failes with a ora -0100.
When I momitor the script and tblDocument has 1 clob, the number of script stays about the same.


The versions are

Windows 2000 server
Oracle 10.2.0.2 on the same machine
PHP 5.2.4 (File downloaded from php site)
Apache 2.2
Maximum number of cursors is 500

Version 5.2.2 and 5.2.3 has the same problem.

When I enable debug I get the following information

OCI8 DEBUG: OCIHandleAlloc at (ext\oci8\oci8_statement.c:61) 
OCI8 DEBUG: OCIStmtPrepare2 at (ext\oci8\oci8_statement.c:77) 
OCI8 DEBUG: OCIAttrSet at (ext\oci8\oci8_statement.c:135) 
OCI8 DEBUG: OCIAttrSet at (ext\oci8\oci8_statement.c:144) 
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:418) 
OCI8 DEBUG: OCIStmtExecute at (ext\oci8\oci8_statement.c:442) 
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:471) 
OCI8 DEBUG: OCIParamGet at (ext\oci8\oci8_statement.c:491) 
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:500) 
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:510) 
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:520) 
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:530) 
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:543) 
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:553) 
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:563) 
OCI8 DEBUG: OCIDescriptorFree at (ext\oci8\oci8_statement.c:571) 
OCI8 DEBUG: OCIDefineByPos at (ext\oci8\oci8_statement.c:694) 
OCI8 DEBUG: OCIParamGet at (ext\oci8\oci8_statement.c:491) 
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:500) 
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:510) 
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:520) 
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:530) 
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:543) 
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:553) 
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:563) 
OCI8 DEBUG: OCIDescriptorFree at (ext\oci8\oci8_statement.c:571) 
OCI8 DEBUG: OCIDefineByPos at (ext\oci8\oci8_statement.c:676) 
OCI8 DEBUG: OCIDefineDynamic at (ext\oci8\oci8_statement.c:719) 
OCI8 DEBUG: OCIParamGet at (ext\oci8\oci8_statement.c:491) 
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:500) 
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:510) 
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:520) 
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:530) 
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:543) 
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:553) 
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:563) 
OCI8 DEBUG: OCIDescriptorFree at (ext\oci8\oci8_statement.c:571) 
OCI8 DEBUG: OCIDefineByPos at (ext\oci8\oci8_statement.c:676) 
OCI8 DEBUG: OCIDefineDynamic at (ext\oci8\oci8_statement.c:719)

OCI8 DEBUG: OCIStmtFetch at (ext\oci8\oci8_statement.c:168) 
OCI8 DEBUG: OCIStmtRelease at (ext\oci8\oci8_statement.c:746) 
OCI8 DEBUG: OCIHandleFree at (ext\oci8\oci8_statement.c:757) 
OCI8 DEBUG: OCISessionEnd at (ext\oci8\oci8.c:1523) 
OCI8 DEBUG: OCIHandleFree at (ext\oci8\oci8.c:1527)

OCI8 DEBUG: OCIServerDetach at (ext\oci8\oci8.c:1531) 
OCI8 DEBUG: OCIHandleFree at (ext\oci8\oci8.c:1535) 
OCI8 DEBUG: OCIHandleFree at (ext\oci8\oci8.c:1539) 
OCI8 DEBUG: OCIHandleFree at (ext\oci8\oci8.c:1543) 
OCI8 DEBUG: OCIHandleFree at (ext\oci8\oci8.c:1547) 
OCI8 DEBUG: OCIHandleFree at (ext\oci8\oci8.c:461) 
OCI8 DEBUG: OCIHandleFree at (ext\oci8\oci8.c:466) 



Reproduce code:
---------------
$l_res=oci_new_connect("<username>","<password>","<sid>");
oci_execute($l_st);

while(1){
	$l_cnt++;
        $l_st=oci_parse($l_res,"select * from tblDocuments  where id=$l_cnt ");

	oci_execute($l_st);	
        $l_row=oci_fetch_row($l_st);
	oci_free_statement($l_st);
	echo $l_cnt,':';print_r($l_row);
}



Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2007-08-31 21:19 UTC] iddekingej at lycos dot com
Sorry "$l_cnt=0" is missing from the code. It should read:

$l_res=oci_new_connect("<username>","<password>","<sid>");
oci_execute($l_st);
$l_cnt=0;
while(1){
	$l_cnt++;
        $l_st=oci_parse($l_res,"select * from tblDocuments  where
id=$l_cnt ");

	oci_execute($l_st);	
        $l_row=oci_fetch_row($l_st);
	oci_free_statement($l_st);
	echo $l_cnt,':';print_r($l_row);
}
 [2007-10-09 08:07 UTC] markus dot knecht at psi dot ch
I can reproduce the problem with your script.

OS: Scientific  Linux 4.x
Server: Oracle9i 9.2.0.7.0 on different machine.
Client: Oracle instant client 10.2 
PHP: 5.2.4 compiled from source (--with-oci8=shared,$ORACLE_HOME ) 

With my previous version 5.1.6 the problem is NOT reproducable.
 [2007-10-10 06:14 UTC] markus dot knecht at psi dot ch
Sorry, the build is --with-oci8=instantclient,$ORACLE_HOME
 [2007-10-31 17:52 UTC] natxo dot cabre at gmail dot com
I can reproduce the problem as well.

OS:  RedHat 4 Enterprise ES
Server: Oracle9i Enterprise Edition Release 9.2.0.7.0
Client: Oracle instant client 10.2 
PHP: 5.2.2 compiled from source (--with-oci8=instantclient,$ORACLE_HOME )
 [2007-11-08 16:38 UTC] br at absb dot de
We experience this bug to a further extend than described before:

- Selecting 2 clobs in one query fails (SELECT c1, c2 FROM c_table)
- Selecting the same clob twice in the same query fails (SELECT c1, c1 from c_table)
- Selecting more than one row with a clob fails (SELECT c1 FROM c_table WHERE ROWNUM < 10)
- Selecting one row with clob WORKS (SELECT c1 FROM c_table WHERE ROWNUM <= 1)
 [2007-11-08 23:34 UTC] martin at catalyst dot net dot nz
Narrowed down the problem to the OCI8 driver, which is versioned separately from PHP. After a bit of testing, the problem appeared in v1.2.4 of OCI8, which got included in PHP v5.2.3

OCI8 v1.2.3 does not exhibit the problem. vOCI8 1.3.0 Beta (as of today) still exhibits the problem. I've reported it on the bugtracker here http://pecl.php.net/bugs/bug.php?id=12407 linking back to this bug.

The different versions of the OCI8 driver are downloadable from http://pecl.php.net/package/oci8 -- HTH!
 [2007-11-08 23:38 UTC] martin at catalyst dot net dot nz
These are the revisions as reported in phpinfo() for the versions I've tested:

grep -r Revision oci8-1.*/oci8.c 
oci8-1.1.1/oci8.c:      php_info_print_table_row(2, "Revision", "$Revision: 1.274 $");
oci8-1.2.3/oci8.c:      php_info_print_table_row(2, "Revision", "$Revision: 1.269.2.16.2.29 $");
oci8-1.2.4/oci8.c:      php_info_print_table_row(2, "Revision", "$Revision: 1.269.2.16.2.38 $");
oci8-1.3.0/oci8.c:      php_info_print_table_row(2, "Revision", "$Revision: 1.269.2.16.2.38.4.1 $");
 [2007-11-08 23:52 UTC] martin at catalyst dot net dot nz
This Moodle bugreport is related: http://tracker.moodle.org/browse/MDL-11429

Note that under moodle (using AdoDB) we are seeing the problem as soon as the resultset contains at least one LOB. If the table has LOBs but the resultset is empty, no cursors are leaked.
 [2007-11-09 01:52 UTC] sixd@php.net
Does setting oci8.statement_cache_size = 0 change the behavior?
 [2007-11-09 03:21 UTC] martin at catalyst dot net dot nz
> Does setting oci8.statement_cache_size = 0 change the behavior?

It does not in our tests, unfortunately.
 [2007-11-09 11:28 UTC] br at absb dot de
We can reproduce the problem with OCI8 versions before 1.2.4: Version 1.2.3, $Revision: 1.269.2.16.2.32 $
 [2007-11-13 21:38 UTC] iarenuno at eteo dot mondragon dot edu

 [2007-11-15 13:55 UTC] markus dot knecht at psi dot ch
What i see after upgrading to PHP 5.2.5:
NOT Working: oci8 1.2.4,$Revision: 1.269.2.16.2.38 $
Working    : oci8 1.2.3,$Revision: 1.269.2.16.2.29 $
 [2007-11-22 10:01 UTC] ghosh at q-one dot com
I'm using OCI8 1.2.4 with Oracle 11g. A previous version doesnt seem to work, so I cannot test with 1.2.3. It also says so in the changelog for 1.2.4: Add Oracle 11g support. Now, whenever I select (c)lobs (even with only 1 lob column),, the table v$temporary_lobs keeps filling up and UGA memory is consumed for each row that's being read until the server aborts with an out-of-memory error. This does not happen when I run my statements directly via SQLplus, so it seems to be an OCI8/PHP bug. So, is this related to this bug or should I file a new one?
 [2007-11-29 16:38 UTC] michael dot virnstein at brodos dot de
I recognized, that when calling oci_free_statement() for every lob column that is returned by the select, the cursor gets closed correctly.
So if i have three lob columns in the query, i have to call oci_free_statment() three times on the statement handle to have it closed correctly.
 [2007-11-29 18:01 UTC] sixd@php.net
This was reproduced with 5.2.3 on Linux.
Please try this patch AND LET US KNOW THE RESULT - thanks!

In php_oci_define_callback function [oci8_statement.c],
zend_list_addref is called for every lob column of each row.  When we
commented out this increment, the statements were destroyed and no
cursor leaks were seen.

case SQLT_RDD:
case SQLT_BLOB:
case SQLT_CLOB:
case SQLT_BFILE: {
...
descr = php_oci_lob_create(outcol->statement->connection, dtype TSRMLS_CC);
               if (!descr) {
                   return OCI_ERROR;
               }
               /*zend_list_addref(outcol->statement->id); Commented out */

 [2007-11-30 10:05 UTC] michael dot virnstein at brodos dot de
Thanks, this fix works for lobs, but i investigated a bit further and realized, that the same problem occures when returning cursors. 
To totally remove the problem you would have to remove "zend_list_addref(outcol->statement->id);" from SQLT_RSET in php_oci_define_callback() also. 
I don't know if this is the right solution at all, i'm not that much into the php-engine, oci8-library and C in general. Wouldn't it be better to close all related cursors when closing the "main"-cursor instead of not registering them?
 [2007-11-30 16:30 UTC] michael at six dot de
Same problem here: php 5.2.5 with oracle 11g client (linux 64bit) against oracle 10.2.0.3 server (solaris 10 sparc 64bit).
Patch works ok, no ORA-01000 errors anymore.
 [2007-11-30 16:36 UTC] michael dot virnstein at brodos dot de
I tried to remove "zend_list_addref(outcol->statement->id);" from SQLT_RSET in php_oci_define_callback function() as well, but that doesn't work really well. It seemed to work at first, but it creates problems when returning the cursor from a function, because it leads to "ORA-01001: invalid cursor" when i try to fetch from the returned cursor.

Testcase:
<?php
 
$conn = ocilogon('user', 'pass', 'db');
 
function fetch($conn, $id)
{
    $result = null;
    $stmt = ociparse($conn, 'select cursor(select * from dual) c from dual');
    ociexecute($stmt, OCI_DEFAULT);
    ocifetchinto($stmt, $result, OCI_ASSOC);
    ociexecute($result['C'], OCI_DEFAULT);
    return $result['C'];
}

for ($id = 1; $id <= 300; $id++) {
    $cur = fetch($conn, $id);
    ocifetchinto($cur, $row, OCI_ASSOC);
    ocifreestatement($cur);    
}
?>

When i run the code above without removing "zend_list_addref(outcol->statement->id);" from SQLT_RSET i have the problem, that i get an "ORA-01000: maximum open cursors exceeded", because the statement within the function doesn't get closed. 

I don't know if that can be implemented easily, but the best thing would be, that a cursor gets closed if it is already out of scope and therefore not accessible from the php-code anymore and all nested cursors got closed already.
 [2007-12-04 02:13 UTC] sixd@php.net
Thanks for the information.  Can you please open a new bug for the result set issue.  Let's use this (#42496) bug only for any issues with the LOB fix.
 [2007-12-04 10:44 UTC] michael dot virnstein at brodos dot de
Ok, see Bug #43492 for the result set issue
 [2007-12-05 10:11 UTC] tim at digicol dot de
Had the same problem (maximum number of open cursors reached when 
selecting CLOBs) with PHP 5.2.4 and 5.2.5 on several boxes.

Example: PHP 5.2.4 (phpinfo says OCI8 version 1.2.3, Revision: 
1.269.2.16.2.37) on Linux (Debian 4.0), compiled against Oracle Instant 
Client 10.2.0, connected to Oracle 10.1.0.4.0.

The patch (commenting out zend_list_addref() in oci8_statement.c) seems 
to work; at least I cannot reproduce the error any longer.

We did not test the patch in production yet, so I cannot say whether 
there are any side effects.
 [2007-12-11 19:45 UTC] sixd@php.net
This bug has been fixed in CVS.

Snapshots of the sources are packaged every three hours; this change
will be in the next snapshot. You can grab the snapshot at
http://snaps.php.net/.
 
Thank you for the report, and for helping us make PHP better.

Fix has been merged to CVS. Thanks to Haneef for the investigation & patch.
 
PHP Copyright © 2001-2014 The PHP Group
All rights reserved.
Last updated: Wed Apr 16 13:02:46 2014 UTC