I'm not a fan of database character encoding and collations. They give me a headache, today being no different. I've extracted out the details of the problem so that it is reproducible. Personally, I feel like its a MySQL bug (I've been reading MySQL documentation all evening on the subject to no avail) so here's a simplified version:
First, create a table with a latin1 collation:
Then, execute a query (don't even need data) in Query Analyzer:
On my system I get the message "Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'concat'".
Now, I could avoid the error using "convert(X as latin1)" around the format, but that leads to ugly SQL code. Any other suggestions? Is there a setting in the connection or database to just make this go away?
Some more info... running either of the following lines prior to executing the query resolves the problem:
The problem is, I don't want to run one these every time I have to execute a query... I tried setting the "default-character-set" in the my.ini/my.cnf file with no luck. Lastly, I tried adding "charset=latin1;" in my connection string but that did not work either.
That means somebody has set the character set in one of the databases or tables you are using, leaving the others to find their default value. Since MySQL was developed in Sweden, it default to Swedish.