MCS (mon_config_service) monitoring stopped working after upgrading UIM from 8.5.1 to version 9.0.2

Document ID : KB000125623
Last Modified Date : 06/02/2019
Show Technical Document Details
Issue:
The MCS stopped working when we upgraded UIM to version 9.0.2. Customer tried a reset the MCS monitoring using this procedure->https://comm.support.ca.com/kb/how-to-reset-mcs-monitoring/kb000012764 but it did not resolve the issue.

Upon startup of the mon_config_service probe v9.02 in the log we saw:

Jan 25 16:57:23:972 [main, mon_config_service] SelfServiceProbe.runProbe:153:     -----STARTING -----
Jan 25
16:57:23:975 [main, mon_config_service] SelfServiceProbe.runProbe:155:    Build properties:
    Archiver-Version: Plexus
Archiver
    Build-Jdk: 1.8.0_45
    Build-Time: 2018-10-23 00:48:13.584
    Built-By: teamcity
    Created-By: Apache Maven
3.3.9
    Implementation-Title: mcs
    Implementation-Vendor: CA Unified Infrastructure Management
    Implementation-
Vendor-Id: com.ca.uim.mcs
    Implementation-Version: 9.0.2
    Manifest-Version: 1.0
    SCM-Revision: 5027
Jan 25
16:57:25:609 [main, mon_config_service] org.flywaydb.core.api.FlywayException: Schema `ca_uim` contains a failed
migration to version 8.6.0.06 !
    at org.flywaydb.core.internal.command.DbMigrate$2.doInTransaction
(DbMigrate.java:218)
    at org.flywaydb.core.internal.command.DbMigrate$2.doInTransaction(DbMigrate.java:173)
    at
org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:72)
    at
org.flywaydb.core.internal.command.DbMigrate.migrate(DbMigrate.java:173)
    at org.flywaydb.core.Flyway
$1.execute(Flyway.java:959)
    at org.flywaydb.core.Flyway$1.execute(Flyway.java:917)
    at
org.flywaydb.core.Flyway.execute(Flyway.java:1373)
    at org.flywaydb.core.Flyway.migrate(Flyway.java:917)
    at
com.ca.uim.mcs.db.SchemaMigrator.update(SchemaMigrator.java:89)
    at
com.nimsoft.selfservice.probe.SelfServiceProbe.runProbe(SelfServiceProbe.java:164)
    at
com.nimsoft.selfservice.probe.SelfServiceProbe.main(SelfServiceProbe.java:103)
Environment:
- UIM v9.02
- mon_config_service 9.02
- Database: MySQL v5.7
Cause:
***This was due to the failure of the drop script to drop some mcs tables. The SSRV2_drop_all_tables.sql script for MYSQL did not cleanup the tables completely without error. Some of the tables were not dropped.
Resolution:
We had to do the following:

Deactivate mon_config_service

Run the drop table script. 

Check the SSVR2 tables - and these were not removed: 
[root@db01 ~]# mysql -u root -pPASS -S /var/lib/mysql/mysql.sock ca_uim -A -e "show tables" | grep -i SSRV2 
ssrv2groupwithpath 
ssrv2policyprofileblob 
ssrv2policytargetstatus 
ssrv2probetemplatespackage 
ssrv2probetemplateupgrade 
ssrv2schemamigration 
ssrv2templatefilter 

Then we dropped the SSVR2 tables/views: 

[root@db01 ~]# mysql -u root -pPASS -S /var/lib/mysql/mysql.sock ca_uim -A -e "show tables" 
mysql) drop table ssrv2groupwithpath; 
mysql) drop table ssrv2policyprofileblob; 
mysql) drop table ssrv2policytargetstatus; 
mysql) drop table ssrv2probetemplatespackage; 
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
mysql) drop table ssrv2probetemplateupgrade; 
mysql) drop table ssrv2probetemplatespackage; 
mysql) drop table ssrv2schemamigration; 
mysql) drop table ssrv2templatefilter; 
mysql) drop view ssrv2groupwithpath; 

Dropped the view the script complained about and then we dropped table-> ssrv2probetemplatespackage

We then followed these steps:

- Deactivate mon_config_service
- Delete it from the file system
- Redeploy it
- Redeploy the mcs template probe packages to the Primary Hub from the local archive
- Activate mon_config_service (we started MCS and it ran the create SSRV2 script without issue)
- Waited a few minutes then checked the Monitoring Tab in USM
- Issue resolved
Additional Information:
MySQL 5.7 version being used was the Community version, and not a commercial version of MySQL (Standard/Enterprise), and that is not currently tested nor supported. 

Help doc reference noting support for MySQL type.
https://docops.ca.com/ca-unified-infrastructure-management/9-0-2/en/installing/pre-installation-planning/install-and-configure-your-database-software/mysql-server

These are the 'commercial' editions of MySQL: