ReportServer Forum

jrulefabric

Member

Registered: 2018-10-02

mysql intervals (expressed as hours:min:sec) cause runtime errors.

The following query when submitted via a standard mysql command line produces the following output...

SELECT 
        pq.genome_prep_start, pq.pipeline_submitted,
        timediff(pq.genome_prep_start, pq.pipeline_submitted) large_pos_duration,
        timediff(pq.pipeline_submitted, pq.genome_prep_start) negative_duration

FROM annotation_service.pipeline_queue pq
LIMIT 2

The two columns in the select are defined as follows..

  `date_submitted` datetime NOT NULL,
  `date_start` datetime DEFAULT NULL,

The results are:

"genome_prep_start","pipeline_submitted","large_pos_duration","negative_duration"
"2017-10-11 13:26:26","2016-02-25 11:29:57","838:59:59","-838:59:59"
"2017-08-22 14:46:09","2016-02-25 12:26:11","838:59:59","-838:59:59"

This same query when run as the SQL block of a "Dynamic List" report produces an error: (Could not ascertain object at position: 3)

Intervals seem to work when they are positive, and relatively small (only 2 digits in the hours section), at least in my testing.   But ReportServer clearly has some problems accepting all the output from a duration field.

Is there a work around?   If this is a bug (and I think it is) what is the process for submitting a bug?

Here is the stack trace...

net.datenwerke.rs.core.service.reportmanager.exceptions.DatabaseConnectionException: Could not ascertain object at position: 3
	at net.datenwerke.rs.base.service.datasources.table.impl.TableDBDataSource.getFieldValue(TableDBDataSource.java:149)
	at net.datenwerke.rs.base.service.reportengines.table.SimpleDataSupplier.getData(SimpleDataSupplier.java:440)
	at net.datenwerke.rs.base.service.reportengines.table.SimpleDataSupplier.getData(SimpleDataSupplier.java:422)
	at net.datenwerke.rs.base.service.reportengines.table.SimpleDataSupplier.getData(SimpleDataSupplier.java:154)
	at net.datenwerke.rs.base.service.reportengines.table.SimpleDataSupplier.getData(SimpleDataSupplier.java:124)
	at net.datenwerke.rs.base.server.table.TableReportUtilityServiceImpl.loadData(TableReportUtilityServiceImpl.java:322)
	at net.datenwerke.security.service.security.aop.SecurityCheckInterceptor.invoke(SecurityCheckInterceptor.java:109)
	at net.datenwerke.gf.service.gwtstacktrace.CatchStacktraceInterceptor.invoke(CatchStacktraceInterceptor.java:38)
	at sun.reflect.GeneratedMethodAccessor232.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at com.google.gwt.user.server.rpc.RPC.invokeAndEncodeResponse(RPC.java:587)
	at com.google.gwt.user.server.rpc.RemoteServiceServlet.processCall(RemoteServiceServlet.java:333)
	at net.datenwerke.security.service.security.aop.SecurityCheckInterceptor.invoke(SecurityCheckInterceptor.java:109)
	at net.datenwerke.gf.service.gwtstacktrace.CatchStacktraceInterceptor.invoke(CatchStacktraceInterceptor.java:38)
	at com.google.gwt.user.server.rpc.RemoteServiceServlet.processCall(RemoteServiceServlet.java:303)
	at net.datenwerke.security.service.security.aop.SecurityCheckInterceptor.invoke(SecurityCheckInterceptor.java:109)
	at net.datenwerke.gf.service.gwtstacktrace.CatchStacktraceInterceptor.invoke(CatchStacktraceInterceptor.java:38)
	at com.google.gwt.user.server.rpc.RemoteServiceServlet.processPost(RemoteServiceServlet.java:373)
	at com.google.gwt.user.server.rpc.AbstractRemoteServiceServlet.doPost(AbstractRemoteServiceServlet.java:62)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:648)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
	at net.datenwerke.security.service.security.aop.SecurityCheckInterceptor.invoke(SecurityCheckInterceptor.java:109)
	at net.datenwerke.gf.service.gwtstacktrace.CatchStacktraceInterceptor.invoke(CatchStacktraceInterceptor.java:38)
	at com.google.inject.servlet.ServletDefinition.doService(ServletDefinition.java:263)
	at com.google.inject.servlet.ServletDefinition.service(ServletDefinition.java:178)
	at com.google.inject.servlet.ManagedServletPipeline.service(ManagedServletPipeline.java:91)
	at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:62)
	at com.google.inject.persist.PersistFilter.doFilter(PersistFilter.java:89)
	at com.google.inject.servlet.FilterDefinition.doFilter(FilterDefinition.java:163)
	at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:58)
	at com.google.inject.servlet.FilterDefinition.doFilter(FilterDefinition.java:168)
	at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:58)
	at com.google.inject.servlet.FilterDefinition.doFilter(FilterDefinition.java:168)
	at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:58)
	at com.google.inject.servlet.ManagedFilterPipeline.dispatch(ManagedFilterPipeline.java:118)
	at com.google.inject.servlet.GuiceFilter.doFilter(GuiceFilter.java:113)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:94)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:492)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:80)
	at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:620)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:502)
	at org.apache.coyote.ajp.AbstractAjpProcessor.process(AbstractAjpProcessor.java:870)
	at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:684)
	at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.doRun(AprEndpoint.java:2527)
	at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.run(AprEndpoint.java:2516)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLException: Bad format for Time '838:59:59' in column 3
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:896)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:885)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
	at com.mysql.jdbc.ResultSetRow.getTimeFast(ResultSetRow.java:862)
	at com.mysql.jdbc.ByteArrayRow.getTimeFast(ByteArrayRow.java:232)
	at com.mysql.jdbc.ResultSetImpl.getTimeInternal(ResultSetImpl.java:5602)
	at com.mysql.jdbc.ResultSetImpl.getTime(ResultSetImpl.java:5372)
	at com.mysql.jdbc.ResultSetImpl.getObject(ResultSetImpl.java:4593)
	at com.mchange.v2.c3p0.impl.NewProxyResultSet.getObject(NewProxyResultSet.java:112)
	at net.datenwerke.rs.base.service.dbhelper.DatabaseHelper$1.getObject(DatabaseHelper.java:120)
	at net.datenwerke.rs.base.service.datasources.table.impl.TableDBDataSource.getFieldValue(TableDBDataSource.java:147)
	... 54 more

Last edited by jrulefabric (2018-10-10 18:11:13)

IF_Eduardo

Administrator

Registered: 2016-11-01

Website

Re: mysql intervals (expressed as hours:min:sec) cause runtime errors.

Hi jrulefabric,

I issued ticket RS-3083 for this and will update here when I have more information.

Regards,
Eduardo

IF_Eduardo

Administrator

Registered: 2016-11-01

Website

Re: mysql intervals (expressed as hours:min:sec) cause runtime errors.

Hi jrulefabric,

please post your complete reportserver version (rsversion.properties) and your mysql version.

Regards,
Eduardo