You are currently reviewing an older revision of this page.

DRAFT - INT-XXXX - How to update MySQL collation to support emojis

Symptom

When attempting to write text containing emojis and certain other Unicode characters to the MySQL business database from a Write to Data Store node, the following error is logged:

2019-08-22 06:56:39,213 [Appian Work Item - 75 - execution00 : UnattendedJavaActivityRequest] ERROR org.hibernate.util.JDBCExceptionReporter - Incorrect string value: 'blah' for column 'blah' at row 1
2019-08-22 06:56:39,214 [Appian Work Item - 75 - execution00 : UnattendedJavaActivityRequest] ERROR com.appiancorp.type.external.writetodatastore.WriteToDataStoreCore - Error when trying to write to the data store. com.appiancorp.suiteapi.process.exceptions.SmartServiceException: userMsg[error.duringWrite.userMsg=An error occurred while trying to write to the entity <entity> [id=<uuid>, type=<type> (id=<id>)] (data store: <data store>). Details: org.hibernate.exception.GenericJDBCException: could not insert: [<entity>]: java.sql.SQLException: Incorrect string value: '<value>' for column '<column>' at row <rownum> Data: TypedValue[it=<id>,v={<value>}

Cause

This is caused by the default character set configuration of the MySQL database not supporting the full set of unicode characters.

Action

First check the current default character set at different levels as well as the current default collation setting for the MySQL database by running the following commands from phpMyAdmin:

select *
from performance_schema.global_variables
where variable_name like '%character%'
or variable_name like '%collation%';

Confirm that you would like to change the default collation, and that it changing it will not break any intended functionality. Once you have confirmed, take a snapshot of the current settings before applying new changes in case you need to restore them later on. Next, run the following commands to manually set the default server level character set and collation:

set global character_set_server = 'utf8mb4';
set global collation_server = 'utf8mb4_unicode_ci';

Any new tables created will inherit the default character set and collation. However, any tables that are already created with the previous character set and collation will not automatically switch their character set to the new one. The customer will need to do that explicitly themselves. For each <table_name>, the customer should run:

alter table <table_name> convert to character set utf8mb4;

It is possible that there might be an incompatibility between two old and new character sets. In this case, you will need to decide for yourself what is the appropriate course of action. For example, latin1 characters only take one byte per character. The maximum index key length in mysql is 767 bytes. So they can have an index on a character field that is 767 bytes. But utf8mb4 can take up to 4 bytes per character. Which means that you can have only have an index on a varchar field that is 767/4=191 characters only. The column length can be more than 191 characters but the index key length cannot be more than 191 characters. Two alternatives could be:

  1. Reduce the length of the column on which there is an index to 191 characters only. Drop the index. Change the character set. Then recreate the index.
  2. Keep the column length as it is but just recreate the index with a maximum key length of 191 chars only. This will work if the index is not unique. It will not work if the index is unique.

Or, you could choose to do nothing about the problem if changing the collation is more costly than enabling storage of emojis. Run this query to help identify which tables have columns that are using a character set:

select table_name, column_name, ordinal_position, column_default, is_nullable, data_type, character_maximum_length, character_set_name, collation_name, column_type
from information_schema.columns
where character_set_name is not null and table_schema = 'Appian'
order by table_name, ordinal_position;

Affected Versions

This article applies to all versions of Appian.

Last Reviewed: September 2019