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: martin.ev...@easysoft.com
> To: da...@justatheory.com
> CC: dbi-dev@perl.org
> 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
