Weird behaviour when exporting to excel.

<p>I'm using the PHP Java Bridge to integrate BIRT into our application, for the most part it's been running well, but I've come across some strange behaviour recently. Initially it started out, that I had a report that ran fine, then as the amount of data in the report grew, it started failing to generate an XLS, and then as it grew more, even the HTML output was failing. I wasn't getting any meaningful error messages, and figured I'd first try to troubleshoot the excel issue as that happened earlier. I theorized the report might be getting long enough to hit a row limit in excel, so I proceeded to upgrade Birt in the JavaBridge to 4.5, so that I could use the XLSX emitter to overcome that suspected error. After upgrading, I went through a bunch of the different reports we have in the system, and all except the problematic report are working as expected. This time however, I was getting a more helpful error, it was a heap memory error, so I increased the heap size in Tomcat, and now got it to get further. Now it will run the html report no matter the size thus far, but the XLSX emitter still fails with a generic error, and I'm unsure how to proceed. The error I'm getting is as follows, and that gets written to the xlsx file instead of the report output. Is there any additional logging I can enable, or any suggestions on how I can troubleshoot further? Any suggestions are much appreciated.</p>
<p> </p>
<p>Thanks<br>
Warren</p>
<p> </p>
<div>o:Exception]:"java.lang.Exception: Invoke failed: [[o:RunAndRenderTask->run. Cause: org.eclipse.birt.report.engine.api.EngineException: Error happened while running the report. VM: 1.7.0_95@<a data-ipb='nomediaparse' href='http://java.oracle.com/'>http://java.oracle.com/"</a>at: </div>
<div>#-12 org.eclipse.birt.report.engine.api.impl.EngineTask.handleFatalExceptions(EngineTask.java:2375)</div>
<div>#-11 org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.doRun(RunAndRenderTask.java:191)</div>
<div>#-10 org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.run(RunAndRenderTask.java:77)</div>
<div>#-9 sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)</div>
<div>#-8 sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)</div>
<div>#-7 sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)</div>
<div>#-6 java.lang.reflect.Method.invoke(Method.java:606)</div>
<div>#-5 php.java.bridge.JavaBridge.Invoke(JavaBridge.java:1044)</div>
<div>#-4 php.java.bridge.Request.handleRequest(Request.java:417)</div>
<div>#-3 php.java.bridge.Request.handleRequests(Request.java:500)</div>
<div>#-2 php.java.bridge.http.ContextRunner.run(ContextRunner.java:145)</div>
<div>#-1 php.java.bridge.ThreadPool$Delegate.run(ThreadPool.java:60)</div>
<div>#0 /mnt/data/wmoxley/Projects/CareWebs/htdocs/project/apps/frontend/modules/reports/actions/java/Java.inc(232): java_ThrowExceptionProxyFactory->getProxy(13, '@V', 'T', true)</div>
<div>#1 /mnt/data/wmoxley/Projects/CareWebs/htdocs/project/apps/frontend/modules/reports/actions/java/Java.inc(360): java_Arg->getResult(true)</div>
<div>#2 /mnt/data/wmoxley/Projects/CareWebs/htdocs/project/apps/frontend/modules/reports/actions/java/Java.inc(366): java_Client->getWrappedResult(true)</div>
<div>#3 /mnt/data/wmoxley/Projects/CareWebs/htdocs/project/apps/frontend/modules/reports/actions/java/Java.inc(560): java_Client->getResult()</div>
<div>#4 /mnt/data/wmoxley/Projects/CareWebs/htdocs/project/apps/frontend/modules/reports/actions/java/Java.inc(1752): java_Client->invokeMethod(10, 'run', Array)</div>
<div>#5 /mnt/data/wmoxley/Projects/CareWebs/htdocs/project/apps/frontend/modules/reports/actions/java/Java.inc(1851): java_JavaProxy->__call('run', Array)</div>
<div>#6 /mnt/data/wmoxley/Projects/CareWebs/htdocs/project/apps/frontend/modules/reports/actions/java/Java.inc(1999): java_AbstractJava->__call('run', Array)</div>
<div>#7 /mnt/data/wmoxley/Projects/CareWebs/htdocs/project/apps/frontend/modules/reports/actions/actions.class.php(448): Java->__call('run', Array)</div>
<div>#8 /mnt/data/wmoxley/Projects/CareWebs/htdocs/project/apps/frontend/modules/reports/actions/actions.class.php(448): java_InternalJava->run()</div>
<div>#9 /mnt/data/wmoxley/Projects/CareWebs/htdocs/project/apps/frontend/modules/reports/actions/actions.class.php(281): reportsActions->generateReportXLS('<?xml version="...', Array, 'Invoicing_Repor...')</div>
<div>#10 /mnt/data/wmoxley/Projects/CareWebs/htdocs/symfony/RELEASE_1_2_12/lib/action/sfActions.class.php(60): reportsActions->executeGenerateReport(Object(sfWebRequest))</div>
<div>#11 /mnt/data/wmoxley/Projects/CareWebs/htdocs/symfony/RELEASE_1_2_12/lib/filter/sfExecutionFilter.class.php(90): sfActions->execute(Object(sfWebRequest))</div>
<div>#12 /mnt/data/wmoxley/Projects/CareWebs/htdocs/symfony/RELEASE_1_2_12/lib/filter/sfExecutionFilter.class.php(76): sfExecutionFilter->executeAction(Object(reportsActions))</div>
<div>#13 /mnt/data/wmoxley/Projects/CareWebs/htdocs/symfony/RELEASE_1_2_12/lib/filter/sfExecutionFilter.class.php(42): sfExecutionFilter->handleAction(Object(sfFilterChain), Object(reportsActions))</div>
<div>#14 /mnt/data/wmoxley/Projects/CareWebs/htdocs/symfony/RELEASE_1_2_12/lib/filter/sfFilterChain.class.php(53): sfExecutionFilter->execute(Object(sfFilterChain))</div>
<div>#15 /mnt/data/wmoxley/Projects/CareWebs/htdocs/symfony/RELEASE_1_2_12/lib/filter/sfCommonFilter.class.php(29): sfFilterChain->execute()</div>
<div>#16 /mnt/data/wmoxley/Projects/CareWebs/htdocs/symfony/RELEASE_1_2_12/lib/filter/sfFilterChain.class.php(53): sfCommonFilter->execute(Object(sfFilterChain))</div>
<div>#17 /mnt/data/wmoxley/Projects/CareWebs/htdocs/project/plugins/sfJqueryFormValidationPlugin/lib/filter/sfJqueryFormValidationFilter.class.php(7): sfFilterChain->execute()</div>
<div>#18 /mnt/data/wmoxley/Projects/CareWebs/htdocs/symfony/RELEASE_1_2_12/lib/filter/sfFilterChain.class.php(53): sfJqueryFormValidationFilter->execute(Object(sfFilterChain))</div>
<div>#19 /mnt/data/wmoxley/Projects/CareWebs/htdocs/project/apps/frontend/lib/exceptionCatchFilter.class.php(26): sfFilterChain->execute()</div>
<div>#20 /mnt/data/wmoxley/Projects/CareWebs/htdocs/symfony/RELEASE_1_2_12/lib/filter/sfFilterChain.class.php(53): ExceptionCatcherFilter->execute(Object(sfFilterChain))</div>
<div>#21 /mnt/data/wmoxley/Projects/CareWebs/htdocs/symfony/RELEASE_1_2_12/lib/filter/sfBasicSecurityFilter.class.php(62): sfFilterChain->execute()</div>
<div>#22 /mnt/data/wmoxley/Projects/CareWebs/htdocs/project/plugins/sfGuardPlugin/lib/sfGuardBasicSecurityFilter.class.php(36): sfBasicSecurityFilter->execute(Object(sfFilterChain))</div>
<div>#23 /mnt/data/wmoxley/Projects/CareWebs/htdocs/symfony/RELEASE_1_2_12/lib/filter/sfFilterChain.class.php(53): sfGuardBasicSecurityFilter->execute(Object(sfFilterChain))</div>
<div>#24 /mnt/data/wmoxley/Projects/CareWebs/htdocs/symfony/RELEASE_1_2_12/lib/filter/sfRenderingFilter.class.php(33): sfFilterChain->execute()</div>
<div>#25 /mnt/data/wmoxley/Projects/CareWebs/htdocs/symfony/RELEASE_1_2_12/lib/filter/sfFilterChain.class.php(53): sfRenderingFilter->execute(Object(sfFilterChain))</div>
<div>#26 /mnt/data/wmoxley/Projects/CareWebs/htdocs/symfony/RELEASE_1_2_12/lib/controller/sfController.class.php(245): sfFilterChain->execute()</div>
<div>#27 /mnt/data/wmoxley/Projects/CareWebs/htdocs/symfony/RELEASE_1_2_12/lib/action/sfAction.class.php(150): sfController->forward('reports', 'generateReport')</div>
<div>#28 /mnt/data/wmoxley/Projects/CareWebs/htdocs/project/apps/frontend/modules/timesheet/actions/actions.class.php(206): sfAction->forward('reports', 'generateReport')</div>
<div>#29 /mnt/data/wmoxley/Projects/CareWebs/htdocs/symfony/RELEASE_1_2_12/lib/action/sfActions.class.php(60): timesheetActions->executeInvoicingReport(Object(sfWebRequest))</div>
<div>#30 /mnt/data/wmoxley/Projects/CareWebs/htdocs/symfony/RELEASE_1_2_12/lib/filter/sfExecutionFilter.class.php(90): sfActions->execute(Object(sfWebRequest))</div>
<div>#31 /mnt/data/wmoxley/Projects/CareWebs/htdocs/symfony/RELEASE_1_2_12/lib/filter/sfExecutionFilter.class.php(76): sfExecutionFilter->executeAction(Object(timesheetActions))</div>
<div>#32 /mnt/data/wmoxley/Projects/CareWebs/htdocs/symfony/RELEASE_1_2_12/lib/filter/sfExecutionFilter.class.php(42): sfExecutionFilter->handleAction(Object(sfFilterChain), Object(timesheetActions))</div>
<div>#33 /mnt/data/wmoxley/Projects/CareWebs/htdocs/symfony/RELEASE_1_2_12/lib/filter/sfFilterChain.class.php(53): sfExecutionFilter->execute(Object(sfFilterChain))</div>
<div>#34 /mnt/data/wmoxley/Projects/CareWebs/htdocs/symfony/RELEASE_1_2_12/lib/filter/sfCommonFilter.class.php(29): sfFilterChain->execute()</div>
<div>#35 /mnt/data/wmoxley/Projects/CareWebs/htdocs/symfony/RELEASE_1_2_12/lib/filter/sfFilterChain.class.php(53): sfCommonFilter->execute(Object(sfFilterChain))</div>
<div>#36 /mnt/data/wmoxley/Projects/CareWebs/htdocs/project/plugins/sfJqueryFormValidationPlugin/lib/filter/sfJqueryFormValidationFilter.class.php(7): sfFilterChain->execute()</div>
<div>#37 /mnt/data/wmoxley/Projects/CareWebs/htdocs/symfony/RELEASE_1_2_12/lib/filter/sfFilterChain.class.php(53): sfJqueryFormValidationFilter->execute(Object(sfFilterChain))</div>
<div>#38 /mnt/data/wmoxley/Projects/CareWebs/htdocs/project/apps/frontend/lib/exceptionCatchFilter.class.php(26): sfFilterChain->execute()</div>
<div>#39 /mnt/data/wmoxley/Projects/CareWebs/htdocs/symfony/RELEASE_1_2_12/lib/filter/sfFilterChain.class.php(53): ExceptionCatcherFilter->execute(Object(sfFilterChain))</div>
<div>#40 /mnt/data/wmoxley/Projects/CareWebs/htdocs/symfony/RELEASE_1_2_12/lib/filter/sfBasicSecurityFilter.class.php(62): sfFilterChain->execute()</div>
<div>#41 /mnt/data/wmoxley/Projects/CareWebs/htdocs/project/plugins/sfGuardPlugin/lib/sfGuardBasicSecurityFilter.class.php(36): sfBasicSecurityFilter->execute(Object(sfFilterChain))</div>
<div>#42 /mnt/data/wmoxley/Projects/CareWebs/htdocs/symfony/RELEASE_1_2_12/lib/filter/sfFilterChain.class.php(53): sfGuardBasicSecurityFilter->execute(Object(sfFilterChain))</div>
<div>#43 /mnt/data/wmoxley/Projects/CareWebs/htdocs/symfony/RELEASE_1_2_12/lib/filter/sfRenderingFilter.class.php(33): sfFilterChain->execute()</div>
<div>#44 /mnt/data/wmoxley/Projects/CareWebs/htdocs/symfony/RELEASE_1_2_12/lib/filter/sfFilterChain.class.php(53): sfRenderingFilter->execute(Object(sfFilterChain))</div>
<div>#45 /mnt/data/wmoxley/Projects/CareWebs/htdocs/symfony/RELEASE_1_2_12/lib/controller/sfController.class.php(245): sfFilterChain->execute()</div>
<div>#46 /mnt/data/wmoxley/Projects/CareWebs/htdocs/symfony/RELEASE_1_2_12/lib/controller/sfFrontWebController.class.php(48): sfController->forward('timesheet', 'invoicingReport')</div>
<div>#47 /mnt/data/wmoxley/Projects/CareWebs/htdocs/symfony/RELEASE_1_2_12/lib/util/sfContext.class.php(159): sfFrontWebController->dispatch()</div>
<div>#48 /mnt/data/wmoxley/Projects/CareWebs/htdocs/project/web/index.php(6): sfContext->dispatch()</div>
<div>#49 {main}]</div>

