Emails with "new smiley" (emoji) not archived - error

Anyone?

Hi Kristjan Geir Mathiesen 

Can you kindly provide full failed query and all related sugarcrm.log entries as well?

Hi André Lopes

Fri May 19 21:20:48 2017 [3947][c301647c-8726-423c-aadb-56d9c8ba7b8f][FATAL] Error inserting into table: emails_text: Query Failed: INSERT INTO emails_text (email_id,from_addr,reply_to_addr,to_addrs,cc_addrs,bcc_addrs,description,description_html,raw_source,deleted)
VALUES ('07525aca-3cd9-11e7-8ad1-00505696154c','EMAIL_ADDRESS',NULL,'EMAIL_ADDRESS',NULL,NULL,'  \r\n','<div class=\"WordSection1\"><p class=\"MsoNormal\"><span style=\"font-family:\'Segoe UI Emoji\', sans-serif;\">  </span></p><p></p></div>',NULL,0): MySQL error 1366: Incorrect string value: '\xF0\x9F\x98\x8A\x0D\x0A' for column 'description' at row 1

Hello Kristjan Geir Mathiesen,

Basically 'utf8_general_ci' collation do not support this type of special characters and throws Incorrect string value error in sql query execution.  You can resolve this issue by changing the field collation to "utf8mb4_general_ci" .

Please execute below query in your database and check.

ALTER TABLE `emails_text` CHANGE `description_html` `description_html` LONGTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL;

Hope this will helps you.

Thanks for the suggestion, Maulik Shah This didn´t work though  I´ll just open a ticket with support.

The solution is this.

You need some changes in code. My solution it is for version 7.5.2.1 (probably in earlier versions will work this steps, extending and changing only MysqliManager class).

1. Copy the file include/database/MysqliManager.php to custom path:

custom/include/database/MysqliManager.php

2. Change connect method like this:

 /**
 * @see DBManager::connect()
 */
 public function connect(array $configOptions = null, $dieOnError = false)
 {
 global $sugar_config;

 if (is_null($configOptions))
 $configOptions = $sugar_config['dbconfig'];

 if(!isset($this->database)) {

 //mysqli connector has a separate parameter for port.. We need to separate it out from the host name
 $dbhost=$configOptions['db_host_name'];
 $dbport=null;
 $pos=strpos($configOptions['db_host_name'],':');
 if ($pos !== false) {
 $dbhost=substr($configOptions['db_host_name'],0,$pos);
 $dbport=substr($configOptions['db_host_name'],$pos+1);
 }

 if (ini_get('mysqli.allow_persistent') && $this->getOption('persistent')) {
 $dbhost = "p:" . $dbhost;
 }

 $this->database = mysqli_connect($dbhost,$configOptions['db_user_name'],$configOptions['db_password'],isset($configOptions['db_name'])?$configOptions['db_name']:'',$dbport);
 if(empty($this->database)) {
 $GLOBALS['log']->fatal("Could not connect to DB server ".$dbhost." as ".$configOptions['db_user_name'].". port " .$dbport . ": " . mysqli_connect_error());
 if($dieOnError) {
 if(isset($GLOBALS['app_strings']['ERR_NO_DB'])) {
 sugar_die($GLOBALS['app_strings']['ERR_NO_DB']);
 } else {
 sugar_die("Could not connect to the database. Please refer to sugarcrm.log for details.");
 }
 } else {
 return false;
 }
 }
 }

 if(!empty($configOptions['db_name']) && !@mysqli_select_db($this->database,$configOptions['db_name'])) {
 $GLOBALS['log']->fatal( "Unable to select database {$configOptions['db_name']}: " . mysqli_connect_error());
 if($dieOnError) {
 if(isset($GLOBALS['app_strings']['ERR_NO_DB'])) {
 sugar_die($GLOBALS['app_strings']['ERR_NO_DB']);
 } else {
 sugar_die("Could not connect to the database. Please refer to sugarcrm.log for details.");
 }
 } else {
 return false;
 }
 }

 $charset = $this->getOption('charset');
 if(empty($charset)){
 $charset = 'utf8';
 }

 // cn: using direct calls to prevent this from spamming the Logs
 mysqli_query($this->database,"SET CHARACTER SET $charset");
 $names = "SET NAMES '$charset'";
 $collation = $this->getOption('collation');

 if(!empty($collation)) {
 $names .= " COLLATE '$collation'";
 }
 mysqli_query($this->database,$names);

 if($this->checkError('Could Not Connect', $dieOnError))
 $GLOBALS['log']->info("connected to db");

 $this->connectOptions = $configOptions;
 return true;
 }

3. Add option charset and modify collation for connection at config.php (or config_override.php):

...

'dbconfigoption' => 
 array (
 'persistent' => false,
 'autofree' => false,
 'debug' => 0,
 'ssl' => false,
 'collation' => 'utf8mb4_general_ci',
 'charset' => 'utf8mb4',
 ),
...

4. Alter all tables and database with queries like this:

# Database:
ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;

# Each table:
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

Thanks Eduardo Cataño Conde for this. My DB admin is not too excited about changing the character set though...  :/

Ok, that change is not risky. That encoding is backward compatible (from utf8 to utf8mb4), because, you can work over a database copy, and compare tables; the data can be checked by your db admin with some diff software. I am sure of the change it is secure.

Check this link:

MySQL :: MySQL 5.5 Reference Manual :: 10.1.9.7 Converting Between 3-Byte and 4-Byte Unicode Character Sets 

Regards.

I noticed this thread, just to close this off:

Emoji support had been added to the Summer '18 (8.1) cloud release  and thus is now also available On-Site in the annual On-Site release Spring '19 (9.0). The fix for this bug requires a database collation change which will be done as part of the upgrade process to 9.0. Instances below 9.0 have to upgrade as the fix can not be backported to earlier versions. 

I know this is a bit late, but those who are stuck on older versions of Sugar for whatever reason, can create a file at path:

custom/modules/InboundEmail/getMessageText.php

with function: 

function custom_getMessageText($msgPart) {

}

In that function you can write a regular expression to parse and replace any unsupported characters

The pattern '/(?:\xF0[\x90-\xBF][\x80-\xBF]{2}|[\xF1-\xF3][\x80-\xBF]{3}|\xF4[\x80-\x8F][\x80-\xBF]{2})/s' can be a nice starting point for doing that.