How to convert any database to a specific charset in IceWarp

In case you are facing the error below then you need to change the charset of your DB. 

WebClient/PRO [00007F93EAB8A700] 20:39:41 [wm-606a24066233c019858065] Failed SQL:INSERT INTO folder (account_id,name,rights,attributes) VALUES (?,?,?,?)Array
(
[0] => michael.serences@x1solar.com
[1] => INBOX/Český_název
[2] => 0
[3] => 64
)
SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: '\xC4\x8Cesk\xC3...' for column `latin_webclient`.`folder`.`name` at row 1

Error is caused by "Code table". To fix this issue you need to perform a change of charset. Recommended charset for IceWarp databases is utf8mb4.

Please make sure you have a backup before you will proceed following the steps, to avoid data loss.

1. EXPORT OF DUMP
DUMP of DB's must proceed separately for schema and data to avoid replacing data in the columns during the SED command.

Dump of schema without data:

mysqldump -uDB_USER -pDB_PASSWORD -hDB_HOST -PDB_PORT --single-transaction --no-data DB_NAME > /root/mig/db/DB_NAME_schema.sql

Replace of collation on the schema DUMP:

sed -i -r 's|latin1_bin|utf8mb4_unicode_ci|g' "/root/mig/db/DB_NAME_schema.sql"

Replace of charset on schema DUMP:

sed -i -r 's|latin1|utf8mb4|g' "/root/mig/db/DB_NAME_schema.sql"

Replace of charset in case you already had "utf8mb4":

sed -i -r 's|utf8mb4mb4|utf8mb4|g' "/root/mig/db/DB_NAME_schema.sql"

DUMP of data without a schema:

mysqldump -uDB_USER -pDB_PASSWORD -hDB_HOST -PDB_PORT --single-transaction --no-create-info DB_NAME > /root/mig/db/DB_NAME_data.sql

2. CREATE OF DB
Create a new database with the correct default charset (collate - collation based on the location more - https://mariadb.com/kb/en/supported-character-sets-and-collations/).

MariaDB [(none)]> CREATE DATABASE DB_NAME default charset utf8mb4 collate utf8mb4_unicode_ci;

3. IMPORT OF FIXED DUMP
Import of edited schema to database:

mysql -uDB_USER -pDB_PASSWORD -hDB_HOST -PDB_PORT DB_NAME < /root/mig/db/DB_NAME_schema.sql

Import of raw data to the database:

mysql -uDB_USER -pDB_PASSWORD -hDB_HOST -PDB_PORT DB_NAME < /root/mig/db/DB_NAME_data.sql