Prior to upgrading to UIM 8.51, execution of the specified SQL query against the MySQL manager hosting the UIM database indicates that your MCS tables are ready for upgrade:
select case when (select count(*) from (select COUNT(*) as duplicates from SSRV2ConfigValue group by profile, field)a where a.duplicates > 1) > 0 then 'Contact CA support before upgrading to UIM 8.51.' else 'It is safe to upgrade to UIM 8.51.' end;
After the upgrade, the mon_config_services probe fails to start and if you try to execute the migration script V8_5_0_07__Create_Group_Path_View.sql found in the <installPath>/nimsoft/probes/service/mon_config_service/lib/mcs-db-scripts.jar archive file it fails as follows:
Migration V8_5_0_07__Create_Group_Path_View.sql failed
------------------------------------------------------
SQL State : HY000
Error Code : 1418
Message : This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
Location : flyway/mysql/V8_5_0_07__Create_Group_Path_View.sql (/opt/nimsoft/probes/service/mon_config_service/file:/opt/nimsoft/probes/service/mon_config_service/lib/mcs-db-scripts.jar!/flyway/mysql/V8_5_0_07__Create_Group_Path_View.sql)
Line : 8
UIM 8.51 or later Server after upgrading
MySql server hosting UIM database
This is caused if you have the following set in your MySQL configuration file:
log_bin=ON
When you create a stored function, you must declare either that it is deterministic or that it does not modify data. Otherwise, it may be unsafe for data recovery or replication.
By default, for a CREATE FUNCTION statement to be accepted, at least one of DETERMINISTIC, NO SQL, or READS SQL DATA must be specified explicitly; otherwise an error occurs.
The error arises if the binary logging option, which is required for replication, is turned on for the MySQL server.
As specified in the https://techdocs.broadcom.com/content/broadcom/techdocs/us/en/ca-enterprise-software/it-operations-management/unified-infrastructure-management/9-0-2/installing/pre-installation-planning/install-and-configure-your-database-software/mysql-server.html document, if you have log_bin=ON set in your MySQL configuration file, you need to also set the following additional variables:
log_bin_trust_function_creators=ON
binlog_format=mixed