mon_config_service migration failed with MySQL server hosting the UIM database

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

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

Environment

UIM 8.51 or later Server after upgrading
MySql server hosting UIM database

Cause

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.

Resolution

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