mysql as datasource and closed idle connections

[Halm Reusser]

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-howto.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