List:General Discussion« Previous MessageNext Message »
From:Marco Pöhler Date:July 19 2005 7:22am
Subject:Re: Illegal mix of collations
View as plain text  
> > Illegal mix of collations (latin1_bin,IMPLICIT) and
> > (utf8_general_ci,COERCIBLE) for operation '='' on query. Default
> 
> What default charset do your slave's tables have? The coercibility 
> value of system constants has changed in 4.1.11. Though it seems not
> related to your problem I recommend you to upgrade to 4.1.12.

The default charset of the slave table is latin1, the same on the
master.

Slave Table create statement:
=============================
CREATE TABLE `sum_day_key_requests` (
...
  `key` varchar(255) character set utf8 collate utf8_bin NOT NULL
default '',
...
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

which is identical with the Master create statement:

CREATE TABLE `sum_day_key_requests` (
  ...
  `key` varchar(255) character set utf8 collate utf8_bin NOT NULL
default '',
...
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Marco

----
Marco Poehler
http://www.kontaktlinsen-preisvergleich.de

Am Montag, den 18.07.2005, 21:34 +0300 schrieb Gleb Paharenko:
> Hello.
> 
> > Illegal mix of collations (latin1_bin,IMPLICIT) and
> > (utf8_general_ci,COERCIBLE) for operation '='' on query. Default
> 
> What default charset do your slave's tables have? The coercibility 
> value of system constants has changed in 4.1.11. Though it seems not
> related to your problem I recommend you to upgrade to 4.1.12.
> 
> 
> Marco P$hler <lists@stripped> wrote:
> > Hello List,
> > 
> > I've tried to set up a slave from an existing master using innobackup.
> > The copy of the database was successful, but when I started the
> > replication, the following error occured:
> > 
> > Illegal mix of collations (latin1_bin,IMPLICIT) and
> > (utf8_general_ci,COERCIBLE) for operation '='' on query. Default
> > database: 'report'. Query: 'UPDATE sum_day_key_requests SET sum = sum +
> > 1 WHERE day = '2005-7-16' AND type = 'redirect' AND client = '1234' AND
> > channel = '43' AND campaign = '...' AND grouping = 'Singleb$se' AND
> > `key` = 'Single Berlin' AND afftraf = 'NULL'
> > 
> > I read the docs and compared charset/collation configuration as you can
> > see below, but it seems to be the same on both servers.
> > 
> > any hints welcome !
> > 
> > thanks in advance
> > 
> > Marco
> > 
> > =============================================================================
> > Master: MySQL 4.1.10
> > --------------------
> > 
> > mysql> show variables like 'coll%';
> > +----------------------+-------------------+
> > | 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)
> > 
> > ps ax | grep mysql
> > 7349 ?        Sl     0:01 /usr/sbin/mysqld --basedir=/
> > --datadir=/var/lib/mysql --user=mysql
> > --pid-file=/var/lib/mysql/HAL2005.pid --skip-locking
> > --open-files-limit=8192 --port=3306 --socket=/var/lib/mysql/mysql.sock
> > 
> > (no --default-character-set=... or --default-collation=...)
> > 
> > /etc/my.cnf doesn't contain any charset or collation information
> > =============================================================================
> > Slave: MySQL 4.1.10
> > -------------------
> > 
> > mysql> show variables like 'coll%';
> > +----------------------+-------------------+
> > | 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)
> > 
> > ps ax | grep mysql
> > 23970 pts/0    Sl     0:01 /usr/sbin/mysqld --basedir=/
> > --datadir=/var/lib/mysql --user=mysql
> > --pid-file=/var/lib/mysql/HAL2006.pid --skip-locking
> > --open-files-limit=8192 --port=3306 --socket=/var/lib/mysql/mysql.sock
> > 
> > (no --default-character-set=... or --default-collation=...)
> > 
> > /etc/my.cnf doesn't contain any charset or collation information


Thread
Illegal mix of collationsMarco Pöhler18 Jul
  • Re: Illegal mix of collationsGleb Paharenko18 Jul
    • Re: Illegal mix of collationsMarco Pöhler19 Jul
      • Re: Illegal mix of collationsGleb Paharenko19 Jul