Strange Illegal mix of collations error

2006-06-16 Thread Dušan Pavlica 

Hello,
could someone help me to explain and resolve this error? Or maybe it is 
a bug.


DROP TABLE IF EXISTS `test`.`karty`;
CREATE TABLE  `test`.`karty` (
 `ICO` char(12) collate latin2_czech_cs NOT NULL default '',
 `CisloProvozu` char(6) collate latin2_czech_cs NOT NULL default '',
 `CisPrac` char(10) collate latin2_czech_cs NOT NULL default '',
 `TypKarty` smallint(6) NOT NULL default '0',
 `Vyhotoveni` smallint(6) NOT NULL default '0',
 `Jmeno` char(20) collate latin2_czech_cs NOT NULL default '',
 `CisloProgramu` smallint(6) NOT NULL default '0',
 `Embasing` char(7) collate latin2_czech_cs NOT NULL default '',
 `VnitrniCislo` char(15) character set latin2 default NULL,
 `Stanice` char(3) collate latin2_czech_cs NOT NULL default '',
 PRIMARY KEY  (`ICO`,`CisloProvozu`,`CisPrac`,`TypKarty`,`Vyhotoveni`),
 UNIQUE KEY `VnitrniCislo` (`VnitrniCislo`)
) ENGINE=InnoDB DEFAULT CHARSET=latin2 COLLATE=latin2_czech_cs;

