==== REQUIRED BASIC INFO OF YOUR IREDMAIL SERVER ====
- iRedMail version (check /etc/iredmail-release): 2024071701 (Backend: openldap, Date: 2024-07-31 09:19:43)
- Deployed with iRedMail Easy or the downloadable installer? iRedMail Easy
- Linux/BSD distribution name and version: Debian 11.7
- Store mail accounts in which backend (LDAP/MySQL/PGSQL): LDAP
- Web server (Apache or Nginx): Nginx
- Manage mail accounts with iRedAdmin-Pro? iRedAdmin-Pro
- [IMPORTANT] Related original log or error message is required if you're experiencing an issue.
====

Hi,

It’d like to fix the more and more frequently seen error in sogo that appears when users use extended UTF-8 characters like emoticons in event’s title or description.
The fix requires converting the sogo database to utf8mb4. More info can be found on sogo’s mantis id=5630

I’ve start to work on an action plan ( find it at the end of the post). And then a few questions appears :
- Is there a plan to fix this in a near future ? and if yes, when ?
- What happen if I fix things myself now, and an update provides another fix later ?
- The backup_mysql.sh script set --default-character-set variable to utf8, it seems to be a good idea to convert all database to utf8mb4, then change this value to utf8mb4, no ?
- Is there any other side effect that I did not find ?

Action plan :
*** Convert SOGo database to utf8mb4
alter database sogo character set utf8mb4 COLLATE utf8mb4_general_ci;

***** sogo_acl table
SHOW FULL COLUMNS FROM sogo_acl;
ALTER TABLE sogo_acl MODIFY c_object varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE sogo_acl MODIFY c_uid varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE sogo_acl MODIFY c_role varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

***** sogo_admin table
SHOW FULL COLUMNS FROM sogo_admin;
ALTER TABLE sogo_admin MODIFY c_key varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE sogo_admin MODIFY c_content mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

***** sogo_alarms_folder
SHOW FULL COLUMNS FROM sogo_alarms_folder;
ALTER TABLE sogo_alarms_folder MODIFY c_path varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE sogo_alarms_folder MODIFY c_name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE sogo_alarms_folder MODIFY c_uid varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

***** sogo_cache_folder
SHOW FULL COLUMNS FROM sogo_cache_folder;
ALTER TABLE sogo_cache_folder MODIFY c_uid varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE sogo_cache_folder MODIFY c_path varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE sogo_cache_folder MODIFY c_parent_path varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE sogo_cache_folder MODIFY c_content longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

***** sogo_folder_info
SHOW FULL COLUMNS FROM sogo_folder_info ;
ALTER TABLE sogo_folder_info MODIFY c_path varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE sogo_folder_info MODIFY c_path1 varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE sogo_folder_info MODIFY c_path2 varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE sogo_folder_info MODIFY c_path3 varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE sogo_folder_info MODIFY c_path4 varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE sogo_folder_info MODIFY c_foldername varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE sogo_folder_info MODIFY c_location varchar(2048) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE sogo_folder_info MODIFY c_quick_location varchar(2048) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE sogo_folder_info MODIFY c_acl_location varchar(2048) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE sogo_folder_info MODIFY c_folder_type varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

***** sogo_quick_appointment
SHOW FULL COLUMNS FROM sogo_quick_appointment ;
ALTER TABLE sogo_quick_appointment MODIFY c_name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE sogo_quick_appointment MODIFY c_uid varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE sogo_quick_appointment MODIFY c_title varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE sogo_quick_appointment MODIFY c_participants text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE sogo_quick_appointment MODIFY c_cycleinfo text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE sogo_quick_appointment MODIFY c_location varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE sogo_quick_appointment MODIFY c_orgmail varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE sogo_quick_appointment MODIFY c_partmails text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE sogo_quick_appointment MODIFY c_partstates text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE sogo_quick_appointment MODIFY c_category varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE sogo_quick_appointment MODIFY c_component varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE sogo_quick_appointment MODIFY c_description text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

***** sogo_quick_contact
SHOW FULL COLUMNS FROM sogo_quick_contact ;
ALTER TABLE sogo_quick_contact MODIFY c_name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE sogo_quick_contact MODIFY c_givenname varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE sogo_quick_contact MODIFY c_cn varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE sogo_quick_contact MODIFY c_sn varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE sogo_quick_contact MODIFY c_screenname varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE sogo_quick_contact MODIFY c_l varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE sogo_quick_contact MODIFY c_mail text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE sogo_quick_contact MODIFY c_o varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE sogo_quick_contact MODIFY c_ou varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE sogo_quick_contact MODIFY c_telephonenumber varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE sogo_quick_contact MODIFY c_categories varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE sogo_quick_contact MODIFY c_component varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

***** sogo_sessions_folder
SHOW FULL COLUMNS FROM sogo_sessions_folder ;
ALTER TABLE sogo_sessions_folder MODIFY c_id varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE sogo_sessions_folder MODIFY c_value varchar(4096) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

***** sogo_store
SHOW FULL COLUMNS FROM sogo_store ;
ALTER TABLE sogo_store MODIFY c_name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE sogo_store MODIFY c_content mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

***** sogo_user_profile
SHOW FULL COLUMNS FROM sogo_user_profile ;
ALTER TABLE sogo_user_profile MODIFY c_uid varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE sogo_user_profile MODIFY c_defaults text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE sogo_user_profile MODIFY c_settings text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;