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

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.

Environment

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

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