How to change case sensitive DB to case insensitive for MariaDB

The following changes require stopping IW and MySQL(MariaDB).

We recommend doing a backup of the whole DB server before you start!

1. Stop IW (be careful on the watchdog - how to turn off watchdog using tool.sh below)

/opt/icewarp/tool.sh set system C_System_Tools_WatchDog_SMTP 0
/opt/icewarp/tool.sh set system C_System_Tools_WatchDog_POP3 0 
/opt/icewarp/tool.sh set system C_System_Tools_WatchDog_IM 0
/opt/icewarp/tool.sh set system C_System_Tools_Watchdog_GW 0
/opt/icewarp/tool.sh set system C_System_Tools_Watchdog_Control 0

2. Create DUMP's for all icewarp DB's

mysqldump -hDB_HOST -uDB_USER -pDB_PASSWORD --opt --quote-names --skip-set-charset --default-character-set=utf8mb4 --single-transaction EXAMPLEOFDB > /exmpleofdb.sql

3. Stop MySQL(MariaDB)

systemctl stop mysql

4. Add value

"lower_case_table_names = 1" to /etc/my.cnf (under [mysqld] section)

5. Start MySQL

systemctl enable mysql
systemctl start mysql

6. Create whole new DB's with utf8mb4 charset

Collation differs with nationality, more -> https://mariadb.com/kb/en/supported-character-sets-and-collations/
Make sure the new DBs have different names than the old ones.

CREATE DATABASE exampleofdb default charset utf8mb4 collate utf8mb4_general_ci;

7. Import DUMPs back to the DB

mysql -hDB_HOST -uDB_USER -pDB_PASSWORD --default-character-set=utf8mb4 exampleofdb < /exampleofdb.sql

8. Start IW services

9. Check test connection on all DB's

System - Advanced - Directory Cache - DB Settings...
Groupware - General - General - DB Settings...
Groupware - WebClient - General - DB Settings...
Groupware - ActiveSync - ActiveSync - DB Settings...
Anti-Spam - General - General - DB Settings...
Anti-Spam - Action - Reports - DB Settings...
System - Storage - Accounts - DB Settings...

*** If you are using some other DB's on the MySQL(MariaDB) then these changes can cause issues ***

Tested environment: MariaDB 10.3, CentOS7