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)
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)