Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
Re: Oracle ping
17 views
Skip to first unread message
Martin J. Evans
Nov 2, 2012, 5:59:30 AM11/2/12
to David E. Wheeler, dbi...@perl.org
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
> 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
Martin J. Evans
Nov 2, 2012, 4:38:56 PM11/2/12
to dbi...@perl.org
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...@easysoft.com
>> To: da...@justatheory.com
>> CC: dbi...@perl.org
>> Subject: 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'
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...@easysoft.com
>> To: da...@justatheory.com
>> CC: dbi...@perl.org
>> Subject: Re: Oracle ping
Greg Sabino Mullane
Nov 4, 2012, 9:47:06 PM11/4/12
to dbi...@perl.org
-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160
> 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."
anytime ever. I personally think a simple select is better than relying
on a low-level library call, as it does a more complete end-to-end check
that is almost certainly closer to what the caller is going to use the
connection for.
> Mysql has 'mysql_ping' the same sort of thing as OCIPing except it can reconnect.
to do, especially in light of the docs for connect_cached.
- --
Greg Sabino Mullane gr...@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201211042146
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iEYEAREDAAYFAlCXKI0ACgkQvJuQZxSWSsgMAACg4VdWNpWYgyrENkXKhO9G5Mez
/fkAoM6hBowTxDCAvKOD6G7yQBgItiO1
=85sU
-----END PGP SIGNATURE-----
Charles Jardine
Nov 5, 2012, 10:35:03 AM11/5/12
to dbi...@perl.org
On 05/11/12 02:47, Greg Sabino Mullane wrote:
>> 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."
>
> This is a bit of a red herring. You could say that about any database action,
> anytime ever. I personally think a simple select is better than relying
> on a low-level library call, as it does a more complete end-to-end check
> that is almost certainly closer to what the caller is going to use the
> connection for.
I rather strongly disagree. It is possible to do useful work over a
>> 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."
>
> This is a bit of a red herring. You could say that about any database action,
> anytime ever. I personally think a simple select is better than relying
> on a low-level library call, as it does a more complete end-to-end check
> that is almost certainly closer to what the caller is going to use the
> connection for.
connection
in situations when a 'simple select' is not possible. My production
Oracle database is protected by a physical standby database. This
database is, in Oracle terms, mounted but not open. A user with the SYSADMIN
privilege can connect to the database. It is possible use virtual views to
monitor the database and ALTER SYSTEM commands to change its state. However
it is not possible to select from any table. SELECT 1 FROM DUAL will fail.
I would like to see $dbh->ping to continue to return a true values in cases
like this.
--
Charles Jardine - Computing Service, University of Cambridge
cj...@cam.ac.uk Tel: +44 1223 334506, Fax: +44 1223 334679
Martin J. Evans
Nov 5, 2012, 10:59:00 AM11/5/12
to Charles Jardine, dbi...@perl.org
All I was saying is you should only read anything useful into ping returning false and not true unless your only purpose is like Oracle's OCIPing which checks your connection is still there (although not usable for some things). I believe the connection caching only does anything when ping returns false.
Like Charles, I also have database systems where the users who log in have no select access at all - in fact all you can do is call some procedures in a few packages (no select, no insert, update, delete, you cannot even seen any schema). A ping implemented as "select xx" is useless to me (not that I rely on it any way).
Henri Asseily
Nov 5, 2012, 11:03:26 AM11/5/12
to Charles Jardine, dbi...@perl.org
I would tend to think that such a ping would be a special case, but I agree with you to keep it as is:
Simply because overloading ping() with a complete eval'ed select statement would be trivial. And the opposite isn't.
---
Henri Asseily
henri.tel
Simply because overloading ping() with a complete eval'ed select statement would be trivial. And the opposite isn't.
---
Henri Asseily
henri.tel
Greg Sabino Mullane
Nov 5, 2012, 11:57:33 AM11/5/12
to dbi...@perl.org
-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160
> I rather strongly disagree. It is possible to do useful work over a
> connection in situations when a 'simple select' is not possible.
> My production Oracle database is protected by a physical standby database.
Ah, well in that case I withdraw my previous statement.
> connection in situations when a 'simple select' is not possible.
> My production Oracle database is protected by a physical standby database.
> However it is not possible to select from any table.
> SELECT 1 FROM DUAL will fail.
have joined the rest of us by now in allowing things like
'SELECT 2+2;' :)
PGP Key: 0x14964AC8 201211051157
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iEYEAREDAAYFAlCX7+kACgkQvJuQZxSWSsiw9gCg7ue5/9c+qvKhw/uTpTx/4cWq
-----BEGIN PGP SIGNATURE-----
8HQAnjBdWDySKjPiwTOlOnoaTwvvdvFQ
=V+96
-----END PGP SIGNATURE-----
0 new messages