Broadcom API Gateway (Software): Route HTTPS assertion throws Java Certificate/SQL Exception "Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation =:

Products

• CA API Gateway

Issue/Introduction

In Software Gateway implementation, routing HTTPS requests with a certificate that contains non-english characters it may cause the following Java exceptions (ssg logs):

WARNING 300 com.l7tech.server.policy.assertion.ServerHttpRoutingAssertion: 4042: Problem routing to https://apigateway.local. Error msg: Unable to obtain HTTP response from  https://apigateway.local: java.security.cert.CertificateException. Caused by: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation =
2021-10-01T11:11:50.363+1000 WARNING 300 com.l7tech.server.MessageProcessor: 3016: Request routing failed with status -1 

OR

java.sql.SQLException: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='

Environment

Broadcom API Gateway Software form factor

Cause

By default, API Gateway stores and so reads Certificates from MySQL "SSG" Database. If MySQL is not configured to use Unicode Transformation Format - 8 bits (UTF8) as charset and table collations, non-english characters may cause a conflict. In this scenario, non-english characters could be contained inside the Certificate's DN Subject Name and/or attributes. 

Use the following MySQL queries to verify how charset and collations are set in the Database, either globally and for SSG DB tables:

mysql> SELECT @@character_set_database, @@collation_database;

mysql> select TABLE_NAME,TABLE_COLLATION from information_schema.TABLES where TABLE_SCHEMA='ssg';

Here an example:

Resolution

In order to avoid issues as such, it is recommended to set MySQL to use UTF8 as default tables charset/collations.

• Backup your database before any change
• Stop Gateway and MySQL service
• Edit MySQL configuration file (my.cnf) and append the following two lines under [mysql] section:

character-set-server=utf8
collation-server=utf8_general_ci

• Restart MySQL and Gateway service

 

NOTE: if for any reason, requirements are preventing you to apply UTF8 globally in the database, you can be selective and adjust only the offended table with the following SQL statement:

ALTER TABLE trusted_cert MODIFY COLUMN subject_dn VARCHAR(2048) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;