Re: Oracle ping
On Nov 5, 2012, at 5:52 AM, John Scoles byter...@hotmail.com wrote: Does it automatically reconnect? That certainly seems like the wrong thing to do, especially in light of the docs for connect_cached. got that from the MySql doc it can be set up to reconnect if ping fails http://dev.mysql.com/doc/connector-python/en/myconnpy_MySQLConnection_ping.html not didn't check the DBD driver to see if it does this though. DBIx::Connector kills that option. https://github.com/theory/dbix-connector/blob/master/lib/DBIx/Connector/Driver/mysql.pm#L8 Best, David
Re: Oracle ping
On Nov 5, 2012, at 8:57 AM, Greg Sabino Mullane g...@turnstep.com wrote: Ah, so Oracle still requires an actual table? Thought they might have joined the rest of us by now in allowing things like 'SELECT 2+2;' :) No, it always requires a FROM clause, which is typically `FROM dual` for stuff like this. Best, David
Re: Oracle ping
On Nov 5, 2012, at 7:59 AM, Martin J. Evans martin.ev...@easysoft.com wrote: Just to reiterate what I eventually said. I don't want ping changed in DBD::Oracle. 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. Hrm. Well I wonder if I *should* just use DBI-ping in DBIx::Connector for Oracle rather than `select 1 from dual`. Or perhaps make it an option to switch to the select statement. Thoughts? David
RE: Oracle ping
Well I made that ping patch for DBIx::Connector a few months ago on one of my production boxes with no impact but then again I am not doing anything fancy (no rac raid etc). If it is critical to DBDx::Connector to ensure a query can be run then no, if you are going for 100% work alike betweeen all DBDs then no again and I am sure H.Merijn would like that. An option would be nice as well Cheers John Subject: Re: Oracle ping From: da...@justatheory.com Date: Mon, 5 Nov 2012 10:26:52 -0800 CC: c...@cam.ac.uk; dbi-dev@perl.org To: martin.ev...@easysoft.com On Nov 5, 2012, at 7:59 AM, Martin J. Evans martin.ev...@easysoft.com wrote: Just to reiterate what I eventually said. I don't want ping changed in DBD::Oracle. 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. Hrm. Well I wonder if I *should* just use DBI-ping in DBIx::Connector for Oracle rather than `select 1 from dual`. Or perhaps make it an option to switch to the select statement. Thoughts? David
Re: Oracle ping
On Nov 9, 2012, at 4:13 AM, John Scoles byter...@hotmail.com wrote: Well I made that ping patch for DBIx::Connector a few months ago on one of my production boxes with no impact but then again I am not doing anything fancy (no rac raid etc). If it is critical to DBDx::Connector to ensure a query can be run then no, if you are going for 100% work alike betweeen all DBDs then no again and I am sure H.Merijn would like that. An option would be nice as well At this point, I'm inclined to leave it as is. Why? Because one of the advantages of DBIx::Connector is fixup mode. Use that mode, and ping will only be called by txn() and friends only in the event of a failure. Which means that its overhead goes away, for the most part, no matter how it's implemented. So yeah, it's slower, but use fixup mode and avoid it altogether. Best, David
Re: Oracle ping
On Nov 2, 2012, at 4:46 AM, John Scoles byter...@hotmail.com wrote: 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. I don’t, I only know what Peter Rabbitson told me in RT #47005. David
RE: Oracle ping
From: g...@turnstep.com To: dbi-dev@perl.org Subject: Re: Oracle ping Date: Mon, 5 Nov 2012 02:47:06 + -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. 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. Yes I am comming over to that way of thinking. Might just need some clarification in the DBD::Oracle doc then. i.e. May be able to ping but you cannot run querries or alike Mysql has 'mysql_ping' the same sort of thing as OCIPing except it can reconnect. Does it automatically reconnect? That certainly seems like the wrong thing to do, especially in light of the docs for connect_cached. got that from the MySql doc it can be set up to reconnect if ping fails http://dev.mysql.com/doc/connector-python/en/myconnpy_MySQLConnection_ping.html not didn't check the DBD driver to see if it does this though. Perhaps just some clarification on the DBI spec would be better to get all the DBD that impliment it in line and doing the same thing?? Cheers - -- Greg Sabino Mullane g...@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-
Re: Oracle ping
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 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 c...@cam.ac.ukTel: +44 1223 334506, Fax: +44 1223 334679
Re: Oracle ping
On 05/11/12 15:35, Charles Jardine wrote: 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 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. Just to reiterate what I eventually said. I don't want ping changed in DBD::Oracle. 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). Martin -- Martin J. Evans Easysoft Limited http://www.easysoft.com
Re: Oracle ping
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 On Nov 5, 2012, at 4:35 PM, Charles Jardine c...@cam.ac.uk wrote: 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 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 c...@cam.ac.ukTel: +44 1223 334506, Fax: +44 1223 334679
Re: Oracle ping
-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. However it is not possible to select from any table. SELECT 1 FROM DUAL will fail. Ah, so Oracle still requires an actual table? Thought they might have joined the rest of us by now in allowing things like 'SELECT 2+2;' :) - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201211051157 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAlCX7+kACgkQvJuQZxSWSsiw9gCg7ue5/9c+qvKhw/uTpTx/4cWq 8HQAnjBdWDySKjPiwTOlOnoaTwvvdvFQ =V+96 -END PGP SIGNATURE-
Re: Oracle ping
-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. 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. Mysql has 'mysql_ping' the same sort of thing as OCIPing except it can reconnect. Does it automatically reconnect? That certainly seems like the wrong thing to do, especially in light of the docs for connect_cached. - -- Greg Sabino Mullane g...@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-
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
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 +
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
Re: Oracle ping
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 +
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
