Illegal Collations and Frustrated Potential Customers

[eliseintaipei]

I get this warm and fuzzy feeling when a non-customer writes to me: "I tried to register to your online store to buy something and got this message:
1267 Illegal mix of collations (ascii_bin,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation 'like'
in:
[SELECT distinct zone_id, zone_name, zone_code FROM zones WHERE zone_country_id = 38 AND (upper(zone_name) like 'QUéBEC%' OR upper(zone_code) like '%QUéBEC%') OR zone_id = 0 ORDER BY zone_code ASC, zone_name]

End of quote.
OK, I get this when I tried to register using the French characters set. My SQL database uses the UTF-8 collation. No problem to register using English characters...
How could I resolve this problem. I got the current version of Zencart installed in early May. (Just for fun, I installed "secretly" a test online store last week and I don't get this problem, I can register using French like a breeze.) Something patched up in the most current version???

Thanks!

[Kim]

What version are you using that gives the error?

[eliseintaipei]

I"m using the current version, installed in late April : it gives me the collation errors. 1.3.7 and it says in the admin that I have the current version too.

The one I just intalled last week works fine... Should I just reinstall the whole thing? I don't want to loose my numerous modifications. Or is there a bunch of files that would just do the trick?

thanks a million

[Chuckl]

It's nothing to do with Zen Cart per se, it's a MySQL collations setting. Looks like the database data has an ascii binary collation, and the 'Quebec' is Latin1. (Or vice versa...)

You need to fix the collations so they are compatible or at least coercible to be compatible. Normally MySQL defaults to latin1_swedish_ci, so perhaps its something funny on the server or connection collation settings.