Find more posts tagged with

Comments

Clement Wong

<p>Not sure what's going on, but maybe if you can provide some additional information...</p>
<p> </p>
<p>What's the version of Tomcat?</p>
<p> </p>
<p>What memory settings do you have configured now for Tomcat?</p>
<p> </p>
<p>What 's the version Java you're using for Tomcat?</p>
<p> </p>
<p>What is the structure of the report? What's the data source? If you are able to attach the design, please do. Otherwise, please describe what the report does and if there are any customizations.</p>
<p> </p>
<p>Do other reports exhibit the same behavior from the app server?</p>
<p> </p>
<p>Does the same report (with the large data set) have issues in the Designer? If no, does increasing the memory in the Designer (via eclipse.ini) help?</p>
<p> </p>
<p>What Excel export emitter are you using? Have you tried others?</p>

SuperYeti2

<p>Hey Clement, info below in red.</p>
<p> </p>
<blockquote class="ipsBlockquote" data-author="Clement Wong" data-cid="143363" data-time="1460998299">
<div>
<p>Not sure what's going on, but maybe if you can provide some additional information...</p>
<p> </p>
<p>What's the version of Tomcat? <span style="color:#ff0000;">6</span></p>
<p> </p>
<p>What memory settings do you have configured now for Tomcat? <span style="color:#ff0000;">I've tried a few variations. This is what I've settled on thus far. I've changed /usr/share/tomcat6/bin/catalina.sh and added the following to the beginning of the file:</span></p>
<div><span style="color:#ff0000;">CATALINA_OPTS="-Djava.awt.headless=true -Dfile.encoding=UTF-8 -server -Xms3084m -Xmx30848m -XX:NewSize=512m -XX:MaxNewSize=512m -XX:PermSize=512m -XX:MaxPermSize=512m -XX:+DisableExplicitGC "</span></div>
<div><span style="color:#ff0000;">export CATALINA_OPTS</span></div>
<div> </div>
<div><span style="color:#ff0000;">Making these changes fixed the 1 report, but there is still 1 that is having the same issues.</span></div>
<div> </div>
<p>What 's the version Java you're using for Tomcat?</p>
<div><span style="color:#ff0000;">java version "1.7.0_95"</span></div>
<div><span style="color:#ff0000;">OpenJDK Runtime Environment (IcedTea 2.6.4) (7u95-2.6.4-0ubuntu0.14.04.2)</span></div>
<div><span style="color:#ff0000;">OpenJDK 64-Bit Server VM (build 24.95-b01, mixed mode)</span></div>
<p> </p>
<p>What is the structure of the report? What's the data source? If you are able to attach the design, please do. Otherwise, please describe what the report does and if there are any customizations.</p>
<p><span style="color:#ff0000;">Datasource is MySQL server. It's a simple table of data, showing all rows, and there are a couple simple calculated fields in the query. Nothing special, I have much more complex reports including JS heavy ones, no issues. I've attached a copy of the report to the post here.</span></p>
<p> </p>
<p>Do other reports exhibit the same behavior from the app server? <span style="color:#ff0000;">There were 2, when I adjusted the memory, 1 of them started working, even with the larger datasets. When I look at the same 2 reports, which are just 2 different views of the same data, 1 is working, 1 isn't it seems once the dataset reaches a certain size.</span></p>
<p> </p>
<p>Does the same report (with the large data set) have issues in the Designer? If no, does increasing the memory in the Designer (via eclipse.ini) help? <span style="color:#ff0000;">When I'm using the designer, I think it limits it to 500 rows or something, and it works fine with that, it also works fine in our integration, there are 15+ other reports that run fine with HTML, PDF, or Excel. This one works fine with HTML and PDF as well, only Excel is an issue once it hits a certain size.</span></p>
<p> </p>
<p>What Excel export emitter are you using? Have you tried others? <span style="color:#ff0000;">I'm using the XLSX emitter that comes with the BIRT engine. This was the main reason I upgraded to 4.5 from 2.6. Also to bring us up to date, but I had the same issues with the older version of the engine as well. Below is my PHP code that calls the Java bridge. Are there other emitters available?</span></p>
<pre class="_prettyXprint">
ob_start();

