Chart Servlet Outages model exception

Description

Found this Outages model exception on the ATO demo server and it looks as if one of the charts is referencing a column that no longer exists in the outages table:

2015-10-20 04:08:52,873 ERROR [qtp1336172275-600478 - /opennms/charts?chart-name=sample-bar-chart2] o.o.w.g.PurdyChartServlet: Error in SQL for chart: sample-bar-chart2
org.postgresql.util.PSQLException: ERROR: column outages.serviceid does not exist
  Position: 75
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161) ~[postgresql-9.3-1100-jdbc4.jar:?]
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890) ~[postgresql-9.3-1100-jdbc4.jar:?]
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255) ~[postgresql-9.3-1100-jdbc4.jar:?]
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:560) ~[postgresql-9.3-1100-jdbc4.jar:?]
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:403) ~[postgresql-9.3-1100-jdbc4.jar:?]
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:283) ~[postgresql-9.3-1100-jdbc4.jar:?]
        at com.mchange.v2.c3p0.impl.NewProxyStatement.executeQuery(NewProxyStatement.java:35) ~[c3p0-0.9.1.2.jar:0.9.1.2]
        at org.jfree.data.jdbc.JDBCCategoryDataset.executeQuery(JDBCCategoryDataset.java:213) ~[jfreechart-1.0.19.jar:?]
        at org.jfree.data.jdbc.JDBCCategoryDataset.executeQuery(JDBCCategoryDataset.java:191) ~[jfreechart-1.0.19.jar:?]
        at org.jfree.data.jdbc.JDBCCategoryDataset.<init>(JDBCCategoryDataset.java:155) ~[jfreechart-1.0.19.jar:?]
        at org.opennms.web.charts.ChartUtils.buildCategoryDataSet(ChartUtils.java:276) ~[opennms-web-api-17.0.0-SNAPSHOT.jar:?]
        at org.opennms.web.charts.ChartUtils.getBarChart(ChartUtils.java:124) ~[opennms-web-api-17.0.0-SNAPSHOT.jar:?]
        at org.opennms.web.charts.ChartUtils.getBarChartPNG(ChartUtils.java:334) ~[opennms-web-api-17.0.0-SNAPSHOT.jar:?]
        at org.opennms.web.graph.PurdyChartServlet.doGet(PurdyChartServlet.java:112) [opennms-webapp-17.0.0-SNAPSHOT.jar:?]
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:575) [geronimo-servlet_3.0_spec-1.0.jar:1.0]
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:668) [geronimo-servlet_3.0_spec-1.0.jar:1.0]
        at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:669) [jetty-servlet-8.1.10.v20130312.jar:8.1.10.v20130312]
        at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1448) [jetty-servlet-8.1.10.v20130312.jar:8.1.10.v20130312]
        at org.opennms.container.web.felix.base.internal.dispatch.InvocationFilterChain.doFilter(InvocationFilterChain.java:53) [17.0.0-SNAPSHOT:?]
        at org.opennms.container.web.felix.base.internal.dispatch.HttpFilterChain.doFilter(HttpFilterChain.java:35) [17.0.0-SNAPSHOT:?]
        at org.opennms.container.web.felix.base.internal.dispatch.FilterPipeline.dispatch(FilterPipeline.java:50) [17.0.0-SNAPSHOT:?]
        at org.opennms.container.web.felix.base.internal.dispatch.Dispatcher.dispatch(Dispatcher.java:42) [17.0.0-SNAPSHOT:?]
        at org.opennms.container.web.bridge.internal.DispatcherFilter.doFilter(DispatcherFilter.java:72) [17.0.0-SNAPSHOT:?]
        at org.opennms.container.web.ProxyFilter.doFilter(ProxyFilter.java:56) [org.opennms.container.servlet-17.0.0-SNAPSHOT.jar:?]
        at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1419) [jetty-servlet-8.1.10.v20130312.jar:8.1.10.v20130312]
        at org.extremecomponents.table.filter.AbstractExportFilter.doFilter(AbstractExportFilter.java:49) [extremecomponents-1.0.1.jar:1.0.1]
        at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1419) [jetty-servlet-8.1.10.v20130312.jar:8.1.10.v20130312]
        at org.opennms.web.servlet.StoreRequestPropertiesFilter.doFilter(StoreRequestPropertiesFilter.java:79) [opennms-web-api-17.0.0-SNAPSHOT.jar:?]
        at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1419) [jetty-servlet-8.1.10.v20130312.jar:8.1.10.v20130312]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330) [spring-security-web-3.2.7.RELEASE.jar:3.2.7.RELEASE]
        at org.opennms.web.springframework.security.AuthFilterEnabler.doFilter(AuthFilterEnabler.java:115) [org.opennms.features.springframework-security-17.0.0-SNAPSHOT.jar:?]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) [spring-security-web-3.2.7.RELEASE.jar:3.2.7.RELEASE]
        at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:118) [spring-security-web-3.2.7.RELEASE.jar:3.2.7.RELEASE]
        at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:84) [spring-security-web-3.2.7.RELEASE.jar:3.2.7.RELEASE]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) [spring-security-web-3.2.7.RELEASE.jar:3.2.7.RELEASE]
        at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:113) [spring-security-web-3.2.7.RELEASE.jar:3.2.7.RELEASE]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) [spring-security-web-3.2.7.RELEASE.jar:3.2.7.RELEASE]
        at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:103) [spring-security-web-3.2.7.RELEASE.jar:3.2.7.RELEASE]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) [spring-security-web-3.2.7.RELEASE.jar:3.2.7.RELEASE]
        at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:113) [spring-security-web-3.2.7.RELEASE.jar:3.2.7.RELEASE]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) [spring-security-web-3.2.7.RELEASE.jar:3.2.7.RELEASE]
        at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:154) [spring-security-web-3.2.7.RELEASE.jar:3.2.7.RELEASE]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) [spring-security-web-3.2.7.RELEASE.jar:3.2.7.RELEASE]
        at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:45) [spring-security-web-3.2.7.RELEASE.jar:3.2.7.RELEASE]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) [spring-security-web-3.2.7.RELEASE.jar:3.2.7.RELEASE]
        at org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilter(BasicAuthenticationFilter.java:150) [spring-security-web-3.2.7.RELEASE.jar:3.2.7.RELEASE]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) [spring-security-web-3.2.7.RELEASE.jar:3.2.7.RELEASE]
        at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:199) [spring-security-web-3.2.7.RELEASE.jar:3.2.7.RELEASE]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) [spring-security-web-3.2.7.RELEASE.jar:3.2.7.RELEASE]
        at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:110) [spring-security-web-3.2.7.RELEASE.jar:3.2.7.RELEASE]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) [spring-security-web-3.2.7.RELEASE.jar:3.2.7.RELEASE]
        at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:50) [spring-security-web-3.2.7.RELEASE.jar:3.2.7.RELEASE]
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [org.apache.servicemix.bundles.spring-web-4.0.5.RELEASE_1.jar:?]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) [spring-security-web-3.2.7.RELEASE.jar:3.2.7.RELEASE]
        at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:87) [spring-security-web-3.2.7.RELEASE.jar:3.2.7.RELEASE]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) [spring-security-web-3.2.7.RELEASE.jar:3.2.7.RELEASE]
        at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:192) [spring-security-web-3.2.7.RELEASE.jar:3.2.7.RELEASE]
        at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:160) [spring-security-web-3.2.7.RELEASE.jar:3.2.7.RELEASE]
        at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:344) [org.apache.servicemix.bundles.spring-web-4.0.5.RELEASE_1.jar:?]
        at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:261) [org.apache.servicemix.bundles.spring-web-4.0.5.RELEASE_1.jar:?]
        at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1419) [jetty-servlet-8.1.10.v20130312.jar:8.1.10.v20130312]
        at org.springframework.orm.hibernate3.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:232) [org.apache.servicemix.bundles.spring-orm-4.0.5.RELEASE_1.jar:?]
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [org.apache.servicemix.bundles.spring-web-4.0.5.RELEASE_1.jar:?]
        at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1419) [jetty-servlet-8.1.10.v20130312.jar:8.1.10.v20130312]
        at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88) [org.apache.servicemix.bundles.spring-web-4.0.5.RELEASE_1.jar:?]
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [org.apache.servicemix.bundles.spring-web-4.0.5.RELEASE_1.jar:?]
        at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1419) [jetty-servlet-8.1.10.v20130312.jar:8.1.10.v20130312]
        at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:455) [jetty-servlet-8.1.10.v20130312.jar:8.1.10.v20130312]
        at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:137) [jetty-server-8.1.10.v20130312.jar:8.1.10.v20130312]
        at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:557) [jetty-security-8.1.10.v20130312.jar:8.1.10.v20130312]
        at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:231) [jetty-server-8.1.10.v20130312.jar:8.1.10.v20130312]
        at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1075) [jetty-server-8.1.10.v20130312.jar:8.1.10.v20130312]
        at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:384) [jetty-servlet-8.1.10.v20130312.jar:8.1.10.v20130312]
        at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:193) [jetty-server-8.1.10.v20130312.jar:8.1.10.v20130312]
        at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1009) [jetty-server-8.1.10.v20130312.jar:8.1.10.v20130312]
        at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:135) [jetty-server-8.1.10.v20130312.jar:8.1.10.v20130312]
        at org.eclipse.jetty.server.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:255) [jetty-server-8.1.10.v20130312.jar:8.1.10.v20130312]
        at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:154) [jetty-server-8.1.10.v20130312.jar:8.1.10.v20130312]
        at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:116) [jetty-server-8.1.10.v20130312.jar:8.1.10.v20130312]
        at org.eclipse.jetty.server.Server.handle(Server.java:364) [jetty-server-8.1.10.v20130312.jar:8.1.10.v20130312]
        at org.eclipse.jetty.server.AbstractHttpConnection.handleRequest(AbstractHttpConnection.java:489) [jetty-server-8.1.10.v20130312.jar:8.1.10.v20130312]
        at org.eclipse.jetty.server.AbstractHttpConnection.headerComplete(AbstractHttpConnection.java:942) [jetty-server-8.1.10.v20130312.jar:8.1.10.v20130312]
        at org.eclipse.jetty.server.AbstractHttpConnection$RequestHandler.headerComplete(AbstractHttpConnection.java:1004) [jetty-server-8.1.10.v20130312.jar:8.1.10.v20130312]
        at org.eclipse.jetty.http.HttpParser.parseNext(HttpParser.java:640) [jetty-http-8.1.10.v20130312.jar:8.1.10.v20130312]
        at org.eclipse.jetty.http.HttpParser.parseAvailable(HttpParser.java:235) [jetty-http-8.1.10.v20130312.jar:8.1.10.v20130312]
        at org.eclipse.jetty.server.AsyncHttpConnection.handle(AsyncHttpConnection.java:82) [jetty-server-8.1.10.v20130312.jar:8.1.10.v20130312]
        at org.eclipse.jetty.io.nio.SelectChannelEndPoint.handle(SelectChannelEndPoint.java:628) [jetty-io-8.1.10.v20130312.jar:8.1.10.v20130312]
        at org.eclipse.jetty.io.nio.SelectChannelEndPoint$1.run(SelectChannelEndPoint.java:52) [jetty-io-8.1.10.v20130312.jar:8.1.10.v20130312]
        at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:608) [jetty-util-8.1.10.v20130312.jar:8.1.10.v20130312]
        at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:543) [jetty-util-8.1.10.v20130312.jar:8.1.10.v20130312]
        at java.lang.Thread.run(Thread.java:745) [?:1.8.0_40]
  Servlet - dispatcher
  URL - /opennms/dashboard.jsp

