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 =:

book

Article ID: 225363

calendar_today

Updated On:

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 '='

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:

Environment

Broadcom API Gateway Software form factor

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;

 

Attachments