Connection-Pools, MySQL and 8 hours
Hi list, we're running Tomcat 5.5 here on Debian Edge, MySQL 5 and we've set up a connectionpool. Example of context.xml: ?xml version=1.0 encoding=UTF-8? Context Resource auth=Container description=DB Connection for SSO name=jdbc/SSODS type=javax.sql.DataSource factory=org.apache.commons.dbcp.BasicDataSourceFactory driverClassName=com.mysql.jdbc.Driver username=xxx password=xxx url=jdbc:mysql://db:3306/apacheSSO maxIdle=5 maxWait=1 maxActive=10 validationQuery=SELECT 1 testOnBorrow=true testWhileIdle=true timeBetweenEvictionRunsMillis=1 minEvictableIdleTimeMillis=28800 poolPreparedStatements=true removeAbandoned=true removeAbandonedTimeout=300 logAbandoned=false/ /Context Unfortunately, we always get an exception after 8 hours of inactivity like 2007-07-09 02:10:16,739 ERROR [TP-Processor7]: Exception performing authentication com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException: Communications link failure during commit(). Transaction resolution unknown. at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:888) at com.mysql.jdbc.Connection.commit(Connection.java:2259) at org.apache.catalina.realm.JDBCRealm.getPassword(JDBCRealm.java:568) at org.apache.catalina.realm.JDBCRealm.authenticate(JDBCRealm.java:399) at org.apache.catalina.realm.JDBCRealm.authenticate(JDBCRealm.java:347) at org.apache.catalina.authenticator.FormAuthenticator.authenticate(FormAuthenticator.java:257) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:416) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105) at org.apache.catalina.authenticator.SingleSignOn.invoke(SingleSignOn.java:392) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148) at org.apache.jk.server.JkCoyoteHandler.invoke(JkCoyoteHandler.java:199) at org.apache.jk.common.HandlerRequest.invoke(HandlerRequest.java:282) at org.apache.jk.common.ChannelSocket.invoke(ChannelSocket.java:767) at org.apache.jk.common.ChannelSocket.processConnection(ChannelSocket.java:697) at org.apache.jk.common.ChannelSocket$SocketConnection.runIt(ChannelSocket.java:889) at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684) at java.lang.Thread.run(Thread.java:595) That means, the session has timed out, Tomcat tries to re-authenticate via a JDBC-Realm, but the connection to the database is gone. It works on the 2nd try, however, I'd like to get rid of that exception in our logs. In an article on MySQL-dev it is said, that, to avoid such a scenario, one shoudl either use connection-pooling, use exception-handling or shoudl use the MySQL-autoconnect-feature: http://dev.mysql.com/doc/refman/5.0/en/connector-j-usagenotes-troubleshooting.html#qandaitem-24-4-5-3-4 On the other hand they've issued a warning not to use the autoreconnect-feature since this might be a secirity-issue. Now my question to you: How do you handle this when using a connection-pool? Just live with those exceptions occuring once or twice a day? Catch the exception and do nothing (not even propagate them to your logs (something I really dislike))? Or is their a trick so that I can convince Tomcat *not* to throw this Exception? Looking forward to your valued comments! Gregor -- what's puzzlin' you, is the nature of my game gpgp-fp: 79A84FA526807026795E4209D3B3FE028B3170B2 gpgp-key available @ http://pgpkeys.pca.dfn.de:11371 - To start a new topic, e-mail: users@tomcat.apache.org To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Connection-Pools, MySQL and 8 hours
The JDBC realm doesn't use the connection pool you've specified below, The JDBC realm uses a single connection, never closes it unless an exception happens. The connection is defined in the Realm section of server.xml Filip Gregor Schneider wrote: Hi list, we're running Tomcat 5.5 here on Debian Edge, MySQL 5 and we've set up a connectionpool. Example of context.xml: ?xml version=1.0 encoding=UTF-8? Context Resource auth=Container description=DB Connection for SSO name=jdbc/SSODS type=javax.sql.DataSource factory=org.apache.commons.dbcp.BasicDataSourceFactory driverClassName=com.mysql.jdbc.Driver username=xxx password=xxx url=jdbc:mysql://db:3306/apacheSSO maxIdle=5 maxWait=1 maxActive=10 validationQuery=SELECT 1 testOnBorrow=true testWhileIdle=true timeBetweenEvictionRunsMillis=1 minEvictableIdleTimeMillis=28800 poolPreparedStatements=true removeAbandoned=true removeAbandonedTimeout=300 logAbandoned=false/ /Context Unfortunately, we always get an exception after 8 hours of inactivity like 2007-07-09 02:10:16,739 ERROR [TP-Processor7]: Exception performing authentication com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException: Communications link failure during commit(). Transaction resolution unknown. at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:888) at com.mysql.jdbc.Connection.commit(Connection.java:2259) at org.apache.catalina.realm.JDBCRealm.getPassword(JDBCRealm.java:568) at org.apache.catalina.realm.JDBCRealm.authenticate(JDBCRealm.java:399) at org.apache.catalina.realm.JDBCRealm.authenticate(JDBCRealm.java:347) at org.apache.catalina.authenticator.FormAuthenticator.authenticate(FormAuthenticator.java:257) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:416) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105) at org.apache.catalina.authenticator.SingleSignOn.invoke(SingleSignOn.java:392) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148) at org.apache.jk.server.JkCoyoteHandler.invoke(JkCoyoteHandler.java:199) at org.apache.jk.common.HandlerRequest.invoke(HandlerRequest.java:282) at org.apache.jk.common.ChannelSocket.invoke(ChannelSocket.java:767) at org.apache.jk.common.ChannelSocket.processConnection(ChannelSocket.java:697) at org.apache.jk.common.ChannelSocket$SocketConnection.runIt(ChannelSocket.java:889) at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684) at java.lang.Thread.run(Thread.java:595) That means, the session has timed out, Tomcat tries to re-authenticate via a JDBC-Realm, but the connection to the database is gone. It works on the 2nd try, however, I'd like to get rid of that exception in our logs. In an article on MySQL-dev it is said, that, to avoid such a scenario, one shoudl either use connection-pooling, use exception-handling or shoudl use the MySQL-autoconnect-feature: http://dev.mysql.com/doc/refman/5.0/en/connector-j-usagenotes-troubleshooting.html#qandaitem-24-4-5-3-4 On the other hand they've issued a warning not to use the autoreconnect-feature since this might be a secirity-issue. Now my question to you: How do you handle this when using a connection-pool? Just live with those exceptions occuring once or twice a day? Catch the exception and do nothing (not even propagate them to your logs (something I really dislike))? Or is their a trick so that I can convince Tomcat *not* to throw this Exception? Looking forward to your valued comments! Gregor - To start a new topic, e-mail: users@tomcat.apache.org To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Connection-Pools, MySQL and 8 hours
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Gregor, Filip Hanik - Dev Lists wrote: The JDBC realm doesn't use the connection pool you've specified below, The JDBC realm uses a single connection, never closes it unless an exception happens. Might I suggest using a JNDI Realm so you can share your configuration between your Realm authenticator and your application? MySQL or a piece of network hardware might be shutting-down your connection after 8 hours. By using the JNDI pool you have configured, DBCP ought to get you a good connection even after that 8 hours has elapsed. Definitely don't use MySQL's auto-reconnect feature. I'm not sure why that even still exists in the 5.x version of the driver. They have been declaring it unfit for use for years. - -chris -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFGkmUI9CaO5/Lv0PARAomJAJwPsoTiS74oRaANdRVs4sK1wJygaQCfQKIP x2BJiJA2i3XQUoNSxI2SL9s= =avI4 -END PGP SIGNATURE- - To start a new topic, e-mail: users@tomcat.apache.org To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]