php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #30808 oci8 cannot connect after restarting DB
Submitted: 2004-11-16 20:41 UTC Modified: 2005-09-08 11:49 UTC
Votes:18
Avg. Score:4.9 ± 0.3
Reproduced:16 of 17 (94.1%)
Same Version:7 (43.8%)
Same OS:4 (25.0%)
From: michael dot caplan at lechateau dot ca Assigned: tony2001 (profile)
Status: Closed Package: OCI8 related
PHP Version: 5.0.2 OS: RHE 3
Private report: No CVE-ID: None
 [2004-11-16 20:41 UTC] michael dot caplan at lechateau dot ca
Description:
------------
Every evening, for whatever reason, our Oracle db (9.2.0) is restated. After it is restarted, I am unable to build a connection with Oracle from PHP untill Apache is restarted.  
 
Whenever I do an ociplogon() or ocilogon() following an Oracle DB restart, it fails but I am unable to get a description of the error.  Restarting Apache "fixes" the problem.  
 
This problem is similar to bug #26829 except that it effects both persistent and non persistent conneciton.



Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2004-11-17 07:35 UTC] tony2001@php.net
Currently you have to uncomment oci_ping() call in _oci_open_server() function (see ext/oci8/oci8.c, line 3048). 
It was planned to add an ini option for that, but it seems that nobody likes new ini options and I quess we'll take care about it in brand new PDO OCI driver.

Personally I do not have any objections to adding new ini option, so I'm marking this resport as suspended for the time present.
 [2004-11-18 15:09 UTC] michael dot caplan at lechateau dot ca
Uncommenting the oci_ping() call does the trick.  Both persitant and non persistant connection then work after an Oracle restart.

But your reply confuses me.  Why a new ini option (you mean at compile time?)?  Shouldn't this be the default behaviour?  And why should I wait till PDO for a stable way to connect to Oracle?  Shouldn't this issue be resolved in the current PHP OCI8 function library?

Thanks,

Michael
 [2004-11-18 15:21 UTC] tony2001@php.net
New ini option is needed because pinging every connection would lead to huge overhead. 
Your situation in fact is an exception, rather then a rule - most people do not restart DB every day, so they would definitely not agree with this slowdown.

 [2004-11-18 16:59 UTC] michael dot caplan at lechateau dot ca
Ah, yes -- I suppose pinging every time would be a huge overhead.  But, how do other db supported by PHP (mysql for example) handle this issue?  I can most definately restart mysqld without needed to restart apache to kill all my mysql persistant connections.  The mysql library seams to handle this without issue (or perhaps this is something that is negotiated on the mysqld side)?

Also, perhaps I am miss understanding you, but you suggest that this issue will be fixed in PDO OCI.  If so, why cannot it be fixed in the current OCI8 library?

Thanks,

Michael
 [2004-11-18 17:25 UTC] patrick dot lambert at noggin dot com
It would be great if this flaw in the OCI8 
implementation could be resolved. It has been bugged 
numerous times over the years. And I don't see why a 
solution would necessarily depend on pre-pinging.
 [2004-11-18 17:54 UTC] tony2001@php.net
>And I don't see why a solution would necessarily depend on pre-pinging.
I'd be glad to hear your proposal, Patrick.

 [2004-11-18 17:59 UTC] michael dot caplan at lechateau dot ca
hi tony,

how does the mysql connect function handle this problem, or does it ping the server each time?
 [2004-11-18 22:53 UTC] tony2001@php.net
Nope, mysql_connect() reconnects every time, as ociNlogon() does, so it does not need to ping server.
ocilogon(), however, doesn't "reconnect" on every request, as this operation in Oracle is rather slow, comparing with MySQL (you could try to compare ociNlogon() vs ocilogon() speed and see the difference).
instead, it only creates new session, but server connection is stored persistently and this causes your problem.
 [2004-11-19 10:14 UTC] giunta dot gaetano at sea-aeroportimilano dot it
It is very funny that I never experienced this problem, given the really heavy usage we do of PHP+Oci at work. But then I stick with php 4 for now: does this bug also apply to the 'old' version?

Anyway, here's what I generally do, both in C++ OCI sw and php scripts that run as services (ie never-ending): 1) connect to db 2) exec query 3) if query fails the close and reopen connection 4) goto 2

In this usage pattern the PHP connection to Oracle is not persistent (it is kept open by the script anyway). And the scripts have been chugging away for years thru network outages, DB upgrades and all.

I wonder if a similar pattern could solve your problem in a PHP as Apache module situation: let the app layer do the actual 'pinging' of the DB instead of tho oci layer (eg try to close and reopen pending db connections when something fails...)
 [2004-11-19 14:20 UTC] michael dot caplan at lechateau dot ca
FYI, I am using a DB abstraction library (PEAR::MDB2), and just realized for non persistant connections it is using ociNlogon() for non persistant connections.  So, to revise my original report, it is not just ociplogon() that is affected, but also ociNlogon() as well.  This certainly does put a new spin on things and perhaps warrants unsuspending this bug report.
 [2005-01-06 22:25 UTC] gid at gifpaste dot net
Our development database server also goes down every night for a cold back up so we get this error every morning in development.  (our production server however is always up)

This is rather disturbing that this is happening even when using ocinlogon.  If I want to create a new connection to oracle, then php should trust that I, the programmer know what I am doing.  Maybe put a note in the manual stating that ocinlogon should ONLY be used after ocilogon or ociplogon fails.

Maybe it's me, but this ini setting seems like a hack to me.  If an oracle connection gets in a corrupted state, then I should be able to detect the bad oracle connection, close it, and try opening a new connection all in php code.  Just because someone takes their database server down every night doesn't mean they should have to pay the penalty of oci_ping on every connection.
 [2005-08-24 00:22 UTC] msheldon at ultradns dot com
"New ini option is needed because pinging every connection would lead to huge overhead."

I've got a better idea.

How about giving us a function to forcibly terminate a database connection? I can detect when this happens in my PHP code. Unfortunately, it doesn't do a darned bit of good, since the only thing trhat will fix the problem is to restart Apache.

I don't need PHP to check the connection for every call, I just need to be able to deal with it when *I* detect a problem.
 [2005-09-08 11:49 UTC] tony2001@php.net
The bug has been fixed in OCI8 v.1.1, which is available in CVS HEAD and PECL (use `pear install oci8-beta` to install it).
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Fri Mar 29 11:01:29 2024 UTC