Re: Oracle ping

On 31/10/12 16:06, David E. Wheeler wrote:
> Fellow DBIers,
>
> When I wrote DBIx::Connector, I borrowed this code from DBIx::Class to “work around an issue”:
>
>      sub ping {
>          my ($self, $dbh) = @_;
>          eval {
>              local $dbh->{RaiseError} = 1;
>              $dbh->do('select 1 from dual');
>          };
>          return $@ ? 0 : 1;
>      }
>
> The reason for this workaround is described in [this comment](https://rt.cpan.org/Ticket/Display.html?id=47005#txn-808055) from Peter Rabbitison:

So, it appears someone discovered that DBD::Oracle's ping method can return true when you are still connected to the database (socket-wise) and yet you cannot issue a query. I didn't know that.

>> DBD::Oracle has some shutdown state in which it will return 1 on ping as long as the socket is still open. This however did not guarantee the server is any longer in a state to execute
>> queries. So what happened was:
>>
>> 1) the weird state is reached
>> 2) a txn_do takes place and fails on the first sql command
>> 3) the code calls ping() and gets a connected reply
>> 4) the txn_do is not retried
>> 5) ...
>> 6) users lose profit
>
> So a few questions about this:
>
> 1. Was this issue reported somewhere?

Not to my knowledge.

> 2. If so, was it fixed or otherwise worked around?

IIRC, DBD::Oracle has 2 ways to do ping in case OCIPing is not available.
If OCIPing is not available it does a OCIServerVersion else OCIPing.

> 3. And if it was fixed, in what version of DBD::Oracle?

As far as I can see it still does what I said under 2.
  
> Thanks,
>
> David
>

I've always assumed from the DBI docs that DBI's ping was not just supposed to say you were still connected, but you were in a state where you could issue queries, do inserts etc. It appears from what you've found that is not the case. It should be rt'ed but if anyone wants to look into what OCIPing really does and what DBD::Oracle should really do I'd appreciate it (as I am short on tuits right now).

Martin
-- 
Martin J. Evans
Easysoft Limited
http://www.easysoft.com