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/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;
here is an example output of the command executed into postfix database

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;

Leave a Reply

Този сайт използва Akismet за намаляване на спама. Научете как се обработват данните ви за коментари.