On 02/11/2012 11:46, John Scoles wrote:
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'
Thinking about it, wrt executing a query or inserting/updating/deleting
it cannot be full proof anyway as things can change after you called
OCIPing/OCIServerVersion. From the point of DBI, the ping method is
mostly used to indicate the connection is dead (ODBC has something
similar in SQL_ATTR_CONNECTION_DEAD). If ping returns false a cached
connection has to be renewed. If ping returns true a cached connection
is not dead at the point ping was called but might not work thereafter.
The replacement in DBIx::Connector which issues a select is better (in
Oracle's case than DBD::Oracle's ping) but still could suffer from
something changing in Oracle server since the "select 1 from dual"
returned ok.
It is a race condition - there is no absolute solution in this case. So,
we can rely on ping (or select 1 from dual) returning false but not on
it returning true.
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?
but as explained above, I doubt you can 100% - there is a period of time
elapsing from checking and doing.
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
Live with it - I doubt you'll get much better in DBD::Oracle and
certainly not across DBDs. You should however, be able to rely on ping
returning false. Perhaps the DBI docs should be updated to explain this
better.
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
and I doubt such a change is possible.
3) Perhaps a new method like dbd->can_query??
which will still suffer from a race condition.
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
Probably not what you want to hear but it is the way it is.
Apologies for not giving this sufficient thought in my last reply - bad
day all round on my part.
Martin
----------------------------------------
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
