This week's book giveaway is in the Jobs Discussion forum.
We're giving away four copies of Customer Requirements for Developers and have Marcho Behler on-line!
See this thread for details.
The moose likes JDBC and Relational Databases and the fly likes MySQL: Collation Nightmare Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Customer Requirements for Developers this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "MySQL: Collation Nightmare" Watch "MySQL: Collation Nightmare" New topic
Author

MySQL: Collation Nightmare

Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3740
    
  10

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?


[OCA 8 Book] [Blog]
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3740
    
  10

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

Joined: Oct 13, 2005
Posts: 43399
    
  32
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

Joined: Oct 23, 2005
Posts: 3740
    
  10

I'm fine with the latin1... I want to know how to make format() return a latin1 concatable format.
 
jQuery in Action, 3rd edition
 
subject: MySQL: Collation Nightmare
 
jQuery in Action, 3rd edition