MySQL从库列类型不一致导致复制异常问题解决

MySQL从库列类型不一致导致复制异常问题

slave_type_conversions  这个参数在mysql5.5.3 引入,目的是启用row 格式的bin-log 的时候,如果主从的column 的数据类型不一致,会导致复制失败,mysql5.5.3 之后支持,主库是int 从库是bigint 这种类型的复制,

这个参数的意义就是控制些类型转换容错性。

如果从库的字段类型范围比主库类型大,那么设置slave_type_conversions=ALL_NON_LOSSY后复制没有问题的。

如果从库类型比主库类型小,比如从int 复制到tinyint ,虽然可以通过设置slave_type_conversions=ALL_LOSSY,ALL_NON_LOSSY让主从不出问题,但是实际上会造成数据丢失的风险。

几种值的设置:

    ALL_LOSSY允许数据截断

    ALL_NON_LOSSY不允许数据截断,如果从库类型大于主库类型,是可以复制的,反过了,就不行了,从库报复制错误,复制终止。

    ALL_LOSSY,ALL_NON_LOSSY: 所有允许的转换都会执行,而不管是不是数据丢失。

    空值(不设置)要求主从库的数据类型必须严格一致,否则都报错。

Mode

 

Effect

 

ALL_LOSSY

 

In this mode, type conversions that would mean loss of information are  permitted.

This does not  imply that non-lossy conversions are permitted, merely that only cases  requiring either lossy conversions or no conversion at all are permitted; for  example, enabling only this mode permits an INT column to be converted to TINYINT (a lossy conversion), but not a TINYINT column to an INT column (non-lossy). Attempting  the latter conversion in this case would cause replication to stop with an  error on the slave.

 

ALL_NON_LOSSY

 

This mode permits conversions that do not require truncation or other  special handling of the source value; that is, it permits conversions where  the target type has a wider range than the source type. 【确保从库的列类型更宽泛些也不会导致复制报错】

Setting this  mode has no bearing on whether lossy conversions are permitted; this is  controlled with the ALL_LOSSY mode. If  only ALL_NON_LOSSY is set,  but not ALL_LOSSY, then  attempting a conversion that would result in the loss of data (such as INT to TINYINT, or CHAR(25) to VARCHAR(20)) causes the slave to stop with an  error.

 

ALL_LOSSY,ALL_NON_LOSSY

 

When this mode is set, all supported type conversions are permitted, whether  or not they are lossy conversions.

 

ALL_SIGNED

 

Treat promoted integer types as signed values (the default behavior).

 

ALL_UNSIGNED

 

Treat promoted integer types as unsigned values.

 

ALL_SIGNED,ALL_UNSIGNED

 

Treat promoted integer types as signed if possible, otherwise as  unsigned.

 

[empty]

 

When slave_type_conversions is not  set, no attribute promotion or demotion is permitted; this means that all  columns in the source and target tables must be of the same types.

This mode is the default.

 

生产环境一个案例:

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/b6f690e36f54a1ce0792607626f3a3bb.html