commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Phil Steitz <phil.ste...@gmail.com>
Subject Re: [dbcp] connection error while querying to db after being 7+ hours idle.
Date Mon, 26 Jul 2010 01:15:29 GMT
Dennis Lundberg wrote:
> On 2010-07-22 23:48, Minhaj wrote:
>> Hi Dennis,
>>
>> Thanks for your reply.
>>
>> Well, some one told me to put the testWhileIdle property true but it's not
>> working for me still same issue; no luck :(
>>
>> Secondly, You must be talking about "minEvictableIdleTimeMillis" property?
>> Aren't you?
> 
> No, I don't use DBCP. I was referring to a feature we have in our home
> made connection pool. That feature allows you to have a validation tread
> run that periodically runs a validation query to make sure the
> connections in the pool are kept alive. For MySQL we use the value
> "SELECT 1" for this validation query.
> 
> As Mark hinted in his reply there is an option in DBCP called
> "validationQuery" that you can use. The difference is that in DBCP this
> query runs every time you get a connection from the pool.

When the validation query gets executed depends on the settings of
the other parameters:

testOnBorrow = true <-> the validation query is run each time a
connection is borrowed from the pool.

testOnReturn = true <-> the validation query is run each time a
connection is returned to the pool.

testWhileIdle = true <-> connections idle in the pool are tested
periodically.  How often they get tested depends on the values of
timeBetweenEvictionRunsMillis (how often pool maintenance happens)
and numTestsPerEvictionRun (how many idle objects are examined each
run) Successive runs cycle repeatedly through the idle connections.
 For example, with testWhileIdle = true, timeBetweenEvictionRuns =
600000, numTestsPerEvictionRun = 10 and a pool with 100 idle
connections, maintenance will run every 10 minutes and it will take
10 runs to cycle through the whole pool, so connections will on
average get tested once every 100 minutes.  If maxIdle = 100, this
is effectively an upper bound on validation frequency.  It is
possible, however, if testOnBorrow = testOnReturn = false, that
connections could "escape" validation indefinitely in this scenario
if they happen to always be checked out to clients when maintenance
runs.

Phil
> 
>> Following is my setting for "minEvictableIdleTimeMillis" property.
>> # The minimum amount of time an object may sit idle in the pool before it is
>> # eligable for eviction by the idle object evictor (if any).
>> # [Default: 180000 (30 min)]
>> dbcp.minEvictableIdleTimeMillis=180000
>> Here you can find the complete configuration details.
>> http://commons.apache.org/dbcp/configuration.html
>>
>> So rather then DBCP what do use? what are your recommendation? c3p0? my
>> application is a spring+hibernate based app.
>>
>>
>> Kind Regards
>>
>> Minhaj
>>
>> PS: for your information my application is a mulithreaded application where
>> lots of threads running concurrently.
>>
>> On Fri, Jul 23, 2010 at 12:50 AM, Dennis Lundberg <dennisl@apache.org>wrote:
>>
>>> On 2010-07-21 07:37, Minhaj wrote:
>>>> Hi,
>>>>
>>>> I'm facing the following problem when I'm trying to access database in
>>> the
>>>> morning. which means after use application after being 7+ hours idle. I
>>> even
>>>> tried to append autoReconnect=true into db connection string but no luck
>>> yet
>>>> :( and the most strangest thing is that this error rise only once in very
>>>> first query, but when i try next time it works perfectly alright.
>>>>
>>>> Anyone ever faced such issue?
>>> Yes. What happens is that the MySQL server closes the connecion at the
>>> server end, due to it not being used for an extended period of time. In
>>> our experience this timeout is 8 hours. The server does not notify DBCP
>>> or the calling application. So you end up with a JDBC connection on the
>>> Java end that is unusable.
>>>
>>> What you need to do is make sure that each connection in the pool is
>>> used every 8 hours or less. I don't use DBCP myself, but there usually
>>> is a timer you can set in  a connection pool that runs a really simple
>>> keep-alive query in a given interval.
>>>
>>>>
>>>> 2010-07-21 07:00:48,554  WARN (JDBCExceptionReporter.java:100)
>>>> [org.hibernate.util.JDBCExceptionReporter, logExceptions] - SQL Error: 0,
>>>> SQLState: 08S01
>>>> 2010-07-21 07:00:48,555 ERROR (JDBCExceptionReporter.java:101)
>>>> [org.hibernate.util.JDBCExceptionReporter, logExceptions] - The last
>>> packet
>>>> successfully received from the server was 677,246,024 milliseconds ago.
>>>  The
>>>> last packet sent successfully to the server was 677,246,024 milliseconds
>>>> ago. is longer than the server configured value of 'wait_timeout'. You
>>>> should consider either expiring and/or testing connection validity before
>>>> use in your application, increasing the server configured values for
>>> client
>>>> timeouts, or using the Connector/J connection property
>>> 'autoReconnect=true'
>>>> to avoid this problem.
>>>>
>>>>
>>>>
>>>> Thanks are in advance.
>>>>
>>>
>>> --
>>> Dennis Lundberg
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
>>> For additional commands, e-mail: user-help@commons.apache.org
>>>
>>>
> 
> 


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org


Mime
View raw message