Answers and comments are inline (mostly). ----- Original Message -----
> From: Jerry Malcolm <2ndgenfi...@gmail.com> > To: Tomcat Users List <users@tomcat.apache.org>; Mark Eggers > <its_toas...@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-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
