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
|
Comments
0 comments
Please sign in to leave a comment.