Scheduled Report not running after server inactive

[djsiders]

I have a scheduled report that runs fine when I first create and test it.  The I set the schedule to run it at 6AM each day.  The report doesn't show up in email, and the output is not delivered to the specified output folder (again both succeeded when testing).  When I look at the scheduled report it shows a 6AM run time from that day.  I have tested and tried this 3 days in a row, and it has failed every time. 

I am wondering if there is some type of issue I am having due to the server being idle for a long period of time.  This server is not really used heavily yet, and would not be used much after 6PM at all.  Is the sudden "wake up" to run at 6AM maybe failing due to stale connections, etc? 

I am using JasperServer 3.0.0, running on Tomcat 5.5 and java 1.5.  The jasper DB is on a MySql instance.  Below is the only error info I can find (from Catalina.out).  Seems to be related, as the Quartz scheduler is listed in the trace, and I have no other scheduled jobs.

java.io.EOFException

STACKTRACE:

java.io.EOFException
 at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1934)
 at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2380)
 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2909)
 at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1600)
 at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1695)
 at com.mysql.jdbc.Connection.execSQL(Connection.java:3020)
 at com.mysql.jdbc.Connection.setAutoCommit(Connection.java:4989)
 at org.apache.tomcat.dbcp.dbcp.DelegatingConnection.setAutoCommit(DelegatingConnection.java:331)
 at org.apache.tomcat.dbcp.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.setAutoCommit(PoolingDataSource.java:317)
 at org.quartz.impl.jdbcjobstore.JobStoreCMT.getNonManagedTXConnection(JobStoreCMT.java:1389)
 at org.quartz.impl.jdbcjobstore.JobStoreCMT.triggeredJobComplete(JobStoreCMT.java:1265)
 at org.quartz.core.QuartzScheduler.notifyJobStoreJobComplete(QuartzScheduler.java:1490)
 at org.quartz.core.JobRunShell.run(JobRunShell.java:262)
 at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:520)

** END NESTED EXCEPTION **

 

Last packet sent to the server was 0 ms ago.
 at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2592)
 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2909)
 at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1600)
 at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1695)
 at com.mysql.jdbc.Connection.execSQL(Connection.java:3020)
 at com.mysql.jdbc.Connection.setAutoCommit(Connection.java:4989)
 at org.apache.tomcat.dbcp.dbcp.DelegatingConnection.setAutoCommit(DelegatingConnection.java:331)
 at org.apache.tomcat.dbcp.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.setAutoCommit(PoolingDataSource.java:317)
 at org.quartz.impl.jdbcjobstore.JobStoreCMT.getNonManagedTXConnection(JobStoreCMT.java:1389)
 at org.quartz.impl.jdbcjobstore.JobStoreCMT.triggeredJobComplete(JobStoreCMT.java:1265)
 at org.quartz.core.QuartzScheduler.notifyJobStoreJobComplete(QuartzScheduler.java:1490)
 at org.quartz.core.JobRunShell.run(JobRunShell.java:262)
 at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:520)

[djsiders]

After a little digging for this problem in general (non JasperServer related). I found others who were seeing this with MySql running web apps under Tomcat, etc. It seems related to the connection pooling, and the connections getting old. From this info, I added a property to the MySql connection string

 

autoReconnect=true

 

This mornings report ran fine. Will continue to test, but this may be a fix.

[djsiders]

The autoReconnect did not solve the problem after testing with much longer inactive period. Once you "wake up" the server, it runs fine again.

[lucianc]

Adding a validation query to the JasperServer JDBC data source might help.  Could you try that?

The validation query could be something like "select 1".

This would either work with testOnBorrow (which is set by default), or with testWhileIdle and timeBetweenEvictionRunsMillis.

Regards,

Lucian

[djsiders]

I can change that directly in the META-INF/context.xml to try, but how do you get the Maven build of JasperServer to include it in there automatically. I know in my home .m2 directory, I set properties in js.jdbc.properties and they somehow get built into the final product. Just can't find anyway to get the validationQuery to be built this way.

[djsiders]

I added the validation query directly by editing META-INF/context.xml, and the jasperserver.xml file in catalina_home/conf/Catalina/localhost

The reports ran for the next two days, a Sunday and Monday morning.  The server would have had little to no activity over the weekend, so this seems to be a good fix.  Also, on Monday morning the server responed much faster than in the past.   You normally had to wait quite a while for the initial screen to load, and then it got better after that.  Now, comes up at normal speed you see during the day.

My only remaining item is how to get the validation into the base "template" for context xml so that it may be included in the WAR file build.  Currently if I build a new WAR file, my modified context.xml will be overwritten without the validation query and I will have to edit after updating on the tomcat server.

 

[lucianc]

I believe that JS 3.0.0 used a property named datasource.extra.params to add extra parameters to the JDBC resource in META-INF/context.xml.  So try adding the line below to your js.jdbc.properties.

HTH,

Lucian

Code:

datasource.extra.params=validationQuery="select 1"

[djsiders]

I added that in and no luck. 

I also found what looks like a "template" context.xml in jasperserver-trunk/build/classes/META-INF

That file shows that param name, but it doesn't fill in.  I even tried eliminating quotes just to see if the value got there.

The other params, like metadata.jdbc.username  are filling in,  just not the extra.params.

Is there possibly another template file that is being used, but doesn't include this param?

Maybe a maven setting?

Code:

from Context.xml

[lucianc]

Sorry for the late reply.

I'm not sure what the problem is, datasource.extra.params works fine for me in JS 3.0.  Try to search all your JS related files and see whether you have this property redefined in another place.

What you can also do (as a workaround) is to directly edit jasperserver-common-war/src/main/resources/META-INF/context.xml and add the data source validation query.

Regards,

Lucian

[djsiders]

I have been using the workaround.  Of course the problem with it is I have to remember to change this after each deployment of a new WAR file since it gets overwritten with the WAR contents. 

I have looked through, and searched all files in my jasper build folders, and the config files in my .m2 folder and can't find any other reference.  It appears the build uses some type of "template" context.xml, as I have found several version that reference the config variables.  Problem is I am unable to determine which one the build is actually using.  I suspect that maybe the extra param variable is not defined in the template the build is actually using, but again, can't find which one that is.

[developerdude]

 I changed various config files to get around the stale connection problem (I have complained her http://jasperforge.org/plugins/espforum/view.php?group_id=112&forumid=102&topicid=53600 that these setting should be the default config because this is a common mySQL problem).

 

in META-INF/jasperserverDS-jdbc-jdbc.xml, I changed the connection URL to:

    <url>jdbc:mysql://localhost:3306/jasperserver?autoReconnect=true&autoReconnectForPools=true&useUnicode=true&amp;characterEncoding=UTF-8</url>

I added:

 

<jdbc-connection-pool-params>

    <test-table-name>SQL SELECT 1</test-table-name>

    <property name="validationQuery" value="select 1">

    <property name="testOnBorrow" value="true">

    <property name="testOnReturn" value="false">

  </jdbc-connection-pool-params>

 

the META-INF/context.xml file has a connection URL of:

 

url="jdbc:mysql://localhost/jasperserver?autoReconnect=true&autoReconnectForPools=true&useUnicode=true&characterEncoding=UTF-8" />

 

Once I made these changes then the pool does the validation test on the connection before using it and this refreshes the connection (or gets a new one) and the problem goes away.

 

[mileidysg]

Thank you for the details... very useful.