How to resolve the Zen Cart Illegal mix of collations error

We had deployed a Zen Cart website and we came up with the following error in the Zen Cart admin panel when clicking on the Tools -> Send Email link.

Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (latin1_general_ci,IMPLICIT) for operation 'locate'

After checking the table collation in PHPmyAdmin I found that the subscribers table (created when the Newsletter Subscribe module was installed) collation was different to the configuration table.

Most people on the internet advised that the collation for the subscribers table and email_address field should be changed to that of the remaining tables in the database. I tried to change the collation of the subscribers table and the email_address field to that of the configuration table but met with no success.

I then changed it to ‘utf8_unicode_ci‘ and the page displayed without any problem or error.

The exact SQL I ran in mySQL was

ALTER TABLE subscribers CHANGE email_address email_address varchar( 96 ) NOT NULL default '' UNIQUE COLLATE 'utf8_unicode_ci';
ALTER TABLE customers COLLATE 'utf8_unicode_ci';
ALTER TABLE subscribers COLLATE 'utf8_unicode_ci';
ALTER TABLE customers CHANGE customers_email_address customers_email_address varchar( 96 ) NOT NULL default '' UNIQUE COLLATE 'utf8_unicode_ci';

The first statement in the SQL above is thanks to DrByte.

Hope the above helped someone.

Tagged with: , ,

Leave a Reply

Your email address will not be published. Required fields are marked *

*