utf8 on master and utf8mb4 on slave leads to "cannot be converted from type" error

[Sveta Smirnova]

Description:
Similar to MySQL bug #71111 (https://bugs.mysql.com/bug.php?id=71111), but test case is simpler, so reported as new bug

If a table has utf8 character set on master and utf8mb4 on slave replication fails with error " Column 1 of table 'test.t1' cannot be converted from type 'varchar(765)' to type 'varchar(255)'"

Workaround:

--slave-type-conversions=ALL_NON_LOSSY

or use statement-based replication

How to repeat:
$cat rpl_bug-master.opt
--innodb-file-per-table=1 --binlog-format=row

$cat rpl_bug-slave.opt
--innodb-file-per-table=1 --binlog-format=row

$cat rpl_bug.test
--source include/master-slave.inc

CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT primary key,
`bug` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

insert into t1 values(1, 'Света тестирует баг');

--sync_slave_with_master

ALTER TABLE t1 CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE t1 MODIFY COLUMN `bug` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL;
show create table t1;

connection master;

insert into t1 values(2, 'Света тестирует баг');

--sync_slave_with_master

[Michael Wang (xw73)]

Test case can be even simple as you do not use the foreign characters to reproduce the problem. You can just use:

insert into t1 values (1, 'abc')

and it will be the same error. It is not what you inserted that causes the error, but that the row has been widened on the same. The unsupported chars will be converted as "?" anyway, for example:

insert into t1 values (2, _utf8mb4 x'F09D8C86')

will be converted as "2,?" in the master table and will be replicated as such on slave.

[Sveta Smirnova (svetasmirnova)]

Please also note what error message , printed says "Column 1 of table 'test.t1' cannot be converted from type 'varchar(765)' to type 'varchar(255)'" while affected column is "column 2", according to mysqlbinlog --verbose output. Not sure if this is result of this bug or should be reported separately.

[Sveta Smirnova (svetasmirnova)]

Note also anomaly in the error message regarding size of the column on master.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1646