possible bug in collation?

[Justin Wiley]

Hi,

I'm having strange issues with collations. I'm using a stock XAMPP installation with mysql Ver 14.12 Distrib 5.0.24a, for Win32 (ia32). I have two tables:

+------------+---------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
| Table | Create Table

+------------+---------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
| employee_2 | CREATE TABLE `employee_2` (
`EmployeeID` int(10) NOT NULL default '0',
`EmployeeLastName` varchar(50) character set latin1 default NULL,
`EmployeeFirstName` varchar(50) character set latin1 default NULL,
`EmployeeNickName` varchar(50) character set latin1 default NULL,
`EmployeeCurrent` tinyint(1) NOT NULL,
PRIMARY KEY (`EmployeeID`),
KEY `EmployeeID` (`EmployeeID`)
) ENGINE=MyISAM AUTO_INCREMENT=59 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci |
+------------+---------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
1 row in set (0.02 sec)

mysql> show create table employee;
+----------+-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
------------------+
| Table | Create Table

|
+----------+-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
------------------+
| employee | CREATE TABLE `employee` (
`id` int(11) default NULL,
`email` varchar(100) collate latin1_general_ci default NULL,
`password` varchar(255) collate latin1_general_ci default NULL,
`lastname` varchar(50) collate latin1_general_ci default NULL,
`firstname` varchar(50) collate latin1_general_ci default NULL,
`nickname` varchar(50) collate latin1_general_ci default NULL,
`iscurrent` int(11) default NULL,
`isadmin` int(11) default NULL,
`created_at` datetime default NULL,
`workload` int(1) default NULL
) ENGINE=MyISAM AUTO_INCREMENT=105 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci |
+----------+-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
------------------+
1 row in set (0.00 sec)

I try to run the following query but I get an error:

mysql> update employee,employee_2 set employee.id=employee_2.employeeid where employee.nickname=employee_2.EmployeeNickName;

ERROR 1267 (HY000): Illegal mix of collations (latin1_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='

mysql> set collation_connection = 'latin1_general_ci';

mysql> show variables like '%coll%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | latin1_general_ci |
| collation_database | latin1_general_ci |
| collation_server | latin1_general_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

Run the query again, same error:

mysql> update employee,employee_2 set employee.id=employee_2.employeeid where employee.nickname=employee_2.EmployeeNickName;

ERROR 1267 (HY000): Illegal mix of collations (latin1_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='

So no matter what I do, it still believes that I'm working in Swedish? Any idea as to the problem?

Thanks,
Justin