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

Hi Kristjan Geir Mathiesen 

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

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.

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;