tomcat-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mark Eggers <its_toas...@yahoo.com>
Subject Re: connection autoReconnect?
Date Sun, 29 Jan 2012 03:16:32 GMT
Answers and comments are inline (mostly).

----- Original Message -----

> From: Jerry Malcolm <2ndgenfilms@gmail.com>
> To: Tomcat Users List <users@tomcat.apache.org>; Mark Eggers <its_toasted@yahoo.com>
> Cc: 
> Sent: Saturday, January 28, 2012 5:32 PM
> Subject: Re: connection autoReconnect?
> 
> Not good news.  I changed every resource statement in server.xml to
> something like this:
> 
>       <Resource* testOnBorrow="true" validateQuery="SELECT
> 1"*name="jdbc/xxxxxxx" auth="Container" 
> type="javax.sql.DataSource"
> maxActive="100" maxIdle="30" maxWait="10000" 
> removeAbandoned="true"
> removeAbandonedTimeout="60" logAbandoned="true" 
> username="xxxxxxxx"
> password="xxxxxxxx" driverClassName="com.mysql.jdbc.Driver"
> url="jdbc:mysql://127.0.0.1/xxxxxxxx"/>
> 

Hopefully the asterisks in your Resource element (<Resource* and *name
are artifacts of your copy and paste. If they're in server.xml, I don't
know if Tomcat would even start. If Tomcat does start, it will probably ignore
malformed XML elements. Check your log files for such messages.
> Zero change.  I'm still getting the exact same error message telling me the
> connection has expired and I should use autoReconnect to fix it.
> 
> First question... is the syntax above correct?  (I saw some resource tag
> examples that used nested  <parameter> tags and other examples that use
> attributes on resource tag like above.  I couldn't find a definitive
> specification to use one over the other.  Is the way I have it ok?


When in doubt, always follow the documentation on the Apache Tomcat
site.

From the documentation:

No components may be nested inside a Resources element

So any documentation that you've read which specifies <parameter> inside
of a Resource element is wrong.
> Second question.... I like to turn on debug/trace for the connector.  But
> the connector/j doc lists a ton of parameters for debug, and I don't have a
> clue how to set all of them.  Can someone just give me a canned config I
> can add that'll trace what's going on in the connector?
> 
> I'm basically at a loss.  If the configuration above is correct, and I'm
> still getting expired connections, I don't know what else to do.  If indeed
> TC 7 changed from round-robin to LIFO, it might explain why it started
> hitting stale connections.  But that still doesn't explain why
> testOnBorrow, validateQuery, and autoReconnect=true don't seem to do
> anything on stale connections.
>

I've had nothing but trouble with autoReconnect="true".

> Maybe with some logging and tracing, something will become obvious.
> 
> Thx.
> 
> Jerry


OK, here's a formatted version of your configuration:

<Resource
testOnBorrow="true"
validateQuery="SELECT 1"
name="jdbc/xxxxxxx"
auth="Container"
type="javax.sql.DataSource"
maxActive="100"
maxIdle="30"
maxWait="10000"
removeAbandoned="true"
removeAbandonedTimeout="60"
logAbandoned="true"
username="xxxxxxxx"
password="xxxxxxxx"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://127.0.0.1/xxxxxxxx"/>

Reordering it so that it follows along with the documentation and adding
the defaults where you've not specified leads to:

<Resource
name="jdbc/xxxxxxx"
auth="Container"
type="javax.sql.DataSource"
driverClassName="com.mysql.jdbc.Driver"
username="xxxxxxxx"
password="xxxxxxxx"
url="jdbc:mysql://127.0.0.1/xxxxxxxx"
initialSize="0"
maxActive="100"
minIdle="0"
maxIdle="30"
maxWait="10000"
validationQuery="SELECT 1"
validationQueryTimeout="-1"
testOnBorrow="true"
testOnReturn="false"
removeAbandoned="true"
removeAbandonedTimeout="60"
logAbandoned="true" />
There are a number of things to note here.

You did not set the initialSize value. By default it is 0. This means
that there are no initial connections to the database.

You did not set the minIdle value. By default, it is 0. This means that
if all of your connections are idle, the pool can shrink to 0.

The correct parameter to specify a validation query is validationQuery.
validateQuery is not correct, and should be ignored. You should see a
warning to that effect in your catalina.out logs.

So, I'm guessing that if you use your Resource element with a DataSource
Realm, something like the following might happen. I'm speculating here
since I've not looked at this part of the code.

1. Tomcat starts up and complains about validateQuery
2. A pool is created with NO active connections
3. You use a form-based login and a DataSource Realm to authenticate
4. The DataSource Realm asks the Resource ( via a JNDI name) for a data source
5. The pool says - I don't have one, but I'll create one
6. You have a testOnBorrow="true" so the pool will use the validation query
7. The pool does not have a validation query to run (see notes above)
8. The default time out for a validation query is -1 - infinite
9. The pool never returns

That's my guess.

Either that, or the pool sees that there is no validation query and
returns immediately with no database connection since there is nothing
in the pool to start with and the pool could not perform a validation query.

I would do the following:

1. Fix initialSize and set it to some reasonable number

A reasonable number depends on your application and your application's
usage.

2. Fix minIdle and set it to some reasonable number

A reasonable number depends on your application and your application's
usage.

3. Fix validateQuery to be validationQuery

Monitor the connections.

On the MySQL side you can use MySQL workbench if you don't wish to use
the command line client. I believe the command is SHOW PROCESSLIST; in 
the command line client

You can monitor the number of connections with JMX on the Tomcat side.

. . . . just my two cents.
/mde/

PS - You do not have to cc: me when you send to the list. All that means
is I get two copies of the email.


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org


Mime
View raw message