possible bug in collation?
Posted by: Justin Wiley
Date: December 07, 2006 02:20PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
possible bug in collation?
3507
December 07, 2006 02:20PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.