Halm, This problem can be solved at the Connection Pool rather than in the JDBC Driver.
Commons DBCP allows the specification of a 'validationQuery' parameter which contains a simple SQL statement. DBCP uses this to test connections before allowing them out of the pool. If the validation query doesn't return at least one row, that connection is discarded and another created. You can opt to testOnBorrow, testOnReturn or testWhileIdle. Our setup is typically against Oracle and only tests on borrow. The query we use is ... SELECT NULL FROM sys.dual Don't know if this exact syntax would work with MySQL but any cheap to run query can be used. Configuration options for DBCP are at: <http://commons.apache.org/dbcp/configuration.html> Dave --On 01 October 2007 09:23 +0200 Halm Reusser <[EMAIL PROTECTED]> wrote: > Dear CAS Users, > > we have as CAS setup with teh SearchModeSearchDatabaseAuthenticationHandler > as AuthenticationHandler. As Datasource we use Mysql, which has de following > configuration in deployerConfigContext.xml. > > <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"> > <property name="driverClassName"> > <value>com.mysql.jdbc.Driver</value> > </property> > <property name="url"> > > <value>jdbc:mysql://127.0.0.1/my_database?autoReconnect=true</value> > </property> > ... > </bean> > > So, everything works very well. But there are two cases, which cause an > Exception: > > - After 8 hours of being idle > - After mysql restart > > The exception which is thrown by CAS looks like this: > > org.springframework.web.util.NestedServletException: Request processing > failed; nested exception is > org.springframework.webflow.engine.ActionExecutionException: Exception thrown > executing [EMAIL PROTECTED] targetAction = > [EMAIL PROTECTED], attributes > = map['method' -> 'submit']] in state 'submit' of flow 'login-webflow' -- > action execution attributes were 'map['method' -> 'submit']'; nested > exception is org.springframework.dao.DataAccessResourceFailureException: > PreparedStatementCallback; SQL [Select count('x') from view_cas Where login = > ? And password = ?]; Communications link failure due to underlying exception: > > ** BEGIN NESTED EXCEPTION ** > > java.io.EOFException > MESSAGE: Can not read response from server. Expected to read 4 bytes, read 0 > bytes before connection was unexpectedly lost. > > STACKTRACE: > ... > > > This Problem is known, generally in Webapps with JDBC Connection. Because > mysqld close idle connections after some defined time (default: 8 hours) > > The solutions i've read, all suggest to use the deprecated autoReconnect=true > argument in the driver url. > (http://tomcat.apache.org/tomcat-5.5-doc/printer/jndi-datasource-examples-how > to.html#MySQL%20DBCP%20Example) > > As you see, i've done this, and i also tried out with the parameters maxWait > etc. But the behavior is still the same. > > > Have anybody of you same troubles and found a working solution? > > A workaround could be to restart the cas webapp all 6 hours with a cronjob. > But this isn't much pretty... > > > Thanks a lot. > > Halm Reusser > > Environment information > ------------------------ > Mysql: 5.0.32 > Mysql Java Connector: 5.0.7 > Tomcat: 5.5.23 > CAS Server: 3.1 > _______________________________________________ > Yale CAS mailing list > [email protected] > http://tp.its.yale.edu/mailman/listinfo/cas ---------------------- David Spencer Information Systems and Computing University of Bristol _______________________________________________ Yale CAS mailing list [email protected] http://tp.its.yale.edu/mailman/listinfo/cas
