Hi guys.
The new smiley´s in Outlook (this is from the Sugarlog):
is causing an error in the Sugarlog when emails with it are being archived. Here is the error:
Mon May 15 09:56:47 2017 [23649][record number][FATAL] Error inserting into table: emails_text: Query Failed: INSERT INTO emails_text
Seems like the new smiley is causing illegal character or something. So, is this a known error or should I just create a support ticket?
Thanks,
KGM
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.
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;
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:
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.