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)