广东快乐十分-医院预防科是做什么的

首页

AD联系:507867812

广东快乐十分

时间:2019-11-12 23:58:28 作者:六合在线 浏览量:66138

广东快乐十分MySQL从库列类型不一致导致复制异常问题解决MySQL从库列类型不一致导致复制异常问题解决MySQL从库列类型不一致导致复制异常问题解决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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

linuxMySQL从库列类型不一致导致复制异常问题解决MySQL从库列类型不一致导致复制异常问题解决,见下图

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

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

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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

linux

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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

linuxMySQL从库列类型不一致导致复制异常问题解决,如下图

MySQL从库列类型不一致导致复制异常问题解决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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

linux

如下图

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

MySQL从库列类型不一致导致复制异常问题解决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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

linux

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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

linux

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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

linux

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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

linux

MySQL从库列类型不一致导致复制异常问题解决MySQL从库列类型不一致导致复制异常问题解决MySQL从库列类型不一致导致复制异常问题解决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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

linux

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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

linux

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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

linux

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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

linux

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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

linux

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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

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

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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

linuxMySQL从库列类型不一致导致复制异常问题解决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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

linux

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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

linuxMySQL从库列类型不一致导致复制异常问题解决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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

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

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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

linux

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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

linuxMySQL从库列类型不一致导致复制异常问题解决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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

linux

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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

linux

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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

linux

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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

linux

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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

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

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

1.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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

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

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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

linux

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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

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

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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

linux

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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

linux

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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

linux

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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

linux

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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

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

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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

linux

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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

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

2.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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

linux

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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

linux

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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

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

3.

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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

linux

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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

linux

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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

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

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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

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

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

MySQL从库列类型不一致导致复制异常问题解决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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

linux

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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

linuxMySQL从库列类型不一致导致复制异常问题解决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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

linuxMySQL从库列类型不一致导致复制异常问题解决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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

linux

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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

linux。广东快乐十分

展开全文
相关文章
BBIN捕鱼大师

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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

linux....

老虎机打法

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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

linux....

环博国际网址

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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

linux....

相关资讯
龙8国际pt老虎机

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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

linux....

太阳城申博

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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

linux....

威尼斯人高尔夫赌场

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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

linux....

威尼斯人高尔夫赌场

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, enablingonlythis mode permits anINTcolumn to be converted toTINYINT(a lossy conversion), but not aTINYINTcolumn to anINTcolumn (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 theALL_LOSSYmode. If onlyALL_NON_LOSSYis set, but notALL_LOSSY, then attempting a conversion that would result in the loss of data (such asINTtoTINYINT, orCHAR(25)toVARCHAR(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]

Whenslave_type_conversionsis 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.

生产环境一个案例:

从库是有个Oracle系统去查数据的,原先从库的cp_shop_activity表示utf8mb4字符集,oracle不支持。因此在这个从库上手动修改了mode字段的字符类型为varchar(200) utf8,修改后的字段类型如下图:

wKioL1jkjPew8-SVAAAu7-trnSw434.png

但是没几天后,发现主从复制报错了,错误码1677。从库上记录的错误日志如下:

wKioL1jkjTnwQkPJAADPzG4P9u0623.png

我们看下主库的mode字段是varchar(50),从库的mode字段是varchar(200)。

wKioL1jkjcfhgrwvAAAszsdD6hM836.png

在从库设置:

stop slave;

set global slave_type_conversions=ALL_NON_LOSSY;

# 默认slave_type_conversions为空,表示强制从库和主库的字段类型一致,不然就停止复制。设置为ALL_NON_LOSSY就稍微宽泛些同时确保不会造成类型转换的问题。

start slave;

show slave status\G

这样即可解决这个问题。

官方文档:https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

linux....

热门资讯