21.0 Analytics_backend.log errors Failed to add data for AE/laslm

Products

• CA Automic Workload Automation - Automation Engine
• CA Automic One Automation

Issue/Introduction

numerous SQL error in the logs, like the following:

2022-10-03 11:41:57.972 ERROR 7596 --- [CollectorThread-22] c.a.a.b.d.ae.collector.LaslmSqlAdapter   : Failed to add data for AE/laslm

com.automic.analytics.backend.data.common.channel.ChannelServiceException: Failed to add data into 'laslm' table in AE channel.
 at com.automic.analytics.backend.data.ae.channel.AeChannelService.addData(AeChannelService.java:61)
 at com.automic.analytics.backend.data.common.collector.ChannelAwareSqlAdapter.collectWithin(ChannelAwareSqlAdapter.java:49)
 at com.automic.analytics.backend.data.common.collector.DefaultSqlAdapter.collect(DefaultSqlAdapter.java:35)
 at com.automic.analytics.backend.data.common.collector.IntervalBasedCollector.collect(IntervalBasedCollector.java:65)
 at com.automic.analytics.backend.data.common.collector.ScheduledCollector.lambda$run$2(ScheduledCollector.java:134)
 at net.jodah.failsafe.Functions$10.call(Functions.java:252)
 at net.jodah.failsafe.SyncFailsafe.call(SyncFailsafe.java:145)
 at net.jodah.failsafe.SyncFailsafe.run(SyncFailsafe.java:81)
 at com.automic.analytics.backend.data.common.collector.ScheduledCollector.run(ScheduledCollector.java:132)
 at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54)
 at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
 at java.base/java.util.concurrent.FutureTask.runAndReset(Unknown Source)
 at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source)
 at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
 at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
 at java.base/java.lang.Thread.run(Unknown Source)
Caused by: org.jooq.exception.DataAccessException: SQL [insert into "public"."laslm_cleaned" ("sequence_number", "laslm_client", "laslm_ah_idnr", "laslm_sloname", "laslm_servname", "laslm_servotype", "laslm_servrunid", "laslm_servfstatus", "laslm_servmsgnr", "laslm_servmsgins", "laslm_servoh_idnr", "laslm_detecttime", "laslm_idnr", "laslm_date", "laslm_oh_idnr", "laslm_lastupdate") select "public"."laslm"."sequence_number", "public"."laslm"."laslm_client", "public"."laslm"."laslm_ah_idnr", "public"."laslm"."laslm_sloname", "public"."laslm"."laslm_servname", "public"."laslm"."laslm_servotype", "public"."laslm"."laslm_servrunid", "public"."laslm"."laslm_servfstatus", "public"."laslm"."laslm_servmsgnr", "public"."laslm"."laslm_servmsgins", "public"."laslm"."laslm_servoh_idnr", "public"."laslm"."laslm_detecttime", "public"."laslm"."laslm_idnr", "public"."laslm"."laslm_date", "public"."laslm"."laslm_oh_idnr", "public"."laslm"."laslm_lastupdate" from "public"."laslm" join (select "inner_laslm"."laslm_idnr", max("inner_laslm"."sequence_number") as "max_sequence_number" from "public"."laslm" as "inner_laslm" where "inner_laslm"."laslm_detecttime" >= cast(? as timestamp with time zone) group by "inner_laslm"."laslm_idnr") as "alias_114497905" on ("public"."laslm"."laslm_idnr" = "alias_114497905"."laslm_idnr" and "public"."laslm"."sequence_number" = "alias_114497905"."max_sequence_number") left outer join "public"."laslm_cleaned" on "public"."laslm"."laslm_idnr" = "public"."laslm_cleaned"."laslm_idnr" where "public"."laslm_cleaned"."laslm_idnr" is null]; ERROR: duplicate key value violates unique constraint "laslm_cleaned_pkey"
  Detail: Key (laslm_idnr)=(9968823) already exists.
 at org.jooq_3.11.11.POSTGRES.debug(Unknown Source)
 at org.jooq.impl.Tools.translate(Tools.java:2430)
 at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:832)
 at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:364)
 at org.jooq.impl.AbstractDelegatingQuery.execute(AbstractDelegatingQuery.java:127)
 at com.automic.analytics.backend.data.common.channel.transaction.CleanDataTransaction.run(CleanDataTransaction.java:74)
 at org.jooq.impl.DefaultDSLContext$3.run(DefaultDSLContext.java:594)
 at org.jooq.impl.DefaultDSLContext$3.run(DefaultDSLContext.java:591)
 at org.jooq.impl.DefaultDSLContext.lambda$transactionResult0$0(DefaultDSLContext.java:522)
 at org.jooq.impl.Tools$10$1.block(Tools.java:4509)
 at java.base/java.util.concurrent.ForkJoinPool.managedBlock(Unknown Source)
 at org.jooq.impl.Tools$10.get(Tools.java:4506)
 at org.jooq.impl.DefaultDSLContext.transactionResult0(DefaultDSLContext.java:574)
 at org.jooq.impl.DefaultDSLContext.transactionResult(DefaultDSLContext.java:491)
 at org.jooq.impl.DefaultDSLContext.transaction(DefaultDSLContext.java:591)
 at com.automic.analytics.backend.data.ae.channel.AeChannelService.cleanDataWithContext(AeChannelService.java:135)
 at com.automic.analytics.backend.data.ae.channel.AeChannelService.lambda$addData$0(AeChannelService.java:57)
 at org.jooq.impl.DefaultDSLContext$3.run(DefaultDSLContext.java:594)
 at org.jooq.impl.DefaultDSLContext$3.run(DefaultDSLContext.java:591)
 at org.jooq.impl.DefaultDSLContext.lambda$transactionResult0$0(DefaultDSLContext.java:522)
 at org.jooq.impl.Tools$10$1.block(Tools.java:4509)
 at java.base/java.util.concurrent.ForkJoinPool.managedBlock(Unknown Source)
 at org.jooq.impl.Tools$10.get(Tools.java:4506)
 at org.jooq.impl.DefaultDSLContext.transactionResult0(DefaultDSLContext.java:574)
 at org.jooq.impl.DefaultDSLContext.transactionResult(DefaultDSLContext.java:491)
 at org.jooq.impl.DefaultDSLContext.transaction(DefaultDSLContext.java:591)
 at com.automic.analytics.backend.data.ae.channel.AeChannelService.addData(AeChannelService.java:53)
 ... 15 common frames omitted
Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "laslm_cleaned_pkey"
  Detail: Key (laslm_idnr)=(9968823) already exists.
 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2552)
 at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2284)
 at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:322)
 at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481)
 at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401)
 at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164)
 at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:153)
 at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
 at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
 at org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:209)
 at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:432)
 at org.jooq.impl.AbstractDMLQuery.execute(AbstractDMLQuery.java:613)
 at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:350)
 ... 38 common frames omitted

Environment

Release : 21.0.3

Cause

Product defect with Analytics component

Resolution

There was a bug in the Analytics component where there are similar errors when pointing to the "public" schema.  This was fixed with 21.0.4 for Analytics. 

Resolution:
Upgrade the Analytics component to at least 21.0.4.  It's best to update all Analytics components (backend, datastore, plugins); the AutomationEngine does not need to be updated.