Error to generate Service Availability report on JasperServer
Article ID: 200754
Updated On:
Products
Issue/Introduction
When I try extract a "Service availability report by Service name" on JasperReports, receive the following error message:
Error Message
There was an error on the server. Try again or contact site administrators. (Error UID: 9e6a6223-c5f4-43d3-8061-11ed180e6e5f)
I try to extract other reports and I have no problems.
This starts after removing the old SAP CABI integration
JASPER SERVER 6.4.3
Spectrum 10.4.0
Environment
Release :
Component : JasperReports
Cause
Error message in the JasperReports:
2020-10-02 12:08:09,329 ERROR AsyncJasperPrintAccessor,pool-6-thread-3:321 - Error during report execution
net.sf.jasperreports.engine.JRRuntimeException: net.sf.jasperreports.engine.JRException: Error executing SQL statement for: Service Availability Main.
at net.sf.jasperreports.engine.fill.JRFillSubreport.prepare(JRFillSubreport.java:874)
at net.sf.jasperreports.engine.fill.JRFillElementContainer.prepareElements(JRFillElementContainer.java:537)
at net.sf.jasperreports.engine.fill.JRFillBand.fill(JRFillBand.java:411)
at net.sf.jasperreports.engine.fill.JRFillBand.fill(JRFillBand.java:386)
at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillSummaryNoLastFooterSamePage(JRVerticalFiller.java:1067)
at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillSummary(JRVerticalFiller.java:1026)
at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReportEnd(JRVerticalFiller.java:287)
at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReport(JRVerticalFiller.java:122)
at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:586)
at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFill.run(BaseFillHandle.java:123)
at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$SynchronousExecutor.execute(EngineServiceImpl.java:945)
at net.sf.jasperreports.engine.fill.BaseFillHandle.startFill(BaseFillHandle.java:169)
at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$AsynchronousReportFiller.fillReport(EngineServiceImpl.java:896)
at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:1812)
at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runWithDataSource(EngineServiceImpl.java:1143)
at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runReport(EngineServiceImpl.java:1072)
at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportRunnable.run(EngineServiceImpl.java:967)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
Caused by: net.sf.jasperreports.engine.JRException: Error executing SQL statement for: Service Availability Main.
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:347)
at com.jaspersoft.jasperserver.api.engine.jasperreports.util.JRTimezoneJdbcQueryExecuter.createDatasource(JRTimezoneJdbcQueryExecuter.java:169)
at com.jaspersoft.commons.util.JSControlledJdbcQueryExecuter.createDatasource(JSControlledJdbcQueryExecuter.java:113)
at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1245)
at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:723)
at net.sf.jasperreports.engine.fill.BaseReportFiller.setParameters(BaseReportFiller.java:440)
at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:554)
at net.sf.jasperreports.engine.fill.BaseReportFiller.fill(BaseReportFiller.java:398)
at net.sf.jasperreports.engine.fill.JRFillSubreport.fillSubreport(JRFillSubreport.java:732)
at net.sf.jasperreports.engine.fill.JRSubreportRunnable.run(JRSubreportRunnable.java:59)
at net.sf.jasperreports.engine.fill.AbstractThreadSubreportRunner.run(AbstractThreadSubreportRunner.java:221)
... 3 more
Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON sm_monitoroutages.monOutageID=sm_monitormaps.monOutageID and v_security_strin' at line 51
Query is:
SELECT
sm_monitoroutages.serviceHealth,
sm_monitoroutages.outageType,
sm_monitors.monName,
sm_monitoroutages.endTime,
sm_monitoroutages.startTime,
sm_monitors.monitorMH,
sm_monitoroutages.monOutageID,
sm_monitors.monDestroyTime,
sm_monitors.monCreateTime,
sm_monitors.monitorType,
sm_slmowns.userMH,
sm_usersmhs.userName,
sm_users.userName,
sm_monitors.monDescription,
sm_monitoroutages.notes,
sm_monitoroutages.userName,
sm_monitoroutages.origServiceHealth
,CASE
WHEN sm_monitoroutages.startTime<? THEN ?
ELSE
sm_monitoroutages.startTime
END as OutageStartTimeAdjusted,
CASE
WHEN sm_monitoroutages.endTime is null THEN ?
WHEN TIMESTAMPDIFF(SECOND,?,sm_monitoroutages.endTime)>0 THEN ?
ELSE
sm_monitoroutages.endTime
END as OutageEndTimeAdjusted,
CASE
WHEN TIMESTAMPDIFF(SECOND,?,sm_monitors.monCreateTime)<0 THEN ?
ELSE
sm_monitors.monCreateTime
END as ModelReportStartTime,
CASE
WHEN sm_monitoroutages.endTime is null THEN ?
WHEN TIMESTAMPDIFF(SECOND,sm_monitors.monDestroyTime,?)>0 THEN
sm_m
at org.mariadb.jdbc.internal.util.ExceptionMapper.get(ExceptionMapper.java:125)
at org.mariadb.jdbc.internal.util.ExceptionMapper.throwException(ExceptionMapper.java:69)
at org.mariadb.jdbc.MariaDbStatement.executeQueryEpilog(MariaDbStatement.java:259)
at org.mariadb.jdbc.MariaDbStatement.execute(MariaDbStatement.java:287)
at org.mariadb.jdbc.MariaDbStatement.executeQuery(MariaDbStatement.java:444)
at org.mariadb.jdbc.MariaDbClientPreparedStatement.executeQuery(MariaDbClientPreparedStatement.java:119)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:310)
... 13 more
2020-10-02 12:08:09,365 ERROR ErrorPageHandlerAction,http-nio-8080-exec-7:118 - Error UID 3bcc3322-c49d-4b88-b37a-5328f8c0692d
net.sf.jasperreports.engine.JRRuntimeException: net.sf.jasperreports.engine.JRException: Error executing SQL statement for: Service Availability Main.
at net.sf.jasperreports.engine.fill.JRFillSubreport.prepare(JRFillSubreport.java:874)
at net.sf.jasperreports.engine.fill.JRFillElementContainer.prepareElements(JRFillElementContainer.java:537)
at net.sf.jasperreports.engine.fill.JRFillBand.fill(JRFillBand.java:411)
at net.sf.jasperreports.engine.fill.JRFillBand.fill(JRFillBand.java:386)
at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillSummaryNoLastFooterSamePage(JRVerticalFiller.java:1067)
at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillSummary(JRVerticalFiller.java:1026)
at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReportEnd(JRVerticalFiller.java:287)
at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReport(JRVerticalFiller.java:122)
at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:586)
at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFill.run(BaseFillHandle.java:123)
at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$SynchronousExecutor.execute(EngineServiceImpl.java:945)
at net.sf.jasperreports.engine.fill.BaseFillHandle.startFill(BaseFillHandle.java:169)
at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$AsynchronousReportFiller.fillReport(EngineServiceImpl.java:896)
at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:1812)
at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runWithDataSource(EngineServiceImpl.java:1143)
at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runReport(EngineServiceImpl.java:1072)
at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportRunnable.run(EngineServiceImpl.java:967)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
Caused by: net.sf.jasperreports.engine.JRException: Error executing SQL statement for: Service Availability Main.
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:347)
at com.jaspersoft.jasperserver.api.engine.jasperreports.util.JRTimezoneJdbcQueryExecuter.createDatasource(JRTimezoneJdbcQueryExecuter.java:169)
at com.jaspersoft.commons.util.JSControlledJdbcQueryExecuter.createDatasource(JSControlledJdbcQueryExecuter.java:113)
at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1245)
at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:723)
at net.sf.jasperreports.engine.fill.BaseReportFiller.setParameters(BaseReportFiller.java:440)
at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:554)
at net.sf.jasperreports.engine.fill.BaseReportFiller.fill(BaseReportFiller.java:398)
at net.sf.jasperreports.engine.fill.JRFillSubreport.fillSubreport(JRFillSubreport.java:732)
at net.sf.jasperreports.engine.fill.JRSubreportRunnable.run(JRSubreportRunnable.java:59)
at net.sf.jasperreports.engine.fill.AbstractThreadSubreportRunner.run(AbstractThreadSubreportRunner.java:221)
... 3 more
Resolution
1. Removing the spectrum organization from Jasper.
2. Stopped Jasper tomcat. Cleared the Jasper_HOME/apache-tomcat/work directory
3. Started the Jasper tomcat
4. Go to OneClick webpage, Report Manager->Jasper Integration, and Save. then redeploy the reports.
5. Uncheck the Report Manager->Preferences Enable Security.
Feedback
