How-to fix Illegal mix of collations errors in mysql

MySQL Jan 27, 2021

If you have errors like my example bellow into your postfix logs - you can fix them easy :

warning: mysql:/usr/local/etc/postfix/mysql_virtual_alias_maps.cf: query failed: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_unicode_ci,COERCIBLE) for operation '='

Get the information(status) about all tables in the specific database using command like:

SHOW TABLE STATUS FROM your_db_name;
example output

Then change your database collation to UTF8 is my recommendation using command like that:
ALTER DATABASE your_db_name CHARACTER SET utf8 COLLATE utf8_general_ci;

Then to change all your tables listed from status command execute multiple times:
ALTER TABLE your_tablename_which_is_not_utf8 CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

Tags

Jordan Ostreff

2G/3G/4G/NSA-5G/SA-5G Mobile Packet Core, Telco Clouds, Ericsson CEE, OpenStack, NFV, Orchestration, SDN, Containerisation, Networking IPv4/IPv6, Industrial Computer Networks, Mathematics, Engineering

Great! You've successfully subscribed.
Great! Next, complete checkout for full access.
Welcome back! You've successfully signed in.
Success! Your account is fully activated, you now have access to all content.
Share with Me via Nextcloud