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.

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

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.

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

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.

>And I don't see why a solution would necessarily depend on pre-pinging.
I'd be glad to hear your proposal, Patrick.

hi tony,

how does the mysql connect function handle this problem, or does it ping the server each time?

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.

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...)

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.

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.

"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.

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).