Spring Batch - Connection closed in when processing is done in external process

Noa Drach picture Noa Drach · Jun 17, 2014 · Viewed 8.4k times · Source
  • I have a job that is built of several steps - one of the steps is a tasklet that activates processing Pentaho

  • I pass to Pentaho the parameters it needs in order to connect to the DB on its own and it works OK

  • The issue I have starts when the processing time in Pentaho is long

  • Pentaho completes successfully and the code in the tasklet that activated it completes OK, but in the job mechanism that wraps it I get an error when it tries to update the job execution table in the db because the connection it has was already closed

     o.s.j.s.SQLErrorCodesFactory: Error while extracting database product name - falling back to empty error codes
     org.springframework.jdbc.support.MetaDataAccessException: Error while extracting DatabaseMetaData; 
     nested exception is         
     com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
         at org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(JdbcUtils.java:296)
         at org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(JdbcUtils.java:320)
         at org.springframework.jdbc.support.SQLErrorCodesFactory.getErrorCodes(SQLErrorCodesFactory.java:214)
         at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.setDataSource(SQLErrorCodeSQLExceptionTranslator.java:141)
         at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.<init>(SQLErrorCodeSQLExceptionTranslator.java:104)
         at org.springframework.jdbc.support.JdbcAccessor.getExceptionTranslator(JdbcAccessor.java:99)
         at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:603)
         at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:812)
         at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:868)
         at org.springframework.batch.core.repository.dao.JdbcExecutionContextDao.persistSerializedContext(JdbcExecutionContextDao.java:230)
         at org.springframework.batch.core.repository.dao.JdbcExecutionContextDao.updateExecutionContext(JdbcExecutionContextDao.java:159)
         at org.springframework.batch.core.repository.support.SimpleJobRepository.updateExecutionContext(SimpleJobRepository.java:203)
     ...
     14:21:37.143 UTC [ERROR] jobScheduler_Worker-2 T:b U: o.s.t.i.TransactionInterceptor: Application exception overridden by rollback exception
     org.springframework.dao.RecoverableDataAccessException: PreparedStatementCallback; SQL [UPDATE BAT_STEP_EXECUTION_CONTEXT SET SHORT_CONTEXT = ?, SERIALIZED_CONTEXT = ? WHERE STEP_EXECUTION_ID = ?]; Communications link failure
    
  • It looks like the connection that the job repository received when the job started was abandoned and I'm trying to understand if there is a way to order it get a new connection or give it some keep alive command

I tried the following workarounds

  • change the step status in a job listener so the job will complete - fails with the same DB error

  • mark this exception as if it can be skipped - fails with the same DB error

     <batch:no-rollback-exception-classes>
       <batch:include class="com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException" />
       <batch:include class="org.springframework.jdbc.support.MetaDataAccessException" />
     </batch:no-rollback-exception-classes>
    

Any ideas how I can work around this?

Can I configure a job listener that will restart the job from the step that follows the Pentaho step?

Additional info I think that the issue is here - org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSource)

This

    ConnectionHolder conHolder = (ConnectionHolder) TransactionSynchronizationManager.getResource(dataSource);

thinks that the connection is valid

so I guess the solution will be to call org.springframework.transaction.support.TransactionSynchronizationManager.unbindResource(Object)

and the question is how can I get the data source object to pass to this method

I will try querying the org.springframework.transaction.support.TransactionSynchronizationManager.getResourceMap() and see where it gets me

update no luck - the get resources map gives me just the repositories I'm using, not the data source. Still digging...

Another update

I'm debugging the process and it seems that the problem is indeed org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSource) the connection holder is holding a connection that is closed but the code here doesn't check if the connection is open; it only checks if the connection isn't null and if it was some weak reference maybe it was enough here - but in this use case it just proceedes with the closed connection instead of requesting a new one.

Answer

Noa Drach picture Noa Drach · Jun 18, 2014

add this to the tasklet definition

<batch:transaction-attributes propagation="NEVER" />

since the Tasklet is doing external processing and doesn't need a spring batch transaction it need to tell spring batch not to open a transaction for this tasklet.

see http://www.javabeat.net/transaction-management-in-spring-batch-components/ http://forum.spring.io/forum/spring-projects/batch/91158-legacy-integration-tasklet-transaction