You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@tomcat.apache.org by Jacob Rhoden <ja...@rhoden.id.au> on 2007/02/22 05:04:58 UTC
Mysql timouts...
I am having the seemingly common "Broken pipe" to mysql problem with
tomcat.
All i could find was some info about a "maxideltime" setting and
"idleconnectiontestperiod" where can I find out info about tuning to
make sure tomcat doesn't serve out stale database connections? The
following settings don't seem to be doing the trick:
<Resource auth="Container" name="jdbc/blah" type="javax.sql.DataSource"
maxActive="5" removeAbandoned="true"
maxIdle="2" maxIdleTime="300" idleConnectionTestPeriod="60"
maxWait="10000" username="blah" password="blah"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/blah?autoReconnect=true&useUnicode=true&characterEncoding=utf8"/>
Thanks guys.
Jacob
---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: Mysql timouts...
Posted by David Smith <dn...@cornell.edu>.
Sorry... my bad. My math doesn't work early in the morning. :-[
At any rate, the validation query run every minute is still acting as a
keep-alive on the connection. It'll never go stale. Personally I'd
drop the testWhileIdle, timeBetweenEvictionRuns, minEvictableIdleTime
and just let the pool test on borrow. That's the default behavior as
long as a validationQuery is specified.
--David
Jacob Rhoden wrote:
> David Smith wrote:
>
>> I think I see what's happening here. You've told the database pool to
>> continually test connections every hour. The validation query itself
>>
>>
>>> validationQuery="select 1" testWhileIdle="true"
>>> timeBetweenEvictionRunsMillis="60000"
>>> minEvictableIdleTimeMillis="60000"
>>>
>>
> This is why I am confused, isnt 60000=60 seconds? It is certinaly not
> doing an idle test every 60 seconds, why does it say Millis?
>
> Best Regards,
> Jacob
>
> ---------------------------------------------------------------------
> To start a new topic, e-mail: users@tomcat.apache.org
> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: Mysql timouts...
Posted by Jacob Rhoden <ja...@rhoden.id.au>.
David Smith wrote:
> I think I see what's happening here. You've told the database pool to
> continually test connections every hour. The validation query itself
>
>> validationQuery="select 1" testWhileIdle="true"
>> timeBetweenEvictionRunsMillis="60000"
>> minEvictableIdleTimeMillis="60000"
>>
This is why I am confused, isnt 60000=60 seconds? It is certinaly not
doing an idle test every 60 seconds, why does it say Millis?
Best Regards,
Jacob
---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: Mysql timouts...
Posted by David Smith <dn...@cornell.edu>.
I think I see what's happening here. You've told the database pool to
continually test connections every hour. The validation query itself
most likely acts as a keep alive on the connection so it never actually
hits the 8 hour default time out of mysql. Open connections will most
likely never go stale or close until tomcat itself is shutdown or the
pool starts closing the extra idle connections.
Don't know what to tell you on the logic for maxIdle. It's not well
documented other than to say that idle connections above x are closed.
And maxIdleTime is not present at all in the docs I've read so it may
not be an option.
--David
Jacob Rhoden wrote:
> Thanks for your reply, I have read through that document and the one
> on the mysql website, and discovered these other parameters, so I
> added this as well. It didnt make any difference. The mysqladmin
> program still reports the connections going untouched for hours.
>
> validationQuery="select 1" testWhileIdle="true"
> timeBetweenEvictionRunsMillis="60000"
> minEvictableIdleTimeMillis="60000"
>
> David Smith wrote:
>
>> As a side note, drop the autoReconnect=true from your database url.
>> It's not recommended and actually does not prevent SQLExceptions on
>> connections that go stale. It just restores the connection for the next
>> request after the exception. The MySQL website has further information
>> regarding what autoReconnect actually does. One of these days, the
>> tomcat docs should be updated to remove that.
>>
>
> I will have to read the documents to find out why having the
> connection automatically reconnected if there was a problem is bad
> thing, it is not clear from first reading.
>
> Best Regards,
> Jacob
>
>
> __________________________________________________________
> Jacob Rhoden - http://rhoden.id.au/
>
> ---------------------------------------------------------------------
> To start a new topic, e-mail: users@tomcat.apache.org
> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
RE: Mysql timouts...
Posted by Tim Lucia <ti...@yahoo.com>.
> -----Original Message-----
> From: Jacob Rhoden [mailto:jacob@rhoden.id.au]
> Sent: Thursday, February 22, 2007 8:44 AM
> To: Tomcat Users List
> Subject: Re: Mysql timouts...
>
> I will have to read the documents to find out why having the connection
> automatically reconnected if there was a problem is bad thing, it is not
> clear from first reading.
It's not a bad thing, it's misunderstood. Auto reconnect says that if I get
an exception because the connection is no longer valid, the NEXT time I get
a connection, automatically reconnect. Your code needs to handle the retry,
in order to benefit from auto reconnect.
Instead, you should have a validation query and set testOnBorrow=true (the
default value). That way, Tomcat (DBCP) guarantees the connection is good
before it hands it off to you, and you do not need to have the retry logic
in your application. The simplest validation query is SELECT 1 (SELECT 1
FROM DUAL - for Oracle).
Tim
---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: Mysql timouts...
Posted by Jacob Rhoden <ja...@rhoden.id.au>.
Thanks for your reply, I have read through that document and the one on
the mysql website, and discovered these other parameters, so I added
this as well. It didnt make any difference. The mysqladmin program still
reports the connections going untouched for hours.
validationQuery="select 1" testWhileIdle="true"
timeBetweenEvictionRunsMillis="60000"
minEvictableIdleTimeMillis="60000"
David Smith wrote:
> As a side note, drop the autoReconnect=true from your database url.
> It's not recommended and actually does not prevent SQLExceptions on
> connections that go stale. It just restores the connection for the next
> request after the exception. The MySQL website has further information
> regarding what autoReconnect actually does. One of these days, the
> tomcat docs should be updated to remove that.
>
I will have to read the documents to find out why having the connection
automatically reconnected if there was a problem is bad thing, it is not
clear from first reading.
Best Regards,
Jacob
__________________________________________________________
Jacob Rhoden - http://rhoden.id.au/
---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: Mysql timouts...
Posted by David Smith <dn...@cornell.edu>.
If you read through
http://tomcat.apache.org/tomcat-5.5-doc/jndi-resources-howto.html,
you'll find the validationQuery attribute is used to test connections
before they are borrowed from the pool. The query can be as simple as
"select 1".
As a side note, drop the autoReconnect=true from your database url.
It's not recommended and actually does not prevent SQLExceptions on
connections that go stale. It just restores the connection for the next
request after the exception. The MySQL website has further information
regarding what autoReconnect actually does. One of these days, the
tomcat docs should be updated to remove that.
--David
Jacob Rhoden wrote:
>
> Jacob Rhoden wrote:
>
>> I am having the seemingly common "Broken pipe" to mysql problem with
>> tomcat.
>>
>> <Resource auth="Container" name="jdbc/blah" type="javax.sql.DataSource"
>> maxActive="5" removeAbandoned="true"
>> maxIdle="2" maxIdleTime="300" idleConnectionTestPeriod="60"
>> maxWait="10000" username="blah" password="blah"
>> driverClassName="com.mysql.jdbc.Driver"
>> url="jdbc:mysql://localhost:3306/blah?autoReconnect=true&useUnicode=true&characterEncoding=utf8"/>
>>
>>
> I have installed a most up to date version of tomcat 5.5.20/mysql5.0.4
> connector on Redhat Enterprise WS 4, and according to the mysqladmin
> tool, the connections are not used (remain in sleep mode for way more
> than the idleConnectionTestPeriod variable), which makes me think this
> configuration is invalid. Is there anyway to ensure tomcat
> tests/refreshes its database connections?
>
> Best Regards,
> Jacob
>
---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: Mysql timouts...
Posted by Jacob Rhoden <ja...@rhoden.id.au>.
Jacob Rhoden wrote:
> I am having the seemingly common "Broken pipe" to mysql problem with
> tomcat.
>
> <Resource auth="Container" name="jdbc/blah" type="javax.sql.DataSource"
> maxActive="5" removeAbandoned="true"
> maxIdle="2" maxIdleTime="300" idleConnectionTestPeriod="60"
> maxWait="10000" username="blah" password="blah"
> driverClassName="com.mysql.jdbc.Driver"
> url="jdbc:mysql://localhost:3306/blah?autoReconnect=true&useUnicode=true&characterEncoding=utf8"/>
>
>
I have installed a most up to date version of tomcat 5.5.20/mysql5.0.4
connector on Redhat Enterprise WS 4, and according to the mysqladmin
tool, the connections are not used (remain in sleep mode for way more
than the idleConnectionTestPeriod variable), which makes me think this
configuration is invalid. Is there anyway to ensure tomcat
tests/refreshes its database connections?
Best Regards,
Jacob
---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org