Certificate addition failing with error

Document ID : KB000071643
Last Modified Date : 22/02/2018
Show Technical Document Details
Issue:

Policy manager throws the following error while trying to import the cert

Caused by: java.lang.Exception: Replaced exception of type 'org.springframework.jdbc.UncategorizedSQLException', with message 'Hibernate flushing: Could not execute JDBC batch update; uncategorized SQLException for SQL [insert into trusted_cert (cert_base64, issuer_dn, serial, ski, subject_dn, thumbprint_sha1, name, revocation_check_policy_goid, revocation_type, security_zone_goid, trust_anchor, trusted_as_saml_attesting_entity, trusted_for_saml, trusted_for_client, trusted_for_server, trusted_for_ssl, verify_hostname, version, goid) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]; SQL state [HY000]; error code [1366]; Incorrect string value: '\xCC\x88rtte...' for column 'subject_dn' at row 1; nested exception is java.sql.BatchUpdateException: Incorrect string value: '\xCC\x88rtte...' for column 'subject_dn' at row 1'

 

Cause:
Run the following command

mysql> SELECT character_set_name FROM information_schema.`COLUMNS` where table_schema = "ssg" and table_name= "trusted_cert" and column_name = "subject_dn";

Below is the output from a faulty node

output from faulty node

Below is the output from working node

Ouput from working node

The reason for this error is either the character set is missing or set to something else other than utf-8
Resolution:
Follow below steps
1. mysqldump --all-databases | gzip > /root/ibdata-shrink-backup.sql.gz
2. use ssg;
3. mysql> ALTER TABLE trusted_cert MODIFY COLUMN subject_dn VARCHAR(2048) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
5. now import the cert through policy manager.