mysql as datasource and closed idle connections

[David Spencer]

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 <halm.reusser at switch.ch> 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 [AnnotatedAction at 1af0af7 targetAction =
> org.jasig.cas.web.flow.AuthenticationViaFormActionCookie at 11126f6, 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
> cas at tp.its.yale.edu
> http://tp.its.yale.edu/mailman/listinfo/cas



----------------------
David Spencer
Information Systems and Computing
University of Bristol