Settings required for generating large reports

[ldangelo]

 Hi All,

I thought I would add this to the forums as I spent a lot of time getting jasper server to generate very large reports (80M on 2 million records)...  Hopefully this will save someone a lot of time...

First, we all know about the virtualizer but this only virtualizes report pages (and not the result set).  By default jasper will fetch the entire result set into memory before it starts to generate the report.  This is very fast but not very memory efficient.  The solution to this problem is to get jdbc to "stream" result's back to jasper server...  In order to stream the result set you need to set your driver to stream results...  The settings below work for mysql but I assume they work for others as well.  So here are the changes you will want to make and why:

Modify applicationContext.xml:

Change the com.jaspersoft.jasperserver.api.engine.common.service.impl.FileVirtualizerFactory to com.jaspersoft.jasperserver.api.engine.common.service.impl.SwapFileVirtualizerFactory and comment out the property directory.  It should look like this when you are done...

   <bean id="fileVirtualizerFactory" class="com.jaspersoft.jasperserver.api.engine.common.service.impl.SwapFileVirtualizerFactory">

        <property name="maxSize" value="400"></property>

<!--        <property name="directory" value="${java.io.tmpdir}"></property> -->

    </bean>

This will change jasper to use a single file for paging instead of multiple files per page...  The report I generated was 400,000 pages so obviously that is a lot of files to be creating...

jasperreports.properties

Use these settings...

net.sf.jasperreports.governor.max.pages.enabled=true

net.sf.jasperreports.governor.timeout.enabled=true

net.sf.jasperreports.jdbc.concurrency=readOnly

net.sf.jasperreports.jdbc.fetch.size=-2147483648

net.sf.jasperreports.jdbc.holdability=close

net.sf.jasperreports.jdbc.result.set.type=forwardOnly

The last four settings are the "trick" to put the mysql connector into "streaming" mode...  It will not load the entire result set into memory and then try and build the report.

 

Lastly you will need to modify your mysql configuration so the jdbc url you use has the following:

 

?maxAllowedPacket=104857600

 

Also you need to modify your my.cnf (mysql configuration) to include the following in the [mysqld] section:

 

max_allowed_packet = 100M

 

So what do these last two settings do?  When jasper server generates a large report with the scheduler it will try and store the results into the repository.  Without these settings you will get a nasty error when jasper tries to insert the output of the report into the repository saying something like "Caused by: com.mysql.jdbc.PacketTooBigException: Packet for query is too large (85530691 > 16777216). You can change this value on the server by setting the max_allowed_packet' variable.".  

 

With the above settings you can write a generated report up to 100M into the repository..  In production we use 160M to give some head room for larger reports...

 

I hope this helps save someone some time in tuning jasper server.  If it helps you feel free to give me some karma points ;^)...

 

BTW) Using these settings I generated a 80M report (csv) on 2 million rows with only 512M allocated to the java heap!..  Obviously, we would use more Heap Space in production but I was looking to restrict the JVM's memory to ensure that the above settings really work.

 

-LeoD

 

[ramnik]

This is really great Information > we have been using FileVirtulizer in internal Performance testing and tit really helps in finding balance between memory vs response time.

 

thanks for putting this on Forum :)

Ramnik Kaur

Senior QA Engineer

[adamd]

Thanks - I needed this post.

 

P.S.  You don't need to comment out "directory", just change it to:

<property name="tempDirectory" value="${java.io.tmpdir}"></property>

Post Edited by adamd at 09/06/2010 06:08

[bhushan_makade]

Thanks for this post. I was really wondering if i can generate the large reports using jasper server.

I have a simillar requirement wherein i need to schedule the report which would be having around 1 million records.

When i tried to run such big report on jasperserver , obviously i got OutOfMemory Exception.

Also, i need to store the generated report in file system in stead of repository.

  We have jasperserver 3.5. I am not sure what version of jasper server you are using because i am not able to find

