JdbcBatchItemWriter and closed connections

[Miko95]

Hi,

I've written an application using Spring Batch. The app reads files during another app writes into them, processes each line using data from an external database and store these lines (records) in this same database.
I discovered a problem that appears very rarely, and I don't know if it's related to Spring Batch.

I left my app running for a long time without file to process, and after a long time I restart to insert new files to process, and at this point, I get this exception:

com.mysql.jdbc.exceptions.jdbc4.CommunicationsExce ption: The last packet successfully received from the server was 62,856,610 milliseconds ago. The last packet sent successfully to the server was 62,856,610 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.

It seems that because the connections in the pool were not used during a long time, they have been closed, and the exception appears when the application tries to use these closed connections. I use a JdbcBatchItemWriter, and I don't know if there are some configuration to do?and where?

Thanks for your help

[Marten Deinum]

This is in no way related to spring batch, but to the fact that you use MySQL. Actually this is a well known issue with MySQL (I suggest google). Basically MySQL kills off connections (server side) when there is a certain time (wait_timeout) of idleness (is that a word ).

I suggest a read of the docs of your connection pool and figure out how to do a validation query when a connection is being obtained from the pool. that way if a connection isn't valid the connection will be killed and a new one will be obtained.

[fransf]

We use C3P0 connection pooling like this and that solved a similar problem for us:

	<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
		<property name="driverClass" value="${jdbc.driverClass}" />
		<property name="jdbcUrl" value="${jdbc.url}" />
		<property name="user" value="${jdbc.username}" />
		<property name="password" value="${jdbc.password}" />
		<property name="preferredTestQuery" value="SELECT 1" />
		<property name="idleConnectionTestPeriod" value="300" />	<!-- Test all connections in the pool every 5 minutes using the preferredTestQuery above -->
	</bean>

Just using the MySQL-ConnectorJ autoReconnect=true parameter doesn't work here, because it still causes an exception to be thrown if the connection is stale, and only after that does the driver reconnect.

[Miko95]

Thanks for your answers