MySQL Bugs: #20635: Default collation is wrong

Bug #20635	Default collation is wrong
Submitted:	22 Jun 2006 20:41	Modified:	13 Oct 2006 11:44
Reporter:	TRUMF	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Charsets	Severity:	S1 (Critical)
Version:	5.0.22	OS:	Windows (win 2k3)
Assigned to:		CPU Architecture:	Any

Description:
I have used mysql forever and have used latin1 as collation. Now with v5.0 when installing I choose latin1_danish_ci as default collation for the intire DB. All char and text fields are listed in querybrowser as latin1_danish_ci.
When joining old tables (from before 5.0) with new tables I get an error comparing different collations.
When looking at the fields in querybrowser both fields are latin1_danish_ci, but when changing to latin1_swedish_ci there is a message that there is nothing to change, but when changing back to latin1_danish_ci it executes and the error is gone.
In short, when installing mysql and choosing latin1_danish_ci it makes it latin1_swedish_ci but appers in querybrowser as latin1_danish_ci.

.NET connector does not report back that there is an error, it just returns an empty record which is even worse because I didn't realize there was an error before it was pointed out to me that a search didn't return results that we knew should return something

How to repeat:
create a db in 4.0 latin1 with a varchar field containing danish characters like &aring; &aelig; &oslash;
dump the db
create 5.0 db with latin1_danish_ci collation
import db
create new table
join between tables
try changing old fields in querybrowser

Thank you for a problem report. Please:

1. Try to repeat with a newer version 5.0.22.
2. Use mysql command line client (mysql) to repeat.
3. Send the results of SHOW TABLE STATUS for the table you loaded from dump (and for the one created anew).
4. Send the results of SHOW VARIABLES LIKE 'char%'

I looked (in QB) at Column details at lande.landekode and producent.countrycode, and both was listed as latin1_danish_ci but when trying to change producent.countrycode to latin1_swedish_ci the message was that there was nothing to change!!! I then tried to change to latin1_german_ci and it executed fine and then I changed it to latin1_danish_ci and then the error dissapered.

My guess is that QB shows database collation instead of column collation... beside of the mixup of collations.

I just don't understand why database default seems to be sweedish when I have a danish system.
Perhaps one of your developers (as many other "foreigners") think that Denmark is the capital of Sweeden :-)

Sorry for a delay with this bug report. Let's forget about QB for a the purpose of this report. Please, send the results of:

SHOW CREATE TABLE producent\G
SHOW CREATE TABLE lande\G
SHOW VARIABLES LIKE 'coll%';

We have to figure out why different collations are used.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

producent  CREATE TABLE `producent` (
  `prodID` int(11) NOT NULL auto_increment,
  `prodname` varchar(100) character set latin1 NOT NULL default '',
  `prodnameudenaccent` varchar(150) character set latin1 NOT NULL default '',
  `address` varchar(150) character set latin1 NOT NULL default '',
  `address2` varchar(150) character set latin1 NOT NULL default '',
  `zipcode` varchar(10) character set latin1 NOT NULL default '',
  `town` varchar(150) character set latin1 NOT NULL default '',
  `countrycode` char(2) collate latin1_danish_ci NOT NULL default '',
  `phone` varchar(25) character set latin1 NOT NULL default '',
  `fax` varchar(25) character set latin1 NOT NULL default '',
  `website` varchar(150) character set latin1 NOT NULL default '',
  `linkname` varchar(150) character set latin1 NOT NULL default '',
  `email` varchar(150) character set latin1 NOT NULL default '',
  `discription` text character set latin1 NOT NULL,
  `dateadded` datetime NOT NULL default '0000-00-00 00:00:00',
  `addedby` int(11) NOT NULL default '0',
  `dateedit` datetime NOT NULL default '0000-00-00 00:00:00',
  `editby` int(11) NOT NULL default '0',
  `txtdateedit` datetime NOT NULL default '0000-00-00 00:00:00',
  `txteditby` int(11) NOT NULL default '0',
  PRIMARY KEY  (`prodID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_danish_ci 

 lande     CREATE TABLE `lande` (
  `landeID` int(10) unsigned NOT NULL auto_increment,
  `landekode` char(2) collate latin1_danish_ci default NULL,
  `navn` varchar(255) collate latin1_danish_ci default NULL,
  `navneng` varchar(255) collate latin1_danish_ci default NULL,
  `beskrivelse` text collate latin1_danish_ci,
  `oprettet` datetime default NULL,
  `oprettetaf` int(11) default NULL,
  `oprettetafnavn` varchar(255) character set latin1 default NULL,
  `rettet` datetime default NULL,
  `rettetaf` int(11) default NULL,
  `aktiv` char(3) character set latin1 default NULL,
  PRIMARY KEY  (`landeID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_danish_ci 

 collation_connection  latin1_swedish_ci 
 collation_database    latin1_swedish_ci 
 collation_server      latin1_swedish_ci

Please, check with 5.0.24a and, in case of same results, send/upload/attach as (private) file dump of your tables. I can not repeat the behaviour described on 5.0.26-BK with random data in both tables.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".