the jasperreports.properties file in it. So it would be great if you can throw some light on it.

  Also,  if you can help with report getting stored on the file system , would appreciate it.

Thanks & Regards,

Bhushan

 

 

 

 

 

 

[shippu4u]

Hi,

the solution is working for generating the large reports but after that its not working on small reports........

so its givig error for my other reports....so please  let me  know the soution for the same

 

 

 

[ernestoo]

What's the error you're getting?

[shippu4u]

After changing the above seetings .I am able to generate teh large report but its not working for my othasr small reports for tahts its giving error.Unable to prepare query statement.

 

com.jaspersoft.jasperserver.api.JSExceptionWrapper: net.sf.jasperreports.engine.JRException: Error preparing statement for executing the report query : selectspid,spname, serviceclass, servicename,reqresponsemode, sapid,sapname, reqauditid, reqverb, reqtimestamp, responsetime,( SELECT round(avg(vres.responsetime), 3) FROM vw_message_process_time_detail vres where vres.reqtimestamp between (?||'-'||?||'-01 00:00:00.000')::timestamp and (?||'-'||?||'-'||?||' 23:59:59.999')::timestamp) AS avgresponsetime,( SELECT count(*) FROM vw_message_process_time_detail vres WHERE vres.responsetime > 3::numeric and vres.reqtimestamp between (?||'-'||?||'-01 00:00:00.000')::timestamp and (?||'-'||?||'-'||?||' 23:59:59.999')::timestamp) AS gt3secfromvw_message_process_time_detailwherereqtimestamp between (?||'-'||?||'-01 00:00:00.000')::timestamp and (?||'-'||?||'-'||?||' 23:59:59.999')::timestamporder byspid, serviceclass, reqresponsemode, sapid, reqverb;	at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createStatement(JRJdbcQueryExecuter.java:192)	at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:123)	at com.jaspersoft.jasperserver.api.engine.jasperreports.util.JRTimezoneJdbcQueryExecuter.createDatasource(JRTimezoneJdbcQueryExecuter.java:164)	at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:676)	at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:596)	at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1223)	at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:853)	at net.sf.jasperreports.engine.fill.JRFiller.fillReport(JRFiller.java:123)	at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:439)	at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:748)	at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:367)	at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.executeReport(EngineServiceImpl.java:876)	at com.jaspersoft.jasperserver.api.engine.jasperreports.domain.impl.ReportUnitRequest.execute(ReportUnitRequest.java:60)	at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.execute(EngineServiceImpl.java:301)	at com.jaspersoft.jasperserver.war.action.ViewReportAction.executeReport(ViewReportAction.java:329)	at com.jaspersoft.jasperserver.war.action.ViewReportAction.verifyData(ViewReportAction.java:222)	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)	at java.lang.reflect.Method.invoke(Method.java:597)	at org.springframework.webflow.util.DispatchMethodInvoker.invoke(DispatchMethodInvoker.java:103)	at org.springframework.webflow.action.MultiAction.doExecute(MultiAction.java:136)	at org.springframework.webflow.action.AbstractAction.execute(AbstractAction.java:203)	at org.springframework.webflow.engine.AnnotatedAction.execute(AnnotatedAction.java:142)	at org.springframework.webflow.engine.ActionExecutor.execute(ActionExecutor.java:61)	at org.springframework.webflow.engine.ActionState.doEnter(ActionState.java:180)	at org.springframework.webflow.engine.State.enter(State.java:200)	at org.springframework.webflow.engine.Transition.execute(Transition.java:229)	at org.springframework.webflow.engine.TransitionableState.onEvent(TransitionableState.java:112)	at org.springframework.webflow.engine.Flow.onEvent(Flow.java:572)	at org.springframework.webflow.engine.impl.RequestControlContextImpl.signalEvent(RequestControlContextImpl.java:207)	at org.springframework.webflow.engine.impl.FlowExecutionImpl.signalEvent(FlowExecutionImpl.java:214)	at org.springframework.webflow.executor.FlowExecutorImpl.resume(FlowExecutorImpl.java:238)	at sun.reflect.GeneratedMethodAccessor436.invoke(Unknown Source)	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)	at java.lang.reflect.Method.invoke(Method.java:597)	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:281)	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:187)	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:154)	at org.acegisecurity.intercept.method.aopalliance.MethodSecurityInterceptor.invoke(MethodSecurityInterceptor.java:66)	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:176)	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:210)	at $Proxy86.resume(Unknown Source)	at org.springframework.webflow.executor.support.FlowRequestHandler.handleFlowRequest(FlowRequestHandler.java:115)	at org.springframework.webflow.executor.mvc.FlowController.handleRequestInternal(FlowController.java:170)	at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153)	at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:45)	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:820)	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:755)	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:396)	at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:360)	at javax.servlet.http.HttpServlet.service(HttpServlet.java:710)	at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)	at logging.ReportLog.doFilter(ReportLog.java:39)	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)	at com.jaspersoft.jasperserver.war.common.UploadMultipartFilter.doFilter(UploadMultipartFilter.java:86)	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:264)	at org.acegisecurity.ui.switchuser.SwitchUserProcessingFilter.doFilter(SwitchUserProcessingFilter.java:335)	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)	at org.acegisecurity.intercept.web.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:107)	at org.acegisecurity.intercept.web.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:72)	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)	at org.acegisecurity.ui.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:110)	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)	at org.acegisecurity.providers.anonymous.AnonymousProcessingFilter.doFilter(AnonymousProcessingFilter.java:125)	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)	at com.jaspersoft.jasperserver.api.metadata.user.service.impl.MetadataAuthenticationProcessingFilter.doFilter(MetadataAuthenticationProcessingFilter.java:142)	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)	at com.jaspersoft.jasperserver.war.util.RequestParameterAuthenticationFilter.doFilter(RequestParameterAuthenticationFilter.java:97)	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)	at org.acegisecurity.ui.basicauth.BasicProcessingFilter.doFilter(BasicProcessingFilter.java:181)	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)	at com.jaspersoft.jasperserver.war.UserPreferencesFilter.doFilter(UserPreferencesFilter.java:178)	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)	at org.acegisecurity.ui.AbstractProcessingFilter.doFilter(AbstractProcessingFilter.java:217)	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)	at com.jaspersoft.jasperserver.war.UserPreferencesFilter.doFilter(UserPreferencesFilter.java:178)	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)	at org.acegisecurity.context.HttpSessionContextIntegrationFilter.doFilter(HttpSessionContextIntegrationFilter.java:191)	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)	at org.acegisecurity.util.FilterChainProxy.doFilter(FilterChainProxy.java:148)	at org.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:90)	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)	at com.jaspersoft.jasperserver.war.util.CharacterEncodingFilter.doFilter(CharacterEncodingFilter.java:70)	at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:138)	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)	at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:230)	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)	at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:179)	at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:84)	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:104)	at org.jboss.web.tomcat.service.jca.CachedConnectionValve.invoke(CachedConnectionValve.java:157)	at org.josso.tc55.agent.SSOAgentValve.invoke(SSOAgentValve.java:271)	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:241)	at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)	at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:580)	at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)	at java.lang.Thread.run(Thread.java:619)Caused by: org.postgresql.util.PSQLException: Fetch size must be a value greater to or equal to 0.	at org.postgresql.jdbc2.AbstractJdbc2Statement.setFetchSize(AbstractJdbc2Statement.java:2731)	at org.apache.commons.dbcp.DelegatingStatement.setFetchSize(DelegatingStatement.java:276)	at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createStatement(JRJdbcQueryExecuter.java:169)	... 109 more

