Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

MySQL: Collation Nightmare

 
Scott Selikoff
author
Saloon Keeper
Posts: 3896
16
Eclipse IDE Flex Google Web Toolkit
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Scott Selikoff
author
Saloon Keeper
Posts: 3896
16
Eclipse IDE Flex Google Web Toolkit
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Campbell Ritchie
Sheriff
Pie
Posts: 47270
52
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Scott Selikoff
author
Saloon Keeper
Posts: 3896
16
Eclipse IDE Flex Google Web Toolkit
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm fine with the latin1... I want to know how to make format() return a latin1 concatable format.
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic