This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

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 Children
  • 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.

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