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
|