Bug #37043 Illegal mix of collations different behavior on Linux and Windows
Submitted: 28 May 2008 16:57 Modified: 30 Jun 2008 18:52
Reporter: Branislav Mogin Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Charsets Severity:S3 (Non-critical)
Version:5.0 OS:Linux
Assigned to: CPU Architecture:Any
Tags: characterset, collation

[28 May 2008 16:57] Branislav Mogin
Description:
Running script on existing database gives an error on Red Hat 5 while working fine on 
Windows XP. Variable values for both servers are bellow.

This is the error:
ERROR 1267 (HY000): Illegal mix of collations
> > (latin1_general_cs,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for 
> > operation '='

How to repeat:
This is the script:
-- -----------------------------------------------------
DROP PROCEDURE IF EXISTS Update108;
DELIMITER $$
CREATE PROCEDURE Update108()
BEGIN
SET @currentDBVersion = readDBINFO('DB_VERSION');
SELECT CONCAT('Update108: Current DB version is
',@currentDBVersion) 'DB Update log';
if @currentDBVersion = '1.0.7.100' THEN
CREATE  TABLE IF NOT EXISTS `TEST1` (
`TESTCOLUMN1` BIGINT NOT NULL ,
`TESTCOLUMN2` VARCHAR(255) DEFAULT NULL ,
`TESTCOLUMN3` BIGINT NULL DEFAULT NULL ,
PRIMARY KEY (`TESTCOLUMN1`) );
SELECT CONCAT('Update108: DB Update done on version 
1.0.8.100',' ') 'DB Update log';
SET @retval = updateDBINFO('DB_VERSION','1.0.8.100');
SELECT CONCAT('Update108: New DB version is set to
','1.0.8.100') 'DB Update log';
END IF;
END$$
DELIMITER ;
CALL Update108();
DROP PROCEDURE IF EXISTS Update108

It is run by:
SOURCE myscript.sql

Existing database on which script runs is created using:
CREATE SCHEMA IF NOT EXISTS `mydb` CHARACTER SET latin1 COLLATE 
latin1_general_cs;

I get very same values from both Windows and RH5:
> show variables like 'character%';
> +--------------------------+----------------------------+
> | Variable_name            | Value                      |
> +--------------------------+----------------------------+
> | character_set_client     | latin1                     |
> | character_set_connection | latin1                     |
> | character_set_database   | latin1                     |
> | character_set_filesystem | binary                     |
> | character_set_results    | latin1                     |
> | character_set_server     | latin1                     |
> | character_set_system     | utf8                       |
> | character_sets_dir       | /usr/share/mysql/charsets/ |
> +--------------------------+----------------------------+
> 8 rows in set (0.00 sec)
> 
> mysql> show variables like 'collation%';
> +----------------------+-------------------+
> | Variable_name        | Value             |
> +----------------------+-------------------+
> | collation_connection | latin1_swedish_ci |
> | collation_database   | latin1_swedish_ci |
> | collation_server     | latin1_swedish_ci |
> +----------------------+-------------------+
> 3 rows in set (0.00 sec)
> 
> 
> 
> mysql> show variables like 'character%'
>     -> ;
> +--------------------------+------------------------------------------
> +--------------------------+----------
> -----+
> | Variable_name            | Value
>      |
> +--------------------------+------------------------------------------
> +--------------------------+----------
> -----+
> | character_set_client     | latin1
>      |
> | character_set_connection | latin1
>      |
> | character_set_database   | latin1
>      |
> | character_set_filesystem | binary
>      |
> | character_set_results    | latin1
>      |
> | character_set_server     | latin1
>      |
> | character_set_system     | utf8
>      |
> | character_sets_dir       | D:\Program Files\MySQL\MySQL Server 5.0\share\chars
> ets\ |
> +--------------------------+------------------------------------------
> +--------------------------+----------
> -----+
> 8 rows in set (0.01 sec)
> 
> mysql> show variables like 'collation%'
>     -> ;
> +----------------------+-------------------+
> | Variable_name        | Value             |
> +----------------------+-------------------+
> | collation_connection | latin1_swedish_ci |
> | collation_database   | latin1_swedish_ci |
> | collation_server     | latin1_swedish_ci |
> +----------------------+-------------------+
> 3 rows in set (0.00 sec)
[31 May 2008 18:52] Sveta Smirnova
Thank you for the report.

Please indicate accurate version of MySQL on both Windows and Linux machines. Also please provide definition of functions readDBINFO and updateDBINFO.
[1 Jul 2008 23:00] Bugs System
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".