Illegal mix of collation during replication
Posted by:
Tuan Ta
Date: October 26, 2011 10:11PM
Occasionally, I get this error message in the mysql error log: "Error 'Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation '='' on query." during replication. The master is a "5.0.91-log MySQL Community Server", and the slave is "5.5.14-log MySQL Community Server". "utf8" is not configured anywhere in the my.cnf file. If I execute the statement manually on the slave, it still fails with the same error message. If I issue "SET character_set_connection=latin1;", I can continue replication by executing the statement manually, skip the statement in the replication, and start slave again.
I'm not sure if this is a bug, or I have something misconfigured. Any help is greatly appreciated.
Failed Query:
UPDATE A, B SET A.colA1 = B.B_PRIMARY_ID WHERE A.colA1='value' AND (INET_NTOA(A.IPADDR) = B.IPADDR) or A.IPADDR = B.IPADDR);
SHOW CREATE TABLE A:
CREATE TABLE A (
`colA1` int(10),
`IPADDR` varchar(128) NOT NULL DEFAULT '',
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SHOW FULL COLUMNS FROM A:
+------------------+---------------------+-------------------+------+-----+---------+----------------+---------------------------------+--------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges |Comment |
+-------------------+--------------------+-------------------+------+-----+---------+----------------+---------------------------------+--------+
| colA1 | int(10) unsigned | NULL | NO | PRI | 0 | | select,insert,update,references | |
| IPADDR | varchar(128) | latin1_swedish_ci | NO | | | | select,insert,update,references | |
+------------------+---------------------+-------------------+------+-----+---------+----------------+---------------------------------+--------+
SHOW CREATE TABLE B:
CREATE TABLE B (
`B_PRIMARY_ID` int(10),
`IPADDR` varchar(15) NOT NULL DEFAULT '',
PRIMARY KEY (`B_PRIMARY_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=XXX DEFAULT CHARSET=latin1;
SHOW FULL COLUMNS FROM B:
+------------------+---------------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+------------------+---------------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+
| B_PRIMARY_ID | int(10) unsigned | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | |
| IPADDR | varchar(15) | latin1_swedish_ci | NO | MUL | | | select,insert,update,references | |
+------------------+---------------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+
mysql> show global 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)