DXIM upgrade from 20.3.x to 20.4.0 failed at 17% "sqlserver_ocmetricnamedviewer"

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)
• CA Unified Infrastructure Management On-Premise (Nimsoft / UIM)
• CA Unified Infrastructure Management SaaS (Nimsoft / UIM)

Issue/Introduction

DX UIM application upgrade failed and uimserver_ia_install.log shows the below error:

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.ocMetricNamedView' and the index name 'UNQ_VIEW_ACCOUNT_IDX1'. The duplicate key value is (xxx, -1, administrator, 79718, <NULL>, Private).

 

Failed at 17%

The install log might show the following errors: 

023-09-15 02:44:47,142 ERROR impl.UIMServerDBSchemaController:run:173 [Thread-45]  - NimException caught
(503) , com.microsoft.sqlserver.jdbc.SQLServerException: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.ocMetricNamedView' and the index name 'UNQ_VIEW_ACCOUNT_IDX1'. The duplicate key value is (Metric View 1 - nameoftheview, -1, administrator, 6772, <NULL>, Private).: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.ocMetricNamedView' and the index name 'UNQ_VIEW_ACCOUNT_IDX1'. The duplicate key value is (Metric View 1 - nameoftheview.nameoftheview.net, -1, administrator, 6772, <NULL>, Private).
    at com.nimsoft.nimdbprovider.base.util.NimDBScriptRunner.runDBScript(NimDBScriptRunner.java:78)
    at com.nimsoft.nimdbprovider.base.provider.NimDBProviderBase.runDBScript(NimDBProviderBase.java:260)
    at com.nimsoft.nimdbprovider.base.provider.NimDBProviderBase.runDBScriptFile(NimDBProviderBase.java:193)
    at com.nimsoft.install.uimserver.action.impl.UIMServerDBSchemaController.runScriptCommon(UIMServerDBSchemaController.java:427)
    at com.nimsoft.install.uimserver.action.impl.UIMServerDBSchemaController.configureNamedViewCreate(UIMServerDBSchemaController.java:379)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at com.nimsoft.install.nimcommon.method.NimMethodCallback.invokeMethod(NimMethodCallback.java:532)
    at com.nimsoft.install.nimcommon.method.NimMethodCallback.invokeMethod(NimMethodCallback.java:516)
    at com.nimsoft.install.uimserver.action.impl.UIMServerDBSchemaController.doConfigure_dbScript(UIMServerDBSchemaController.java:273)
    at com.nimsoft.install.uimserver.action.impl.UIMServerDBSchemaController.doConfigure(UIMServerDBSchemaController.java:184)
    at com.nimsoft.install.uimserver.action.impl.UIMServerDBSchemaController.run(UIMServerDBSchemaController.java:171)
    at java.lang.Thread.run(Thread.java:748)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.ocMetricNamedView' and the index name 'UNQ_VIEW_ACCOUNT_IDX1'. The duplicate key value is (Metric View 1 - nameoftheview.nameoftheview.net, -1, administrator, 6772, <NULL>, Private).
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:258)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1535)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:845)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:752)
    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7151)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2478)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:219)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:199)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.execute(SQLServerStatement.java:729)
    at com.nimsoft.nimdbprovider.base.util.NimDBScriptRunner.executeSQLCommand(NimDBScriptRunner.java:105)
    at com.nimsoft.nimdbprovider.base.util.NimDBScriptRunner.runDBScript(NimDBScriptRunner.java:60)
    ... 14 more

 

Environment

• Release: DX UIM 20.3.3
• Upgrade from UIM version 20.3.3 to 20.4, or 20.3.0 to 20.4

Cause

• DB related issue when upgrading in some scenarios

Resolution

Access the database and run the SQL queries (compiled in the attached .sql files) in the UIM DB from the preferred DB Studio or client utility, e.g., SQL Server Studio, or from the SQL Query Editor in OC SLM but keep in mind the database client tool is preferred and displays all errors if and when they occur.

1. OC_JUL2021_SQLSERVER.sql (for MS SQL Server)
2. OC_JUL2021_ORACLE.sql (for Oracle)
3. OC_JUL2021_MYSQL.sql (for MySQL)

Additional Information

When adding the constraint with:

ALTER TABLE OCMETRICNAMEDVIEW ADD CONSTRAINT UNQ_VIEW_ACCOUNT_IDX1 UNIQUE (VIEW_NAME, ACCOUNT_ID, CREATEDBY, CS_ID, GROUP_ID, VIEW_TYPE);

The following message might be displayed:

Msg 1505, Level 16, State 1, Line 10
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.ocMetricNamedView' and the index name 'UNQ_VIEW_ACCOUNT_IDX1'. The duplicate key value is (Metric View 1 - nameoftheview.namveovethrevie.net, -1, administrator, 6772, <NULL>, Private).
Msg 1750, Level 16, State 1, Line 10
Could not create constraint or index. See previous errors.
The statement has been terminated.

If this is the case, duplicate rows in the table must be deleted manually. If you need assistance, please open a support case to help with deleting the rows manually.