Saving Emoji characters to Appian Cloud MySQL database

Question

Hi, 
 We just encountered an error where a user entered an emoji into a form's text description, which then failed to save in the MySQL database. Apparently this is due to the collation that is set on the database (utf8_general_ci) does not support such characters. 
 (FYI, the character was & # 55357; & # 56842; - aka smiling face with smiling eyes: http://apps.timwhitlock.info/unicode/inspect/hex/1F60A) 
 What is the recommended way to handle such data? 
 Thanks, 
 Mike

Mike Cichy · Accepted Answer

Please make sure to open a Support case to report this.

aloks0189 · Answer

hi mikej117 you can follow the following steps to allow MySQL Database Table to store emoji : 1) Set the database charset to utf8mb4 2) Set the charset of column to utf8mb4 Below are the commands which you would like to execute =============================================================================================================== ALTER TABLE Tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin ALTER TABLE `comments` CHANGE `text` `text` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL; 
 
 Hope this will help You

Dave Lewis · Answer

Another thing very much worth investigating is how those emoji got into the title in the first place. It's unlikely someone would intentionally go through all the trouble of adding one to legitimate business data, but very likely that an alternate program would automatically convert a string of characters into the emoji in question. 
 
I can't mention the number of times I've tried typing a code snippet into Skype and it's produced an Angry Face or Winking face when I actually wanted it to preserve the punctuation. Just imagine what it might produce with Javascript like this: var total = x^3>(a);) It's a particular pain point with JIRA, that won't let me punctuate the way I want without making things bold or red or whatever. Just let me add punctuation without doing weird stuff unless I tell you. 
 
Faced with your situation, I'd want to know what program the user was using to create the string, and what key combination (such as ;)) that could produce the emoji. Since that wasn't the intent, you could use the code() function on incoming characters, and if you find one of the problem ones translate them into the original intended sequence of characters. 
 
Alternatively, there also might be a way to turn the auto-convert-to-emoji off on the other program.