net.sf.jasperreports.engine.JRException: Error preparing statement for executing the report query : select spid,spname, serviceclass, servicename,reqresponsemode, sapid,sapname, reqauditid, reqverb, reqtimestamp, responsetime, ( SELECT round(avg(vres.responsetime), 3) FROM vw_message_process_time_detail vres where vres.reqtimestamp between (?||'-'||?||'-01 00:00:00.000')::timestamp and (?||'-'||?||'-'||?||' 23:59:59.999')::timestamp) AS avgresponsetime, ( SELECT count(*) FROM vw_message_process_time_detail vres WHERE vres.responsetime > 3::numeric and vres.reqtimestamp between (?||'-'||?||'-01 00:00:00.000')::timestamp and (?||'-'||?||'-'||?||' 23:59:59.999')::timestamp) AS gt3sec from vw_message_process_time_detail where reqtimestamp between (?||'-'||?||'-01 00:00:00.000')::timestamp and (?||'-'||?||'-'||?||' 23:59:59.999')::timestamp order by spid, serviceclass, reqresponsemode, sapid, reqverb;

net.sf.jasperreports.engine.JRException: Error preparing statement for executing the report query : selectspid,spname, serviceclass, servicename,reqresponsemode, sapid,sapname, reqauditid, reqverb, reqtimestamp, responsetime,( SELECT round(avg(vres.responsetime), 3) FROM vw_message_process_time_detail vres where vres.reqtimestamp between (?||'-'||?||'-01 00:00:00.000')::timestamp and (?||'-'||?||'-'||?||' 23:59:59.999')::timestamp) AS avgresponsetime,( SELECT count(*) FROM vw_message_process_time_detail vres WHERE vres.responsetime > 3::numeric and vres.reqtimestamp between (?||'-'||?||'-01 00:00:00.000')::timestamp and (?||'-'||?||'-'||?||' 23:59:59.999')::timestamp) AS gt3secfromvw_message_process_time_detailwherereqtimestamp between (?||'-'||?||'-01 00:00:00.000')::timestamp and (?||'-'||?||'-'||?||' 23:59:59.999')::timestamporder byspid, serviceclass, reqresponsemode, sapid, reqverb;	at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createStatement(JRJdbcQueryExecuter.java:192)	at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:123)	at com.jaspersoft.jasperserver.api.engine.jasperreports.util.JRTimezoneJdbcQueryExecuter.createDatasource(JRTimezoneJdbcQueryExecuter.java:164)	at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:676)	at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:596)	at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1223)	at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:853)	at net.sf.jasperreports.engine.fill.JRFiller.fillReport(JRFiller.java:123)	at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:439)	at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:748)	at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:367)	at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.executeReport(EngineServiceImpl.java:876)	at com.jaspersoft.jasperserver.api.engine.jasperreports.domain.impl.ReportUnitRequest.execute(ReportUnitRequest.java:60)	at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.execute(EngineServiceImpl.java:301)	at com.jaspersoft.jasperserver.war.action.ViewReportAction.executeReport(ViewReportAction.java:329)	at com.jaspersoft.jasperserver.war.action.ViewReportAction.verifyData(ViewReportAction.java:222)	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)	at java.lang.reflect.Method.invoke(Method.java:597)	at org.springframework.webflow.util.DispatchMethodInvoker.invoke(DispatchMethodInvoker.java:103)	at org.springframework.webflow.action.MultiAction.doExecute(MultiAction.java:136)	at org.springframework.webflow.action.AbstractAction.execute(AbstractAction.java:203)	at org.springframework.webflow.engine.AnnotatedAction.execute(AnnotatedAction.java:142)	at org.springframework.webflow.engine.ActionExecutor.execute(ActionExecutor.java:61)	at org.springframework.webflow.engine.ActionState.doEnter(ActionState.java:180)	at org.springframework.webflow.engine.State.enter(State.java:200)	at org.springframework.webflow.engine.Transition.execute(Transition.java:229)	at org.springframework.webflow.engine.TransitionableState.onEvent(TransitionableState.java:112)	at org.springframework.webflow.engine.Flow.onEvent(Flow.java:572)	at org.springframework.webflow.engine.impl.RequestControlContextImpl.signalEvent(RequestControlContextImpl.java:207)	at org.springframework.webflow.engine.impl.FlowExecutionImpl.signalEvent(FlowExecutionImpl.java:214)	at org.springframework.webflow.executor.FlowExecutorImpl.resume(FlowExecutorImpl.java:238)	at sun.reflect.GeneratedMethodAccessor436.invoke(Unknown Source)	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)	at java.lang.reflect.Method.invoke(Method.java:597)	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:281)	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:187)	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:154)	at org.acegisecurity.intercept.method.aopalliance.MethodSecurityInterceptor.invoke(MethodSecurityInterceptor.java:66)	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:176)	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:210)	at $Proxy86.resume(Unknown Source)	at org.springframework.webflow.executor.support.FlowRequestHandler.handleFlowRequest(FlowRequestHandler.java:115)	at org.springframework.webflow.executor.mvc.FlowController.handleRequestInternal(FlowController.java:170)	at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153)	at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:45)	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:820)	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:755)	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:396)	at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:360)	at javax.servlet.http.HttpServlet.service(HttpServlet.java:710)	at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)	at logging.ReportLog.doFilter(ReportLog.java:39)	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)	at com.jaspersoft.jasperserver.war.common.UploadMultipartFilter.doFilter(UploadMultipartFilter.java:86)	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:264)	at org.acegisecurity.ui.switchuser.SwitchUserProcessingFilter.doFilter(SwitchUserProcessingFilter.java:335)	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)	at org.acegisecurity.intercept.web.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:107)	at org.acegisecurity.intercept.web.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:72)	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)	at org.acegisecurity.ui.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:110)	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)	at org.acegisecurity.providers.anonymous.AnonymousProcessingFilter.doFilter(AnonymousProcessingFilter.java:125)	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)	at com.jaspersoft.jasperserver.api.metadata.user.service.impl.MetadataAuthenticationProcessingFilter.doFilter(MetadataAuthenticationProcessingFilter.java:142)	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)	at com.jaspersoft.jasperserver.war.util.RequestParameterAuthenticationFilter.doFilter(RequestParameterAuthenticationFilter.java:97)	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)	at org.acegisecurity.ui.basicauth.BasicProcessingFilter.doFilter(BasicProcessingFilter.java:181)	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)	at com.jaspersoft.jasperserver.war.UserPreferencesFilter.doFilter(UserPreferencesFilter.java:178)	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)	at org.acegisecurity.ui.AbstractProcessingFilter.doFilter(AbstractProcessingFilter.java:217)	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)	at com.jaspersoft.jasperserver.war.UserPreferencesFilter.doFilter(UserPreferencesFilter.java:178)	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)	at org.acegisecurity.context.HttpSessionContextIntegrationFilter.doFilter(HttpSessionContextIntegrationFilter.java:191)	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)	at org.acegisecurity.util.FilterChainProxy.doFilter(FilterChainProxy.java:148)	at org.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:90)	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)	at com.jaspersoft.jasperserver.war.util.CharacterEncodingFilter.doFilter(CharacterEncodingFilter.java:70)	at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:138)	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)	at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:230)	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)	at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:179)	at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:84)	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:104)	at org.jboss.web.tomcat.service.jca.CachedConnectionValve.invoke(CachedConnectionValve.java:157)	at org.josso.tc55.agent.SSOAgentValve.invoke(SSOAgentValve.java:271)	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:241)	at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)	at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:580)	at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)	at java.lang.Thread.run(Thread.java:619)Caused by: org.postgresql.util.PSQLException: Fetch size must be a value greater to or equal to 0.	at org.postgresql.jdbc2.AbstractJdbc2Statement.setFetchSize(AbstractJdbc2Statement.java:2731)	at org.apache.commons.dbcp.DelegatingStatement.setFetchSize(DelegatingStatement.java:276)	at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createStatement(JRJdbcQueryExecuter.java:169)	... 109 more

