We are getting the following error in UMP: Could not load acls:500, error

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)
• CA Unified Infrastructure Management SaaS (Nimsoft / UIM)

Issue/Introduction

We started getting the following error in the UMP when in the AccountAdmin 
trying to access ACL/LDAP tab
“Could not load acls:500, error”.




In the portal logs we can see the following error:

07 Apr 2020 11:56:30,672 ERROR [DataFactoryException:102] (conn=440364) Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='

07 Apr 2020 11:56:30,673 ERROR [DataFactoryException:104]

07 Apr 2020 11:56:30,674 ERROR [DataFactoryException:107] java.sql.SQLException: (conn=440364) Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='__         at

 

java.lang.Thread.run(Thread.java:745)__Caused by: java.sql.SQLException: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='_Query is: select acls.*, af.value as ump_alarm_filters from CM_ACCOUNT_ACLS acls left join umpAclAlarmFilters af on acls.acl = af.acl__             at org.mariadb.jdbc.internal.util.LogQueryTool.exceptionWithQuery(LogQueryTool.java:153)__              at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:255)__ at org.mariadb.jdbc.MariaDbPreparedStatementClient.executeInternal(MariaDbPreparedStatementClient.java:209)__  ... 62 more__ [Sanitized]

Environment

UIM 8.x, 9.x,20.x

Cause

There was a change to the database collation.

Resolution

Have to correct the collation on the back end database.

To test you can run the following query directly on the database
select acls.*, af.value as ump_alarm_filters from CM_ACCOUNT_ACLS acls left join umpAclAlarmFilters af on acls.acl = af.acl

to check the database collation you can use the following:
SHOW VARIABLES LIKE '%collation%';


During the issue time we have observed collation is different.
my sql collation_database  should be utf8_unicode_ci. but we have noticed the collation set to utf_general_ci.

due to this we have observed Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT). 

to resolve this we have recreated the table with utf8_general_ci.

The table definition when the issue occurs:

CREATE TABLE `umpaclalarmfilters` (
  `acl` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `value` longtext CHARACTER SET utf8 COLLATE utf8_unicode_ci,
  PRIMARY KEY (`acl`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

The changes done to collation and dropped the table. then recreated the table with below definition to resolve the issue:

CREATE TABLE `umpaclalarmfilters` (
  `acl` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `value` longtext CHARACTER SET utf8 COLLATE utf8_general_ci,
  PRIMARY KEY (`acl`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;