MySQL CommunicationsException: Communications link failure


Sometimes I seem to lose connection to my mysql-database and then a connection is thrown and the program stops executing as it is now. I would like to try to reconnect to the database lets say 5 times more with a sleep() of 10 seconds in between to see if I can get the connection back. Which I guess will almost always be the case.

Is there some functionality for this in spring, like a reconnect() function somewhere or what would be the best way to accomplish this?

Not sure why the connection drops in the first case but may be some mysql-problem.

Any help would be appreciate

Solution

Are you actually loosing the connection? I guess your problem is that you are using database connection pools and are keeping open a number of connections. After about 8 hours MySQL will silently kill the connection-handler on it’s side. As soon as you try to use the connection that was connected to a killed handler, you will get the exceptions you are having.

the keep-alive option never really worked for us, so we switched to a connection pool, that is able to test a connection before returning it to the application. This validation-query solved allmost all of our problems.

Here comes our spring-config for the datasource:

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="${cweb.database.driver}"/>
        <property name="url" value="${cweb.database.url}"/>
        <property name="username" value="${cweb.database.user}"/>
        <property name="password" value="${cweb.database.password}"/>
        <property name="initialSize" value="5"/>
        <property name="maxIdle" value="5"/>
        <property name="maxActive" value="20"/>
        <property name="validationQuery" value="SELECT 1"/>
        <property name="testOnBorrow" value="true"/>
        <property name="testWhileIdle" value="true"/>
        <property name="timeBetweenEvictionRunsMillis" value="10000"/>
        <property name="minEvictableIdleTimeMillis" value="60000"/>
</bean>

The trick with this solution is that the pool will issue the “SELECT 1” statement every 10 seconds on an idling connection. If a connection died, the pool will re-connect within 10 seconds. On the other hand whenever a connection is returned, at first the “SELECT 1” query is executed, and only if this was successfull, the connection will be returned. If it was not successfull, a new connection will be created and returned (of course this connection too will be tested first)

src: http://www.experts-exchange.com/Programming/Languages/Java/J2EE/Frameworks/Spring/Q_26468458.html

Advertisements

Spring JNDI Datasource and Development Datasource


<bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
		<property name="jndiName" value="com/dev/sss" />
		<property name="defaultObject" ref="developmentDataSource" />
	</bean>

	<!-- fall back datasource if JNDI look up of main datasource fails -->
	<bean id="developmentDataSource" class="org.apache.commons.dbcp.BasicDataSource"
		destroy-method="close">

		<property name="driverClassName" value="${jdbc.driverClassName}" />
		<property name="url" value="${jdbc.url}" />
		<property name="username" value="${jdbc.username}" />
		<property name="password" value="${jdbc.password}" />

		<property name="initialSize">
			<value>10</value>
		</property>
		<property name="maxActive">
			<value>0</value>
		</property>
		<property name="maxWait">
			<value>5000</value>
		</property>

	</bean>