Environment

demo

Acceptance / Success Criteria

None

Lucidchart Diagrams

Activity

Show:

Alejandro Galue February 25, 2016 at 3:39 PM

https://github.com/OpenNMS/opennms/pull/618

Alejandro Galue February 25, 2016 at 3:33 PM

Here are the correct SQL queries:

Query 1

select service.servicename, count(*) as Count from outages, service, ifservices where service.serviceid = ifservices.serviceid and outages.ifserviceid = ifservices.id and outages.ifregainedservice is NULL and outages.iflostservice > now() - interval '7 days' group by service.servicename order by Count desc;

Query 2

select service.servicename, count(*) as Count from outages, service, ifservices where service.serviceid = ifservices.serviceid and outages.ifserviceid = ifservices.id and outages.ifregainedservice is NOT NULL and outages.iflostservice > now() - interval '7 days' group by service.servicename order by Count desc;

Alejandro Galue February 25, 2016 at 3:24 PM
Edited

Fortunately, it seems like a configuration issue. I found the following on chart-configuration.xml:

<tns:series-def number="1" series-name="Current" use-labels="true" >
    <tns:jdbc-data-set db-name="opennms" sql="select service.servicename, count(*) as Count from outages, service where outages.serviceid=service.serviceid and ifregainedservice is NULL and iflostservice > now() - interval '7 days' group by service.servicename order by Count desc;" />
