Emoji in client emails cause complete note failure

This has been a constant issue but I wondered if anyone had found a workaround yet. We allow our clients to reply to tickets via email, and the email reply can also re-open the ticket. 

We get a LOT of people who reply to a ticket with a "Thanks!" and smiley face emoji. That causes the ticket to re-open, but no note is included.

Is there any way to strip emojis out of an email before WHD processes it?

Parents Reply
  • 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.