php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #33747 php5: Too many Oracle-Sessions on INSERT Statements
Submitted: 2005-07-18 13:05 UTC Modified: 2005-09-16 01:00 UTC
From: alfred dot trapp at tvi-services dot de Assigned: tony2001 (profile)
Status: No Feedback Package: OCI8 related
PHP Version: 5CVS-2005-07-19 OS: Linux
Private report: No CVE-ID: None
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: alfred dot trapp at tvi-services dot de
New email:
PHP Version: OS:

 

 [2005-07-18 13:05 UTC] alfred dot trapp at tvi-services dot de
Description:
------------
We have an application currently running without problems (Fedora 3 + PHP 4.3.3(from source) + apache 1.3.33(from source) + Oracle Client 9.2 oci8).
Now we have tested php 5.0.4 with Oracle Client 9.2 / 10.1 connecting to an Oracle 9.2 Database with either ocilogon, ocinlogon and ociplogon making around 500 INSERTS in a loop. On database side we get a so called Oracle Session Explode
while inserting, doing around 30 inserts with data and the rest are empty INSERTS without any (NULL) values. Actually there are around 50 Oracle Sessions opened, most of them without any data (detected with PL/SQL-Developer from Allround Automations). 
Also we get >>Number of Sessions exceeded<< from the Database.
On the productive version with php 4.3.3 we have one Oracle Session with all 500 rows full of correct data inserted.


Reproduce code:
---------------
Configure Command 
	 
'./configure' '--build=i686-redhat-linux-gnu' '--host=i686-redhat-linux-gnu' '--target=i386-redhat-linux-gnu' '--program-prefix=' '--prefix=/usr' '--exec-prefix=/usr' '--bindir=/usr/bin' '--sbindir=/usr/sbin' '--sysconfdir=/etc' '--datadir=/usr/share' '--includedir=/usr/include' '--libdir=/usr/lib' '--libexecdir=/usr/libexec' '--localstatedir=/var' '--sharedstatedir=/usr/com' '--mandir=/usr/share/man' '--infodir=/usr/share/info' '--cache-file=../config.cache' '--with-libdir=lib' '--with-config-file-path=/etc' '--with-config-file-scan-dir=/etc/php.d' '--disable-debug' '--with-pic' '--with-apxs2' '--disable-rpath' '--with-bz2' '--with-curl' '--with-exec-dir=/usr/bin' '--with-freetype-dir=/usr' '--with-png-dir=/usr' '--enable-gd-native-ttf' '--without-gdbm' '--with-gettext' '--with-gmp' '--with-iconv' '--with-jpeg-dir=/usr' '--with-openssl' '--with-png' '--with-pspell' '--with-expat-dir=/usr' '--with-pcre-regex=/usr' '--with-zlib' '--with-layout=GNU' '--enable-exif' '--enable-ftp' '--enable-magic-quotes' '--enable-sockets' '--enable-sysvsem' '--enable-sysvshm' '--enable-sysvmsg' '--enable-track-vars' '--enable-trans-sid' '--enable-yp' '--enable-wddx' '--with-pear=/usr/share/pear' '--with-kerberos' '--enable-ucd-snmp-hack' '--with-unixODBC=shared,/usr' '--enable-memory-limit' '--enable-shmop' '--enable-calendar' '--enable-dbx' '--enable-dio' '--with-oci8=/db/oracle/product/10.1/client' '--with-mime-magic=/usr/share/file/magic.mime' '--without-sqlite' '--with-libxml-dir=/usr' '--with-xml' '--with-apxs2=/usr/sbin/apxs' '--without-mysql' '--without-gd' '--without-odbc' '--disable-dom' '--disable-dba'

Program Code

$connection=ocilogon($g_tvp_user, $g_tvp_pw, $g_tvp_sid);                   
$tabellenname="ERGEBNISSE_".$_SESSION['tvipilot']['user_id'];
for($i=0;$i<count($a_feldwerte);$i++){
   	if($a_feldwerte[$i]!="''" && $a_feldwerte[$i]){
	    $queryinsert="INSERT INTO $tabellenname (".$tabinsert.") VALUES (".$a_feldwerte[$i].")";
    	$stmt=ociparse($connection,$queryinsert);
  		if(true!=ociexecute($stmt)){
  			$dateiname="log/statement".$_SESSION['tvipilot']['user_id']."_".$_SESSION['tvipilot']['arbeitsprofil']."_".$_SESSION['tvipilot']['darstellungsprofil'].".log";
  			$fh_rs=fopen($dateiname,"a");
  			if (!fwrite($fh_rs, $queryinsert."\n")) {
				print ("Kann in die Datei $dateiname nicht schreiben\n");
			}
			fclose($fh_rs);
  		}
        unset($queryinsert);
    }
}
ocifreestatement($stmt);
ocilogoff($connection);