...
<tns:series-def number="2" series-name="Resolved" use-labels="true" >
    <tns:jdbc-data-set db-name="opennms" sql="select service.servicename, count(*) as Count from outages, service where outages.serviceid=service.serviceid and ifregainedservice is NOT NULL and iflostservice > now() - interval '7 days' group by service.servicename order by Count desc;" />

The above query is not correct.

Alejandro Galue February 25, 2016 at 3:21 PM

The outages table was modified for 17, as part of the refactoring work for completely remove Capsd.

Here is the current state:

opennms=# \d outages
                  Table "public.outages"
       Column       |           Type           | Modifiers 
--------------------+--------------------------+-----------
 outageid           | integer                  | not null
 svclosteventid     | integer                  | 
 svcregainedeventid | integer                  | 
 iflostservice      | timestamp with time zone | not null
 ifregainedservice  | timestamp with time zone | 
 suppresstime       | timestamp with time zone | 
 suppressedby       | character varying(256)   | 
 ifserviceid        | integer                  | not null
Indexes:
    "pk_outageid" PRIMARY KEY, btree (outageid)
    "one_outstanding_outage_per_service_idx" UNIQUE, btree (ifserviceid) WHERE ifregainedservice IS NULL
    "outages_ifserviceid_idx" btree (ifserviceid)
    "outages_regainedservice_idx" btree (ifregainedservice)
    "outages_svclostid_idx" btree (svclosteventid)
    "outages_svcregainedid_idx" btree (svcregainedeventid)
