This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
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


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "MySQL: Collation Nightmare" Watch "MySQL: Collation Nightmare" New topic
Author

MySQL: Collation Nightmare

Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3700
    
    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
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3700
    
    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: 37874
    
  22
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
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3700
    
    5

I'm fine with the latin1... I want to know how to make format() return a latin1 concatable format.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: MySQL: Collation Nightmare
 
Similar Threads
trouble with string
collation conflict
EJB - Everything works except persist()
Which OSS database servers support Unicode ?
Java won't send SQL query