Shulou(Shulou.com)06/01 Report--

Official document: https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

The parameter slave_type_conversions is introduced in mysql5.5.3 to enable bin-log in row format. If the data type of the master-slave column is inconsistent, it will lead to replication failure. It is supported after mysql5.5.3. The master library is int and the slave database is bigint.

The meaning of this parameter is to control some type conversion fault tolerance.

If the field type range of the slave library is larger than that of the master library type, then there is no problem with replication after setting slave_type_conversions=ALL_NON_LOSSY.

If the slave library type is smaller than the master library type, such as copying from int to tinyint, you can set slave_type_conversions=ALL_LOSSY,ALL_NON_LOSSY so that the master never goes wrong, but there is actually a risk of data loss.

Settings for several values:

ALL_LOSSY: allow data truncation

ALL_NON_LOSSY: data truncation is not allowed. If the slave database type is greater than the master database type, it can be copied, but vice versa. Copy error is reported from the database, and the replication is terminated.

ALL_LOSSY,ALL_NON_LOSSY: all allowed transformations are performed, regardless of data loss.

Null value (not set): requires that the data types of the master and slave libraries must be strictly consistent, otherwise all errors are reported.

Mode

Effect

ALL_LOSSY

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

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

ALL_NON_LOSSY

This mode permits conversions that do not require truncation or other special handling of the source value; that is, it permits conversions where the target type has a wider range than the source type. [make sure that the column types of the slave library are broader and will not cause replication errors]

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

ALL_LOSSY,ALL_NON_LOSSY

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

ALL_SIGNED

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

ALL_UNSIGNED

Treat promoted integer types as unsigned values.

ALL_SIGNED,ALL_UNSIGNED

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

[empty]

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

This mode is the default.

A case study of production environment:

There is an oracle system to check the data from the library. The original cp_shop_activity from the library represents the utf8mb4 character set, which is not supported by oracle. Therefore, the character type of the mode field is manually modified to varchar (200) utf8 on this slave library, and the modified field type is shown below:

But a few days later, it was found that the master-slave copy reported an error, error code 1677. The error log recorded from the library is as follows:

Let's take a look at the mode field of the master library is varchar (50), and the mode field of the slave library is varchar.

After setting up the slave library:

Stop slave

Set global slave_type_conversions=ALL_NON_LOSSY

# the default slave_type_conversions is empty, which means that the field types of the slave library and the master library are forced to be the same, otherwise the replication will be stopped. Setting it to ALL_NON_LOSSY is slightly broader while ensuring that it does not cause type conversion problems.

Start slave

Show slave status\ G

This will solve the problem.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.