Foreign-key constraints:
    "fk_eventid1" FOREIGN KEY (svclosteventid) REFERENCES events(eventid) ON DELETE CASCADE
    "fk_eventid2" FOREIGN KEY (svcregainedeventid) REFERENCES events(eventid) ON DELETE CASCADE
    "ifservices_fkey2" FOREIGN KEY (ifserviceid) REFERENCES ifservices(id) ON DELETE CASCADE

So, the exception is correct, there is no column called serviceid. It has to be the ID of a monitored service (i.e. an entry on the ifservices table)

Fixed

Details
Assignee
Alejandro Galue
Reporter
David Hustace
Components
Architecture
Database
Web UI - Dashboard
Fix versions
17.1.1
Meridian-2016.1.0
Affects versions
17.0.0
Priority
Blocker

PagerDuty

Created October 20, 2015 at 4:17 AM

Updated February 26, 2016 at 1:50 AM

Resolved February 25, 2016 at 8:51 PM

Chart Servlet Outages model exception

Description

Environment

Acceptance / Success Criteria

Lucidchart Diagrams

Activity

Alejandro Galue February 25, 2016 at 3:39 PM

Alejandro Galue February 25, 2016 at 3:33 PM

Alejandro Galue February 25, 2016 at 3:24 PMEdited

Alejandro Galue February 25, 2016 at 3:21 PM

DetailsAssigneeAlejandro GalueAlejandro GalueReporterDavid HustaceDavid HustaceComponentsArchitectureDatabaseWeb UI - DashboardFix versions17.1.1Meridian-2016.1.0Affects versions17.0.0PriorityBlocker

Details

Assignee

Reporter

Components

Fix versions

Affects versions

Priority

PagerDutyPagerDuty Incident

PagerDuty

Flag notifications

Something's gone wrong

Something's gone wrong

Alejandro Galue February 25, 2016 at 3:24 PM
Edited

Details
Assignee
Alejandro Galue
Reporter
David Hustace
Components
Architecture
Database
Web UI - Dashboard
Fix versions
17.1.1
Meridian-2016.1.0
Affects versions
17.0.0
Priority
Blocker

PagerDuty