DROP TABLE IF EXISTS `test`.`karty_imp_tmp`;
CREATE TABLE  `test`.`karty_imp_tmp` (
 `PracJmeno` char(30) collate latin2_czech_cs NOT NULL default '',
 `VnitrniCislo` char(15) collate latin2_czech_cs default NULL,
 `CisPrac` char(10) collate latin2_czech_cs NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin2 COLLATE=latin2_czech_cs;

INSERT INTO test.karty 
values('','01','01',10,1,'test',0,'R0','9001AB12D3E','');
INSERT INTO test.karty_imp_tmp 
values('test','9001AB12D3E','01');


SELECT k.*, kt.* FROM karty k JOIN karty_imp_tmp kt USING (vnitrnicislo);
returns error: Illegal mix of collations (latin2_general_ci,IMPLICIT) 
and (latin2_czech_cs,IMPLICIT) for operation '='


I don't know where latin2_general_ci collation comes from.

OS WinXP, MySQL 4.1.15-nt-log

Thanks in advance

Dusan Pavlica

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Strange Illegal mix of collations error

2006-06-16 Thread Barry 

Dušan Pavlica schrieb:

Hello,
could someone help me to explain and resolve this error? Or maybe it is 
a bug.


DROP TABLE IF EXISTS `test`.`karty`;
CREATE TABLE  `test`.`karty` (
 `ICO` char(12) collate latin2_czech_cs NOT NULL default '',
 `CisloProvozu` char(6) collate latin2_czech_cs NOT NULL default '',
 `CisPrac` char(10) collate latin2_czech_cs NOT NULL default '',
 `TypKarty` smallint(6) NOT NULL default '0',
 `Vyhotoveni` smallint(6) NOT NULL default '0',
 `Jmeno` char(20) collate latin2_czech_cs NOT NULL default '',
 `CisloProgramu` smallint(6) NOT NULL default '0',
 `Embasing` char(7) collate latin2_czech_cs NOT NULL default '',
 `VnitrniCislo` char(15) character set latin2 default NULL,
 `Stanice` char(3) collate latin2_czech_cs NOT NULL default '',
 PRIMARY KEY  (`ICO`,`CisloProvozu`,`CisPrac`,`TypKarty`,`Vyhotoveni`),
 UNIQUE KEY `VnitrniCislo` (`VnitrniCislo`)
) ENGINE=InnoDB DEFAULT CHARSET=latin2 COLLATE=latin2_czech_cs;

DROP TABLE IF EXISTS `test`.`karty_imp_tmp`;
CREATE TABLE  `test`.`karty_imp_tmp` (
 `PracJmeno` char(30) collate latin2_czech_cs NOT NULL default '',
 `VnitrniCislo` char(15) collate latin2_czech_cs default NULL,
 `CisPrac` char(10) collate latin2_czech_cs NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin2 COLLATE=latin2_czech_cs;

INSERT INTO test.karty 
values('','01','01',10,1,'test',0,'R0','9001AB12D3E',''); 

INSERT INTO test.karty_imp_tmp 
values('test','9001AB12D3E','01');


SELECT k.*, kt.* FROM karty k JOIN karty_imp_tmp kt USING (vnitrnicislo);
returns error: Illegal mix of collations (latin2_general_ci,IMPLICIT) 
and (latin2_czech_cs,IMPLICIT) for operation '='


I don't know where latin2_general_ci collation comes from.


Well one table or the column has this collation.
Check your structures.
= `VnitrniCislo` char(15) character set latin2 default NULL,
shouldn it be
= `VnitrniCislo` char(15) collate latin2_czech_cs default NULL,

Barry

--
Smileys rule (cX.x)C --o(^_^o)
Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Strange Illegal mix of collations error

2006-06-16 Thread Dušan Pavlica 

flyerheaven - Barry Krein napsal(a):

Dušan Pavlica schrieb:

Barry napsal(a):

Dušan Pavlica schrieb:

Hello,
could someone help me to explain and resolve this error? Or maybe 
it is a bug.


DROP TABLE IF EXISTS `test`.`karty`;
CREATE TABLE  `test`.`karty` (
 `ICO` char(12) collate latin2_czech_cs NOT NULL default '',
 `CisloProvozu` char(6) collate latin2_czech_cs NOT NULL default '',
 `CisPrac` char(10) collate latin2_czech_cs NOT NULL default '',
 `TypKarty` smallint(6) NOT NULL default '0',
 `Vyhotoveni` smallint(6) NOT NULL default '0',
 `Jmeno` char(20) collate latin2_czech_cs NOT NULL default '',
 `CisloProgramu` smallint(6) NOT NULL default '0',
 `Embasing` char(7) collate latin2_czech_cs NOT NULL default '',
 `VnitrniCislo` char(15) character set latin2 default NULL,
 `Stanice` char(3) collate latin2_czech_cs NOT NULL default '',
 PRIMARY KEY  
(`ICO`,`CisloProvozu`,`CisPrac`,`TypKarty`,`Vyhotoveni`),

 UNIQUE KEY `VnitrniCislo` (`VnitrniCislo`)
) ENGINE=InnoDB DEFAULT CHARSET=latin2 COLLATE=latin2_czech_cs;

DROP TABLE IF EXISTS `test`.`karty_imp_tmp`;
CREATE TABLE  `test`.`karty_imp_tmp` (
 `PracJmeno` char(30) collate latin2_czech_cs NOT NULL default '',
 `VnitrniCislo` char(15) collate latin2_czech_cs default NULL,
 `CisPrac` char(10) collate latin2_czech_cs NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin2 COLLATE=latin2_czech_cs;

INSERT INTO test.karty 
values('','01','01',10,1,'test',0,'R0','9001AB12D3E',''); 

INSERT INTO test.karty_imp_tmp 
values('test','9001AB12D3E','01');


SELECT k.*, kt.* FROM karty k JOIN karty_imp_tmp kt USING 
(vnitrnicislo);
returns error: Illegal mix of collations 
(latin2_general_ci,IMPLICIT) and (latin2_czech_cs,IMPLICIT) for 
operation '='


I don't know where latin2_general_ci collation comes from.


Well one table or the column has this collation.
Check your structures.
= `VnitrniCislo` char(15) character set latin2 default NULL,
shouldn it be
= `VnitrniCislo` char(15) collate latin2_czech_cs default NULL,

Barry

I have seen it too and  checked both structures in MySQL Table Editor 
and both columns had exactly same definitions. And both tables have 
same character set and collation


Dusan


Nah look at your structrue:
CisloProgramu` smallint(6) NOT NULL default '0',
  `Embasing` char(7) collate latin2_czech_cs NOT NULL default '',
  `VnitrniCislo` char(15) character set latin2 default NULL,
  `Stanice` char(3) collate latin2_czech_cs NOT NULL default '',
Here its just character set latin2

 CREATE TABLE  `test`.`karty_imp_tmp` (
  `PracJmeno` char(30) collate latin2_czech_cs NOT NULL default '',
  `VnitrniCislo` char(15) collate latin2_czech_cs default NULL,

And here is it collate latin2_czech_cs

That's causing the error.

Barry


You are right. If I create tables where both columns VnitrniCislo have 
exactly same definitions, it doesn't throw the error.
Bug must be in the MySQL Table Editor which shows in column details 
wrong collations


Thanks, Barry.

Dusan

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]