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

[Stefan Pantke]

MySQL in use: 4.1.10a

A PHP WebApp emits this MySQL error message:

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

Obviously, the two tables have different collations: latin1_swedish_ci and latin1_general_ci. The respective 'name' columns contain german diacritical characters, which appear to be correctly stored as seen through phpMyAdmin.

If I try to run a join between these tables which uses

k.name <> s.name,

I get the above error. Since I've never seen this problem before, I wonder how to fix it.

Q1: Do we have a best-practice to solve the situation?

Using phpMyAdmin, I changed the collation of the second table to latin1_swedish_ci, but the app emits exactly the same error.

Q2: Does the data stored in a table keeps a charset/encoding, even if I change the collation of the table?

Likely, my questions are not as exact as required to give an good answer. Sorry!

Kind regards,

Stefan

PS: Don't ask, why the default collation is 'latin1_swedish_ci' ;-)



Edited 3 time(s). Last edit at 03/29/2007 11:30AM by Stefan Pantke.