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

book

Article ID: 188337

calendar_today

Updated On:

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]

Cause

There was a change to the database collation.

Environment

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

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;