org.postgresql.util.PSQLException: Fetch size must be a value greater to or equal to 0.

org.postgresql.util.PSQLException: Fetch size must be a value greater to or equal to 0.	at org.postgresql.jdbc2.AbstractJdbc2Statement.setFetchSize(AbstractJdbc2Statement.java:2731)	at org.apache.commons.dbcp.DelegatingStatement.setFetchSize(DelegatingStatement.java:276)	at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createStatement(JRJdbcQueryExecuter.java:169)	at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:123)	at com.jaspersoft.jasperserver.api.engine.jasperreports.util.JRTimezoneJdbcQueryExecuter.createDatasource(JRTimezoneJdbcQueryExecuter.java:164)	at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:676)	at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:596)	at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1223)	at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:853)	at net.sf.jasperreports.engine.fill.JRFiller.fillReport(JRFiller.java:123)	at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:439)	at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:748)	at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:367)	at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.executeReport(EngineServiceImpl.java:876)	at com.jaspersoft.jasperserver.api.engine.jasperreports.domain.impl.ReportUnitRequest.execute(ReportUnitRequest.java:60)	at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.execute(EngineServiceImpl.java:301)	at com.jaspersoft.jasperserver.war.action.ViewReportAction.executeReport(ViewReportAction.java:329)	at com.jaspersoft.jasperserver.war.action.ViewReportAction.verifyData(ViewReportAction.java:222)	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)	at java.lang.reflect.Method.invoke(Method.java:597)	at org.springframework.webflow.util.DispatchMethodInvoker.invoke(DispatchMethodInvoker.java:103)	at org.springframework.webflow.action.MultiAction.doExecute(MultiAction.java:136)	at org.springframework.webflow.action.AbstractAction.execute(AbstractAction.java:203)	at org.springframework.webflow.engine.AnnotatedAction.execute(AnnotatedAction.java:142)	at org.springframework.webflow.engine.ActionExecutor.execute(ActionExecutor.java:61)	at org.springframework.webflow.engine.ActionState.doEnter(ActionState.java:180)	at org.springframework.webflow.engine.State.enter(State.java:200)	at org.springframework.webflow.engine.Transition.execute(Transition.java:229)	at org.springframework.webflow.engine.TransitionableState.onEvent(TransitionableState.java:112)	at org.springframework.webflow.engine.Flow.onEvent(Flow.java:572)	at org.springframework.webflow.engine.impl.RequestControlContextImpl.signalEvent(RequestControlContextImpl.java:207)	at org.springframework.webflow.engine.impl.FlowExecutionImpl.signalEvent(FlowExecutionImpl.java:214)	at org.springframework.webflow.executor.FlowExecutorImpl.resume(FlowExecutorImpl.java:238)	at sun.reflect.GeneratedMethodAccessor436.invoke(Unknown Source)	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)	at java.lang.reflect.Method.invoke(Method.java:597)	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:281)	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:187)	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:154)	at org.acegisecurity.intercept.method.aopalliance.MethodSecurityInterceptor.invoke(MethodSecurityInterceptor.java:66)	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:176)	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:210)	at $Proxy86.resume(Unknown Source)	at org.springframework.webflow.executor.support.FlowRequestHandler.handleFlowRequest(FlowRequestHandler.java:115)	at org.springframework.webflow.executor.mvc.FlowController.handleRequestInternal(FlowController.java:170)	at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153)	at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:45)	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:820)	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:755)	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:396)	at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:360)	at javax.servlet.http.HttpServlet.service(HttpServlet.java:710)	at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)	at logging.ReportLog.doFilter(ReportLog.java:39)	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)	at com.jaspersoft.jasperserver.war.common.UploadMultipartFilter.doFilter(UploadMultipartFilter.java:86)	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:264)	at org.acegisecurity.ui.switchuser.SwitchUserProcessingFilter.doFilter(SwitchUserProcessingFilter.java:335)	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)	at org.acegisecurity.intercept.web.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:107)	at org.acegisecurity.intercept.web.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:72)	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)	at org.acegisecurity.ui.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:110)	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)	at org.acegisecurity.providers.anonymous.AnonymousProcessingFilter.doFilter(AnonymousProcessingFilter.java:125)	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)	at com.jaspersoft.jasperserver.api.metadata.user.service.impl.MetadataAuthenticationProcessingFilter.doFilter(MetadataAuthenticationProcessingFilter.java:142)	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)	at com.jaspersoft.jasperserver.war.util.RequestParameterAuthenticationFilter.doFilter(RequestParameterAuthenticationFilter.java:97)	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)	at org.acegisecurity.ui.basicauth.BasicProcessingFilter.doFilter(BasicProcessingFilter.java:181)	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)	at com.jaspersoft.jasperserver.war.UserPreferencesFilter.doFilter(UserPreferencesFilter.java:178)	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)	at org.acegisecurity.ui.AbstractProcessingFilter.doFilter(AbstractProcessingFilter.java:217)	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)	at com.jaspersoft.jasperserver.war.UserPreferencesFilter.doFilter(UserPreferencesFilter.java:178)	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)	at org.acegisecurity.context.HttpSessionContextIntegrationFilter.doFilter(HttpSessionContextIntegrationFilter.java:191)	at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)	at org.acegisecurity.util.FilterChainProxy.doFilter(FilterChainProxy.java:148)	at org.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:90)	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)	at com.jaspersoft.jasperserver.war.util.CharacterEncodingFilter.doFilter(CharacterEncodingFilter.java:70)	at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:138)	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)	at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:230)	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)	at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:179)	at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:84)	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:104)	at org.jboss.web.tomcat.service.jca.CachedConnectionValve.invoke(CachedConnectionValve.java:157)	at org.josso.tc55.agent.SSOAgentValve.invoke(SSOAgentValve.java:271)	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:241)	at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)	at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:580)	at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)	at java.lang.Thread.run(Thread.java:619)

