Emoji in client emails cause complete note failure

Web Help Desk does enable you to manipulate text in mail via Java Regex within Setup > E-Mail > Options > Incoming Mail but it is a bit of a dark art.

You could change the actual Mail account being used to be not HTML based, set it as just Text or Rich Text and emojis should be stripped out of converted to text equilivents.

Just FYI - 12.7.12 now allows emojis to come through in the mote. However, emojis still break the Approval email process.

Thanks for the additional info - just for clarity what database type and collation are you using with your Web Help Desk?

We are using an external MySQL with utf8_general_ci collation, and utf8 character set.

Wow, that is usually the most problematic being on a 2 byte character set.

We advise to convert columns to utf8mb4 (which is a 4 byte character set) where foreign characters or emojis might be present. 

I'll need to schedule downtime to change the database to utf8mb4 to test that. Emojis do work fine with our notes that come in from clients via email, it is only the Approval emails with emojis that fail though.

I'll need to schedule downtime to change the database to utf8mb4 to test that. Emojis do work fine with our notes that come in from clients via email, it is only the Approval emails with emojis that fail though.

FYI - This is what one of our customers DBA's did.

ALTER DATABASE whd CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
ALTER TABLE whd.job_ticket CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE whd.job_ticket CHANGE QUESTION_TEXT QUESTION_TEXT text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

It seemed to work well for them but I don't know if Approval Notes are kept on same tables without checking the schema. 

I will see if this works. This is a snip of the error listed in the logs when trying to approve a ticket that has an emoji in the response. It is related to SQL and column 'EXPLANATION' so maybe I only have to touch that one table, where ever that is.

2023-04-27 14:57:23.367 [MailTicketDaemonExecutor-thread-1] ERROR e.e.eof.ERXEOAccessUtilities - Missing EOFailedAdaptorOperationKey or EOFailedDatabaseOperationKey in com.webobjects.eoaccess.EOGeneralAdaptorException: EvaluateExpression failed: <com.macsdesign.util.MDSMySQLPlugIn$MDSMySQLExpression: "UPDATE whd.TKT_APP_STEP_CLIENT_VOTE SET EXPLANATION = ?, VOTE = ? WHERE ID = ?" withBindings: 1:"Replying with an emo...pdesk.psd150.org%2f>"(explanation), 2:1(vote), 3:6169(id)>:
Next exception:SQL State:HY000 -- error code: 1366 -- msg: Incorrect string value: '\xF0\x9F\x99\x82\x0D\x0A...' for column 'EXPLANATION' at row 1: {EOAdaptorOperationsKey = ({_adaptorOperator = "EOAdaptorInsertOperator"; _entity = "ClientNote"; _changedValues = {jobTicketId = 174091; clientId = 1089; creatorDisplayString = <com.webobjects.foundation.NSKeyValueCoding$Null>; dateOverride = <com.webobjects.foundation.NSKeyValueCoding$Null>; id = 40178; originalCreationDate = <com.webobjects.foundation.NSKeyValueCoding$Null>; date = 2023-04-27 19:57:23 Etc/GMT; noteText = "Replying with an emoji again. &#x1f642;

utf8mb4_unicode_ci fixed the errors in the log about the emoji's in the note, but still can't approve a ticket via email with an emoji in the email. I turned on trace debug and see that it's reading the email and appears to handle the ticket.content fine but fails with this line:

[MailTicketDaemonExecutor-thread-1]  WARN w.h.com.macsdesign.util.exchange - Unable to parse date from emailMessage

It does accept the email as a new note, and the message text, including emoji, is successfully added as a note, but it never changes from Awaiting Approval to Approved. 

After all this, I then tried without an emoji in the email and it still failed ... so now I wonder if my issue is related to the recent 12.7.12 upgrade or something completely different now. I'll see if can narrow it down somehow.

Ok, I think I had to just create a new ticket for testing after I changed utf8mb4_unicode_ci (I just kept using the same ticket from last week). I created a new ticket that required approval and it processed through email system successfully even with an emoji in the email. 

Cool, looks good.