mon_config_service migration failed with MySQL server hosting the UIM database

Document ID : KB000007889
Last Modified Date : 14/02/2018
Show Technical Document Details
Issue:

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 Server after upgradingMySql 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 Upgrade the UIM Server Wiki 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