USM is not loading after upgrading to UMP 8.51

Document ID : KB000007152
Last Modified Date : 14/02/2018
Show Technical Document Details
Issue:

After upgrading to UMP 8.51, the UMP USM portlet hangs in a loading state for very long periods of time the first time a user logs in.  This can take from 20 minutes to an hour for the portlet to load.  A query similar to the following is seen running on the MySQL database server:

SELECT dev.cs_id,
meci.master_id,
cim.ci_metric_id,
dev.dev_id
FROM CM_DEVICE dev
JOIN
CM_CONFIGURATION_ITEM ci ON ci.dev_id = dev.dev_id
JOIN
CM_CONFIGURATION_ITEM_METRIC cim ON cim.ci_id = ci.ci_id
LEFT JOIN
CM_CONFIG_ITEM_TO_MASTER meci ON meci.ci_id = ci.ci_id
WHERE 1=1
and (dev.dev_id IN ( 'D043BA3B5CB3B253453AA98BD717FF42A','D5323bd4bb84c2502a8ff28336cdb2710'.,<.....>);

This occurs when there are groups defined in USM with a large number of devices.  The list of devices contained in the IN () group in the above query is very large (500+ in this instance).

Environment:
UIM/UMP 8.51 upgrade with MySQL 5.6 database manager hosting the UIM database
Cause:

A non-unique index for the column name ‘ci_id’ for the table ‘CM_CONFIG_ITEM_TO_MASTER’ table was missing after the upgrade.

Resolution:

Add the missing index to the 'CM_CONFIGURE_ITEM_TO_MASTER' table:

create index cm_config_item_to_master_idx01 on cm_config_item_to_master(ci_id);

Additional Information:

There is no need to restart the UMP wasp probe after making the change to the table in the MySQL database.