Enterprise Dashboard error: failed migration to version 19 !

Products

• Service Virtualization

Issue/Introduction

Started the Enterprise Dashboard but got below errors:
 
1.

Caused by: org.flywaydb.core.api.FlywayException: Schema "EDDB101" contains a failed migration to version 19 !
    at org.flywaydb.core.internal.command.DbMigrate$2.doInTransaction

==========================================================================================

2.

2024-12-20T04:19:09,379Z (22:19) [main] INFO  com.ca.dradis.db.FlywayDbMigration - Pending Schema Version: 19

2024-12-20T04:19:09,696Z (22:19) [main] ERROR org.flywaydb.core.internal.command.DbMigrate - Migration of schema "DASHBDADMIN101" to version 19 failed! Please restore backups and roll back database and code!

2024-12-20T04:19:09,712Z (22:19) [main] WARN  org.springframework.boot.web.servlet.context.AnnotationConfigServletWebServerApplicationContext - Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dradisInitBean': Invocation of init method failed; nested exception is org.flywaydb.core.internal.dbsupport.FlywaySqlScriptException: 

Migration V19__Update_DDL.sql failed

------------------------------------

SQL State  : 23000

Error Code : 2299

Message    : ORA-02299: cannot validate (DASHBDADMIN101.UNIQUE_COMPOSITE_KEY) - duplicate keys found

 

Location   : com/ca/dradis/db/migration/oracle/scripts/V19__Update_DDL.sql (/data/home/dtsadm/dts108/file:/data/home/dtsadm/dts108/lib/dradis/dradis-10.8.0.jar!/com/ca/dradis/db/migration/oracle/scripts/V19__Update_DDL.sql)

Line       : 50

Statement  : ALTER TABLE telemetry_metric

ADD CONSTRAINT unique_composite_key UNIQUE (sku_id,product_version,instance_id, collection_date)

2024-12-20T04:19:09,767Z (22:19) [main] ERROR org.springframework.boot.SpringApplication - Application run failed

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dradisInitBean': Invocation of init method failed; nested exception is org.flywaydb.core.internal.dbsupport.FlywaySqlScriptException: 

Migration V19__Update_DDL.sql failed

------------------------------------

SQL State  : 23000

Error Code : 2299

Message    : ORA-02299: cannot validate (DASHBDADMIN101.UNIQUE_COMPOSITE_KEY) - duplicate keys found

Location   : com/ca/dradis/db/migration/oracle/scripts/V19__Update_DDL.sql (/data/home/dtsadm/dts108/file:/data/home/dtsadm/dts108/lib/dradis/dradis-10.8.0.jar!/com/ca/dradis/db/migration/oracle/scripts/V19__Update_DDL.sql)

Line       : 50

Statement  : ALTER TABLE telemetry_metric

ADD CONSTRAINT unique_composite_key UNIQUE (sku_id,product_version,instance_id, collection_date)

Environment

DevTest Release : 10.8

Oracle 19c on Linux

Cause

 Issue no 1 was caused due to "schema_version" table in the EnterpriseDashboard tables was created with an all lower-case name; and this is due to a restriction placed by the Flyway Database.

Issue no 2 was caused due to failed execution of update sql file due to duplicate entries in the telemetry_metric table. 

 

Resolution

For issue no 1, get the DBA involved and manually change the column "success" value on the "schema_version" table from 0 to 1.

1. If run into issue no 2 again, find the duplicate records first:
   select count(collection_date) as cnt ,collection_date, instance_id , product_version from TELEMETRY_METRIC group by collection_date, instance_id , product_version order by cnt desc
2. Each date should have 5 count. If its more than 5, then find and delete the duplicate records.
   SELECT * FROM TELEMETRY_METRIC where collection_date = '<date with more than 5 records>'
   DELETE FROM TELEMETRY_METRIC where TELEMETRY_UUID in ('<1st id>','<2nd id>')

3. Get V19__Update_DDL.sql file  by extracting migration.zip and go to migration/<db name>/scripts/V19__Update_DDL.sql 

   After cleaning, we have to run the V19__Update_DDL of respective DB  starting from the below command to last command:
   ALTER TABLE telemetry_metric ADD CONSTRAINT unique_composite_key UNIQUE (sku_id,product_version,instance_id, collection_date);