Here is the cleanup process which includes cleaning up some leftover tables in MySQL 5.7 Community Edition for UIM 20.40.
In IM or AC,
1. Deactivate mon_config_service
2. Delete mon_config_service
3. Go the mon_config_service folder on the Primary hub file system and delete it.
4. If not done already, download and install MySQL Workbench, then use it as your interface/tool to manage the tables as its much easier than using the command line.
5. Make sure the database user has full access to the UIM database for dropping tables, views, etc., e.g., administrator
6. Run these DROP TRIGGER statements:
DROP TRIGGER IF EXISTS T_CM_GROUP_INS_SSRV2DEVICEGROUP;
DROP TRIGGER IF EXISTS T_CM_GROUP_UPDT_SSRV2DEVICEGROUP;
DROP TRIGGER IF EXISTS T_CM_GROUP_DEL_SSRV2DEVICEGROUP;
DROP TRIGGER IF EXISTS T_CM_GROUP_MEMBER_INS_SSRV2DEVICE;
DROP TRIGGER IF EXISTS T_CM_GROUP_MEMBER_UPDT_SSRV2DEVICE;
DROP TRIGGER IF EXISTS T_CM_GROUP_MEMBER_DLT_SSRV2DEVICE;
DROP TRIGGER IF EXISTS T_CM_GROUP_MEMBER_INSERT;
DROP TRIGGER IF EXISTS T_CM_GROUP_MEMBER_UPDATE;
DROP TRIGGER IF EXISTS T_CM_GROUP_MEMBER_DELETE;
DROP TRIGGER IF EXISTS T_SSRV2DEVICEGROUP_INS_LASTMODIFIED;
7. Then:
SET FOREIGN_KEY_CHECKS = 0;
8. Next, run the following DROP TABLE statements and take note of any failures:
DROP TABLE IF EXISTS SSRV2DevicePackage;
DROP TABLE IF EXISTS SSRV2Version;
DROP TABLE IF EXISTS SSRV2Template;
DROP TABLE IF EXISTS SSRV2SelectOption;
DROP TABLE IF EXISTS SSRV2Removal;
DROP TABLE IF EXISTS SSRV2ProfileCondition;
DROP TABLE IF EXISTS SSRV2ProfileComposite;
DROP TABLE IF EXISTS SSRV2Profile;
DROP TABLE IF EXISTS SSRV2PollerGroup;
DROP TABLE IF EXISTS SSRV2Poller;
DROP TABLE IF EXISTS SSRV2PackageTemplate;
DROP TABLE IF EXISTS SSRV2PackageAccount;
DROP TABLE IF EXISTS SSRV2Package;
DROP TABLE IF EXISTS SSRV2ObjectLock;
DROP TABLE IF EXISTS SSRV2Field;
DROP TABLE IF EXISTS SSRV2DynamicDeviceDataRequest;
DROP TABLE IF EXISTS SSRV2DeviceGroup;
DROP TABLE IF EXISTS SSRV2DeviceCustomAttributeValue;
DROP TABLE IF EXISTS SSRV2DeviceCustomAttrValue;
DROP TABLE IF EXISTS SSRV2DeviceCustomAttribute;
DROP TABLE IF EXISTS SSRV2Device;
DROP TABLE IF EXISTS SSRV2DefaultConfigValue;
DROP TABLE IF EXISTS SSRV2DefaultProfile;
DROP TABLE IF EXISTS SSRV2ContainerProperty;
DROP TABLE IF EXISTS SSRV2Container;
DROP TABLE IF EXISTS SSRV2ConfigValue;
DROP TABLE IF EXISTS SSRV2ConditionEvalRecordProfile;
DROP TABLE IF EXISTS SSRV2ConditionEvalRecProfile;
DROP TABLE IF EXISTS SSRV2ConditionEvalRecord;
DROP TABLE IF EXISTS SSRV2CallbackParameter;
DROP TABLE IF EXISTS SSRV2Callback;
DROP TABLE IF EXISTS SSRV2BackupProfile;
DROP TABLE IF EXISTS SSRV2BackupConfigValue;
DROP TABLE IF EXISTS SSRV2AuditTrailModification;
DROP TABLE IF EXISTS SSRV2AuditTrail;
9. SET FOREIGN_KEY_CHECKS = 1;
10. For any tables OR views that throw errors and were not dropped run these statements one at a time, so you can identify which ones failed, e.g.,
DROP TABLE IF EXISTS ssrv2groupwithpath; #For example, this DROP table failed and had to be adjusted since it was a View not a table
DROP TABLE IF EXISTS ssrv2policyprofileblob;
DROP TABLE IF EXISTS ssrv2policytargetstatus;
DROP TABLE IF EXISTS ssrv2probetemplatespackage;
DROP TABLE IF EXISTS ssrv2probetemplateupgrade;
DROP TABLE IF EXISTS ssrv2profileevent;
DROP TABLE IF EXISTS ssrv2schemamigration;
DROP TABLE IF EXISTS ssrv2templatefilter;
11. Run this query to see any leftover SSRV2 tables, views etc., that were not dropped:
select * from information_schema.tables where table_name like 'SSRV2%';
Even a single leftover table may prevent the successful redeployment/reinstallation of mon_config_service!
12. Then after adjusting one table drop to a View DROP,
DROP VIEW IF EXISTS ssrv2groupwithpath;
DROP TABLE IF EXISTS ssrv2policyprofileblob;
DROP TABLE IF EXISTS ssrv2policytargetstatus;
DROP TABLE IF EXISTS ssrv2probetemplatespackage;
DROP TABLE IF EXISTS ssrv2probetemplateupgrade;
DROP TABLE IF EXISTS ssrv2profileevent;
DROP TABLE IF EXISTS ssrv2schemamigration;
DROP TABLE IF EXISTS ssrv2templatefilter;
13. Run-> select * from information_schema.tables where table_name like 'SSRV2%';
There should be NO SSRV2* tables left!
To finish up the process, follow these last steps:
14. Redeploy mon_config_service 20.40 from the Primary Hub's local archive
15. After the deployment is completed, via Raw Configure mode, set mon_config_service loglevel to 5 and logsize to 50000
16. Activate mon_config_service
17. Open the log and press F4 to highlight and enter the string 'flyway' to make sure there are no further Flyway errors/exceptions in the log.
18, The mon_config_service should start up, turn green, and get a port and a PID.
19. Redeploy the mcs_template probe packages to the Primary Hub
20. Restart mon_config_service