sfConfig::set('screen_id', '065.5');

if ($reportTitle === '')
$reportTitle = 'downloaded.xlsx';
else
$reportTitle .= '-' . date('Y-m-d_H-i-s') . '.xlsx';

header("Content-type: application/xlsx");
header("Content-Disposition: attachment; filename=" . $reportTitle);

define ("JAVA_HOSTS", "127.0.0.1:8080");
define ("JAVA_SERVLET", "/JavaBridge/JavaBridge.phpjavabridge");

require_once("java/Java.inc");

session_start();

$ctx = java_context()->getServletContext();
$birtReportEngine = java("org.eclipse.birt.php.birtengine.BirtEngine")->getBirtEngine($ctx);
java_context()->onShutdown(java("org.eclipse.birt.php.birtengine.BirtEngine")->getShutdownHook());

try {
$strReport = new java("java.lang.String", $report_xml);

$rptStream = new java("java.io.ByteArrayInputStream", $strReport->getBytes("UTF-8"));

$report = $birtReportEngine->openReportDesign($rptStream);

$task = $birtReportEngine->createRunAndRenderTask($report);

if (!is_null($params)) {
foreach ($params as $key => $param) {
$task->setParameterValue($key, $param);

}
}

$task->setParameterValue('sf_guard_user_id', $this->getUser()->getId());

$request = sfContext::getInstance()->getRequest();
$url = 'http' . ($request->isSecure() ? 's' : '') . '://' . $request->getHost();
$task->setParameterValue('base_url', $url);

$taskOptions = new java("org.eclipse.birt.report.engine.api.EXCELRenderOption");
$outputStream = new java("java.io.ByteArrayOutputStream");
$taskOptions->setOutputStream($outputStream);
$taskOptions->setOutputFormat("xlsx");

$task->setRenderOption($taskOptions);
$task->run();
$task->close();

} catch (JavaException $e) {
/*
error_log("Birt Error Occurred: ");
error_log("File: " . $e->getFile());
error_log("Line: " . $e->getLine());
error_log("Message: " . $e->getMessage());
error_log("Stack Trace: " . $e->getTraceAsString());
*/
echo $e; //"Error Calling BIRT";

}

if (!is_null($outputStream))
echo java_values($outputStream->toByteArray());
</pre>
</div>
</blockquote>
<p> </p>
<p>Thanks in advance</p>
<p>Warren</p>

Clement Wong

<p>Thanks for your detailed answers.</p>
<p> </p>
<p>Please note that the MySQL JDBC Driver (<a data-ipb='nomediaparse' href='https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-implementation-notes.html'>https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-implementation-notes.html</a>): </p>
<p> </p>
<p><em>By default, ResultSets are completely retrieved and stored in memory. In most cases this is the most efficient way to operate, and due to the design of the MySQL network protocol is easier to implement. If you are working with ResultSets that have a large number of rows or large values, and cannot allocate heap space in your JVM for the memory required, you can tell the driver to stream the results back one row at a time.</em></p>
<p> </p>
<p>Since we are dealing with large datasets, can you please add "<span style="font-family:'courier new', courier, monospace;">?useCursorFetch=true&defaultFetchSize=2000</span>" to the end of your JDBC URL.</p>
<p> </p>
<p>So it would look like:</p>
<p><span style="font-family:'courier new', courier, monospace;">jdbc:mysql://localhost:3306/production?useCursorFetch=true&defaultFetchSize=2000</span></p>
<p> </p>
<p> </p>
<p>Regarding my previous question about running in the Designer, it should <strong>not </strong>limit you to 500 rows if you choose from the main menu <em>Run > View Report > As XLS, As XLS_SPUDSOFT, or As XLSX</em> .</p>
<p> </p>
<p>What (data) size, do you start seeing the issue?</p>
<p> </p>
<p> </p>
<p>For the problematic report, can you run it in the Designer with the large data set and as XLSX? We are trying to isolate the issue outside of Tomcat and the PHP Bridge.</p>
<p>
Are you able to test with Tomcat 7, and/or with Java 8?</p>