How-to fix Illegal mix of collations errors in mysql

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

warning: mysql:/usr/local/etc/postfix/ 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:

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;

Share with Me via Nextcloud