em.log floods with : "SQLState 23505" and "duplicate key value violates unique constraint ts_defects_reaggr_int_intidx"
Article ID: 94168
CA Application Performance Management Agent (APM / Wily / Introscope)INTROSCOPE
There is huge amount of SQL errors in the em.log:
WARNING: SQL Error: 0, SQLState: 23505 Feb 28, 2018 8:51:44 AM org.hibernate.util.JDBCExceptionReporter logExceptions SEVERE: ERROR: duplicate key value violates unique constraint "ts_defects_reaggr_int_intidx" Detail: Key (ts_interval)=(...) already exists. ...
It is repeating in em.log and the file keeps growing 10 Gb per day. What is the meaning and how to prevent it?
The APM database is overloaded by a huge amount of defects being processed.
Here is an example in the EM log: [com.timestock.tess.services.scheduler.DefectAggregationTask] Aggregating 287743 defects in interval from 18-4-2017 11:00 to 18-4-2017 11:59
Because of the high number of defects being processed the defect reaggregation is also lagging.
"ts_defects_reaggr_intervals" table stores the defects that came in during aggregation of the interval the defect belongs to.
These SEVERE messages keep coming because DefectAggregationTask is a scheduler and keeps on running
Release: CEMUGD00200-10.5-Introscope to CA Application-Performance Management-Upgrade Mai Component:
1. Go to the CEM console: -Reduce the number of defects defined, check if too many defects have been defined -Reevaluate the defect threshold requirements, check if the defects thresholds are too low -Disable some transactions
2. Remove the "console" output in all the <EM_HOME>/config/IntroscopeEnterpriseManager.properties: From: log4j.logger.Manager=INFO, console, logfile To: log4j.logger.Manager=INFO, logfile
Another option can be to customize the EM_HOME/bin/EMCtrl.sh to redirect the standard out to '/dev/null' However, this will disable the em.log
If the problem persists, gather the below diagnostic files and contact CA Support.
1. postgres logs availables from POSTGRES_HOME\data\pg_log\*
2. output of the below queries: select * from ts_defects_reaggr_intervals order by ts_interval select tablename from pg_tables where tablename like 'ts_us_sessions_map_20%' order by tablename;
3. Enable EM DEBUG logging in the TIM Collector EM for a few minutes only (10 minutes) Open the <EM_HOME>/config/IntroscopeEnterpriseManager.properties Set log4j.logger.Manager=DEBUG, logfile This is a hot property there is no need to restart the EM.