Analytics does not start with ERROR: relation "laslm_laslm_detecttime_idx" already exists
search cancel

Analytics does not start with ERROR: relation "laslm_laslm_detecttime_idx" already exists

book

Article ID: 273571

calendar_today

Updated On:

Products

CA Automic Workload Automation - Automation Engine

Issue/Introduction

Due to unknown reasons, the Analytics backend won't start anymore with the following error in analytics_backend.log:

2023-09-07 11:10:13.490  INFO 1266341 --- [main] o.f.core.internal.command.DbMigrate      : Migrating schema "public" to version 2 - add laslm detecttime indices
2023-09-07 11:10:13.501 ERROR 1266341 --- [main] o.f.core.internal.command.DbMigrate      : Migration of schema "public" to version 2 - add laslm detecttime indices failed! Changes successfully rolled back.
2023-09-07 11:10:13.502  WARN 1266341 --- [main] ConfigServletWebServerApplicationContext : Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'flywayInitializer' defined in class path resource [org/springframework/boot/autoconfigure/flyway/FlywayAutoConfiguration$FlywayConfiguration.class]: Invocation of init method failed; nested exception is org.flywaydb.core.internal.command.DbMigrate$FlywayMigrateSqlException: 
Migration V2__add_laslm_detecttime_indices.sql failed
-----------------------------------------------------
SQL State  : 42P07
Error Code : 0
Message    : ERROR: relation "laslm_laslm_detecttime_idx" already exists
Location   : db/migration/V2__add_laslm_detecttime_indices.sql (/softwares/automic/analytics/backend/file:/softwares/automic/analytics/backend/analytics-backend.jar!/BOOT-INF/classes!/db/migration/V2__add_laslm_detecttime_indices.sql)
Line       : 1
Statement  : CREATE INDEX laslm_laslm_detecttime_idx
ON laslm
USING BTREE
(laslm_detecttime)

If we check the postgresql database, we observe that indeed this index laslm_laslm_detecttime_idx already exists, so it's normal that this procedure fails.

How can we fix this Analytics installation?

 

Environment

Release : 12.3 and 21.x

Component: Automation Engine

Sub-Component: Analytics Backend

Cause

Unknown

Resolution

There are two options to fix this issue, please choose the one that you prefer:

a) Recommended and long-term solution in case you don't want to run into similar problem in the future while upgrading to a newer version of Analytics:

Migrate data from flyway_schema_history table to schema_version table.

Please take a backup of the database first and then this can be accomplished in two ways:

  1. Export/import data from flyway_schema_history to schema_version
  2. The following procedure:
    drop table public.schema_version;
    alter table public.flyway_schema_history rename to public.schema_version;

b) Quick and short-term solution (specially if not changing version anytime soon).

The schema migration needs to be disabled to workaround the issue, for that please edit the file application.properties and add at the end:
spring.flyway.enabled=false

Then start Analytics again, this time it should start correctly.

Additional Information

Please open a case with Technical Support in case of any doubt about the procedures described in this article.