MySQL replication exception problem caused by inconsistent column type of library

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

slave_type_conversions   This parameter is in mysql5.5.3 introduced to enable row bin-log when the master-slave column mysql5.5.3 after support, the main library is int from library is bigint this type of replication,

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

If the field type range from the library is larger than the main library type, set Slave_type_conversions=all_non_lossy post-replication is not a problem.

If the library type is smaller than the main library type, such as from int copied to tinyint , although you can set the Slave_type_conversions=all_lossy,all_non_lossy let the master and slave do not problem, but actually can cause the risk of data loss.

settings for several values:

All_lossy : Allow data truncation

All_non_lossy : data truncation is not allowed, if from the library type is larger than the main library type, it can be replicated, reversed, and not, replication error from the library, replication terminated.

All_lossy,all_non_lossy: all allowed conversions are performed, regardless of data loss.

null value (not set): requires the master/slave library data type must be strictly consistent, otherwise all error.

Mode	Effect
All_lossy	In this mode, type conversions that would mean loss of information is permitted. This does isn't imply that non-lossy conversions is permitted, merely that's only cases requiring either lossy conversi ONS or no conversion at all is permitted; For example, enabling onlyThis mode permits anINTcolumn to Bes 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 a error on the slave.
All_non_lossy	This mode permits conversions that does not require truncation or other special handling of the source value; It permits conversions where the target type has a wider range than the source type. "Make sure that the column type from the library is broader and does not result in replication errors" Setting This mode have no bearing on whether lossy conversions be permitted; this was controlled with theAll_lossymode. If onlyAll_non_lossyis set, and notAll_lossy, then attempting a conversion so would result in the loss of data (such asINT toTINYINT, orCHAR (+) toVARCHAR (+)) causes the slave to stop with an error.
all_lossy,all_non_lossy	when This mode was set, all supported type conversions be permitted, whether  or not they be 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 are permitted; This means that all columns in the source and target T Ables must be of the same types. This mode is the default.

production Environment one case:

from the library there is a Oracle system to check the data, the original from the library cp_shop_activity represents UTF8MB4 Character Set, Oracle not supported. Therefore , the character type of the mode field modified manually from the library is varchar (UTF8), and the Modified field type is as follows:

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M00/8F/8A/wKioL1jkjPew8-SVAAAu7-trnSw434.png "title=" 11. PNG "alt=" Wkiol1jkjpew8-svaaau7-trnsw434.png "/>

but not a few days later, found that the master-slave replication error, the wrong code 1677 . The error log logged from the library is as follows:

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M00/8F/8A/wKioL1jkjTnwQkPJAADPzG4P9u0623.png "title=" 22. PNG "alt=" Wkiol1jkjtnwqkpjaadpzg4p9u0623.png "/>

We look at the main library's mode field is varchar, from the library's mode field is varchar ($).

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M00/8F/8A/wKioL1jkjcfhgrwvAAAszsdD6hM836.png "title=" 44. PNG "alt=" Wkiol1jkjcfhgrwvaaaszsdd6hm836.png "/>

In the From Library settings:

Stop slave;

set global slave_type_conversions=all_non_lossy;

# default slave_type_conversions is empty, which means that the field type of the library and the main library is forced to match, or the copy is stopped. setting to All_non_lossy is a bit more general and ensures that no type conversions are caused.

Start slave;

Show Slave Status\g

This solves the problem.

This article is from the "do-it-yourself MySQL chicken" blog, make sure to keep this source http://lee90.blog.51cto.com/10414478/1913061

MySQL replication exception problem caused by inconsistent column type of library