commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dennis Lundberg <denn...@apache.org>
Subject Re: [dbcp] connection error while querying to db after being 7+ hours idle.
Date Fri, 23 Jul 2010 10:30:40 GMT
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.

> 
> 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
>>
>>
> 


-- 
Dennis Lundberg

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


Mime
View raw message