You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Andrew Willerding <aw...@itsurcom.com> on 2012/12/11 15:57:02 UTC
Database timeout questions
I had a situation yesterday that I didn't expect and therefore didn't
handle correctly. My code is executing a Stored Procedure on an Oracle
DB using the performGenericQuery method. The problem was that the
Oracle server was not responding to the stored procedure and the
eventually the query returned "successfully" with an empty result. I
was expecting some sort of exception to be thrown (like a timeout) in
this situation but it definitely was not the case. I currently measure
the transaction time and the empty response was returning consistently
at 60 seconds - it normally takes less than 2 seconds. I am not sure if
this 60 second timeout is set on the Oracle server or somewhere in the
Cayenne connection.
I have three questions:
1) Is there a way to set a timeout value within the Cayenne connection,
or better yet, an individual query to force a timeout exception if a
response is not received within a certain period of time?
2) Assuming the timeout is triggered from the Oracle server for the
situation I describe above, other than examining the timeout value
within my code, is there a way to differentiate between a successful
transaction and one where the DB server times out and returns an empty
result set?
3) Assuming that the answer is "no" to both questions 1 & 2, is there a
way to "kill" a query in order to release the DB connection and return
it to the pool?
I am using V3.0.2.
Thanks,
Andrew
Re: Queries during lost connections
Posted by Mike Kienenberger <mk...@gmail.com>.
On Fri, Dec 28, 2012 at 7:50 AM, Marek Šabo <ms...@buk.cvut.cz> wrote:
> is there any hook for handling lost connections in Cayenne?
I can't find our most recent documentation on this, but it used to
happen automatically with supported databases.
https://cwiki.apache.org/removed/CAY/reconnect.html
http://objectstyle.org/cayenne/lists/cayenne-user/2005/04/0053.html
I have a Cayenne 1.1 system that I maintain which uses Oracle, and
while it may have to fail on two or three queries because of the cycle
of errors returned from an Oracle database when the connection is
lost, it does recover after about three queries.
> My use-case is a system that uses Cayenne with remote database. But when the
> network is down it still needs to fall-back to set of last known values.
>
> I would like to know if Cayenne can let me know somehow that it lost
> connection to DB.
>
> Another thing is - when the DB connection is lost, does Cayenne still serve
> whatever it has in cache? I use only selects and only one insert.
You will have to write your own code to handle what to do during the
failed queries. A disconnect-related error should come back as a
CayenneRuntimeException. When you handle it, you will need to
determine if it's a a lost connection error (it probably could be
assumed to be once you're in production) and then return a different
set of queries. I haven't tried to do this, so I can't tell you the
best way to pull the objects out of a cache.
Queries during lost connections
Posted by Marek Šabo <ms...@buk.cvut.cz>.
Hi all,
is there any hook for handling lost connections in Cayenne?
My use-case is a system that uses Cayenne with remote database. But when
the network is down it still needs to fall-back to set of last known values.
I would like to know if Cayenne can let me know somehow that it lost
connection to DB.
Another thing is - when the DB connection is lost, does Cayenne still
serve whatever it has in cache? I use only selects and only one insert.
Thanks for ideas,
Marek
Re: Database timeout questions
Posted by Andrus Adamchik <an...@objectstyle.org>.
Presumably SQLException is thrown:
http://docs.oracle.com/javase/6/docs/api/java/sql/Statement.html#setQueryTimeout%28int%29
Andrus
On Dec 12, 2012, at 5:38 AM, Andrew Willerding <aw...@itsurcom.com> wrote:
> Thanks for the "old" advice. ;-) I will definitely try it although I'm not sure if the condition that created this situation may happen again on the Oracle server. A lot of people have a lot of explaining to do to figure out what was going wrong. Luckily I'm just a dumb client in this situation.
>
> One more question though. If it does work and the driver accepts the timeout value I set, what can I expect to happen when the timer expires? Is there a specific exception I need to catch or a condition I need to check?
>
> Andrew
>
>
> On 12/11/2012 02:33 PM, Andrus Adamchik wrote:
>> At the JDBC level timeout can be controlled via Statement.setQueryTimeout(..). However YMMV between different DBs and drivers. Don't have much recent Oracle experience, but if Oracle driver happens to support this API, you can use this old advice [1] which is still applicable. Moreover if you have any success with it, please open a Jira and we'll integrate it in the basic Cayenne SelectQuery.
>>
>> Still your description sounds odd. If there is problem with a query on the Oracle side, I would expect an exception. Though fwiw Oracle driver has always been an odd one among all the DBs supported by Cayenne.
>>
>> Andrus
>>
>> [1] http://cayenne.195.n3.nabble.com/query-timeout-needed-td1070.html
>>
>> On Dec 11, 2012, at 5:57 PM, Andrew Willerding <aw...@itsurcom.com> wrote:
>>
>>> I had a situation yesterday that I didn't expect and therefore didn't handle correctly. My code is executing a Stored Procedure on an Oracle DB using the performGenericQuery method. The problem was that the Oracle server was not responding to the stored procedure and the eventually the query returned "successfully" with an empty result. I was expecting some sort of exception to be thrown (like a timeout) in this situation but it definitely was not the case. I currently measure the transaction time and the empty response was returning consistently at 60 seconds - it normally takes less than 2 seconds. I am not sure if this 60 second timeout is set on the Oracle server or somewhere in the Cayenne connection.
>>>
>>> I have three questions:
>>>
>>> 1) Is there a way to set a timeout value within the Cayenne connection, or better yet, an individual query to force a timeout exception if a response is not received within a certain period of time?
>>>
>>> 2) Assuming the timeout is triggered from the Oracle server for the situation I describe above, other than examining the timeout value within my code, is there a way to differentiate between a successful transaction and one where the DB server times out and returns an empty result set?
>>>
>>> 3) Assuming that the answer is "no" to both questions 1 & 2, is there a way to "kill" a query in order to release the DB connection and return it to the pool?
>>>
>>> I am using V3.0.2.
>>>
>>> Thanks,
>>>
>>> Andrew
>
>
Re: Database timeout questions
Posted by Andrew Willerding <aw...@itsurcom.com>.
Thanks for the "old" advice. ;-) I will definitely try it although
I'm not sure if the condition that created this situation may happen
again on the Oracle server. A lot of people have a lot of explaining to
do to figure out what was going wrong. Luckily I'm just a dumb client
in this situation.
One more question though. If it does work and the driver accepts the
timeout value I set, what can I expect to happen when the timer
expires? Is there a specific exception I need to catch or a condition I
need to check?
Andrew
On 12/11/2012 02:33 PM, Andrus Adamchik wrote:
> At the JDBC level timeout can be controlled via Statement.setQueryTimeout(..). However YMMV between different DBs and drivers. Don't have much recent Oracle experience, but if Oracle driver happens to support this API, you can use this old advice [1] which is still applicable. Moreover if you have any success with it, please open a Jira and we'll integrate it in the basic Cayenne SelectQuery.
>
> Still your description sounds odd. If there is problem with a query on the Oracle side, I would expect an exception. Though fwiw Oracle driver has always been an odd one among all the DBs supported by Cayenne.
>
> Andrus
>
> [1] http://cayenne.195.n3.nabble.com/query-timeout-needed-td1070.html
>
> On Dec 11, 2012, at 5:57 PM, Andrew Willerding <aw...@itsurcom.com> wrote:
>
>> I had a situation yesterday that I didn't expect and therefore didn't handle correctly. My code is executing a Stored Procedure on an Oracle DB using the performGenericQuery method. The problem was that the Oracle server was not responding to the stored procedure and the eventually the query returned "successfully" with an empty result. I was expecting some sort of exception to be thrown (like a timeout) in this situation but it definitely was not the case. I currently measure the transaction time and the empty response was returning consistently at 60 seconds - it normally takes less than 2 seconds. I am not sure if this 60 second timeout is set on the Oracle server or somewhere in the Cayenne connection.
>>
>> I have three questions:
>>
>> 1) Is there a way to set a timeout value within the Cayenne connection, or better yet, an individual query to force a timeout exception if a response is not received within a certain period of time?
>>
>> 2) Assuming the timeout is triggered from the Oracle server for the situation I describe above, other than examining the timeout value within my code, is there a way to differentiate between a successful transaction and one where the DB server times out and returns an empty result set?
>>
>> 3) Assuming that the answer is "no" to both questions 1 & 2, is there a way to "kill" a query in order to release the DB connection and return it to the pool?
>>
>> I am using V3.0.2.
>>
>> Thanks,
>>
>> Andrew
Re: Database timeout questions
Posted by Andrus Adamchik <an...@objectstyle.org>.
At the JDBC level timeout can be controlled via Statement.setQueryTimeout(..). However YMMV between different DBs and drivers. Don't have much recent Oracle experience, but if Oracle driver happens to support this API, you can use this old advice [1] which is still applicable. Moreover if you have any success with it, please open a Jira and we'll integrate it in the basic Cayenne SelectQuery.
Still your description sounds odd. If there is problem with a query on the Oracle side, I would expect an exception. Though fwiw Oracle driver has always been an odd one among all the DBs supported by Cayenne.
Andrus
[1] http://cayenne.195.n3.nabble.com/query-timeout-needed-td1070.html
On Dec 11, 2012, at 5:57 PM, Andrew Willerding <aw...@itsurcom.com> wrote:
> I had a situation yesterday that I didn't expect and therefore didn't handle correctly. My code is executing a Stored Procedure on an Oracle DB using the performGenericQuery method. The problem was that the Oracle server was not responding to the stored procedure and the eventually the query returned "successfully" with an empty result. I was expecting some sort of exception to be thrown (like a timeout) in this situation but it definitely was not the case. I currently measure the transaction time and the empty response was returning consistently at 60 seconds - it normally takes less than 2 seconds. I am not sure if this 60 second timeout is set on the Oracle server or somewhere in the Cayenne connection.
>
> I have three questions:
>
> 1) Is there a way to set a timeout value within the Cayenne connection, or better yet, an individual query to force a timeout exception if a response is not received within a certain period of time?
>
> 2) Assuming the timeout is triggered from the Oracle server for the situation I describe above, other than examining the timeout value within my code, is there a way to differentiate between a successful transaction and one where the DB server times out and returns an empty result set?
>
> 3) Assuming that the answer is "no" to both questions 1 & 2, is there a way to "kill" a query in order to release the DB connection and return it to the pool?
>
> I am using V3.0.2.
>
> Thanks,
>
> Andrew