aspose file tools*
The moose likes JDBC and the fly likes MySQL: Collation Nightmare Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "MySQL: Collation Nightmare" Watch "MySQL: Collation Nightmare" New topic
Author

MySQL: Collation Nightmare

Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3716
    
    5

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?


My Blog: Down Home Country Coding with Scott Selikoff
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3716
    
    5

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: 40061
    
  28
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: 3716
    
    5

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
 
subject: MySQL: Collation Nightmare