#1986 [HHQ-2245] EAM_ESCALATION_STATE table needs its unique index

[Hyperic Team]

http://jira.hyperic.com/browse/HHQ-2245
2008-06-29 04:00:06,071 WARN [JMS SessionPool Worker-14716] [org.hyperic.hq.escalation.server.session.EscalationManagerEJBImpl] There is more than one escalation in progress for alert def id=20786; type=-559038737


org.hibernate.NonUniqueResultException: query did not return a unique result: 8


        at org.hibernate.impl.AbstractQueryImpl.uniqueElement(AbstractQueryImpl.java:820)


        at org.hibernate.impl.CriteriaImpl.uniqueResult(CriteriaImpl.java:305)


        at org.hyperic.hq.escalation.server.session.EscalationStateDAO.find(EscalationStateDAO.java:68)


        at org.hyperic.hq.escalation.server.session.EscalationManagerEJBImpl.escalationStateExists(EscalationManagerEJBImpl.java:357)


        at org.hyperic.hq.escalation.server.session.EscalationManagerEJBImpl.startEscalation(EscalationManagerEJBImpl.java:318)


        at sun.reflect.GeneratedMethodAccessor1040.invoke(Unknown Source)


        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)


        at java.lang.reflect.Method.invoke(Method.java:597)


        at org.jboss.invocation.Invocation.performCall(Invocation.java:345)


        at org.jboss.ejb.StatelessSessionContainer$ContainerInterceptor.invoke(StatelessSessionContainer.java:214)

From a deployed HQ instance this stack trace shows that multiple EscalationStates may be created at once due to the transactional nature of the HQ alerting system.

To alleviate we need to create a unique index on EAM_ESALATION_STATE which will not allow this scenario to occur.