MySQL Bugs: #47673: rbr replication fails with mismatching collations or charsets

Bug #47673	rbr replication fails with mismatching collations or charsets
Submitted:	28 Sep 2009 10:54
Reporter:	Sven Sandberg	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:		OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Row-based replication may fail when the character sets or collations are different on master and slave.

If the character sets are different, the slave will simply have the wrong data for characters that have different encodings on master and slave.

If the collations are different for a column that is KEY, and two strings are considered equal by master and different by slave, then the slave may stop with a duplicate key error.

How to repeat:
--source include/master-slave.inc
--source include/have_log_bin.inc

--echo ==== Different character sets: wrong data on slave ====
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET latin1);
sync_slave_with_master;
DROP TABLE t1;
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8);

--connection master
INSERT INTO t1 VALUES ('åäö');
SELECT * FROM t1;
sync_slave_with_master;
SELECT * FROM t1;

--connection master
DROP TABLE t1;

--echo ==== Different collations: slave stops with error ====

CREATE TABLE t1 (a VARCHAR(10) COLLATE utf8_bin UNIQUE KEY);
--sync_slave_with_master
DROP TABLE t1;
CREATE TABLE t1 (a VARCHAR(10) COLLATE utf8_swedish_ci UNIQUE KEY);

--connection master
INSERT INTO t1 VALUES ('a'), ('A');
--sync_slave_with_master

Suggested fix:
Workaround:
Always use the same character set and collation on master and slave.

Fix:
Currently, the binlog does not contain information about the character set and collation for row events. This should be added to the Table_map_log_event. When the event is applied, check that both the character set and the collation are identical.

This was added as WL#4618 in 2008.