[sendarnav]

Hi,

Thanks for this post, i have similar requirement, it is really works. Iam still testing the functionality. I have question does these settings work if i try run multiple reports with large data. Any help is appreciated.

Thanks,

Surya

 

 

[kjordan2001]

 Is it possible to make this work for a report that has subqueries (i.e. a table) or a subreport?  When it goes to execute the other query, I get this exception:

 

java.sql.SQLException: Streaming result set com.mysql.jdbc.RowDataDynamic@51a9bf1f is still active. No statements may be issued when any streaming result sets are open and in use on a given connection. Ensure that you have called .close() on any active streaming result sets before attempting more queries.	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:934)	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:931)	at com.mysql.jdbc.MysqlIO.checkForOutstandingStreamingData(MysqlIO.java:2735)	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1899)	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2151)	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2619)	at com.mysql.jdbc.StatementImpl.executeSimpleNonQuery(StatementImpl.java:1606)	at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2213)

[sendarnav]

I have made the following configuration change in Jasper Server ..

<bean id="fileVirtualizerFactory" class="com.jaspersoft.jasperserver.api.engine.common.service.impl.SwapFileVirtualizerFactory">

        <property name="maxSize" value="400"></property>

<!--        <property name="directory" value="${java.io.tmpdir}"></property> -->

 </bean>

 

How do i know the virtualizer is getting used while report generation.

[briinamar]

Hi, this works in my iReport 3.7.1 version but just using JRGzipVirtualizer virtualizer, not with JRSwapFileVirtualizer :( 

My question is: How can I configure this parameters in jasper grails plugin?

[debadarshan.behera]

I am also facing the same problem. After making the change, I am getting the error com.jaspersoft.jasperserver.api.JSExceptionWrapper: net.sf.jasperreports.engine.JRException: Error preparing statement for executing the report query..

Any help appriciated..