1
Problem on Character Encoding Incorrect string value
Problem reported by Bill Ogdon - 1/22/2018 at 8:32 PM
Locked
Resolved
Hi Developer, 

I have installed smartertrack on our server 2 years ago with IIS 7 as the web server. Lately, i found so many incoming spool eml files that never become ticket in our department. They were just have failed in their status and have no success when i do 'retry' action.

This issue also happen on creating ticket via API web services.

 

My first intuition, it is because of encoding problem on database/tables or maybe inside the application itself. I am using MySql v.5.5.3.x as the database and find out that bodytext/bodyhtml in a ticketmessages table have UTF-8 encoding. There are several our respondent send their message using 'emojis'/icon or stuff blended in their message. i am suggesting it should be use utf8mb4 as the encoding and utfmb4_unicode for the collation. But i believe others solution may applied to this problem.

 

in Diagnostics under setting, i have checked the Errors log and see this error as below :

 

Failed to add the message to ticket number 'xxx-xxxxxxxx-xxxx' Incorrect string value: '\xF0\x9F\x98\x80\xF0\x9F...' for column 'BodyHtml' at row 1 Incorrect string value: '\xF0\x9F\x98\x80\xF0\x9F...' for column 'BodyHtml' at row 1

 

Due to above error, message was not store on the database. This is also happen when create new ticket as well as add message to existing ticket.

 

The error always happen when there is strange character in the original message or in the 'Subject' of mail, but message/ticket with plain text or html format are working correctly.

 

We don't have plan to upgrade to version 13, since i have done fresh installation with this version but the problem is still there. Version 13 also still using UTF8 as the recommended encoding character.

 

We don't mind if 'emoji' or other chars are unreadable, but at least it should be store the rest of readable text to the database so agents can still read and not miss any single ticket. please fix this serious error really soon. Thank you

5 Replies

Reply to Thread
0
Bill Ogdon Replied
Hi Derek Curtis, could you tell the tech team, please use utf8mb4 for connection string to mysql, and change the entire encoding app to utf8mb4 instead of regular UTF8. I want smartertrack support emoji unicode character. please help.
0
Andrew Barker Replied
Employee Post
We are aware of this encoding issue when using MySQL. We are planning to include a complete fix in a future version. For now, our support department can provide assistance with obtaining a custom build and making the necessary modifications to your database to allow for utf8mb4 encoding.
Andrew Barker Software Developer SmarterTools Inc. (877) 357-6278 www.smartertools.com
0
Bill Ogdon Replied
Rod Lasky was taking assistance to make custom build for this. But it is seems still not function correctly. Current build version is 12.6.6596.25760.
I have done the mySql side to allow proper encoding and it was successfully accepting the unicode char. But the application side still not doing the same.
0
Andrew Barker Replied
Employee Post
Bill,
Please see Rod's reply to your other post about this issue.
Andrew Barker Software Developer SmarterTools Inc. (877) 357-6278 www.smartertools.com
0
Derek Curtis Replied
Employee Post
I'm going to lock this thread as there's more discussion here about this issue:
 
Derek Curtis COO SmarterTools Inc. (877) 357-6278 www.smartertools.com