Terracotta Discussion Forums (LEGACY READ-ONLY ARCHIVE)

We are running with strange issue. We use quartz to load the list job. But, when you leave the system ruuning overnight, the very first job everyday is failed because the following exception. Subsequent jobs are executed with no problem. We use DBCP connection pool and MSSQL DB.

Looks like Quartz tries to use the connection which is used by someone outside the quartz, from connection pool and failed to override its default settings

Any thoughts.

2009-11-06 02:29:27,810 WARN [com.arjuna.ats.jta.logging.loggerI18N] [com.arjuna.ats.internal.jta.transaction.arjunacore.lastResource.multipleWarning] [com.arjuna.ats.internal.jta.transaction.arjunacore.lastResource.multipleWarning] Multiple last resources have been added to the current transaction. This is transactionally unsafe and should not be relied upon. Current resource is org.jboss.resource.connectionmanager.TxConnectionManager$LocalXAResource@40ff16d1
2009-11-06 02:29:27,813 WARN [org.quartz.impl.jdbcjobstore.JobStoreTX] Failed to override connection auto commit/transaction isolation.
java.sql.SQLException: I/O Error: Connection reset
at net.sourceforge.jtds.jdbc.TdsCore.executeSQL(TdsCore.java:1049)
at net.sourceforge.jtds.jdbc.TdsCore.submitSQL(TdsCore.java:895)
at net.sourceforge.jtds.jdbc.ConnectionJDBC2.setAutoCommit(ConnectionJDBC2.java:2026)
at org.apache.commons.dbcp.DelegatingConnection.setAutoCommit(DelegatingConnection.java:331)
at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.setAutoCommit(PoolingDataSource.java:317)
at org.quartz.impl.jdbcjobstore.AttributeRestoringConnectionInvocationHandler.setAutoCommit(AttributeRestoringConnectionInvocationHandler.java:91)
at org.quartz.impl.jdbcjobstore.AttributeRestoringConnectionInvocationHandler.invoke(AttributeRestoringConnectionInvocationHandler.java:65)
at $Proxy196.setAutoCommit(Unknown Source)
at org.quartz.impl.jdbcjobstore.JobStoreSupport.getConnection(JobStoreSupport.java:658)
at org.quartz.impl.jdbcjobstore.JobStoreTX.getNonManagedTXConnection(JobStoreTX.java:72)
at org.quartz.impl.jdbcjobstore.JobStoreSupport.executeInNonManagedTXLock(JobStoreSupport.java:3671)
at org.quartz.impl.jdbcjobstore.JobStoreTX.executeInLock(JobStoreTX.java:93)
at org.quartz.impl.jdbcjobstore.JobStoreSupport.executeWithoutLock(JobStoreSupport.java:3579)
at org.quartz.impl.jdbcjobstore.JobStoreSupport.retrieveJob(JobStoreSupport.java:1317)
at org.quartz.core.QuartzScheduler.getJobDetail(QuartzScheduler.java:1295)
at org.quartz.impl.StdScheduler.getJobDetail(StdScheduler.java:551)
at com.dconx.swx.ejb.helpers.ListloadUtil.scheduleDefaultJobForClient(ListloadUtil.java:1427)

Looks like the physical network connection fails over night due to no activity timeout.

Do you have a validate connection query set in your configuration? That would detect the problem and re-create a new connection before the datasource hands the connection out to Quartz for usage.

Thanks for your reply

I have a valid sql query as follows in quartz.properties

org.quartz.datasource.myDS.validationQuery = select 1

Sorry, I'm not sure what else to suggest.

The stack trace clearly shows that the connection reset, and your description of it being the first job to run after overnight helps support that idea that the idle connection goes bad.

Having the validation query should clear that up though.

I suggest reviewing DBCP settings.

This is still happening...

i am using the quartz version 1.8.0

Logs:
-----------------------------------------------------------------------------------
2011-01-18 14:22:14,229 WARN [org.quartz.impl.jdbcjobstore.JobStoreTX] Failed to override connection auto commit/transaction isolation.
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was437823 seconds ago.The last packet sent successfully to the server was 437823 seconds ago, which 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.
at sun.reflect.GeneratedConstructorAccessor246.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
--------------------------------------------------------------------------------------

the quartz.properties is as follows
Code:

 # Define the datasource to use
 org.quartz.dataSource.qzDS.driver = com.mysql.jdbc.Driver
 org.quartz.dataSource.qzDS.URL = jdbc:mysql://localhost:3306/quartz
 org.quartz.dataSource.qzDS.user = root
 org.quartz.dataSource.qzDS.password = PASSWORD
 org.quartz.dataSource.NAME.validationQuery =select 1

Important Note

We already had these kind of time out issues with DBCP connection pool. So we have started to use the c3p0 for efficient time out handling.

So please suggest the way to change the connection pool provider if possible.

A positive reply for this post is available on
http://forums.terracotta.org/forums/posts/list/0/4828.page#24564