Bug #72665 for DBD-Oracle: ping not checking if connection is active

Wed Nov 23 11:00:51 2011 jason.crummack [...] easysoft.com - Ticket created

Subject:

ping not checking if connection is active

http://search.cpan.org/~timb/DBI-1.616/DBI.pm#ping states that ping should check that the connection is running, I'm finding that this isn't the case with sessions that have been closed from the oracle server side based on hitting session cpu quota limits. to setup a simple per session quota Show quoted text

SQL> ALTER SYSTEM SET resource_limit=TRUE SCOPE=BOTH; SQL> CREATE PROFILE testprofile LIMIT CPU_PER_SESSION 100; -- 1 second SQL> ALTER USER testuser PROFILE testprofile;

this is telling oracle to close this session once it's had 1 second of cpu time (one second only for testing purposes), we use this mechanism to avoid any long running queries via our services. If I have code that runs as this user i'll receive the following ORA-02392: exceeded session limit on CPU usage, you are being logged off once i've hit the quota after this point calling ping returns 1 but if I attempt to use the handle to create a new statement i get ORA-01012: not logged on. should ping be checking that the current handle is still connected/useable?

Fri Nov 25 14:37:51 2011 bohica [...] ntlworld.com - Correspondence added

On Wed Nov 23 11:00:51 2011, jscrummack wrote: Show quoted text

> http://search.cpan.org/~timb/DBI-1.616/DBI.pm#ping > > states that ping should check that the connection is running, I'm > finding that this isn't the case with sessions that have been closed > from the oracle server side based on hitting session cpu quota limits. > > to setup a simple per session quota >

> SQL> ALTER SYSTEM SET resource_limit=TRUE SCOPE=BOTH; > SQL> CREATE PROFILE testprofile LIMIT CPU_PER_SESSION 100; -- 1 second > SQL> ALTER USER testuser PROFILE testprofile;

> > this is telling oracle to close this session once it's had 1 second of > cpu time (one second only for testing purposes), we use this mechanism > to avoid any long running queries via our services. > > If I have code that runs as this user i'll receive the following > > ORA-02392: exceeded session limit on CPU usage, you are being logged off > > once i've hit the quota > > after this point calling ping returns 1 but if I attempt to use the > handle to create a new statement i get > > ORA-01012: not logged on. > > should ping be checking that the current handle is still

connected/useable? There are 2 ping methods which are used depending on which Oracle client you are using. The method you are using is OCIPing which does a round trip with a null packet and it would seem this does not check your session is still active. The other method ora_server_version (or whatever it is - something like that, cannot be bothered to look it up again right now) might check the session is active. If it does then either the default should be changed to that (to comply with DBI) or there should be some way to pick the ping method. Martin -- Martin J. Evans Wetherby, UK

Fri Nov 25 14:37:52 2011 The RT System itself - Status changed from 'new' to 'open'

Sun Nov 27 16:13:10 2011 Tim.Bunce [...] pobox.com - Correspondence added

Subject:	Re: [rt.cpan.org #72665] ping not checking if connection is active
Date:	Sun, 27 Nov 2011 21:12:48 +0000
To:	Martin J Evans via RT <bug-DBD-Oracle [...] rt.cpan.org>
From:	Tim Bunce <Tim.Bunce [...] pobox.com>

On Fri, Nov 25, 2011 at 02:37:51PM -0500, Martin J Evans via RT wrote: Show quoted text

> There are 2 ping methods which are used depending on which Oracle client > you are using. The method you are using is OCIPing which does a round > trip with a null packet and it would seem this does not check your > session is still active. The other method ora_server_version (or > whatever it is - something like that, cannot be bothered to look it up > again right now) might check the session is active. If it does then > either the default should be changed to that (to comply with DBI) or > there should be some way to pick the ping method.

The default should be something that checks the session. A network-connection-only ping could be provided as a private method (eg ora_network_ping). Tim.

Tue Nov 29 04:24:29 2011 bohica [...] ntlworld.com - Correspondence added

On Wed Nov 23 11:00:51 2011, jscrummack wrote: Show quoted text

> http://search.cpan.org/~timb/DBI-1.616/DBI.pm#ping > > states that ping should check that the connection is running, I'm > finding that this isn't the case with sessions that have been closed > from the oracle server side based on hitting session cpu quota limits. > > to setup a simple per session quota >

> SQL> ALTER SYSTEM SET resource_limit=TRUE SCOPE=BOTH; > SQL> CREATE PROFILE testprofile LIMIT CPU_PER_SESSION 100; -- 1 second > SQL> ALTER USER testuser PROFILE testprofile;

> > this is telling oracle to close this session once it's had 1 second of > cpu time (one second only for testing purposes), we use this mechanism > to avoid any long running queries via our services. > > If I have code that runs as this user i'll receive the following > > ORA-02392: exceeded session limit on CPU usage, you are being logged

off Show quoted text

> > once i've hit the quota > > after this point calling ping returns 1 but if I attempt to use the > handle to create a new statement i get > > ORA-01012: not logged on. > > should ping be checking that the current handle is still

connected/useable? ora_ping (what DBI ping calls) works when OCIPing is used. The problem here was a wrapper around DBI's ping. Martin -- Martin J. Evans Wetherby, UK

Tue Nov 29 04:24:55 2011 bohica [...] ntlworld.com - Status changed from 'open' to 'rejected'

Bug #72665 for DBD-Oracle: ping not checking if connection is active

Preferred bug tracker