Bug #59424 replicate from smaller length on master to bigger length on slave doesn't work
Submitted: 11 Jan 2011 17:29 Modified: 11 Jan 2011 17:34
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server: Row Based Replication ( RBR ) Severity:S2 (Serious)
Version:5.5.8 OS:Any
Assigned to: CPU Architecture:Any

[11 Jan 2011 17:29] Shane Bester
According to the docs:
"All other things being equal, it is always possible to replicate from a column of a given data type to another column of the same type and same size or width, where applicable, or larger. For example, you can replicate from a CHAR(10) column to another CHAR(10), or from a CHAR(10) column to a CHAR(25) column without any problems. "

But, this is not possible.  Master with VARCHAR(1) and slave with VARCHAR(2) breaks slave like this:

Slave SQL: Column 0 of table 'test.t1' cannot be converted from type 'varchar(1)' to type 'varchar(2)', Error_code: 1677

How to repeat:
Setup a clean running RBR 5.5 master/slave replication.

#on master:
drop table if exists t1;
create table t1(a varchar(1) charset latin1)engine=myisam;

#on slave, replace the table with larger width:
drop table if exists t1;
create table t1(a varchar(2)charset latin1)engine=myisam;

#on master:
insert into t1 values ('a');

#on slave, will be broken:
show slave status\G
[11 Jan 2011 17:34] Shane Bester
Maybe NOT a bug.

set global slave_type_conversions=ALL_NON_LOSSY;

This works...