php.ini Modifications

max_execution_time = 600     
max_input_time = 300	
memory_limit = 128M 
extension_dir = "/usr/lib/php/modules"

Expected result:
----------------
One Oracle Session with all 500 rows full of correct data inserted.

Actual result:
--------------
On database side we get a so called Oracle Session Explode
while inserting, doing around 30 inserts with data and the rest are empty INSERTS without any (NULL) values. Actually there are around 50 Oracle Sessions opened, most of them without any data (detected with PL/SQL-Developer from Allround Automations).

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2005-07-19 10:20 UTC] alfred dot trapp at tvi-services dot de
Hi sniper,
thanks for the proposal, but first tests with latest win32
PHP/5.1.0-dev indicates the same bug.
 [2005-07-20 14:07 UTC] tony2001@php.net
Please provide a clean, short and OCI8-only reproduce script. Without all those $_SESSION, fopen etc. Just a short code that contains only oci8*() calls and reproduces the problem. 
Thanks.
 [2005-07-20 14:36 UTC] alfred dot trapp at tvi-services dot de
Hi tony

Simplified Program Code

$connection=ocilogon($user, $pwd, $sid);                   
$tablename="RESULT_".$user;

for($i=0;$i<count($result);$i++){
    $queryinsert="INSERT INTO $tablename (".$tabinsert.") ".
                 "VALUES (".$result[$i].")";
    $stmt=ociparse($connection,$queryinsert);
    ociexecute($stmt);
    unset($queryinsert);
}
ocifreestatement($stmt);
ocilogoff($connection);

u'r welcome
 [2005-07-20 14:41 UTC] tony2001@php.net
Obviously this code won't work as nobody knows where do you get $result & $tabinsert.
 [2005-07-20 15:05 UTC] alfred dot trapp at tvi-services dot de
$connection=ocilogon($user, $pwd, $sid);                   
$tablename="RESULT";

for($i=0;$i<count($result);$i++){
    $queryinsert="INSERT INTO $tablename (film_id, sendungs_id, s_id , intra_anfangszeit, akt_anfangszeit, sender,titel,bew,gt,texte,bild,sparte,stnr,genre) VALUES (".$result[$i].")";
	$stmt=ociparse($connection,$queryinsert);
	ociexecute($stmt);
    unset($queryinsert);
}

and $result is a dynamically filled array from another resource.
The wanted restriction was >>not more than 20 lines of code<<. If you want to know how the result array gets filled, i have to send you the whole script.
 [2005-07-20 15:18 UTC] tony2001@php.net
As far as I understand it doesn't depend on the table, am I right?
So why not to provide a script that CREATEs the table, INSERTs data into it and demonstrates the problem?
Basically, what I want is a script that I can copy, paste, run and see the problem. That's all.
Without all those variables that doesn't exist in the script, without non-existent tables etc.
It's so easy, why do I need to ask you several times about it?

Also, are you sure that ONLY THIS script causes the issue?
If you just replace it with simple <?php oci_connect(<user>, <passwd>, <dbname>);?> - doesn't it behave in the same wrong way? 
It looks like another one duplicate of bug #32361, but you're talking about INSERTs for some reason.
 [2005-07-20 17:04 UTC] alfred dot trapp at tvi-services dot de
I'm sorry about that, but for now i can't provide such a script. I tried it with a short script on a test table with inserting 500 numeric values into one column only and could not reproduce the mentioned bug.
Maybe you are right and it is a duplicate to bug #32361. I will watch furthermore to proposals concerning that bug and may be able to prepare a script that reproduces my problem.
Thanks for trying to help.
 [2005-09-08 11:52 UTC] tony2001@php.net
Please try OCI8 v.1.1, which is available in CVS HEAD and PECL (use `pear install oci8-beta` to install it).
 [2005-09-16 01:00 UTC] php-bugs at lists dot php dot net
No feedback was provided for this bug for over a week, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Sat Dec 21 15:01:29 2024 UTC