How to fix an error message "Illegal mix of collations"

Based on the MySQL configuration, customers using IceWarp 14.2 may encounter issues with Groupware database inconsistencies in collations. The main affected table will be Table Event.

Symptoms:

• problems with opening attachments
• problems sending emails with attachments
• Opening events will result in an error

Error "Database error or no value returned." will pop up in WebClient.

Example of error message 

The following error message can pop up in the Error log; tables/columns might be different. However, the main issue is with "Illegal mix of collations."

SYSTEM [00007FCC05BF6640] 06:45:31 DB groupware : Error executing query: Illegal mix of collations for operation ‘UNION’, SQL:
Select EVN_ID, EVNGRP_ID, EVNOWN_ID, EvnLockOWN_ID, EvnModifiedOWN_ID, EVNRCR_ID, Evn_EditCounter, Evn_Created, Evn_Modified, Evn_Deleted, EvnFolder, EvnOriginalFolder, EvnTitle, EvnNote, EvnDescFormat, EvnLocation, EvnPriority, EvnComplete, EvnCompleted, EvnColor, EvnClass, EvnShareType, EvnTimeFormat, EvnType, EvnStatus, EvnOrganizer, EvnContact, EvnURL, EvnStartDate, EvnStartTime, EvnEndDate, EvnEndTime, EvnRID, EvnUID, EvnExpire, EvnSequence, EvnFlags,EvnMeetingId,EvnLockAppMask,EvnLockHash,EvnLockInfo,EvnLinkID,EvnLinkType,Evn_MetaData,EVNCOMEVNID,EvnDocInvite,EvnDocExpire,EvnDocPass,EvnDocRights From Event Where (EVN_ID = ‘6691137997ee’) AND (EVNGRP_ID = ‘65d4b1ea0697’)
UNION
Select EVN_ID, EVNGRP_ID, EVNOWN_ID, EvnLockOWN_ID, EvnModifiedOWN_ID, EVNRCR_ID, Evn_EditCounter, Evn_Created, Evn_Modified, Evn_Deleted, EvnFolder, EvnOriginalFolder, EvnTitle, EvnNote, EvnDescFormat, EvnLocation, EvnPriority, EvnComplete, EvnCompleted, EvnColor, EvnClass, EvnShareType, EvnTimeFormat, EvnType, EvnStatus, EvnOrganizer, EvnContact, EvnURL, EvnStartDate, EvnStartTime, EvnEndDate, EvnEndTime, EvnRID, EvnUID, EvnExpire, EvnSequence, EvnFlags,EvnMeetingId,EvnLockAppMask,EvnLockHash,EvnLockInfo,EvnLinkID,EvnLinkType,Evn_MetaData,EVNCOMEVNID,EvnDocInvite,EvnDocExpire,EvnDocPass,EvnDocRights From EventChat Where (EVN_ID = ‘6691137997ee’) AND (EVNGRP_ID = ‘65d4b1ea0697’)

Repair process

1. Choose GW DB
2. Run this SQL query

SHOW FULL COLUMNS FROM Event WHERE Field = 'EvnLocation'

3. Copy value from Collation column
4. Run this SQL query, use the copied collation value instead of "collation"

ALTER TABLE EventChat MODIFY COLUMN EvnLocation longtext CHARACTER SET collation