Convert IW DB's to specific Character set

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