note:  following article is only for version prior 11.4.5, it is strongly recommended to trim utf8mb4 characters in db and  convert back to utf8 prior upgrading to 11.4.5 and higher.

Support for utf8mb4 will be fully implemented since version 12.0.1 (only for create and migration - upgrade from utf8 to utf8mb4 is not supported)

In case you are facing following errors in error log: 

SYSTEM [49E8] 04:53:30:688 DB lib Error executing query: Incorrect string value: '\xF0\x9F\x94\xA8' for column 'SndSubject' at row 1, SQL: INSERT INTO Senders (SndEmail, SndAuthorized, SndCreatedOn, SndCreatedAt, SndFolder, SndOwner, SndDomain, SndIP, SndWord, SndSubject) VALUES ('update@booblol.in','2',2457443,17610,'2016022404533053443458','popo.gape@miraclehospitality.com','miraclehospitality.com','209.105.231.156','TVV8 S2JQ','Cleaning? Plastic? Tool Kits? We got them allߔ觩')

We have a solution based on this article:

http://stackoverflow.com/questions/13653712/java-sql-sqlexception-incorrect-string-value-xf0-x9f-x91-xbd-xf0-x9f

The solution is to convert databases to use utf8mb4 character set and reconfigure IceWarp Server to use it as well (set API variable c_system_mysqldefaultcharset to utf8mb4).

To prevent data loss or other issues caused by unexpected behavior, backup your databases prior executing conversion script and follow these steps:

- stop all IceWarp Server services
- run the script enclosed below
- set API variable c_system_mysqldefaultcharset to utf8mb4 (run tool.sh set system c_system_mysqldefaultcharset utf8mb4)
- restart all IceWarp Server services

Following bash script is for automatic collation of all tables used by Icewarp to utf8mb4:

-----------------------------------------------------------------------------------------------------------------------------------------------------------

#!/bin/bash

#
# The purpose of this script is to ease the proces of database charset and collation conversion
# by default, it coverts to utf8mb4 charset and utf8mb4_unicode_ci collation, however you can use what ever you like
# Using unicode collation variant is more accureate while general is faster (choice is yours)
# switch to utf8mb4 enables support of insertions of 4bytes symbols into mysql 

# databases you do not want to affect should be listed into exclude file (exclude_dbs.txt) one per line 
# it is also possible to alter the line where database names are read so grep selects instead of excluding databases
# script also attempts automatically modify column indexes to varchar(191) if they are bigger than that
# otherwise it leaves them unaffected
# maximum size of the index will vary depending on character set converted to (191 is for default)

###

#

#replace user and pass with your mysql credentials

#

user=yourusername
pass=yourpassword

char=utf8mb4

coll=utf8mb4_unicode_ci

#uncomment and set ip if mysql is not running on same machine as script is executed:

#host=$(echo '-h 192.168.7.27')

####

#
echo "Script for automatic conversion of database charset has started,  please backup your databases and check whether your selection of databases to process is correct indeed."
read -p "Do you wish to continue? (yes/no)" CONT
if [ "$CONT" == "yes" ]; then
 # read db names
 databases=`mysql ${host} -u$user -p$pass -N -e "SHOW DATABASES;" | tr -d "| " | grep -v -f exclude_dbs.txt`
 for db in $databases; do
       mysql ${host} -u$user -p$pass -N -e "ALTER DATABASE ${db} CHARACTER SET = ${char} COLLATE = ${coll};"
       echo "Reading tables from database: $db"
       tables=$(mysql ${host} -u$user -p$pass -N -e "SHOW TABLES FROM ${db}" | tr -d "| ")
       for table in $tables; do
                >|text
               echo $table
               # alter tables
               mysql ${host} -u$user -p$pass -N -e "ALTER TABLE ${db}.${table} CONVERT TO CHARACTER SET ${char} COLLATE ${coll};"
               if [ $? -gt 0 ]; then
                 echo "SHOW FTABLES: ${db}.${table}"
                 mysql ${host} -u$user -p$pass -N -e "show indexes in ${db}.${table} where column_name in (select column_name from information_schema.statistics where column_name in (select column_name from information_schema.columns where table_schema = '${db}' and column_type > 'varchar(191)'));" >> text
                 cat text | while read line
                 do
                   query=$(echo -e "${db}\t${line}" | awk -F"\t" '{printf("alter table %s.%s modify %s varchar(191);\n", $1, $2, $6)}')
                   mysql ${host} -u$user -p$pass -N -e "${query}" >>sql.log 2>&1
                   if [ $? -gt 0 ]; then
                     echo -e "error in:\n $query"
                   fi
                 done
                 mysql ${host} -u$user -p$pass -N -e "ALTER TABLE ${db}.${table} CONVERT TO CHARACTER SET ${char} COLLATE ${coll};"
               fi
       done
   done
elif [ "$CONT" == "no" ]
   then
    echo "script has been succesfully terminated";
 exit
  else
    echo "well, correct answer is yes or no";
 exit
fi

--------------------------------------------------------------------------------------------------------------------------------------------------------------

Usage is very simple, modify user and pass variables and replace following raw of the script with one of below written lines, You have 3 possibilities how to select which databases to collate:

1)databases=`mysql -u$user -p$pass -N -e "SHOW DATABASES;" | tr -d "| " | grep -v -f /path/to/exclude_dbs.txt`

create txt file exclude_dbs.txt where you can write all databases you wish to exclude from collation - write them on separate lines

2)databases=`mysql -u$user -p$pass -N -e "SHOW DATABASES;" | tr -d "| " | grep IW_`

this is usable if you have databases used in icewarp named with same pre-fix e.g. IW_%%database%%

3)databases=`mysql -u$user -p$pass -N -e "SHOW DATABASES;" | tr -d "| "`

this will select everything

In any case of troubles, you can use this script to revert back to utf8 with simply replacing utf8mb4 to utf8.

Note: whole scenario is currently under testing and should be used on your own risk, for more reference see following article:

https://mathiasbynens.be/notes/mysql-utf8mb4

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

a.rusek,o.vanek