RE: Oracle ping

Boy I think I really started something here.

I just heard back from OIC connection over at Oracle and here is his two cents on the issue

"From an OCI point of view the OCIPing/OCIServerVersion calls are as
good as it gets for basic aliveness of the connection. I'd never have
thought they would guarantee the DB was writable. I believe they just
check the OCI and Network stacks. Since the connection doesn't know
anything about DB privileges or what action might be attempted on the
connection, I don't think the calls could do more. Also by the time
an application does execute some SQL, the the
connection/node/server/network could have dropped and so the application
'will have to check & recover from write failures at that point anyway."

So OCIPing and OCIServerVersion do the same thing and yes they 'Do not check that a query can be executed'

As for history of Ping in DBD::Oracle 

All version up till 1.21 use a query with a do
1.22 till 1.24 use just OCIServerversion
1.25 and later use either OICPing or OCIServerversion depending on the Oracle client.

I think in the case of DBIx::Connector you want to really 100% know that a connection can execute a Query? 
 The DBI doc sort of implies that but I checked a few other DBD and I see that DBD::Pg  uses an 'select' and Mysql has 'mysql_ping' the same sort of thing as OCIPing except it can reconnect.  

So For three DBDs you may see three separate behavours for DBH->ping

1) working but a zombie
2) error
3) reconnection, error or a zombie

So perhaps we need

1) A clarification on what DBH->ping does or is suppose to do (Tim Jump in)
2) A if DBH->Ping really means I can run a query a change to DBD::Oracle 
3) Perhaps a new method like dbd->can_query??

David if you have a repeatable test or set of conditions or recipe of how you get into this state where DBD::Oracle pings but cannot run queries my buddies over at Oracle would love to get it.

Cheers
John

----------------------------------------
> Date: Fri, 2 Nov 2012 09:59:30 +0000
> From: [email protected]
> To: [email protected]
> CC: [email protected]
> Subject: 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