After upgrading to 9.5, we are unable to launch CEM console and NonUniqueResultException errors in MOM log file

Products

• CA Application Performance Management Agent (APM / Wily / Introscope)
• INTROSCOPE

Issue/Introduction

Description:

After upgrading APM to 9.5.x from 9 with an Oracle APM database, on rare occasions, the following errors appear in the MOM Enterprise Manager log file:

8/12/14 04:47:43.398 PM EEST [ERROR] [InitializationPool.Thread1] [Manager.com.timestock.tess.util.DbUtils] Select failed for com.timestock.tess.data.objects.Setting

org.hibernate.NonUniqueResultException: query did not return a unique result:2
               at org.hibernate.impl.AbstractQueryImpl.uniqueElement(AbstractQueryImpl.java:813)
               at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:804)
               at com.timestock.tess.util.DbUtils.getObjectWithRetries(DbUtils.java:4533)
               at com.timestock.tess.util.DbUtils.getObjectWithRetries(DbUtils.java:4486)
               at com.timestock.tess.util.DbUtils.getSettingValue(DbUtils.java:8969)
               at com.timestock.tess.services.processors.BizEventProcessor.initialize(BizEventProcessor.java:118) 
               at com.timestock.tess.services.service.ServiceBase.initialize(ServiceBase.java:62)
               at com.timestock.tess.services.service.ServicesManager.initialize(ServicesManager.java:123)
               at com.timestock.tess.services.initialization.RunInitializers$IntializeWrapper.call(RunInitializers.java:87)
               at com.timestock.tess.services.initialization.RunInitializers$IntializeWrapper.call(RunInitializers.java:1)
               at com.timestock.tess.util.ThreadPool$ConcurrentCallableTask.call(ThreadPool.java:218)
               at java.util.concurrent.FutureTask$Sync.innerRun(Unknown Source)
               at java.util.concurrent.FutureTask.run(Unknown Source)
               at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
               at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
               at java.lang.Thread.run(Unknown Source)

If the environment uses APM CE (CEM) then it may be also not possible to launch the CEM console.

This problem has been reported when upgrading from 9.1.2 to 9.5.2 and from 9.1.2 to 9.5.3. It has not been possible to recreate the problem in CA testing environments

Solution:

This problem is due to duplicate records being created in the ts_settings table during the upgrade.

To check if this situation applies, run the following SQL query:

select * from ts_settings order by ts_id;

Review the results and check to see if the Servicedesk entries are duplicated as shown below in a result snippet:

TS_ID VERSION_INFO TS_KEY TS_VALUE TS_DESCRIPTION TS_DEFAULT TS_SOFT_DELETE
.......
"1,033" 1 servicedesk.usd_user servicedesk username 0
"1,034" 1 servicedesk.usd_host servicedesk host 0
"1,035" 1 servicedesk.usd_port servicedesk port 8080 0
"1,036" 1 servicedesk.usd_password servicedesk password 0
"1,037" 1 servicedesk.enableusdplugin false enable service desk plugin false 0
"1,038" 1 servicedesk.issuecategory category of issue 0
"1,039" 1 servicedesk.cem_host cem host 0
"1,040" 1 servicedesk.cem_port cem port 8081 0
"1,041" 1 servicedesk.issuefilter issue filter */* 0
"1,042" 1 servicedesk.issuedescription issue description 0
"1,043" 1 servicedesk.issuesummary issue summary 0
"1,044" 1 servicedesk.enablelow true enable low severity true 0
"1,045" 1 servicedesk.enablemoderate true enable moderate severity true 0
"1,046" 1 servicedesk.enablesevere true enable severe severity true 0
"1,047" 1 servicedesk.enablecritical true enable critical severity true 0
"1,048" 1 servicedesk.incidentlink http://[HOST]:[PORT]/wily/cem/tess/app/biz/bizEventDetail.html?pId=[INCIDENT_ID] direct incident link 0
"1,049" 1 servicedesk.use_itil true use ITIL true 0
"1,053" 1 servicedesk.usd_user servicedesk username 0
"1,054" 1 servicedesk.usd_host servicedesk host 0
"1,055" 1 servicedesk.usd_port servicedesk port 8080 0
"1,056" 1 servicedesk.usd_password servicedesk password 0
"1,057" 1 servicedesk.enableusdplugin false enable service desk plugin false 0
"1,058" 1 servicedesk.issuecategory category of issue 0
"1,059" 1 servicedesk.cem_host cem host 0
"1,060" 1 servicedesk.cem_port cem port 8081 0
"1,061" 1 servicedesk.issuefilter issue filter */* 0
"1,062" 1 servicedesk.issuedescription issue description 0
"1,063" 1 servicedesk.issuesummary issue summary 0
"1,064" 1 servicedesk.enablelow true enable low severity true 0
"1,065" 1 servicedesk.enablemoderate true enable moderate severity true 0
"1,066" 1 servicedesk.enablesevere true enable severe severity true 0
"1,067" 1 servicedesk.enablecritical true enable critical severity true 0
"1,068" 1 servicedesk.incidentlink http://[HOST]:[PORT]/wily/cem/tess/app/biz/bizEventDetail.html?pId=[INCIDENT_ID] direct incident link 0
"1,069" 1 servicedesk.use_itil true use ITIL true 0

In the above example, the data for TS_ID values 1033 through 1049 is duplicated in TS_ID values 1053 through 1069.

The actual values for TS_ID columns storing the Servicedesk info varies for each environment.

To correct the problem, the duplicated rows need to be removed. In the above example, running the following SQ does this:

delete from ts_settings where ts_id >= 1053 and ts_id <= 1069;

You will need to substitute the correct TS_ID values for your environment.

The above query should remove 17 rows. Re-run the original Select statement to verify that the duplication problem has been rectified and if so then commit the change.

NOTE: As this process involves manually modifying the APM database , make an adequate backup prior to making this change. CA Technologies cannot be held responsible for loss of data when no reliable backup exists.

If you have any concerns over the procedure, contact CA Support.

Environment

Release:
Component: APMISP