RE: [PHP-DEV] PHP 4.0 Bug #9876: OCIPLogon flaky after restart Or acle

I wouldn't exactly say the OCI interface has a bug. This is pretty common behavior for oracle drivers ( JDBC/ODBC drivers tend to do the same thing ). That's not to say that is an ideal situation ( It's nice to not have to bounce a server-farm when the database goes away ). As I understand the driver ( and it's limited at this point ), OCIPLogon returns back an oracle connection from a pool ( a straight zend_hash_find in oci_open_session ). It dosn't check to make sure that it is an active connection ( which would require a round-trip to the database engine which would be unnecessary the majority of the time ). The OCILogon gets around this by opening a new connection with the database each time ( which is considerably more expensive ). I don't have a great answer, but what you could/should do is to close the session when you receive a Oracle error of 03113 ( end of file on communcation channel ) or 03114 ( not connected to oracle ) is to close the persistant connection. That will free the PHP cached connection. The code would be something like ( and I havent tested this ). Using this would keep the error from happening more than once. function OCIExecuteWrapper($id,$stmt,$mode) { @OCIExecute($stmt,$mode); $err = OCIError($stmt); if(($err != false && ($err['code'] == 03113 || $err['code'] == 03114)) { trigger_error("Oracle Error: ". $err['code'] . $err['message']); OCILogoff($id); } return $err } The problem with pushing this kind of check into the OCI layer would be that, if a connection is lost your statements would be lost and need to be re-established ( including all bind variables ). I don't see how this could be done automatically. However, it does seem that automatically throwing away a know unconnected session would be a pretty interesting idea. -----Original Message----- From: merrill@missioncriticallinux.com [mailto:merrill@missioncriticallinux.com] Sent: Tuesday, March 20, 2001 9:56 AM To: php-dev@lists.php.net Subject: [PHP-DEV] PHP 4.0 Bug #9876: OCIPLogon flaky after restart Oracle From: merrill@missioncriticallinux.com Operating system: Redhat 6.1 Linux PHP version: 4.0.4pl1 PHP Bug Type: OCI8 related Bug description: OCIPLogon flaky after restart Oracle ./configure' '--prefix=/var/php' '--with-config-file-path=/var/php' '--enable-track-vars' '--enable-sigchild' '--with-apache=../apache_1.3.14' '--with-ftp' '--with-xml' '--with-mcrypt=/var/libmcrypt' '--with-pgsql=/var/postgresql' '--with-oci8=/u01/app/oracle/product/8.1.6' '--with-mysql=/var/mysql' When using OCIPLogon for persistent connections, I'm noticing that after restarting Oracle, that my PHP application starts getting Oracle errors(ORA-03113 and ORA-03114). This can be fixed by restarting the webserver after restarting Oracle. I also notice that changing my Oracle connections from OCIPLogon to OCILogon fixes the problem - after making that change I get no errors after restarting Oracle, and I *don't* have to restart the webserver. The OCI8 interface must have a bug, right? I shouldn't have to restart the webserver after restarting Oracle just because I'm using persistent connections, should I? -- Edit Bug report at: http://bugs.php.net/?id=9876&edit=1 -- PHP Development Mailing List <http://www.php.net/> To unsubscribe, e-mail: php-dev-unsubscribe@lists.php.net For additional commands, e-mail: php-dev-help@lists.php.net To contact the list administrators, e-mail: php-list-admin@lists.php.net