Description:
In row based replication, when replicating from a table with a field
with character set set to UTF8mb3 to the same table with the same
field set to character set UTF8mb4 I get a confusing error message:
"Column 0 of table 'test.t1' cannot be converted from type
'varchar(3)' to type 'varchar(1)'"
Even though RBR has a conversion framework in place for replicating
between different, but compatible types, it is stated in the
documentation that replication between columns with different character
sets is not supported (*). However the error message emitted seems
a bit inaccurate.
The binary log does not contain information about the character set of
the data in the fields it is replicating, thence it seems that:
(a) the source field length is the plain length in bytes of the data
transported, without considering the character encoding.
(b) the destination field length is the actual length of the field in
the destination table, considering the character encoding.
Thence there is inconsistency between (a) and (b).
(*) https://dev.mysql.com/doc/refman/5.7/en/replication-features-differing-tables.html
How to repeat:
1. Create a .test file and add the following test case to it:
--source include/master-slave.inc
--source include/have_binlog_format_row.inc
SET SQL_LOG_BIN=0;
CREATE TABLE t1 (c1 VARCHAR(1) CHARACTER SET 'utf8mb3');
SET SQL_LOG_BIN=1;
--source include/rpl_connection_slave.inc
SET SQL_LOG_BIN=0;
CREATE TABLE t1 (c1 VARCHAR(1) CHARACTER SET 'utf8mb4');
SET SQL_LOG_BIN=1;
--source include/rpl_connection_master.inc
INSERT INTO t1 VALUES ('a');
DROP TABLE t1;
--source include/sync_slave_sql_with_master.inc
--source include/rpl_end.inc
2. Run the test case:
perl mysql-test-run.pl --mem rpl_x.test
3. Watch the test fail and look at the error message:
[...]
Last_Error Column 0 of table 'test.t1' cannot be converted from type 'varchar(3)' to type 'varchar(1)'
[...]
Suggested fix:
.
Description: In row based replication, when replicating from a table with a field with character set set to UTF8mb3 to the same table with the same field set to character set UTF8mb4 I get a confusing error message: "Column 0 of table 'test.t1' cannot be converted from type 'varchar(3)' to type 'varchar(1)'" Even though RBR has a conversion framework in place for replicating between different, but compatible types, it is stated in the documentation that replication between columns with different character sets is not supported (*). However the error message emitted seems a bit inaccurate. The binary log does not contain information about the character set of the data in the fields it is replicating, thence it seems that: (a) the source field length is the plain length in bytes of the data transported, without considering the character encoding. (b) the destination field length is the actual length of the field in the destination table, considering the character encoding. Thence there is inconsistency between (a) and (b). (*) https://dev.mysql.com/doc/refman/5.7/en/replication-features-differing-tables.html How to repeat: 1. Create a .test file and add the following test case to it: --source include/master-slave.inc --source include/have_binlog_format_row.inc SET SQL_LOG_BIN=0; CREATE TABLE t1 (c1 VARCHAR(1) CHARACTER SET 'utf8mb3'); SET SQL_LOG_BIN=1; --source include/rpl_connection_slave.inc SET SQL_LOG_BIN=0; CREATE TABLE t1 (c1 VARCHAR(1) CHARACTER SET 'utf8mb4'); SET SQL_LOG_BIN=1; --source include/rpl_connection_master.inc INSERT INTO t1 VALUES ('a'); DROP TABLE t1; --source include/sync_slave_sql_with_master.inc --source include/rpl_end.inc 2. Run the test case: perl mysql-test-run.pl --mem rpl_x.test 3. Watch the test fail and look at the error message: [...] Last_Error Column 0 of table 'test.t1' cannot be converted from type 'varchar(3)' to type 'varchar(1)' [...] Suggested fix: .